PLEASE COMPLETE THE BELOW WITH CELL REFERENCES OR EXPLANATIONS ON HOW TO CALCULATE THE ANSWERS :)

A B E F G H 1 $148.71 budgeted cost to make a unit 10 Data Scenario: 11 You have just been hired into a management position which requires the application of your budgeting skills. 12 You find out that budgeting has not been a priority of the company. 13 You have contacted various areas on the organization and have accumulated the information below to assist you 14 in preparing a comprehensive budget. 15 16 Manufacturing Inc. produces a part used in the production of engines. 17 Actual Sales and Projected sales in units: 18 March (Actual) 38,000 19 April 44,000 20 May 45,000 21 June 50,000 22 July 52,000 23 24 Sales are the following type: 56% Cash sales collected in month of sale 25 44% Credit sales collected in the following month of sale 26 27 The following data pertains to the manufacturing process. 28 1. Finished goods inventory March 31st 35,200 units 29 Desired ending finished goods for each month 80% of next month's sales volume 30 31 2. Direct materials used: 32 Direct Material Per-Unit Usage Cost per Pound 33 Metal 8 pounds $6 34 The beginning balance of each month needs to be able to produce 50% of that month's estimated sales volume 35 Beginning material in pounds as of April 1st 176,000 36 Direct materials paid in month purchased. 37 38 3. The direct labor used per unit 4 hours $12.00 per hour 39 Direct labor paid in month incurred. 40 41 4. Overhead each month is estimated based on direct labor hours per variable cost. All costs that use cash are paid in month incurred. 42 Fixed cost Variable cost 43 Supplies $1.00 0.60 45 Maintenance $27,000 0.40 46 Supervision 15,000 47 Depreciation 19,000 48 Taxes 11,000 20 49 Other 60.000 1.10 50 Total $132,000 $3.10 51 52 5. Monthly selling and administrative expenses are based on units sold per variable cost. All costs that use cash are paid in month incurred. 53 Fixed cost Variable cost 54 Salaries $40,000 55 Commissions $1 56 Depreciation 15,000 57 Shipping 0.7 58 Other 10,000 0.4 59 Total $65,000 $2.10 60 61 6. Unit selling price $124 per unit 62 63 7. Cash balance as of April 1st $150,000 44 Power B D E F Manufacturing Inc. Sales Budget For quarter ended June 30, 20XX 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 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 41,600 91,600 40.000 51,600 139,000 117,600 256,600 111.200 145,400 A 1 Part 1 Solution 2 #1 Sales Budget 3 Guidance: 4 Make sure you are using oel references or formulas 5 throughout your budgets. 6 7 April 8 Units 9 Selling Price 10 Sales $ 11 12 #2 Production Budget 13 14 15 16 April 17 Sales Budget (Reference Budget 1) 18 Plus desired ending inventory 19 Total Inventory requirements 20 Less: Beginning Inventory 21 Units to be produced 22 23 #3 Direct Material Purchases Budget 24 25 26 27 April 28 Units to be produced (Reference Budget 2) 29 Direct Materials per unit (pounds) 30 Production needs (pounds) 31 Desired ending inventory (pounds) 32 Total needs (pounds) 33 Less: Beginning inventory (pounds) 34 Purchases needed of Direct materials (pounds) 35 Cost per pound 36 Total purchases of direct materials 37 38 #4 Manufacturing Cost Budget 39 40 41 42 April 43 Direct Materials: 44 Production needs(pounds)--found on Budget 3 45 Cost per pound 46 Total Cost of material issued to production 47 48 Direct Labor: 49 Units to be produced (Reference Budget 2) 50 Direct labor time per unit (hours) 51 Total hours needed 52 Cost per hour 53 Total cost of Direct Labor 54 55 Manufacturing Overhead: 56 Budgeted direct labor hours needed (Reference Row 51 above) 57 Variable overhead rate 58 Budgeted variable overhead 59 Budgeted Fixed overhead 60 Total Manufacturing Overhead 61 Total Manufacturing Cost 62 63 #5 Selling and Administrative Expenses Budget 64 65 66 67 April 68 Budgeted Sales in units (Reference Budget 1) 69 Variable Selling and Administrative expenses per unit 70 Total variable expenses 71 Fixed Selling and Administrative expenses 72 Total selling and administrative expenses 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 S6 $7,171,200 Manufacturing Inc. Manufacturing Cost Budget For quarter ended June 30, 20XX May June Quarter 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 49.000 47 44,800 4 179.200 $12 $2,150,400 196,000 $12 $2,352,000 51,600 4. 206,400 $12 $2,476,800 145,400 4 581,600 $12 $6,979,200 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 Manufacturing Inc. Selling and Administrative Expenses Budget For quarter ended June 30, 20XX | May June 44,000 45,000 $2.10 $2.10 $92,400 $94,500 65,000 65,000 $157,400 $159,500 Quarter 50.000 $2.10 $ 105,000 65,000 $170,000 139,000 $2.10 $291,900 65,000 $356.900 B E F 74 #6 Cash Budget 75 76 Manufacturing Inc. Cash Budget For quarter ended June 30, 20XX June $150,000 - April May Quarter 77 78 79 Beginning cash 80 Add Cash Collections of Sales: 81 Cash sales collected in month sale 82 Credit sales Collected in following month 83 Total Cash receipts $3,055,360 2,073,280 $5,128,640 $3,124,800 2,400,640 $5,525,440 $3,472,000 2,455,200 $5,927,200 $9,652,160 6,929,120 $16,581,280 85 Cash Available 86 Less Cash Disbursements: 87 Purchases (Budget 3) $2,174,400 $2,472,000 $2,524,800 $7,171,200 88 Direct Labor (Budget 4) 2,150,400 2,352,000 2,476,800 6,979,200 89 Overhead that uses cash 90 Selling and administrative expenses that use cash 91 Total Cash Disbursements $4,324,800 $4,824,000 $2,476,800 $6,979,200 92 Ending Cash 93 94 95 96 #7 Based on the quarterly cash budget you prepared, provide recommendations on cash management. Your comments should be directed at management 97 98 99 100 101 #8 Budgeted Income statement for Second quarter 102 Manufacturing Inc. 103 Budgeted Income Statement 104 For the Secord Quarter ended June 30, 20XX 105 106 Sales (Budget #1) $ 17,236,000 107 Cost of Goods Sold: 108 Beginning Finished Goods 109 Total Manufacturing Costs (Budget #4) 110 Cost of Goods Available for Sale 111 Ending finished goods 112 Budgeted Cost of Goods Sold 113 Gross Profit 114 Less: Selling and administrative expenses (Budget 5) 115 Income before income taxes 116 117 #9 What if the company decides to lay off one of the part-time administrative staff. The monthly salaries will be reduced by $6,000, what budgets are effected? Why? 118 What is the New Net income(Loss) for the quarter? 1191 120 121 If you have linked everything correctly, you should only have to change the monthly salary on the data sheet. 122 Please change the salary back to the original amount of before you submit