I need these questions answered with the excel sheets showing the work added from the original spreadsheet!

copy, 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. 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 25%. What is the new operating income? What is the new WACM % ? What is the new MOS % ? Briefly explain your findings to the client. A 7 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 3 Op 9 "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. 0 WA 21 22 23 24 25 26 27 28 29 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. 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Uni Oper Opera Expect motions S Operating income WACM percentage MOS% Operating income WACM/unit Units to earn target profit Operating income Operating leverage factor Expected % change in op inc NEW ORIGINAL $ $ $ 900.00 48% 38% 900.00 8.00 1188 $ 900.00 2.67 13% Change 6 15 16 Summer 2022 Product Sales price per unit Variable costs per unit Sales comm (510*10%) Launch t purchase cost per Shipping & handing cost per Total variable cost per unit 10 11 Monthly volume 12 13 Product 14 Sam price per un Variable costs per un Sale comm(53010 Tree it purchase.cost per Shipping & handing cost Total variable est per unit Monthly volume Fixed costs per month Mary per month trade show entry fe Total Fuad cents per mont Target profit per month Total number of un Expected change in hune (8) 17 38 19 30 21 22 25 34 25 26 27 28 29 30 31 32 ASSUMPTIONS 5 $ 1 $ Trem fine 1 1 3000 100 S 7.00 S 00 1 18.00 100 $ 1.000.00 500.00 1.500.00 $ 8.00000 300.00 IN 10.00 1.00 1.00 2.00 4.00 200 S S Productes Unit CM CMS Breakoven paint in uns in sales revenue Target profit volume unit in sales revenue Product 2 JAR CM CMS Target profit volume Run Launch t 15 11 treat time 1 15 1.00 60% 750 3.500.00 1143 4543133 11.00 40% 3.750.00 292 CARE PLANTER Pro Forma Centribution Margin cam For the month anding 30 Contestan meg Lass and cost Opening come WACH CMARI al m sont of each Contrbuton mege WACH Multgrodat freen pean in uns Multiproduct fapt pt pt Opening Leverage factor Expacted change in operating income ( sang 13 Caration of Waghted merge Mount obat 600 100 1200.00 1 1 Prada Product 3,000.00 1 1,000.00 $ 800001 1800:00 S 1,300.00 1.200.00 1 $ OFSERT SIE Podat 257 SIN 33 $1 I 11.00 [100] 1200 805 1400.00 419 befut Tine Product T 125 6 1 125000 5 475.00$ $135.00 Product 1 762 TH 131647 11,875.00 HE 2104 31 1.000.00 160000 1,400 00 1.500.00 900.00 48% CYTACHE 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. 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 25%. 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 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. WA Uni 4 5 6 7 Oper 28 29 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. Opera 30 31 Expect 32 33 34 35 36 37 Operating income WACM percentage MOS% Operating income WACM/unit Units to earn target profit Operating income Operating leverage factor Expected % change in op inc NEW ORIGINAL $ $ $ 900.00 48% 38% 900.00 8.00 1188 $ 900.00 2.67 13% Change copy, 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. 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 25%. What is the new operating income? What is the new WACM % ? What is the new MOS % ? Briefly explain your findings to the client. A 7 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 3 Op 9 "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. 0 WA 21 22 23 24 25 26 27 28 29 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. 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Uni Oper Opera Expect motions S Operating income WACM percentage MOS% Operating income WACM/unit Units to earn target profit Operating income Operating leverage factor Expected % change in op inc NEW ORIGINAL $ $ $ 900.00 48% 38% 900.00 8.00 1188 $ 900.00 2.67 13% Change 6 15 16 Summer 2022 Product Sales price per unit Variable costs per unit Sales comm (510*10%) Launch t purchase cost per Shipping & handing cost per Total variable cost per unit 10 11 Monthly volume 12 13 Product 14 Sam price per un Variable costs per un Sale comm(53010 Tree it purchase.cost per Shipping & handing cost Total variable est per unit Monthly volume Fixed costs per month Mary per month trade show entry fe Total Fuad cents per mont Target profit per month Total number of un Expected change in hune (8) 17 38 19 30 21 22 25 34 25 26 27 28 29 30 31 32 ASSUMPTIONS 5 $ 1 $ Trem fine 1 1 3000 100 S 7.00 S 00 1 18.00 100 $ 1.000.00 500.00 1.500.00 $ 8.00000 300.00 IN 10.00 1.00 1.00 2.00 4.00 200 S S Productes Unit CM CMS Breakoven paint in uns in sales revenue Target profit volume unit in sales revenue Product 2 JAR CM CMS Target profit volume Run Launch t 15 11 treat time 1 15 1.00 60% 750 3.500.00 1143 4543133 11.00 40% 3.750.00 292 CARE PLANTER Pro Forma Centribution Margin cam For the month anding 30 Contestan meg Lass and cost Opening come WACH CMARI al m sont of each Contrbuton mege WACH Multgrodat freen pean in uns Multiproduct fapt pt pt Opening Leverage factor Expacted change in operating income ( sang 13 Caration of Waghted merge Mount obat 600 100 1200.00 1 1 Prada Product 3,000.00 1 1,000.00 $ 800001 1800:00 S 1,300.00 1.200.00 1 $ OFSERT SIE Podat 257 SIN 33 $1 I 11.00 [100] 1200 805 1400.00 419 befut Tine Product T 125 6 1 125000 5 475.00$ $135.00 Product 1 762 TH 131647 11,875.00 HE 2104 31 1.000.00 160000 1,400 00 1.500.00 900.00 48% CYTACHE 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. 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 25%. 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 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. WA Uni 4 5 6 7 Oper 28 29 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. Opera 30 31 Expect 32 33 34 35 36 37 Operating income WACM percentage MOS% Operating income WACM/unit Units to earn target profit Operating income Operating leverage factor Expected % change in op inc NEW ORIGINAL $ $ $ 900.00 48% 38% 900.00 8.00 1188 $ 900.00 2.67 13% Change