Prepare a cash budget for the month of JULY only from the Beginning Cash Balance to the Ending Cash Balance. Do not include minimum cash balance in the original cash budget calculations. After you complete the July cash budget, what would you recommend the minimum cash balance be set at? Show ALL CALCULATIONS to get credit. See the budgeted information by month that is at the end of your information data sheet and the following: JULY 1 balance in Cash =$30,000. Collections: 60% collected in the current month and 40% is collected in the next month. Labor payments: 70% of hourly wages paid in the current month and 30% is paid in the next month. Materials payments: 100% is paid in the next month. Overhead payments: 100% is paid in the current month. Do not include minimum cash balance in the initial cash budget. At the end show what you think the minimum cash balance should be based on this month of information. OPERATING ASSETS BUDGET Budgeted operating assets ACTUAL Actual operating assets BUDGETED INCOME STMT FOR 2020 BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET Total 2020 REVENUE 66,921 LABOR HOURS January-20 1,498 February-20 1,747 March-20 April-20 1,747 78,074 1,747 78,074 78,074 May-20 1,747 78,074 June-20 1,997 89,228 July-20 2,496 111,534 August-20 3,744 167,302 September-20 2,995 133,841 October-20 2,496 November-20 1,498 December-20 1,248 111,534 66,921 55,767 24,960 $1.115,344 $860,000 $860,000 DIRECT LABOR SERVICE SELLING/ADMIN MATERIALS OVERHEAD OVERHEAD 18,900 30,671 35,783 22,050 35,783 22,050 35,783 22,050 22,050 25,200 51,118 31,500 76,677 47,250 61,342 37,800 51,118 31,500 30,671 18,900 25,558 15,750 $511,181 $315,000 35,783 40,894 10,500 12,250 12,250 12,250 12,250 2,208 2,524 3,155 4,732 3,785 17,500 3,155 10,500 1,893 8,750 1,576 $175,000 $31.545 14,000 17,500 1,893 2,208 2,208 2,208 26,250 21,000 PROFIT 4,957 5,783 5,783 5,783 5,783 6,610 8,261 12,393 9,914 8,261 4,957 4,133 $82,618 BUDGET Total hourly labor budgeted per hour BUDGET Total budgeted labor dollars ACTUAL Total dollars paid in labor BILLED Total labor dollars billed out BUDGET Budgeted hours work and bill ACTUAL BILLED LABOR: for 12 hourly employees, including benefits BUDGET ACTUAL BUDGET ACTUAL BILLED BUDGET Budget overhead markup % (multiplied by labor+materials) BUDGET Budgeted total overhead costs ACTUAL Actual total overhead costs BILLED Billed overhead to customers BUDGET Budgeted service overhead costs ACTUAL Actual service overhead costs Actual hours worked Billed hours BUDGET BUDGET OVERHEAD AND OVERHEAD MARKUP: NOTE: SERVICE Overhead is a product cost, SELLING & ADMIN Overhead is a period cost. 25% Budgeted selling & admin overhead costs Actual selling & admin overhead costs REVENUE: Budgeted revenue Collected actual revenue Billed revenue PROFIT: Budget profit markup % (multiplied by labor+materials) Budget profit in dollars ACTUAL Actual profit BILLED Billed profit $20.48 $511,181 $559,897 $488,243 24,960 25,210 23,840 $206,545 $231,346 $168,119 $175,000 $186,540 $31,545 $44,806 $1,115,344 $1,048,744 $1,048,744 10.00% 82,618 (63,645) 80,057 FALL 2021 FINAL PROJECT MAINTENANCE DIVISION of CEDAR PROPERTIES MAINTENANCE ACCOUNTING INFORMATION for 2020: Total markup %-Overhead markup % + Profit markup % Billed to customer-[(hours worked on job x billed labor rate/hour)+ (materials used)] x [Total markup Hours worked (billed) on each job=(Time spent at job site+ Time to and from that location) rounded up to 0.25 of an hour (15 minute increments) BILLED The billed labor cost is: BILLED BILLED BILLED BILLED BILLED BILLED BILLED BUDGET ACTUAL BILLED average hourly wage cost paid to maintenance workers x (52 weeks pald/48 weeks worked to account for paid time off) Hourly rate including paid time off Hourly rate including paid time off Average employer hourly wage costs (employer FICA, unemployment taxes, health Insurance) TOTAL BILLED LABOR COST Budget begin, materials Inventory on 1/1/2020 Actual begin, materials Inventory on 1/1/2020 Materials budgeted to purchase HAVE MERENDA MOTE BUDGET ACTUAL BUDGET ACTUAL Total materials billed out BUT $14.00 1.0833 $15.17 MATERIALS: Note: Budgeted beginning and ending materials inventory were assumed to be the same, so materials purchased equals materials used Materials budgeted to purchase $315,000 Materials actually purchased $321,146 Materials billed to customers $312,325 ACTUAL Ending materials inventory on 12/31/2020 before year-end adjustm Budget ending materials inventory count on 12/31/2020 ACTUAL Actual ending materials inventory count on 12/31/2020 BUDGET $15.17 35% 5.31 $20.48 30,000 30,200 315,000 -321,146 HOUR 23,722 30,000 30,200