Instructions:

L1 L2 L3 L4 L5 L6 L7 L8 L9 20 21 'n' A B C D 2. Spaces for your answers are indicated by cells highlighted in yellow, e.g., => Do not modify or enter any foruInulas in cells highlighted in these colors => 3. Put the answers in the appropriate cells. You may reference another cell that contains the results of a calculation. I: : A B C D m 1 Part 1: High Low Calculations 2 3 Use the data in the following table to answer 4 questions regarding the production of a product Xnew. 6 Month Labor Hours Mfg. OH Costs 7 January 4,738 $ 399,684 8 February 4,953 408,965 9 March 4,883 403,815 10 April 5,677 457,656 11 May 4,465 390,263 12 June 5,505 451,085 13 July 4,135 363,054 14 August 5,743 459,855 15 September 5,084 423,221 16 October 5,109 412,494 17 November 3,797 336,453 18 December 3,875 358,638 19 20 Q1. Use the high low method to estimate the amount 21 of variable overhead per labor hour. 22 Place your answer in highlighted cell.25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 4o 41 42 43 44 45 46 47 48 A B C Q2. Use the high low method to estimate the amount of xed overhead per month. Place your answer in highlighted cell. Additional data about Xnew is given below: : Sales price per unit oanew Direct Labor costs per unit oanew Xnew uses 1/2 hour of labor per unit Direct Material costs per unit oanew Variable Sales and Administrative costs per unit oanew Fixed Sales and Administrative costs traceable to Xnevv per month $ 210.00 $ 22.50 $ 40.20 $ 52.00 $ 102,000 Q3. What is the estimated contribution margin of a unit oanew? Q4. What is the monthly break-even number of units? Q5. What is the expected profit if 8,600 units are produced and sold? [Before taxes] Q6. Ifthe tax rate is 20% how many units need to be sold in a month to earn $310,000 after tax? 50 Q7. If the cost of materials increases by $1.50 per 51 unit what is the new break-even point? 52 53 Q8. What is the expected total profit if discounting the 54 price by 8% increases expected sales to 9,500 units? 55 Use before tax profits for this question. 56A B C D E F 1 Part 2 Decision Making 2 Holland Alternator manufactures replacement alternators used 3 in high performance automobiles. Financial data for FY2021 4 is reported below. Treat each of the questions independently of 5 one another except where noted. Taxes do not need to be considered 6 in your calculations. 7 8 Data 9 Manufacturing 10 Materials $37.00 per unit 11 Labor $41.00 per unit 12 Variable Overhead $70.00 per unit 13 Fixed Overhead $916,667 annually 14 Marketing and Administrative 15 Variable Cost $29.00 per unit 16 Fixed Costs $566,667 annually 17 Sales Price (Average) $263.33 per unit 18 Expected Sales (Production) Volume 18,000 annually 19 Maximum Sales (Production) Volume 24,000 annually 20 Actual Sales (Production) Volume in FY2021 19,540 annually 21 22 Q9. Determine the contribution margin for a single $86.33 23 unit of the product.A B C D E F G 17 Sales Price (Average) $263.33 per unit 18 Expected Sales (Production) Volume 18,000 annually 19 Maximum Sales (Production) Volume 24,000 annually 20 Actual Sales (Production) Volume in FY2021 19,540 annually 21 22 Q9. Determine the contribution margin for a single $86.33 23 unit of the product. 24 25 Q10. Determine Holland's profit for FY2021. 26 27 Q11. Assuming no changes in costs for FY2022, what 28 would be Holland's profit if they reduced prices by 29 5% and increased volume by 7% over actual FY2021 30 volume? 31 32 Q12. Holland is forecasting annual sales of 20,000 33 units in FY2022. A foreign automaker has asked Holland 34 to supply 5,000 alternators to be used as an OEM part. 35 The automaker is offering a price of $210 each. Holland 36 does not expect any changes to its cost structure if it 37 supplies the parts. Calculate the change in Holland's 38 profits if it accepts the offer. A positive number 39 should indicate an increase in profits. Note that accepting the order 40 may require Holland to reduce its regular sales