$| x \\/ e Instructions: mmummhmMn _L_n_n_n_n_n_n_n_n_n mmummth-no b31828 A B C D E F G H | Using the Excel template spreaihheet as a guide, for the 3:"I quarter of the year (July, Augmt and September, as well as the total for the 3\" quarter). I Instructions: _| 1. Prepare the sales budget and the cash collection schedule. 2. Determine the amount of Accounts Receivable as of September 30. . Prepare the production budget in units. . Prepare the direct materials and purchases budget in pounds and total purchases in both pounds and dollars. . Prepare the schedule of cash disbursements for direct materials. . Prepare the direct labor budget. . Prepare the budget for the overhead expenses. 3 4 5 6. Determine the amount of Accounts Payable as of September 30. 7 8 9 . Prepare the budget for the selling and administrative expenses. 10. Prepare the cash budget. 11. Determine the unit product cost. 12. Determine the cost of goods sold for the budget. 13. Determine the ending inventory balance for: Direct materials Finished goods Other: Your schedules should be in proper format, including dollar signs, alignments, titles, and underscoring, among others. For this project, you should use the capabilities of Excel to complete the budgets, e.g. formulae, links, if statements. Sales Information Units June Information June 41,000 Sales $ 492,000 July 42,000 Accounts Receivable, 6/30/X1 137,760 August 43,500 September 44.000 October 44,000 Collection pattern: November 46,000 month of sale 72% month after 28% Sales price $ 12.00 Finished Goods Inventory: Direct Materials Inventory: Ending Inventory, units, 6/30/X1 5,040 per unit (pounds) 1.5 Desired Ending Inventory (for next month's needs) 12% Desired Ending Inventory (for next month's needs) 22% 15 Cost per unit $ 5.05 Ending Inventory, units, 6/30/X1 13,919 Direct materials are paid for in the month following the purchase. Direct Labor: Accounts Payable, June 30, X1 for DM $ 313,250 0 Labor 0.25 hours per unit Rate per hour $ 15.00 Direct labor is paid for in the month that it is incurred. Manufacturing Overhead: Variable MOH cost per unit produced - all cash expenses 0.50 Total Selling and Administrative expenses: 3,000 Total Fixed MOH cost per month 3,500 includes the noncash expense of depreciation 6 Cash expenses (paid during the month incurred) 2,000 all fixed Depreciation included in Fixed MOH 1,500 27 All cash expenses are paid in the month of incurrence. Cash Information: Equipment Purchase: 0 Balance, 6/30/X1 21,500 August (cash expenditure) $ 5,000 Minimum cash balance 20,000 Interest rate per month 1.5% Income Tax Payment: Borrowing is made at the beginning of the month. August 14,000 Repayments of principal and interest must be repaid at the end of the quarter. 5 All borrowings and repayments are made in INCREMENTS of $1,000. Dividend Payment: 36 July $ 10,000 Assignment Instructions Fox Inc Data Budget Template +A B C D E F G H 3 Sales Budget July August September Quarter 4 Sales - units 42,000 5 Price/unit 12 6 Total Sales 504,000 A/R (end of quarter Schedule of Collections and Accounts Receivable July August September Quarter only) 10 June Sales 11 July Sales $ 362,880 $ 141,120 $ 504,000 12 August Sales 13 September Sales 4 Total Cash Collected 15 16 Production Budget July August September Quarter October November June 18 Sales needs - units 42,000 19 Ending Inventory THIS NUMBER FOR JULY NEEDS TO BE FILLED II 20 Total Manufacturing Needs 12,000 Beginning Inventory 5,040 Total Production Needs 36,960 Direct Materials Purchases Budget July August September Quarter October 26 Production Needs - units 36,960 27 Pounds per Unit 1.50 28 Direct Material Needs for Production 55,440 19 Ending Inventory - units THIS NUMBER FOR JULY NEEDS TO BE FILLED IN. Total Direct Material Needs 55,440 Beginning Inventory - units 12,197 32 Direct Material Purchases - Units 43,24: 83 Cost per Unit 5.05 Direct Material Purchases - Cost 218,378 15 16 Cash Disbursements Budget for Direct Materials July August September Quarter A/P 38 39 Direct Labor Budget July August September Quarter 10 Production Units 36,960 41 DL Hours per Uni 0.25 12 DL Rate per Hou 15.00 13 Direct Labor Cost 138,600 14 15 Manufacturing Overhead Budget July August September Quarter Production Units 36,960 17 Variable MOH Costs per Unit D.50 Total Variable MOH Costs 18,480 19 Fixed MOH Costs 3,500 Total Manufacturing Overhead 21,980 Less: Noncash items 2,000 52 Cash Disbursements for MOH 19,980 53 54 Selling and Administrative Expenses Budget July 55 Total Expenses 3,000 6 Cash Expenses 2,000FOX Company Cash Budget For the Quarter Ended September 30, 20X1 July August September Quarter Beginning Cash Balance $5 Cash collections 16 Cash Available 8 Cash Disbursements Payments for direct materials 0 Payments for direct labor Payments for manufacturing overhead Payments for selling and administrative costs 3 Income tax payment 4 Payments for equipment 5 Dividend payment 6 Total Disbursements 8 Net Cash Inflow 79 30 Borrowings 31 Repayment of Principal 32 Interest payments 83 Total Financing 14 Ending Cash Balance B5 36 Interest Calculation Vanables: Principal 38 Monthly Interest Rate 1.5% 19 Time 3 2 10 Total Interest Payment PRODUCT COST per UNIT Direct materials per unit (pounds per unit X DM unit cost) 15 Direct labor per unit MOH per unit Variable Fixed (this includes the cash and noncash expenses) 19 TOTAL 01 02 COST OF GOODS SOLD 03 TOTAL UNITS SOLD 04 Product cost per unit 05 TOTAL COST OF GOODS SOD 06 DIRECT FINISHED 07 ENDING INVENTORY MATERIALS GOODS 08 UNITS 09 Product cost per unit 10 TOTAL INVENTORY COST