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 3" quarter). Instructions: 1. Prepare the sales budget and the cash collection schedule. 2. Determine the amount of Accounts Receivable as of September 30. 3. Prepare the production budget in units. 4. Prepare the direct materials and purchases budget in pounds and total purchases in both pounds and dollars. 5. Prepare the schedule of cash disbursements for direct materials. 6. Determine the amount of Accounts Payable as of September 30. 7. Prepare the direct labor budget. 8. Prepare the budget for the overhead expenses. 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. I will be glad to review your work before you hand in the final budget but the spreadsheet is due by the end of the day, Friday, April 3rd. However, you are welcome to turn it in earlier. Please turn this assignment in electronically as I am interested in reviewing the formulae and links that you prepared for this purpose. Please let me know if you have any questions. Sales Information June Units 41000 June Information 492.000 Sales Accounts Receivable, B300X1 137,780 July August September October November 42000 43,500 44,000 44000 48000 Collection pattem: Touth of salc month afler 7255 2255 Sales orice 12.00 Finished Goods Inventory: Ending Inventary, units, 90/X1 Desired Ending Inventory for next month's reeds 5.040 129 Direct Materials Inventory: per unit (pounds) Desired Ending Inventary (for next month's neeck) Cast per unit Ending Inventory, units, 6/30X1 Drect materiale sre paid for in the mom tolowing the wchoes. 5.05 13,919 Accounts Payable, June 30, X1 for DM 313.250 Direct Labor: Labor Rale per hour Disct labor is paid for in the months 0.25 hours per unil 15.00 incurred. Total Selling and Administrative expenses: Cash expenses foedwig Wie with incurred) 3.000 2,000 Manufacturing Overhead: Variable: MOH cost per unit produced all cash expenses Tall Fixed MOHcces per month Depreciation included in Fixed MOH Al csah expenses are paid in the mouth of Incurrence. 0.50 3,500 includes the rancash expense of depreciation 1,500 all fixed Equipment Purchase: August (cash experiture) 5,000 Cash Information: Balancu B20X1 Minimum cash balance Interest rate per month Borrowing is modisme begiving of the month Rapanuis ar principal and internas e do theam of the owner Al burrowings and repairs are made in INCREMENTS of $1.000. 21500 20,000 1.5% Income Tax Payment: August 14,000 Dividend Payment: July 10,000 Quarter 129,500 Sales Budget Sales - units Price/unit Total Sales July 42,000 12 504.000 $ $ August September 43,500 44,000 12 $ 12 $ 522,000 $ 528,000 $ 1,554,000 August September A/R (end of quarter only) sepe Quarter July 137,760 362,880 504,000 Schedule of Collections and Accounts Receivable June Sales July Sales August Sales September Sales Total Cash Collected $ $ 141,120 375,840 $ $ 516,960 $ 146,160 380,160 526,320 500,640 $ $ 1,543,920 $ 1,543,920 Quarter November June Production Budget - Sales needs - units Ending Inventory Total Manufacturing Needs Beginning Inventory Total Production Needs July 42,000 5,220 47.220 5,040 42,180 August 43,500 5.280 48.780 5,220 43.560 43 560 September 44,000 5,280 49.280 5,280 44.000 October 44,000 5,520 49,520 5,280 44,240 129.740 Quarter 129,740 October 44,240 66,360 - Direct Materials Purchases Budget Production Needs - units Pounds per Unit Direct Material Needs for Production Ending Inventory - units Total Direct Material Needs Beginning Inventory - units Direct Material Purchases - Units Cost per Unit Direct Material Purchases - Cost July 42,180 1.50 63,270 14,375 77,645 13,919 63,725 $ 5.05 $ 321,813 $ August September 43,560 44,000 1.50 1.50 65,340 66,000 14,520 14,599 79,860 80,599 14,375 14.520 65,485.00 66.079 5.05 $ 5.05 330,699 $ 333,699 $ 195,289 986,212 Cash Disbursements Budget for Direct Materials July August September Quarter A/P August September Quarter Direct Labor Budget Production Units DL Hours per Unit DL Rate per Hour Direct Labor Cost July 42,180 0.25 15.00 158,175 $ August September Quarter Manufacturing Overhead Budget Production Units Variable MOH Costs per Unit Total Variable MOH Costs Fixed MOH Costs Total Manufacturing Overhead Less: Noncash items Cash Disbursements for MOH July 42,180 0.50 21,090 3,500 24,590 2,000 22,590 July Selling and Administrative Expenses Budget Total Expenses Cash Expenses 3,000 2,000 S FOX Company Cash Budget For the Quarter Ended September 30, 20X1 July August September Quarter Beginning Cash Balance Cash collections Cash Available Cash Disbursements Payments for direct materials Payments for direct labor Payments for manufacturing overhead Payments for selling and administrative costs Income tax payment Payments for equipment Dividend payment Total Disbursements Net Cash Inflow Borrowings Repayment of Principal Interest payments Total Financing Ending Cash Balance Interest Calculation Variables: 7 Principal 3 Monthly Interest Rate Time Total Interest Payment 1.5% 3 3 PRODUCT COST per UNIT 4 Direct materials per unit (pounds per unit X DM unit cost) 5 Direct labor per unit 5 MOH per unit 7 Variable 3 Fixed (this includes the cash and noncash expenses) TOTAL 2 COST OF GOODS SOLD TOTAL UNITS SOLD Product cost per unit 5 TOTAL COST OF GOODS SOD DIRECT MATERIALS FINISHED GOODS 7 ENDING INVENTORY 8 UNITS 9 Product cost per unit 0 TOTAL INVENTORY COST 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 3" quarter). Instructions: 1. Prepare the sales budget and the cash collection schedule. 2. Determine the amount of Accounts Receivable as of September 30. 3. Prepare the production budget in units. 4. Prepare the direct materials and purchases budget in pounds and total purchases in both pounds and dollars. 5. Prepare the schedule of cash disbursements for direct materials. 6. Determine the amount of Accounts Payable as of September 30. 7. Prepare the direct labor budget. 8. Prepare the budget for the overhead expenses. 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. I will be glad to review your work before you hand in the final budget but the spreadsheet is due by the end of the day, Friday, April 3rd. However, you are welcome to turn it in earlier. Please turn this assignment in electronically as I am interested in reviewing the formulae and links that you prepared for this purpose. Please let me know if you have any questions. Sales Information June Units 41000 June Information 492.000 Sales Accounts Receivable, B300X1 137,780 July August September October November 42000 43,500 44,000 44000 48000 Collection pattem: Touth of salc month afler 7255 2255 Sales orice 12.00 Finished Goods Inventory: Ending Inventary, units, 90/X1 Desired Ending Inventory for next month's reeds 5.040 129 Direct Materials Inventory: per unit (pounds) Desired Ending Inventary (for next month's neeck) Cast per unit Ending Inventory, units, 6/30X1 Drect materiale sre paid for in the mom tolowing the wchoes. 5.05 13,919 Accounts Payable, June 30, X1 for DM 313.250 Direct Labor: Labor Rale per hour Disct labor is paid for in the months 0.25 hours per unil 15.00 incurred. Total Selling and Administrative expenses: Cash expenses foedwig Wie with incurred) 3.000 2,000 Manufacturing Overhead: Variable: MOH cost per unit produced all cash expenses Tall Fixed MOHcces per month Depreciation included in Fixed MOH Al csah expenses are paid in the mouth of Incurrence. 0.50 3,500 includes the rancash expense of depreciation 1,500 all fixed Equipment Purchase: August (cash experiture) 5,000 Cash Information: Balancu B20X1 Minimum cash balance Interest rate per month Borrowing is modisme begiving of the month Rapanuis ar principal and internas e do theam of the owner Al burrowings and repairs are made in INCREMENTS of $1.000. 21500 20,000 1.5% Income Tax Payment: August 14,000 Dividend Payment: July 10,000 Quarter 129,500 Sales Budget Sales - units Price/unit Total Sales July 42,000 12 504.000 $ $ August September 43,500 44,000 12 $ 12 $ 522,000 $ 528,000 $ 1,554,000 August September A/R (end of quarter only) sepe Quarter July 137,760 362,880 504,000 Schedule of Collections and Accounts Receivable June Sales July Sales August Sales September Sales Total Cash Collected $ $ 141,120 375,840 $ $ 516,960 $ 146,160 380,160 526,320 500,640 $ $ 1,543,920 $ 1,543,920 Quarter November June Production Budget - Sales needs - units Ending Inventory Total Manufacturing Needs Beginning Inventory Total Production Needs July 42,000 5,220 47.220 5,040 42,180 August 43,500 5.280 48.780 5,220 43.560 43 560 September 44,000 5,280 49.280 5,280 44.000 October 44,000 5,520 49,520 5,280 44,240 129.740 Quarter 129,740 October 44,240 66,360 - Direct Materials Purchases Budget Production Needs - units Pounds per Unit Direct Material Needs for Production Ending Inventory - units Total Direct Material Needs Beginning Inventory - units Direct Material Purchases - Units Cost per Unit Direct Material Purchases - Cost July 42,180 1.50 63,270 14,375 77,645 13,919 63,725 $ 5.05 $ 321,813 $ August September 43,560 44,000 1.50 1.50 65,340 66,000 14,520 14,599 79,860 80,599 14,375 14.520 65,485.00 66.079 5.05 $ 5.05 330,699 $ 333,699 $ 195,289 986,212 Cash Disbursements Budget for Direct Materials July August September Quarter A/P August September Quarter Direct Labor Budget Production Units DL Hours per Unit DL Rate per Hour Direct Labor Cost July 42,180 0.25 15.00 158,175 $ August September Quarter Manufacturing Overhead Budget Production Units Variable MOH Costs per Unit Total Variable MOH Costs Fixed MOH Costs Total Manufacturing Overhead Less: Noncash items Cash Disbursements for MOH July 42,180 0.50 21,090 3,500 24,590 2,000 22,590 July Selling and Administrative Expenses Budget Total Expenses Cash Expenses 3,000 2,000 S FOX Company Cash Budget For the Quarter Ended September 30, 20X1 July August September Quarter Beginning Cash Balance Cash collections Cash Available Cash Disbursements Payments for direct materials Payments for direct labor Payments for manufacturing overhead Payments for selling and administrative costs Income tax payment Payments for equipment Dividend payment Total Disbursements Net Cash Inflow Borrowings Repayment of Principal Interest payments Total Financing Ending Cash Balance Interest Calculation Variables: 7 Principal 3 Monthly Interest Rate Time Total Interest Payment 1.5% 3 3 PRODUCT COST per UNIT 4 Direct materials per unit (pounds per unit X DM unit cost) 5 Direct labor per unit 5 MOH per unit 7 Variable 3 Fixed (this includes the cash and noncash expenses) TOTAL 2 COST OF GOODS SOLD TOTAL UNITS SOLD Product cost per unit 5 TOTAL COST OF GOODS SOD DIRECT MATERIALS FINISHED GOODS 7 ENDING INVENTORY 8 UNITS 9 Product cost per unit 0 TOTAL INVENTORY COST