Launch-it $ Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 6.00 60% Total Calculation of Weighted average CM per unit Product #1 Product #2 1,200.00 $ 1,200.00 $ 200 100 $ 2,400.00 300 ASSUMPTIONS Product #1: Sales price per unit Variable costs per unit: Commision Launch cost per unit Shipping and handling Total variable cost per unit Product #1 Launch-it Unit CM $ 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 $ $ Product #1 Product #2 2,000.00 $ 3,000.00 $ S 6.00 $ 12.00 $ 8.00 250.00 2,500.00 | Sales revenue $ minus variable costs: Commisions $ 1,583.33 | Shipping and handling $ 15,833.33 | Purchase cost $ Total Variable costs $ $ 200.00 S 400.00 $ 200.00 $ 800.00 $ 300.00 $ 800.00 $ 700.00 $ 1,800.00 $ Monthly volume $ Total Contribution Margin Sales units Total Contribution Margin 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 Multiproduct Target profit point: 1,500.00 Units Target profit 900.00 Price per unit Sales revenue at target profit 48% Contribution Margin S $ 300.00 188.00 $ 1,200.00 $ 1,200.00 S Treat-time $ Product #1 $ $ $ $ Product #2: Sales price per unit Variable costs per unit: Commison Treat cost per unit Shipping and handling Total variable cost per unit Treat-time $ 30.00 ||Product #2 Unit CM S 3.00 CM % $ 7.00 Breakeven point: $ 8.00 in units $ 18.00 -in sales revenue Product #2 $ 100.00 $ 63.00 $ 30.00 $ 1,890.00 200.00 125.00 10.00 1,250.00 12.00 40% | Fixed costs: $ 3,140.00 Entry fees 125.00 || Salary 3,750.00 || Total fixed costs: $ s $ $ $ Total Product #1 $ S $ 200.00 792.00 10.00 7,920.00 Product #2 S 100.00 $ 396.00 $ 30.00 $ 11,880.00 $ $ $ 300.00 1,188.00 40.00 Monthly volume $ $ Operating income 791.67 23,750.00 WACM% S $ 19,800.00 S $ 100.00 || Target profit volume: -in units -in sales revenue $ 1,000.00 $ 500.00 $ 1,500.00 Fixed costs per month: Salary Entry fee Total fixed costs per month Margin of Safety (in $) $ 1,860.00 Target profit per month $ 8,000.00 Margin of Safety % 37.209 Expected change in volume (%) 5.00% Operating Leverage Factor $ 2.67 Expected % change in operating income (%) 13.33% Once you have built the model, use it to answer Jake's questions about his business. Treat each situation as a separate scenario. All comparisons should be made to the original assumptions. NEW Change ORIGINAL $ 900.00 Operating income Brief explanation: 1. 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. WACM percentage 48% MOS% 38% Operating income $ 900.00 Brief explanation: 2. Save a copy of your original model to a new spreadsheet called "new sales mix". Say the monthly sales volume is now expected to be 175 "Treat-times" and 125 "Launch-its" (same total units, but a different sales mix). What is the new operating income? What is the new WACM/unit? Given this sales mix, how many units (in total) will Jake need to sell to earn his target profit? Briefly explain your findings to the client. WACM/unit $ 8.00 Units to earn target profit Operating income $ 900.00 Brief explanation: Operating leverage factor $ 2.67 3. Save a copy of your original model to a new spreadsheet called "alternative contract". Say Jake's employee wanted to negotiate a different work contract: $1,500 per month plus 5% of revenue. Given his original sales volume and mix, how would this contract have changed Jake's operating income? What is the new operating leverage factor? What is the new expected percentage change in operating income if volume increases as expected in the future? Briefly explain your findings to the client. Expected % change in op inc $ 13.33 Launch-it $ Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 6.00 60% Total Calculation of Weighted average CM per unit Product #1 Product #2 1,200.00 $ 1,200.00 $ 200 100 $ 2,400.00 300 ASSUMPTIONS Product #1: Sales price per unit Variable costs per unit: Commision Launch cost per unit Shipping and handling Total variable cost per unit Product #1 Launch-it Unit CM $ 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 $ $ Product #1 Product #2 2,000.00 $ 3,000.00 $ S 6.00 $ 12.00 $ 8.00 250.00 2,500.00 | Sales revenue $ minus variable costs: Commisions $ 1,583.33 | Shipping and handling $ 15,833.33 | Purchase cost $ Total Variable costs $ $ 200.00 S 400.00 $ 200.00 $ 800.00 $ 300.00 $ 800.00 $ 700.00 $ 1,800.00 $ Monthly volume $ Total Contribution Margin Sales units Total Contribution Margin 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 Multiproduct Target profit point: 1,500.00 Units Target profit 900.00 Price per unit Sales revenue at target profit 48% Contribution Margin S $ 300.00 188.00 $ 1,200.00 $ 1,200.00 S Treat-time $ Product #1 $ $ $ $ Product #2: Sales price per unit Variable costs per unit: Commison Treat cost per unit Shipping and handling Total variable cost per unit Treat-time $ 30.00 ||Product #2 Unit CM S 3.00 CM % $ 7.00 Breakeven point: $ 8.00 in units $ 18.00 -in sales revenue Product #2 $ 100.00 $ 63.00 $ 30.00 $ 1,890.00 200.00 125.00 10.00 1,250.00 12.00 40% | Fixed costs: $ 3,140.00 Entry fees 125.00 || Salary 3,750.00 || Total fixed costs: $ s $ $ $ Total Product #1 $ S $ 200.00 792.00 10.00 7,920.00 Product #2 S 100.00 $ 396.00 $ 30.00 $ 11,880.00 $ $ $ 300.00 1,188.00 40.00 Monthly volume $ $ Operating income 791.67 23,750.00 WACM% S $ 19,800.00 S $ 100.00 || Target profit volume: -in units -in sales revenue $ 1,000.00 $ 500.00 $ 1,500.00 Fixed costs per month: Salary Entry fee Total fixed costs per month Margin of Safety (in $) $ 1,860.00 Target profit per month $ 8,000.00 Margin of Safety % 37.209 Expected change in volume (%) 5.00% Operating Leverage Factor $ 2.67 Expected % change in operating income (%) 13.33% Once you have built the model, use it to answer Jake's questions about his business. Treat each situation as a separate scenario. All comparisons should be made to the original assumptions. NEW Change ORIGINAL $ 900.00 Operating income Brief explanation: 1. 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. WACM percentage 48% MOS% 38% Operating income $ 900.00 Brief explanation: 2. Save a copy of your original model to a new spreadsheet called "new sales mix". Say the monthly sales volume is now expected to be 175 "Treat-times" and 125 "Launch-its" (same total units, but a different sales mix). What is the new operating income? What is the new WACM/unit? Given this sales mix, how many units (in total) will Jake need to sell to earn his target profit? Briefly explain your findings to the client. WACM/unit $ 8.00 Units to earn target profit Operating income $ 900.00 Brief explanation: Operating leverage factor $ 2.67 3. Save a copy of your original model to a new spreadsheet called "alternative contract". Say Jake's employee wanted to negotiate a different work contract: $1,500 per month plus 5% of revenue. Given his original sales volume and mix, how would this contract have changed Jake's operating income? What is the new operating leverage factor? What is the new expected percentage change in operating income if volume increases as expected in the future? Briefly explain your findings to the client. Expected % change in op inc $ 13.33