Module 5 Excel Workbook Assignment
MBA 6315
Problem A (20 points):
Salmon Inc. sold 21,000 units of its only product and incurred a $85,000 loss (ignoring
taxes) for the current year as shown here. During a planning session for year 2017’s
activities, the productions manager notes that variable costs can be reduced 50% by
installing a machine that automates several operations, To obtain these savings, the
company must increase its annual fixed costs by $150,000. The maximum output capacity
of the company is 40,000 units per year.
Salmon Company
Contribution Margin Income Statement
For the Year Ending December 31, 2016
Sales
Variable Costs
Contribution Margin
Fixed Costs
Pre-Tax Loss
$ 1,575,000
1,260,000
315,000
400,000
$
(85,000)
Required:
1 Compute the break-even point in dollar sales for year 2016. (2 points)
2
Compute the predicted break-even point in dollar sales for year 2017 assuming the
machine is installed and no change occurs in the unit selling price. (Round all numbers to
the nearest dollar.) (3 points)
3
Prepare a forecasted contribution margin income statement for 2017 that shows the
expected results with the machine installed. Assume that the unit selling price and the
number of units sold will not change, and no income taxes will be due. (5 points)
4
Compute the sales level required in both dollars and units to earn $500,000 of target
pretax income in 2017 with the machine installed and no change in unit sales price.
(Round all numbers to the nearest dollar and to whole units.) (5 points)
5 Prepare a forecasted contribution margin income statement that shows the results at the
sales level computed in part 4. Assume no income taxes will be due. (5 points)
Solution: Please place
the space below and e
it with a border so tha
1
2
3
4
Check Figures: Item 3: Pre-Tax Income = $395,000; Item 5: Contribution Margin = $1,050,000
5
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.
Break-Even Point in Dollar Sales for 2016:
Predicted Break-Even Point in Dollar Sales for 2017:
Forcasted Contribution Margin Income Statement for 2017:
Sales level required in Dollars for 2017 to earn target pretax income of $500,000:
Sales level required in Units for 2017 to earn target pretax income of $500,000:
Forecasted Contribution Margin Income Statement at Sales Level computed in Item 4:
Module 5 Excel Workbook Assignment
MBA 6315
Solution: Please place your
the space below and either
it with a border so that it ca
Problem B (30 points):
EasyBake Company is able to produce two toy products, a Little Oven and a Big Oven,
with the same machine in its factory. NOTE that only ONE Oven can be manufactured
at a time using this machine. The Company currently makes both ovens, but
management is concerned that this strategy is not providing maximum benefit. They
are thinking of adding a second shift and have asked you to figure out their best
course of action. The following information is available:
1 Contribution Margin by
EasyBake Data:
2 Units of each Oven that
Contribution Margin, as
Selling price per unit
Variable costs per unit
Contribution margin per unit
Machine hours to produce ONE unit
Maximum customer demand for each
Oven per month (max they can sell)
Little Oven
110
30
80
0.4 hours
Big Oven
150
80
70
1.0 hours
550 units
200 units
3 Total Contribution Marg
The company presently operates the machine for a single eight-hour shift for 22
working days each month. Management is thinking about operating the machine for
two shifts, which will increase its productivity by another eight hours per day for 22
days per month. This change would require $15,000 additional fixed costs per month.
Required (NOTE–show all calculations for full credit):
1 Determine the contribution margin per machine hour that each product
generates. (4 points)
2
How many units of the Little Oven and the Big Oven should the company produce
if it continues to operate with only one shift? Assume all ovens produced are sold.
(4 points)
3 How much total contribution margin does this sales mix produce each month? (4
points)
4 If the company adds another shift, how many units of the Little Oven and the Big
Oven should it produce? (4 points)
5 How much total contribution margin would this mix produce each month? (4 points)
6 Should the company add the new shift? (4 points)
7 Suppose that, based on market research, the company determines that it can
increase the Little Oven’s maximum sales to 650 units per month by spending
$12,000 per month in marketing efforts, which is a fixed cost. Should the company
pursue this strategy and the double shift? Provide support for your answer. (6
points)
NOTE that this problem is NOT asking you to calculate Break-Even.
Check Figures: Item 4: Big Ovens = 132 Units; No check figure provided for the Little…
Item 7: Little Ovens Contribution Margin = $52,000
4 Units of each Oven that
Contribution Margin, as
5 Total Contribution Marg
6 Should the company ad
7 Should the company ad
second shift? Why or wh
ution: Please place your answer and supporting calculations in
e space below and either highlight your final answer or surround
with a border so that it can be easily identified by the grader.
Contribution Margin by Machine Hour for each Oven:
Units of each Oven that company should produce to maximize
Contribution Margin, assuming only one production shift:
Total Contribution Margin resulting from solution to Item 2:
Units of each Oven that company should produce to maximize
Contribution Margin, assuming TWO production shifts:
Total Contribution Margin resulting from solution to Item 4:
Should the company add the second shift? Why or why not?
Should the company add the advertising campaign and the
second shift? Why or why not? Show all relevant calculations.
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:
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
80.00%
$200
$125
$580,000
64,000
Doors
20.00%
$500
$350
$320,000
16,000
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:
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)
Check Figures: Item 4: Doors = $1,000,000; Check Figure for Windows is not provided.
Item 6: Total Contribution Margin = $7,200,000;
Item 8: Windows Pre-Tax Target Income = $4,642,000; Check Figure for Doors is not provided.
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.
1 Total Units (products combined) to Break Even:
Total
100.000%
$900,000
80,000
2 Units of Each Product to Break Even:
, Wtd CM, Wtd Average
to answer start on line
J of this worksheet:
nt? (3 points)
ak-even point? (3 points)
3 Total Sales Dollars (products combined) to Break Even:
4 Sales Dollars by Product to Break Even:
5 Current Contribution Margin Income Statement at Break Even:
6 Contribution Margin Income Statement at Current Sales:
7 Margin of Safety at Current Sales Level:
e; a 10% increase in both
Dollars constant.
need to start at the
e this out. (10 points)
ome? (4 points)
8 Contribution Margin Income Statement at the Target Level of Pretax Income:
9 Units of each Product to reach Target Pretax Income:
10 Margin of Safety at Target Sales Level:
