Please provide cell references for answers below:
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 1 Part 1 Solution 2 #1 Sales Budget 3 4 Guidance: Make sure you are using cell references or formulas throughout your budgets. Manufacturing Inc. Sales Budget For quarter ended June 30, 20XX 5 April 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 $ April 6 7 8 Units 9 Selling Price 10 Sales 11 12 #2 Production Budget 13 14 15 16 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 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 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 April 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 B C D E F 42 April May June Quarter 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 54 Manufacturing Inc. 65 Selling and Administrative Expenses Budget 66 For quarter ended June 30, 20XX 67 April May June Quarter 68 Budgeted Sales in units (Reference Budget 1) 59 Variable Selling and Administrative expenses per unit 70 Total variable expenses 71 Fixed Selling and Administrative expenses 72 Total selling and administrative expenses 73 74 #6 Cash Budget 75 Manufacturing Inc. 76 Cash Budget 77 For quarter ended June 30, 20XX 78 April May June Quarter 79 Beginning cash B0 Add Cash Collections of Sales: 91 Cash sales collected in month sale 82 Credit sales Collected in following month 83 Total Cash receipts 84 85 Cash Available 96 Less Cash Disbursements: 87 Purchases (Budget 3) 88 Direct Labor (Budget 4) 89 Overhead that uses cash 90 Selling and administrative expenses that use cash 91 Total Cash Disbursements 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) 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
