Show formulas used

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