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.
Here is what the questions look like in excel
Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) AP 11 General X * Paste Conditional Formatting Format as Table Cell Styles Insert 9X Delete 48- 0 y OA $ % 68 98 Ideas Sart & Filter Format v Find & Select Sensitivity 2x fx 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% 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 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 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 30 31 32 33 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 Total 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 7 & 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 25 27 28 79 30 31 32 33 34 35 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 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 Margin of Safety in $) 27,690 37 38 39 40 41 42 43 Margin of Safety % 85% Pratit Sensitivity Multipler Expected change in operating income EXCEL HINT: To copy an entire worksheet, right click on the worksheet tab at the bottom of the screen and choose "Move or Copy". Then check the "create a copy" box. Once you have the copy, choose "rename". 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. EXCEL HINT: To copy a cell from a different worksheet, put a + in the cell where you want the number to go, and then go back to the original worksheet, put your cursor on the cell, and then press enter. NEW ORIGINAL Change 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. Operating income Brief explanation: WACM percentage MOSK Operating income 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 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. WACM/unit Units to earn target profit Operating income Brief explanation: 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 expected in the future? Briefly explain your findings to the client. Profit sensitivity multiplier Expected % change in op inc Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) AP 11 General X * Paste Conditional Formatting Format as Table Cell Styles Insert 9X Delete 48- 0 y OA $ % 68 98 Ideas Sart & Filter Format v Find & Select Sensitivity 2x fx 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% 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 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 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 30 31 32 33 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 Total 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 7 & 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 25 27 28 79 30 31 32 33 34 35 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 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 Margin of Safety in $) 27,690 37 38 39 40 41 42 43 Margin of Safety % 85% Pratit Sensitivity Multipler Expected change in operating income EXCEL HINT: To copy an entire worksheet, right click on the worksheet tab at the bottom of the screen and choose "Move or Copy". Then check the "create a copy" box. Once you have the copy, choose "rename". 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. EXCEL HINT: To copy a cell from a different worksheet, put a + in the cell where you want the number to go, and then go back to the original worksheet, put your cursor on the cell, and then press enter. NEW ORIGINAL Change 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. Operating income Brief explanation: WACM percentage MOSK Operating income 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 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. WACM/unit Units to earn target profit Operating income Brief explanation: 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 expected in the future? Briefly explain your findings to the client. Profit sensitivity multiplier Expected % change in op inc
