Launch-it Launch-it 110 6.00 60% Product #1 Unit CM CM% Breakeven point: -in units in sales revenue Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 ASSUMPTIONS Product #1: Sales price per unit Variable costs per unit: Supplies Variable Selling cust (10% 101 Shipping and Handling Total Variable Custs Contribution Margin Monthly Volume $ S 250.00 2.500.00 Product #1 Total 1 2000l Product #2 3000 1800 1200 2 4 sales Less: varible cost Contribution Margin Less: Fixed Cost Net Income 800 1200 Target profit volume: -in units in sales revenue 1 $ $ 5000 2600 2400 15001 900 1,583.33 9,600.00 200 IWACM% 28.50 19.20% 48% Treat-time 30 Treat-time 12.00 40.00 I Product #2: Sales Price Per Unit Variable costs per unit Supplies Variable Selling cost (10%*30) Shipping and Handling Total Variable Custs Contribution Margin Monthly volume Product #2 Unit CM CM% Breakeven point: -in units -in sales revenue Total $ $ 125.00 3,750.00 Calculation of Weighted average CM per unit Product #1 Product #2 2000|| 3000 800 1800 1200 1200 Sales Less: Variable Costs Contribution Margin 18 12 100 50001 2600 24001 Target prolit volume: in units in sales revenue WACM/unit S IS 1,583.33 9.500.00 Fixed costs per month Employee Cost Trade Show entry Fees Total fixed costs per month 1000 500 1500 Product #2 Multiproduct Breakeven point: in units Sales revenue at breakeven Product #1 125 1250 Total 188 3125 Target profit per month 8000 1875 Expected change in volume (%) 5% Multiproduct Target profit point: in units Sales revenue at target profit Product #1 792 7917 Product #2 396 11825 Total 1188 1 0792 Margin of Safety (in %) 1875 Margin of Safety % 38% Operating Leverage Factor 26 Expected % change in operating income (%) nicki scott - 3 Share 0 Comment: E B AutoSave On Summer 2019 Act 212CVP Excel project1. Read-Only - Excel Search File Home Insert Draw Page Layout Formulas Data Review View Help Shape Format IX Cut Calibri (Body - 11 AA === 23 Wrap Test Paste Copy Format Painter BIU - A Morgo & Contar $ -% % 98-98 Clipboards Aign 5 Number TextBox 1 f A B C D E F G H Call Sort & Conditional Formatas Forg e Insert Delete Format Find & Selart Ideas Clear Calls oling M N O P Q R S 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". O 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 in the cell where yo want the number to go, and then go back to the original worksheet, put your curs on the cell to select it, and then press enter. NEW ORIGINAL Change. 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 WACMX? What is the new MOS%? Briefly explain your findings to the client. WACM percentage MOS% Operating income Brief explanation: 19 2. Save a copy of your original model to a new spreadsheet called "new sales mis". 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 21 Units to earn target profit Operating income Brief explanation: Operating leverage factor 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 Launch-it Launch-it 110 6.00 60% Product #1 Unit CM CM% Breakeven point: -in units in sales revenue Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 ASSUMPTIONS Product #1: Sales price per unit Variable costs per unit: Supplies Variable Selling cust (10% 101 Shipping and Handling Total Variable Custs Contribution Margin Monthly Volume $ S 250.00 2.500.00 Product #1 Total 1 2000l Product #2 3000 1800 1200 2 4 sales Less: varible cost Contribution Margin Less: Fixed Cost Net Income 800 1200 Target profit volume: -in units in sales revenue 1 $ $ 5000 2600 2400 15001 900 1,583.33 9,600.00 200 IWACM% 28.50 19.20% 48% Treat-time 30 Treat-time 12.00 40.00 I Product #2: Sales Price Per Unit Variable costs per unit Supplies Variable Selling cost (10%*30) Shipping and Handling Total Variable Custs Contribution Margin Monthly volume Product #2 Unit CM CM% Breakeven point: -in units -in sales revenue Total $ $ 125.00 3,750.00 Calculation of Weighted average CM per unit Product #1 Product #2 2000|| 3000 800 1800 1200 1200 Sales Less: Variable Costs Contribution Margin 18 12 100 50001 2600 24001 Target prolit volume: in units in sales revenue WACM/unit S IS 1,583.33 9.500.00 Fixed costs per month Employee Cost Trade Show entry Fees Total fixed costs per month 1000 500 1500 Product #2 Multiproduct Breakeven point: in units Sales revenue at breakeven Product #1 125 1250 Total 188 3125 Target profit per month 8000 1875 Expected change in volume (%) 5% Multiproduct Target profit point: in units Sales revenue at target profit Product #1 792 7917 Product #2 396 11825 Total 1188 1 0792 Margin of Safety (in %) 1875 Margin of Safety % 38% Operating Leverage Factor 26 Expected % change in operating income (%) nicki scott - 3 Share 0 Comment: E B AutoSave On Summer 2019 Act 212CVP Excel project1. Read-Only - Excel Search File Home Insert Draw Page Layout Formulas Data Review View Help Shape Format IX Cut Calibri (Body - 11 AA === 23 Wrap Test Paste Copy Format Painter BIU - A Morgo & Contar $ -% % 98-98 Clipboards Aign 5 Number TextBox 1 f A B C D E F G H Call Sort & Conditional Formatas Forg e Insert Delete Format Find & Selart Ideas Clear Calls oling M N O P Q R S 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". O 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 in the cell where yo want the number to go, and then go back to the original worksheet, put your curs on the cell to select it, and then press enter. NEW ORIGINAL Change. 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 WACMX? What is the new MOS%? Briefly explain your findings to the client. WACM percentage MOS% Operating income Brief explanation: 19 2. Save a copy of your original model to a new spreadsheet called "new sales mis". 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 21 Units to earn target profit Operating income Brief explanation: Operating leverage factor 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