Please use the information above to answer questions 1-3. Please show your formulas and how you got each number as they need to be tied to cell references and formulas in excel afterwards. Cell references don't need to be listed, just formulas used.
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 June Quarter Units Selling Price Sales May 44,000 $124 5,456,000 $ #2 Production Budget Manufacturing Inc. Production Budget For quarter ended June 30, 20XX May June April Quarter Sales Budget (Reference Budget 1) Plus desired ending inventory Total Inventory requirements Less: Beginning Inventory Units to be produced #3 Direct Material Purchases Budget Manufacturing Inc. Direct Material Purchases Budget For quarter ended June 30, 20XX May June April Quarter 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
