PLEASE SHOW WITH CALCULATION BY USING FORMULA CELLS

THANK YOU

Copy Send BIU UAE Format Painter Merge & Center Clipboard Bluetooth Font Alignment f. CVP and Sensitivity Analysis, Resource Constraint (Multiple Products) A B C E F G H CVP and Sensitivity Analysis, Resource Constraint (Multiple Products) Hobby Shop Incorporated produces three different models with the following annual data (this is the base case) Total Selling price per unit Variable cost per unit Expected unit sales Sales mix % Fixed costs Plane 20 5 30,000 30% Car 14 7 50,000 50% Boat 24 8 20,000 20% 100,000 100% 650,000 Assume the sales mix remains the same at all levels of sales except for requirements i and Required: Sa Calculate the weighted average contribution margin per unit 5 b How many units in total must be sold to break even? 5 How many units of each product must be sold to break even? 7 How many units in total must be sold to earn an annual profit of $500,000? 3 How many units of each product must be sold to earn an annual profit of $500,000? 9 d Using the base case information, prepare a contribution margin income statement for the year 0. Calculate the weighted average contribution margin ratio. 17 Find the break-even point in sales dollars. What amount of sales dollars is required to earn an annual profit of $400,000? h Go back to the base case contribution margin income statement prepared in requirement d. What would the operating profit be if the plane sales price 24 (1) increases 10 percent, or 25 (2) decreases 10 percent? (Assume total sales remains at 100,000 units.) Go back to the base case contribution margin income statement prepared in requirement d. If the sales mix shifts more toward the Car product than to the other two products, would the break-even 26 point in units increase or decrease? (Detailed calculations are not necessary.) Explain. Assume the company has a limited number of labor hours available in production, and management would like to make efficient use of these labor hours. The Plane product requires 4 labor hours per unit, the Car product requires 3 labor hours per unit, and the Boat product requires 5 hours per unit. The company sells everything it produces. Based on this information, calculate the contribution margin per labor hour for each model fround to the nearest cent), and determine the top two models the company would prefer to sell to maximize overall company profit. 27 28 1 30 31 32 0.27229 6.25 Extra Credit