Here are the orginal assumption results.
Do the Following:
1. Need the new Operating Income, WACM percentage, MOS% based on the given scenario of the suppluer expecting to increase the coat of the products by 20% and a brief explanation.
2. Need the new Operating Income, WACM/Unit, and the Units to earn target profit based on the given scenario of the monthly sales volume going from 175 "treat-times" and 125 "launch-its" and a brief explanation.
3. Need the new Operating Income, Operating Leverage factor and Expected % change in op inc
based on the givem scenario of work contract being $1500 per month plus 5% revenue and a brief explanation.
NEW Chance Operating income ORIGINAL $ 900.00 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 WACM? What is the new MOS? Briefly explain your findings to the client WACM percentage 4896 MOSN 38% Operating income $ 900.00 Brief explanation 2. Save another copy of your original model to a new spreadsheet called "new sales ma". Say the monthly sales volume is now expected to be 175 "Treat times and 125 "Launch-its" (same total units, but 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 lake need to sell to earn his target profit? Briefly explain your findings to the client. WACM/unit $ 8.00 Units to earn target profit 1188 Operating income $ 900.00 Brief explanation: 3. Save a third 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 it volume increases as expected in the future? Briefly explain your findings to the client Operating leverage factor 5 2.67 Expected change in op in 1396 Lounch.it $ Launch it $ 10.00 6.00 601 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: Product cost Shipping cost Sales Commission Total variable cost per unit Product 1 Unit CM CM Breakeven point in units in sales revenue S $ $ s $ 100 2.00 1.00 4.00 250 2,500.00 Target profit volume in units -in sales revenue Sales Less: Variable costs Contribution Marin Less: Fixed costs Operating income Product 01 Product 2 s 2.000.00 3,000.00 $ 8000015 1.800.00 Is 1,200.00 1,200.00 $ $ $ Total 5,000.0 2.600.0 2,400C 1,500. 900.C Monthly volume $ 1583 15,833.33 200.00 $ WACM 4 Treat time $ 30.00 Treat time $ 12.00 Product 2: Sales price per unit Variable costs per unit Product cost Shipping Cost Sales Commission Total variable cost per unit 405 $ 5 $ 3 Total Product 2 Unit CM CM Breakeven point -in units -in sales revenue 7.00 8.00 3.00 18.00 125 3.750.00 Calculation of Welchted average CM per unit Product Product ? S 6.00 $ 12.00 200 100 $ 1,200.00 1,200.00 Unit cm Sales MIN Contribution margin $ 2,4000 Monthly volume 100 Target profit volume -in units in sales tenue WACM/unit $ 8C 792 23.750.00 $ Fixed costs per month Work contract Trade show entry fees $ 1.000.00 $ 500.00 800 Total Shipping Cost Sales Commission Total variable cost per unit $ $ $ 3.00 18.00 Breakeven point -in units -in sales revenue $ 125 3,750,00 Unitem Sales Mix Contribution margin Product 01 Product 2 S 6.00 12.00 200 100 1.200.00 1,200.00 $ 30 2,400.00 Monthly volume 100 Target profit volume in units -in sales revenue WACM/unit S 7921 23 750.00 S Fixed costs per month Work contract Trade show entry fees Total fixed costs per month $ 1,000.00 $ 500.00 $ 1.500.00 Multiproduct Breakeven point in units Sales revenue at breakeven Product 31 Product 625 $ 3.250.00 $ 1.875.00 $ 125 Total 1875 3,1250C Target profit per month $ 8,000.00 Expected change in volume 2 Multiproduct Tarrat profit point in units Sales revenue at tarrat profit Product Product 02 396 7916.67S 11875.00 5 792 Total 1188 19,791.6% Margin of safety in 5 S 1875.00 Margin of Safety 389 Operating Leverare Factor 2.67 Expected change in operatin income 1394 NEW Chance Operating income ORIGINAL $ 900.00 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 WACM? What is the new MOS? Briefly explain your findings to the client WACM percentage 4896 MOSN 38% Operating income $ 900.00 Brief explanation 2. Save another copy of your original model to a new spreadsheet called "new sales ma". Say the monthly sales volume is now expected to be 175 "Treat times and 125 "Launch-its" (same total units, but 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 lake need to sell to earn his target profit? Briefly explain your findings to the client. WACM/unit $ 8.00 Units to earn target profit 1188 Operating income $ 900.00 Brief explanation: 3. Save a third 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 it volume increases as expected in the future? Briefly explain your findings to the client Operating leverage factor 5 2.67 Expected change in op in 1396 Lounch.it $ Launch it $ 10.00 6.00 601 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: Product cost Shipping cost Sales Commission Total variable cost per unit Product 1 Unit CM CM Breakeven point in units in sales revenue S $ $ s $ 100 2.00 1.00 4.00 250 2,500.00 Target profit volume in units -in sales revenue Sales Less: Variable costs Contribution Marin Less: Fixed costs Operating income Product 01 Product 2 s 2.000.00 3,000.00 $ 8000015 1.800.00 Is 1,200.00 1,200.00 $ $ $ Total 5,000.0 2.600.0 2,400C 1,500. 900.C Monthly volume $ 1583 15,833.33 200.00 $ WACM 4 Treat time $ 30.00 Treat time $ 12.00 Product 2: Sales price per unit Variable costs per unit Product cost Shipping Cost Sales Commission Total variable cost per unit 405 $ 5 $ 3 Total Product 2 Unit CM CM Breakeven point -in units -in sales revenue 7.00 8.00 3.00 18.00 125 3.750.00 Calculation of Welchted average CM per unit Product Product ? S 6.00 $ 12.00 200 100 $ 1,200.00 1,200.00 Unit cm Sales MIN Contribution margin $ 2,4000 Monthly volume 100 Target profit volume -in units in sales tenue WACM/unit $ 8C 792 23.750.00 $ Fixed costs per month Work contract Trade show entry fees $ 1.000.00 $ 500.00 800 Total Shipping Cost Sales Commission Total variable cost per unit $ $ $ 3.00 18.00 Breakeven point -in units -in sales revenue $ 125 3,750,00 Unitem Sales Mix Contribution margin Product 01 Product 2 S 6.00 12.00 200 100 1.200.00 1,200.00 $ 30 2,400.00 Monthly volume 100 Target profit volume in units -in sales revenue WACM/unit S 7921 23 750.00 S Fixed costs per month Work contract Trade show entry fees Total fixed costs per month $ 1,000.00 $ 500.00 $ 1.500.00 Multiproduct Breakeven point in units Sales revenue at breakeven Product 31 Product 625 $ 3.250.00 $ 1.875.00 $ 125 Total 1875 3,1250C Target profit per month $ 8,000.00 Expected change in volume 2 Multiproduct Tarrat profit point in units Sales revenue at tarrat profit Product Product 02 396 7916.67S 11875.00 5 792 Total 1188 19,791.6% Margin of safety in 5 S 1875.00 Margin of Safety 389 Operating Leverare Factor 2.67 Expected change in operatin income 1394
