Module 5 Excel Workbook Assignment MBA 6315 Problem C (50 points): Weather-Tight sells windows and doors. The following data applies to the company's operating activities: Windows Doors Total Current sales mix in units percentage 80.00% 20.00% 100.000% Selling Price per Unit $200 $500 Variable Cost per Unit $125 $350 Fixed Costs $5,80,000 $3,20,000 $9,00,000 Current Unit Volume 64,000 16,000 80,000 Assume all units produced are sold. 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 Column J of this worksheet: Windows Doors Total Current Unit Volume 64,000 16,000 80,000 Windows Doors Total Selling Price per Unit 200 500 Variable Cost per Unit 125 350 Contribution Margin Per Unit 75 150 Sales Mix Weight 80.00% 20.00% Weighted Contribution Margin 60 30 Weighted Average contribution Margin 90 Sales Mix 80.00% 20.00% Weighted selling Price 160 100 260 Windows Doors Total Revised Selling Price 220.00 550.00 Revised Variable Cost 137.50 385.00 Revised Contribution per unit 82.50 165.00 Revised CM Ratio 37.50% 30.00% Required: 1 What is the current break-even point in units for both products combined? (3 points) 2 What is the number of units of each product that need to be sold at the current break-even point? (3 points) 3 What is the current break-even point in sales dollars for both products combined? (3 points) 4 How much, in sales dollars, of each product does the company currently need to sell at the break-even point? (3 points) 5 Show the Current Contribution Margin Income Statement at Break-Even. (8 points) 6 Show the Contribution Margin Income Statement at the Current Sales Level. (8 points) 7 What is the Margin of Safety at the Current Sales Level? (4 points) 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. (10 points) 9 What is the Sales Unit Volume of each product needed to achieve the Target Level of Pretax Income? (4 points) 10 What is the Margin of Safety at the Target Sales Level? (4 points)
© 2015 MSA Homework Help All Rights Reserved
Disclaimer: MSA Homework Help provides reference papers to the student and we strongly recommend you not to submit the papers as it is. Please use our solutions as model answer to improve your skills.