1 Part 1 2 I have a template set up on the Budgetsolution worksheet that you should use to complete the required budgets and requirements stated below. 3 You need to use cell references in the development of your budgets. You will earn zero credit if you type in the numbers and do not use cell referencing. 4 You should use this worksheet as your data field and only use cell references and formulas in your budgets. 5 Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet has formatted budgets for you to complete. 6 The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you do not need to retype the budgets if you 7 have used cell references and formulas throughout. 8 9 Data Scenario: 10 You have just been hired into a management position which requires the application of your budgeting skills. 11 You find out that budgeting has not been a priority of the company. 12 You have contacted various areas on the organization and have accumulated the information below to assist you 13 in preparing a comprehensive budget. 14 15 Manufacturing Inc. produces a part used in the production of engines. 16 Actual Sales and Projected sales in units: 17 March (Actual) 18 April 19 May 20 June 21 July 22 38,000 40,000 42,000 45,000 44,000 23 Sales are the following type: 24 25 51% Cash sales collected in month of sale 49% Credit sales collected in the following month of sale 26 The following data pertains to the manufacturing process. 27 1. Finished goods inventory 28 Desired ending finished goods for each month 29 30 2. Direct materials used: 31 Direct Material 32 Metal Per-Unit Usage 8 pounds March 31st Cost per Pound $7 33 The beginning balance of each month needs to be able to produce 34 Beginning material in pounds as of April 1st 35 Direct materials paid in month purchased. 36 37 3. The direct labor used per unit 4 hours 32,000 units 80% of next month's sales volume 48% of that month's estimated sales volume 153,600 $14.00 per hour 38 Direct labor paid in month incurred. 39 $148.71 budgeted cost to make a unit 40 4. Overhead each month is estimated based on direct labor hours per variable cost. All costs that use cash are paid in month incurred. 41 42 Supplies Fixed cost Variable cost $0.80 43 Power 44 Maintenance $20,000 45 Supervision 15,000 46 Depreciation 47 Taxes 19,000 48 Total 49 11,000 $65,000 0.60 0.40 $1.80 50 5. Monthly selling and administrative expenses are based on units sold per variable cost. All costs that use cash are paid in month incurred. 51 52 Salaries 53 Commissions 54 Depreciation 55 Shipping 56 Total 57 Fixed cost $30,000 10,000 $40,000 58 6. Unit selling price 59 60 7. Cash balance as of April 1st $200 per unit $60,000 61 Variable cost $1 0.7 $1.70 62 Required: You must use cell references on the BudgetSolution worksheet, by referencing this worksheet that contains the data. 63 Prepare the following second quarter budgets and answer the questions listed on the template provided on the BudgetSolution Worksheet. I have adapted the budget model to meet the needs of this company. 64 If I bolded a line item, that is a header and does not need computation on that row. 65 Please note the quarter column is for the quarter so not all lines should be added across in the quarter column. When you have beginning and ending inventory or cash balances this is for the quarter and should be brought over to the quarter column. 66 1. Sales Budget per month and quarter. 67 2. Production Budget per month and quarter. 68 3. Direct materials purchase budget per month and quarter. 69 4. Manufacturing Cost budget per month and quarter. 70 5. Selling and administrative expenses budget per month and quarter. 71 6. Cash budget per month and quarter. 72 7. Budgeted income statement (ignore income tax) for the quarter. 73 8. What if the company decides to lay off one of the part-time administrative staff. The monthly salaries will be reduced by $5,000, list all budgets that will change? Why? 74 What is the New Net income(Loss) for the quarter? 75 76 77 78 79 80 81 82 83 84 Project4_Intro Part1 BudgetSolution Part 2 Part 3 Part 4 + | 1 Part 1 Solution 2 #1 Sales Budget 3 4 5 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 37 38 #4 Manufacturing Cost Budget 39 40 41 42 Guidance: Make sure you are using cell references or formulas throughout your budgets. Manufacturing Inc. Sales Budget For quarter ended June 30, 20XX April May 40,000 April Manufacturing Inc. Production Budget June Quarter For quarter ended June 30, 20XX May Manufacturing Inc. June Quarter April Direct Material Purchases Budget For quarter ended June 30, 20XX May June Quarter April Manufacturing Inc. Manufacturing Cost Budget For quarter ended June 30, 20XX May June Quarter 40 41 42 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 April 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 73 74 #6 Cash Budget 75 76 77 78 79 Beginning cash 80 Add Cash Collections of Sales: 81 Cash sales collected in month sale April Manufacturing Cost Budget For quarter ended June 30, 20XX May Manufacturing Inc. June Quarter Selling and Administrative Expenses Budget For quarter ended June 30, 20XX May June Manufacturing Inc. Cash Budget For quarter ended June 30, 20XX May Quarter June Quarter 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 84 85 Cash Available 86 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 #7 Budgeted Income statement for Second quarter 95 96 97 98 Manufacturing Inc. Budgeted Income Statement For the Secord Quarter ended June 30, 20XX 99 Sales (Budget #1) 100 Cost of Goods Sold: 101 Beginning Finished Goods 102 Total Manufacturing Costs (Budget #4) 103 Cost of Goods Available for Sale 104 Ending finished goods 105 Budgeted Cost of Goods Sold 106 Gross Profit 107 Less: Selling and administrative expenses (Budget 5) 108 Income before income taxes 109 110 #8 What if the company decides to lay off one of the part-time administrative staff. The monthly salaries will be reduced by $5,000, list all budgets that will change? Why? 111 What is the New Net income(Loss) for the quarter? 112 113 114 If you have linked everything correctly, you should only have to change the monthly salary on the data sheet. 115 Please change the salary back to the original amount of before you submit. 116 117 118