For the question below, #6 , please show the calculations and formulas for those answers given.

Data Scenario: You have just been hired into a management position which requires the application of your budgeting skills. You find out that budgeting has not been a priority of the company. You have contacted various areas on the organization and have accumulated the information below to assist you in preparing a comprehensive budget. Manufacturing Inc. produces a part used in the production of engines. Actual Sales and Projected sales in units: March (Actual) 38,000 April 44,000 May 45,000 June 50,000 July 52,000 Sales are the following type: 56% Cash sales collected in month of sale 44% Credit sales collected in the following month of sale The following data pertains to the manufacturing process. 1. Finished goods inventory March 31st Desired ending finished goods for each month 35,200 units 80% of next month's sales volume $148.71 budgeted cost to make a unit Metal $6 2. Direct materials used: Direct Material Per-Unit Usage Cost per Pound 8 pounds The beginning balance of each month needs to be able to produce Beginning material in pounds as of April 1st Direct materials paid in month purchased. 50% of that month's estimated sales volume 176,000 4 hours $12.00 per hour 3. The direct labor used per unit Direct labor paid in month incurred. 4. Overhead each month is estimated based on direct labor hours per variable cost. All costs that use cash are paid in month incurred. Fixed cost Variable cost Supplies $1.00 Power 0.60 Maintenance $27,000 0.40 Supervision 15,000 Depreciation 19,000 Taxes 11,000 Other 60,000 1.10 Total $132,000 $3.10 5. Monthly selling and administrative expenses are based on units sold per variable cost. All costs that use cash are paid in month incurred. Fixed cost Variable cost Salaries $40,000 Commissions $1 Depreciation 15,000 Shipping 0.7 Other 10,000 0.4 Total $65,000 $2.10 6. Unit selling price $124 per unit 7. Cash balance as of April 1st $150,000 Part 1 Solution #1 Sales Budget Guidance: Make sure you are using cell references or formulas throughout your budgets. Manufacturing Inc. Sales Budget For quarter ended June 30, 20XX April Units Selling Price Sales May 44,000 $124 5,456,000 $ June 45,000 $124 5,580,000 $ Quarter 50,000 $124 6,200,000 $ 139,000 $124 17.236.000 $ #2 Production Budget April Sales Budget (Reference Budget 1) Plus desired ending inventory Total Inventory requirements Less: Beginning Inventory Units to be produced Manufacturing Inc. Production Budget For quarter ended June 30, 20XX | May June 44,000 45,000 36,000 40,000 80,000 85,000 35,200 36,000 44,800 49,000 Quarter 50,000 41600 91,600 40,000 51,600 139,000 117,600 256,600 111,200 145,400 #3 Direct Material Purchases Budget April Units to be produced (Reference Budget 2) Direct Materials per unit (pounds) Production needs (pounds) Desired ending inventory (pounds) Total needs (pounds) Less: Beginning inventory (pounds) Purchases needed of Direct materials (pounds) Cost per pound Total purchases of direct materials Manufacturing Inc. Direct Material Purchases Budget For quarter ended June 30, 20XX May June 44,800 49,000 8 8 358,400 392,000 180,000 200,000 538,400 592,000 176,000 180,000 362,400 412,000 $6 $6 $2,174,400 $2,472,000 Quarter 51,600 8 412,800 208,000 620,800 200,000 420,800 $6 $2,524,800 145,400 8 1,163,200 588,000 1,751,200 556,000 1,195,200 $6 $7,171,200 #4 Manufacturing Cost Budget Manufacturing Inc. Manufacturing Cost Budget For quarter ended June 30, 20XX June April May Quarter Direct Materials: Production needs (pounds)--found on Budget 3 Cost per pound Total Cost of material issued to production 358,400 $6 $2,150,400 392,000 $6 $2,352,000 412,800 $6 $2,476,800 1,163,200 $6 $6,979,200 44,800 4 179,200 $12 $2,150,400 49,000 4 196,000 $12 $2,352,000 51,600 4 206,400 $12 $2,476,800 145,400 4 581,600 $12 $6,979,200 Direct Labor: Units to be produced (Reference Budget 2) Direct labor time per unit (hours) Total hours needed Cost per hour Total cost of Direct Labor Manufacturing Overhead: Budgeted direct labor hours needed (Reference Row 51 above Variable overhead rate Budgeted variable overhead Budgeted Fixed overhead Total Manufacturing Overhead Total Manufacturing Cost 179,200 3.10 $555,520 132,000 $687,520 $4,988,320 196,000 3.10 $607,600 132,000 $739,600 $5,443,600 206,400 3.10 $639,840 132,000 $771,840 $5,725,440 581,600 3.10 $1,802,960 132,000 $1,934,960 $15,893,360 #5 Selling and Administrative Expenses Budget Manufacturing Inc. Selling and Administrative Expenses Budget April May Budgeted Sales in units (Reference Budget 1) Variable Selling and Administrative expenses per unit Total variable expenses Fixed Selling and Administrative expenses Total selling and administrative expenses 44,000 $2.10 $92,400 65,000 $157,400 June 45,000 $2.10 $94,500 65,000 $159,500 Quarter 50,000 $2.10 $105,000 65,000 $170,000 139,000 $2.10 $291,900 65,000 $356,900 6 Manufacturing Inc. Rate answer here. Cash Budget For the quarter ended June 30, 20XX April May June Quarter Beginning cash balance 150,000 143,320 (20,340) 150,000 Add: Cash Collections of Sales Cash sales collected in month sale 3,055,360 3,124,800 3,472,000 9,652,160 Credit sales collected in following month 2,073,280 2,400,640 2,455,200 6,929,120 Total cash receipts 5,128,640 5,525,440 5,927,200 16,581,280 Total Available Cash 5,278,640 5,668,760 5,906,860 16,731,280 Less: Cash Disbursements Purchases 2,174,000 2,472,000 2,524,800 7,170,800 Direct Labor 2,150,400 2,352,000 2,476,800 6,979,200 Overhead that uses cash 668,520 720,600 752,840 2,141,960 Selling & Administrative Expenses that use cash 142,400 144,500 155,000 441,900 Total Cash Disbursements 5,135,320 5,689,100 5,909,440 16,733,860 Ending Cash 143,320 (20,340) (2,580) (2,580)