Save a copy of your original model to a new spreadsheet called supplier cost increase. Say...

   

Transcribed Image Text:

Save a copy of your original model to a new spreadsheet called "supplier cost increase". Say the supplier is expected to increase the cost of the products by 20%. What is the new operating income? What is the new WACM%? What is the new MOS%? Briefly explain your findings to the client. Operating income WACM percentage MOS% NEW ORIGINAL $ 900.00 48% 38% Change ASSUMPTIONS Product #1: Sales price per unit Variable costs per unit: Commision Launch cost per unit Shipping and handling Total variable cost per unit Monthly volume Product #2: Sales price per unit Variable costs per unit: Commison Treat cost per unit Shipping and handling Total variable cost per unit Monthly volume Fixed costs per month: Salary Entry fee Total fixed costs per month Target profit per month Expected change in volume (%) Product #1 Unit CM Launch-it $ 10.00 CM % Breakeven point: $ 1.00 -in units $ 1.00 -in sales revenue $ 2.00 $ 4.00 Target profit volume: -in units $ 200.00 -in sales revenue Treat-time $ 30.00 Product #2 Unit CM $ 3.00 CM % $ 7.00 Breakeven point: $ 8.00 -in units $ 18.00 -in sales revenue $ 100.00 Target profit volume: -in units -in sales revenue $ 1,000.00 $ 500.00 $ 1,500.00 $ 8,000.00 5.00% Launch-it $ $ $ $ $ Treat-time $ $ S $ S Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 Product #1 Product #2 2,000.00 $ 3,000.00 $ $ Sales revenue minus variable costs: Commisions $ Shipping and handling $ Purchase cost $ Total Variable costs $ 200.00 $ 400.00 $ 200.00 $ 800.00 $ 300.00 $ 800.00 $ 700.00 $ 1,800.00 $ Contribution Margin $ 1,200.00 S 1,200.00 $ Fixed costs: Entry fees $ Salary $ Total fixed costs: $ Operating income 6.00 60% 250.00 2,500.00 1,583.33 15,833.33 12.00 40% 125.00 3,750.00 791.67 23,750.00 WACM% $ Contribution Margin Sales units Contribution Margin Total 5,000.00 WACM/Unit 500.00 1,200.00 900.00 2,600.00 Multiproduct Breakeven point: Units 2,400.00 Breakeven units Price per unit Sales revenue at breakeven 500.00 1,000.00 1,500.00 Multiproduct Target profit point: Units Target profit 900.00 Price per unit Sales revenue at target profit 48% $ Calculation of Weighted average CM per unit Product #1 S Product #1 $ $ $ Product #2 1,200.00 $ 1,200.00 $ 200 100 6.00 $ 12.00 $ 100.00 Product #2 200.00 $ $ 125.00 $ 63.00 $ 10.00 $ 30.00 1,250.00 $ 1,890.00 $ Product #2 200.00 $ 792.00 $ 10.00 $ 7,920.00 $ 100.00 S 396.00 $ 30.00 $ 11,880.00 $ $ 1,860.00 37.20% $ 2.67 13.33% S Product #1 $ $ $ $ Margin of Safety (in $) Margin of Safety % Operating Leverage Factor Expected % change in operating income (%) Total Total Total 2,400.00 300 8.00 300.00 188.00 3,140.00 300.00 1,188.00 40.00 19,800.00 Save a copy of your original model to a new spreadsheet called "supplier cost increase". Say the supplier is expected to increase the cost of the products by 20%. What is the new operating income? What is the new WACM%? What is the new MOS%? Briefly explain your findings to the client. Operating income WACM percentage MOS% NEW ORIGINAL $ 900.00 48% 38% Change ASSUMPTIONS Product #1: Sales price per unit Variable costs per unit: Commision Launch cost per unit Shipping and handling Total variable cost per unit Monthly volume Product #2: Sales price per unit Variable costs per unit: Commison Treat cost per unit Shipping and handling Total variable cost per unit Monthly volume Fixed costs per month: Salary Entry fee Total fixed costs per month Target profit per month Expected change in volume (%) Product #1 Unit CM Launch-it $ 10.00 CM % Breakeven point: $ 1.00 -in units $ 1.00 -in sales revenue $ 2.00 $ 4.00 Target profit volume: -in units $ 200.00 -in sales revenue Treat-time $ 30.00 Product #2 Unit CM $ 3.00 CM % $ 7.00 Breakeven point: $ 8.00 -in units $ 18.00 -in sales revenue $ 100.00 Target profit volume: -in units -in sales revenue $ 1,000.00 $ 500.00 $ 1,500.00 $ 8,000.00 5.00% Launch-it $ $ $ $ $ Treat-time $ $ S $ S Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 Product #1 Product #2 2,000.00 $ 3,000.00 $ $ Sales revenue minus variable costs: Commisions $ Shipping and handling $ Purchase cost $ Total Variable costs $ 200.00 $ 400.00 $ 200.00 $ 800.00 $ 300.00 $ 800.00 $ 700.00 $ 1,800.00 $ Contribution Margin $ 1,200.00 S 1,200.00 $ Fixed costs: Entry fees $ Salary $ Total fixed costs: $ Operating income 6.00 60% 250.00 2,500.00 1,583.33 15,833.33 12.00 40% 125.00 3,750.00 791.67 23,750.00 WACM% $ Contribution Margin Sales units Contribution Margin Total 5,000.00 WACM/Unit 500.00 1,200.00 900.00 2,600.00 Multiproduct Breakeven point: Units 2,400.00 Breakeven units Price per unit Sales revenue at breakeven 500.00 1,000.00 1,500.00 Multiproduct Target profit point: Units Target profit 900.00 Price per unit Sales revenue at target profit 48% $ Calculation of Weighted average CM per unit Product #1 S Product #1 $ $ $ Product #2 1,200.00 $ 1,200.00 $ 200 100 6.00 $ 12.00 $ 100.00 Product #2 200.00 $ $ 125.00 $ 63.00 $ 10.00 $ 30.00 1,250.00 $ 1,890.00 $ Product #2 200.00 $ 792.00 $ 10.00 $ 7,920.00 $ 100.00 S 396.00 $ 30.00 $ 11,880.00 $ $ 1,860.00 37.20% $ 2.67 13.33% S Product #1 $ $ $ $ Margin of Safety (in $) Margin of Safety % Operating Leverage Factor Expected % change in operating income (%) Total Total Total 2,400.00 300 8.00 300.00 188.00 3,140.00 300.00 1,188.00 40.00 19,800.00

Answer rating: 100% (QA)

Assumptions Product 1 Sales per unit Variable per unit Product Cost Shipping Sales Commission Total ... View the full answer