Please help in EXCEL and show workings. Thanks! #5-10

Solution: Please place your answer and supporting calculations in the space below and either highlight your final answer or surround it with a border so that it can be easily identified by the grader. Problem D (38 points): Weather-Tight sells windows and doors. The following data applies to the company's operating activities: 1 Total Units (products combined) to Break Even: Total 100.000% 2 Units of Each Product to Break Even: Current sales mix in units percentage Selling Price per Unit Variable Cost per Unit Fixed Costs Current Unit Volume Assume all units produced are sold. Windows 82.00% $200 $125 $580,000 65,600 Doors 18.00% $500 $350 $320,000 14,400 3 Total Sales Dollars (products combined) to Break Even: $900,000 80,000 4 Sales Dollars by Product to Break Even: 5 Current Contribution Margin Income Statement at Break Even: Use the space provided below for organized calculations (including things like Current Unit Volume, Wtd CM, Wtd Average CM, Sales Mix, etc. See the narrated lectures for guidance. The detailed questions you are required to answer start on line 40 of this worksheet and are also listed briefly in the SOLUTION space provided starting in 6 Contribution Margin Income Statement at Current Sales: 7 Margin of Safety at Current Sales Level: 8 Contribution Margin Income Statement at the Target Level of Pretax Income: 9 Units of each Product to reach Target Pretax Income: Required: 1 What is the current break-even point in units for both products combined? (2 points) 2 What is the number of units of each product that need to be sold at the current break-even point? (2 points) 3 What is the current break-even point in sales dollars for both products combined? (2 points) 4 How much, in sales dollars, of each product does the company currently need to sell at the break-even point? (2 points) 5 Show the Current Contribution Margin Income Statement at Break-Even. (6 points) 6 Show the Contribution Margin Income Statement at the Current Sales Level. (7 points) 7 What is the Margin of Safety at the Current Sales Level? (3 points) 10 Margin of Safety at Target Sales Level: The Company is working on budgets for next year and plans a 10% increase in Target Pretax Income; a 10% increase in both Selling Price and Variable Cost; and a 20% increase in Fixed Costs, keeping the Current Sales Mix in Dollars 8 Show the Contribution Margin Income Statement at the Target Pretax Income Level. HINT--you need to start at the bottom of the Contribution Margin Income Statement and work your way up to the top 9 What is the Sales Unit Volume of each product needed to achieve the Target Level of Pretax Income? (3 points) 10 What is the Margin of Safety at the Target Sales Level? (3 points) Check Figures: Item 4: Doors = $915,254; Check Figure for Windows is not provided. Item 6: Total Contribution Margin = $7,080,000; Item 8: Windows Pre-Tax Target Income = $4,774,000; Check Figure for Doors is not provided. 24.21% 10.63% Wt Ave CM ratio by unit sales dollars Wt Avg CM ratio for total sales dollars Multiply units needed by selling pric $ 34.84% 1,667,796.61 $ 915,254.24 5 Current Contribution Margin Income Statement at Break Even: Break-Even Sales Less: Variable Costs Contribution Margin Less: Fixed Costs Break-even Pre-Tax $1,667,796.61 1,271,186 396,610 $580,000 (183,390) $ 915,254.24 3,559,322.03 $ (2,644,067.80) $320,000 $ (2,964,067.80) $ 2,583,050.85 4,830,508,47 (2,247,457.63) $900,000 (3,147,457.63) 6 Contribution Margin Income Statement at Current Sales: Required: 1 What is the current break-even point in units for both products combined? (2 points) 2 What is the number of units of each product that need to be sold at the current break-even point? (2 points) 3 What is the current break-even point in sales dollars for both products combined? (2 points) 4 How much, in sales dollars, of each product does the company currently need to sell at the break-even point? (2 points) 5 Show the Current Contribution Margin Income Statement at Break-Even. (6 points) 6 Show the Contribution Margin Income Statement at the Current Sales Level. (7 points) 7 What is the Margin of Safety at the Current Sales Level? (3 points) Current Level Sales Less: Variable Costs Contribution Margin Less: Fixed Costs Current Pre-Tax Income Windows $ 13,120,000 8,200,000 4,920,000 $580,000 $ 4,340,000 Doors $ 7,200,000 5,040,000 $ 2,160,000 $320,000 $ 1,840,000 Total $ 20,320,000 13,240,000 $ 7,080,000 $900,000 $ 6.180.000 $ 7 Margin of Safety at Current Sales Level: The Company is working on budgets for next year and plans a 10% increase in Target Pretax Income; a 10% increase in both Selling Price and Variable Cost; and a 20% increase in Fixed Costs, keeping the Current Sales Mix in Dollars constant. 8 Show the Contribution Margin Income Statement at the Target Pretax Income Level. HINT--you need to start at the bottom of the Contribution Margin Income Statement and work your way up to the top to figure this out. (8 points) 9 What is the Sales Unit Volume of each product needed to achieve the Target Level of Pretax Income? (3 points) 10 What is the Margin of Safety at the Target Sales Level? (3 points) 8 Contribution Margin Income Statement at the Target Level of Pretax Income: Check Figures: Item 4: Doors = $915,254; Check Figure for Windows is not provided. Item 6: Total Contribution Margin = $7,080,000; Item 8: Windows Pre-Tax Target Income = $4,774,000; Check Figure for Doors is not provided. 9 Units of each Product to reach Target Pretax Income: 10 Margin of Safety at Target Sales Level