Question

Mark from Mark's Mowers wants to make some changes to his business. He has asked each of his department managers (Production, Marketing and Sales) to submit a plan for growth to you the General Manager.

Currently Mark is selling 500 lawn mowers a month at $250 each. His variable cost per lawnmower is $180 each. His fixed cost per month are $ 25,000.

For purposes of this project assume that each scenario is within the relevant range (no larger space is needed, no additional people will be needed, and no additional fixed cost will be necessary.  

Question 1. What is the current income / loss that Mark is incurring per month?  

Question 2. What is Mark's current breakeven in units?

Question 3. What is Mark's current breakeven in dollars?  

The Production Department is considering three different alternatives.  

1. Switch to better raw materials which would increase Variable cost by 10% per unit. They believe this would increase brand awareness and consequently increase unit sales by at least 12.5%  

2. Switch to lower quality materials and become a "price conscience" distributer. The amount that the Production department felt best was to decrease Variable cost by 20%. They believe that this would likely reduce unit sales by no more than 20%.

3. Finally, the Production Department thought a combination of lower quality materials reducing variable costs by 10% combined with a lower retail price of $195 per mower would increase unit sales by at least 25%.  

Question 4. How much money will Mark make in each of the separate situations?  

Question 5. Which of the scenarios is best and why?  

The Marketing Department is considering three different alternatives.  

1. Do $5,000 in advertising. The Marketing department believe this would increase sales by at least 35 units.  

2. Do $ 10,000 in advertising. The Marketing department believe this would increase sales by at least 65 units.

3. Do $ 6,000 in advertising combined with a "sales" price of $225 (a 10% discount). The believe that this would increase sales by at least 50 units.

Question 6. How much money will Mark make in each of the separate situations?  

Question 7. Which of the scenarios is best and why?  

The Sales Department is also considering three different alternatives.  

1. Offer a commission of $ 5 per mower to the sales force. They believe that this will increase sales by at least 20 units.  

2. Offer a commission to the sales force of $15 per mower. They believe that this will increase sales by at least 35 units per month.  

3. Offer a commission of $ 25 per mower and increase the cost of the mower by $10. Even with the higher price, they believe it would increase mower sales by at least 40 mowers per month.  

Question 8. How much money will Mark make in each of the separate situations?

Question 9. Which of the scenarios is best and why?  

Question 10. As the General manager of Mark's Mowers which of the above scenarios do you this is best going forward and why?

this project it has to be done in EXCEL and that is what I am really struggling with.

Answer 1

1. Current income per month = 500 * $ ( 250 - 180 ) - $ 25,000 = $ 10,000.

2. Current break-even in units = Fixed Cost per Month / Contribution Margin per Unit = $ 25,000 / $ 70 = 357.14 units

3. Current break-even in dollars = ( $ 25,000 / $ 70 ) * $ 250 = $ 89,285.71

4.

	Alt. 1	Alt. 2	Alt. 3
Sales Revenue	$ 140,750	$ 100,000	$ 121,875
Variable Costs	111,474	57,600	101,250
Contribution Margin	29,276	$ 42,400	$ 20,625
Fixed Cost	25,000	25,000	25,000
Net Income	$ 4,276	$ 17,400	$ ( 4,375)

5. Alternative 2 is the best scenario.

Original contribution ratio = $ ( 250 - 180) / $ 250 = 0.28 or 28 %

Contribution margin ratio for Alternative 2 = $ 42,400 / $ 100,000 = 42.40 %

Break-even point for Alternative 2 = $ 25,000 / $ ( 250 - 144) =235.85 units

Therefore, Alternative 2 is the best scenario because:

a. Net income is higher

b. Contribution margin ratio is higher

c. BEP is much lower than 357.14 units.