1. Save a copy of your original model to a new spreadsheet called "supplier cost decrease". Say the supplier is expected to decrease 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.

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 900 "Treat-times" and 850

"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.

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: $3,000 per month plus 10% of revenue. Given his original sales volume and mix, how would this contract have changed Jake's operating income? What is the new profit multiplier? 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.

Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) AP 11 General Insert 9X Delete 48- 0 Conditional Formatting Format as Table Cell Styles y Paste OA $ % Ideas Sart & Filter Format v Find & Select Sensitivity 2x A B D E F G H K 3 ASSUMPTIONS Launch-it 4 Launch-it Product 1 Unit CM CM % Product 91 Sales price per unit 4.50 Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 10 45% 5 6 7 8 9 9 Total Variable costs per unit: COGS Shipping and handling Commissions Total variable cost per unit Breakeven point: -in units in sales revenue 1 3. 1.5 5.5 333.33333 3333.3333 Product #1 1000 5500 4500 Product #2 22500 16875 5625 10 2 Sales Variable Expense Contribution Margin Fixed Expenses NOI 11 1 Target profit volume: -in units - in sales revenue 12 13 2111.1111 21111.111 Monthly volume 1000 WACIM% Product 12: Sales price per unit Treat-time 30 Treat-time Product #2 Unit CM CM% 7.5 25% Variable costs per unit: COGS Shipping and handling Commission Total variable cost per unit 7 11 4.5 22.5 Breakeven point: -in units -in sales revenue Total 200 6000 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Contribuion margin per unit Sales Mix Contribution Margin Calculation of Weighted average CM per unit Product #1 Product #2 4.5 7.5 1000 750 4500 5625 Monthly volume 750 1 1 Target profit volume: -in units -in sales revenue 1266.6667 38000 WACM/unit Fixed costs per month: Salaries Entry Fees Total fixed costs per month 1000 500 1500 Product #1 Product #2 Total Target profit per month B,000 Multiproduct Breakeven point: -in units Sales revenue at breakeven 146 1481 111 3333 Expected change in volume (%) 5% 34 I Multiproduct Target profit point: Product #1 Product #2 Total 7 & Total 10 Sales Variable Expanse Contribution Margin Fixed Expenses Produsul 1000 5500 4500 Predst.2 22500 15875 5525 32500 22375 10125 1500 3525 NOI WACM X 31.15 11 12 13 14 15 16 17 18 19 20 21 Tata! Contrition martin per unit Sales ML Contribution Margin Calculation of Weled average CM per unit Product #1 Product 12 4.5 7.5 1000 750 4500 55251 10125 WACM/unit 23 24 25 25 27 28 79 30 31 32 33 Product #1 Tata! Multiprodukt Breakeven point in units Sales revenue at breakeven Pred2 148 1481 111 3333 259 4815 Product #1 Multiproduct Target protit point in units Sales revenue at target profit 938 7906 Product #2 704 17789 Total 1642 25695 37 Margin of Safety in $) 27,690 39 40 41 42 Margin of Safety % 85% Pratit Sensitivity Multipler Expected change in operating income Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) AP 11 General Insert 9X Delete 48- 0 Conditional Formatting Format as Table Cell Styles y Paste OA $ % Ideas Sart & Filter Format v Find & Select Sensitivity 2x A B D E F G H K 3 ASSUMPTIONS Launch-it 4 Launch-it Product 1 Unit CM CM % Product 91 Sales price per unit 4.50 Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 10 45% 5 6 7 8 9 9 Total Variable costs per unit: COGS Shipping and handling Commissions Total variable cost per unit Breakeven point: -in units in sales revenue 1 3. 1.5 5.5 333.33333 3333.3333 Product #1 1000 5500 4500 Product #2 22500 16875 5625 10 2 Sales Variable Expense Contribution Margin Fixed Expenses NOI 11 1 Target profit volume: -in units - in sales revenue 12 13 2111.1111 21111.111 Monthly volume 1000 WACIM% Product 12: Sales price per unit Treat-time 30 Treat-time Product #2 Unit CM CM% 7.5 25% Variable costs per unit: COGS Shipping and handling Commission Total variable cost per unit 7 11 4.5 22.5 Breakeven point: -in units -in sales revenue Total 200 6000 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Contribuion margin per unit Sales Mix Contribution Margin Calculation of Weighted average CM per unit Product #1 Product #2 4.5 7.5 1000 750 4500 5625 Monthly volume 750 1 1 Target profit volume: -in units -in sales revenue 1266.6667 38000 WACM/unit Fixed costs per month: Salaries Entry Fees Total fixed costs per month 1000 500 1500 Product #1 Product #2 Total Target profit per month B,000 Multiproduct Breakeven point: -in units Sales revenue at breakeven 146 1481 111 3333 Expected change in volume (%) 5% 34 I Multiproduct Target profit point: Product #1 Product #2 Total 7 & Total 10 Sales Variable Expanse Contribution Margin Fixed Expenses Produsul 1000 5500 4500 Predst.2 22500 15875 5525 32500 22375 10125 1500 3525 NOI WACM X 31.15 11 12 13 14 15 16 17 18 19 20 21 Tata! Contrition martin per unit Sales ML Contribution Margin Calculation of Weled average CM per unit Product #1 Product 12 4.5 7.5 1000 750 4500 55251 10125 WACM/unit 23 24 25 25 27 28 79 30 31 32 33 Product #1 Tata! Multiprodukt Breakeven point in units Sales revenue at breakeven Pred2 148 1481 111 3333 259 4815 Product #1 Multiproduct Target protit point in units Sales revenue at target profit 938 7906 Product #2 704 17789 Total 1642 25695 37 Margin of Safety in $) 27,690 39 40 41 42 Margin of Safety % 85% Pratit Sensitivity Multipler Expected change in operating income