Please show step by step in Excel Build spreadshast modals to answer the followi
ID: 2535907 • Letter: P
Question
Please show step by step in Excel
Build spreadshast modals to answer the following questions All work must be shown The following annual information is known about a company: Material inputs to marofacturin Management salaries Supplies needed for marnfacuring Uitilithes used in tomproduction # of unts sold 17,400,000 52.750.000 58.345.000 53.500,000 59.990.000 S6:235.000 2,100,000 Marcting and advertising Production worker mages Utilite used in production activities Property taxes Czit price 5,132 a Calculate the tant variable cost total se cost· tant contribution ttm m contr utson fate, net mcome and break-eren m both units and dollara b. The marketing research manager shows that if the compan lovers ts price br 5%, unt sales will rise by 5%. Should the company ower its price? Justify your decision c. au e a he pflee re nam ed ? ?? ne ue to economic resu res fixe¢ costs are recasted to me br 6% and var able cos a v 496 text vear At the a me eve of ou ut b at per e tage m ust the aethn price fise to mB 1 e 8 me le el oftet 1tico ne achte e n a)Explanation / Answer
Answer 1
Breakeven Point (in Units) = Fixed Cost / Contribution Margin per unit = 10,572,000 / 190.38 = 55,531.04 ~ 55,531 Units
Breakeven Point (in $ ) = Fixed Cost / Contribution Margin (%) = 10,572,000 / 25.42% = $ 41,592,059
Calculation of Net Income
Answer 2
Revised Sales in Units = 75,132+5% = 78,889
Revised Unit selling Price = 749-5% = 711.55
Since reduction in selling price results in reduction of profit, the company should not lower its prices.
Answer 3
Revised Fixed Cost = 10,572,000+6%= 11,206,320
Revised Variable Cost = 558.62 +4% = 580.96
Variable Cost Direct Material 1,74,00,000 Supplies for manufacturing 83,45,000 Utilities used in production activities 62,35,000 Production Worker Wages 99,90,000 Total Variable Cost 4,19,70,000 No. of Units Sold 75,132 Variable Cost per unit 558.62