I just need help with 1 2 and 3 of the last picture uploaded. I was able to calculate the colored charts using excel.
ASSUMPTIONS Launch it Launch it Product #1: Sales price per unit | Product 1 Unit CM CM% Jake's Pet Supplies Pro Forma Contribution Margin income Statement For the month ending June 30 10 4523 Breakeven point: Variable costs per unit: COGS Shipping and handling Commissions Total variable cost per unit in sales revenue 3333 1 3 15 5.5 Product Product #2 100001 22500 5500 16875 4500 5625 Sales Variable Expense Contribution Martin Fred Expenses NO Total 32500 22375 1012 1500 8525 Target profit volume: in units in sales revenue 1848.89 18488.89 Monthly volume 1000 Treat time WACM X 14% 56 31 Product N2: Sales price per unit Treat time Product #2 Unit CM CM% 75 25% Variable costs per unit: 7 11 Shipping and handling Commission Total variable cost per unit Breakeven point: in units in sales revenue Total 200 6000 22.5 Calculation of Weighted average CM per unit Product Product 12 10000 22.500 5500 16873 45001 5625 32.500 Contribtulon margin per unit Sales Mix Contribution Margin Monthly volume 750 Target profit volume: in units Insales revenue 22375 10125 5.79 1400 42000 WACM/unit Fixed costs per month Salaries Entry Fees Total fred costs per month 1000 SOO 1500 Product 1 Total Target profit per month 1750 Multiproduct Breakeven point: in units Sales revenue at breakeven Product #2 148 1481 111 3333 25 4815 Expected change in volume %) Produst Multiproduct Target profit point: in units Sales revenue at target profit Produs2 938 9383 7041 21111 Total 1642 30494 Margin of Safety (ins 2768 Margin of Safety % 852 Profit Sensitivity Multiplier 117 Expected change in operating income (6) B D E F G H K L L M N o P Q R S T U V w 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 8625 Operating income Brief explanation: 1. Save a copy of your original model to a new spreadsheet called "supplier cost decrease". 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. 1 WACM percentage 31% MOS% 85 Operating income 8625 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 5 6 7 8 9 10 11 112 113 14 15 16 17 18 19 20 21 21 22 23 24 25 26 27 28 20 29 30 31 32 22 33 34 35 36 37 38 39 40 41 41 42 Operating income 8625 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 as expected in the future? Briefly explain your findings to the client. Profit sensitivity multiplier Expected % change in op inc 43 44 45 ASSUMPTIONS Launch it Launch it Product #1: Sales price per unit | Product 1 Unit CM CM% Jake's Pet Supplies Pro Forma Contribution Margin income Statement For the month ending June 30 10 4523 Breakeven point: Variable costs per unit: COGS Shipping and handling Commissions Total variable cost per unit in sales revenue 3333 1 3 15 5.5 Product Product #2 100001 22500 5500 16875 4500 5625 Sales Variable Expense Contribution Martin Fred Expenses NO Total 32500 22375 1012 1500 8525 Target profit volume: in units in sales revenue 1848.89 18488.89 Monthly volume 1000 Treat time WACM X 14% 56 31 Product N2: Sales price per unit Treat time Product #2 Unit CM CM% 75 25% Variable costs per unit: 7 11 Shipping and handling Commission Total variable cost per unit Breakeven point: in units in sales revenue Total 200 6000 22.5 Calculation of Weighted average CM per unit Product Product 12 10000 22.500 5500 16873 45001 5625 32.500 Contribtulon margin per unit Sales Mix Contribution Margin Monthly volume 750 Target profit volume: in units Insales revenue 22375 10125 5.79 1400 42000 WACM/unit Fixed costs per month Salaries Entry Fees Total fred costs per month 1000 SOO 1500 Product 1 Total Target profit per month 1750 Multiproduct Breakeven point: in units Sales revenue at breakeven Product #2 148 1481 111 3333 25 4815 Expected change in volume %) Produst Multiproduct Target profit point: in units Sales revenue at target profit Produs2 938 9383 7041 21111 Total 1642 30494 Margin of Safety (ins 2768 Margin of Safety % 852 Profit Sensitivity Multiplier 117 Expected change in operating income (6) B D E F G H K L L M N o P Q R S T U V w 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 8625 Operating income Brief explanation: 1. Save a copy of your original model to a new spreadsheet called "supplier cost decrease". 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. 1 WACM percentage 31% MOS% 85 Operating income 8625 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 5 6 7 8 9 10 11 112 113 14 15 16 17 18 19 20 21 21 22 23 24 25 26 27 28 20 29 30 31 32 22 33 34 35 36 37 38 39 40 41 41 42 Operating income 8625 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 as expected in the future? Briefly explain your findings to the client. Profit sensitivity multiplier Expected % change in op inc 43 44 45
