Formulas Data Review View Help Tell me what you want to do Prob. 3 10 Budgeted Amounts 100 SCORE: Variable costs: Direct materials Direct labor Factory overhead per unit 12.00 8.00 1.50 Fixed costs Production supervisor salary Depreciation expense in dollars 90,000 $30,000 Budgeted Level (expected level of production) 25,000 units wants to know what the total manufacturing costs will be at the ction (i.e. producing 20,000 vs. 25,000 vs. 30,000) 30,000 Level of production Variable costs Direct materials 20,000 25,000 NOTE:. Each of the highlighte should contain an Exce Formulas should use ce to refer back to input Direct labor Factory overhead Total Variable Costs Fixed Costs: Production supervisor salary Depreciation expense Total Fixed Costs TOTAL COSTS REQUIRED: 1. Create a flexi budget for the three levels of productions (20,000, 25,000, and 30,000 units). Be sure to use Excel formulas for all calculations in the flexi budget. Adjust spacing as needed. 2. Why would a business prepare a budget for three levels of production rather than one level? View Help Tell me what you want to do eHome Insert Page Layout Formulas Data Review INPUT: Product A Product B $83.00 46.00 NOTE: Each of the highli should contain an Formulas should to refer back to in For percentages, g For units, round to For money, round t Adjust spacing as r Be sure to check th Selling price per unit Variable cost of goods sold per unit 53.00 20.00 Variable selling & administrative expenses per unit S 3.00 S 0 Total Fixed cost of goods sold 1 Total Fixed Selling & administrative expenses s 100,000.00 3 PURPOSE: 4 To determine which product would yield the highest profit 90,000.00 6 OUTPUT: 17 Contribution margin per unit 18 Contribution margin percentage 19 20 Break even point in units 21 Break even point in sales dollars Product A Product B 23 Number of units to be sold to earn profit of $50,000 -1111 I ] 24 25 The profit if the company sold 20,000 units 27 REQUIRED: 28 1.Create the Excel formulas required for each output listed above. Ue cell references back to 29 Input data. 31 2. If the company had the money to produce only one of the products. What would be your recommendation? Explain. 34 35 38 39 40 41 43 roblem Problem 2Problem Exan#2 Excel Spring2019 Excel Home InsertPage Layout Formlas Data Revieww HelpTell me what you want to do 43 from Sales Department Projected sales in units- qtr. 1 Projected sales in units-qtr.2 1 Projected sales in units-gtr. 3 58,000 40,000 Projected sales in units-gtr.4 ted NOTES: 1.Your completed problem must meet the requ 15 from Production Department 6Desired ending inventory-each quarter 17 18 OUTPUT: 10% ofthe next quarter's sales in units OUTPUT section should refer back to INPUT da 4Be sure to check that your unit of measure is co Total Adjst spacing as needed atr 1 otr, 2 tr, 3 20 25 Production budeet for quarters 1,2, and 3 in UNITS and DOLLARS Provide TOTAL colummns for the three quarters for UNITS and DOLLARSs 2 Prepare a production budget for quarters 1, 2, and 3 in UNITS ONLY. Provide a total colum for the three quarters Use Excel formulas and be sure (1) to use cell references to refer back to INPUT and (2) to link the production budget to the sales budget 6 1. Why is it so important for a company to prepare a prodaction budget? 4 What are two of the assumptions that are "built into" the production budget? 1 NOTES: Your completed problem must meet the requirements below .OUTPUT section should refer back to INPUT data using cell refernces L Be sure to label each item in column A (e.g sales in units) Be sure to check that your unit of measure is correct (e-g units, dolars, etc.) s Adjunt spacing as needed Problem Problerm2