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.
AutoSave OFF Dojo CVP Excel project- Jake's pet supply business - student file - Saved to my Mac Q Search Sheet Insert Formulas Data Review View Share Home 138 Page Layout foc A 3 C E F G H L M Launch-it $ 1 2 3 4 5 6 6 7 Launch-It $ 6.00 50.00% Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 10.00 ASSUMPTIONS Product #1: Sales price per unit Variable costs per unit: Sales Commission Purchase price Shipping & Handline Total variable cost per unit Product #1 Unit CM CMX Breakeven point -In units -In sales revenue 250 2,500.00 $ $ $ $ $ 1.00 1.00 2.00 4.00 8 Product 2,000.00 $ 800.00 $ 1,200.00 $ Sales Revenue Less: Variable Costs Contribution Marein Less: Fixed Expenses Operatine Income $ $ S Product 12 3,000.00 $ 1,800.00 $ 1,200.00 $ 1 $ $ Total 5,000.00 2,600.00 2,400.00 1,500.00 900.00 Tareet orofit yolume -In units -In sales revenue 10 11 1583.33 15,833.00 Monthly volume 200 units $ 12 13 WACM 28.80% 19.20% 48.00% Treat-time $ 30.00 Treat time $ Product #2: Sales price per unit Variable costs per unit: Sales Commission Purchase Proe Shippine & Handling Total variable cost per unit 12.00 40.00% $ $ | $ $ $ 3.00 7.00 8.00 Product #2 Unit CM Breakeven point -In units - In sales revenue 125 3,750.00 Calculation of Weighted average CM per unit Product Product #2 $ 2,000.00 $ 3,000.00 $ $ 800.00 $ 1,800.00 $ $ 1,200.00 S 1,200.00 $ Total 5,000.00 2.600.00 2,400.00 Sales Revenue Less: Variable Costs Contribution Margin 18.00 $ Monthly volume 100 units Target profit volume -In unit: in sales revenue WACM/unit 5 8.00 791.67 23,750.00 $ S 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Fixed costs per month Work Contract Trade sho Entry Fres Tatal fixed costs per month $ $ 1,000.00 500.00 1,500.00 Multiproduct Breakeven paint: in units Product #1 Product #2 125 63 $ 1,250.00 $ 1,875.00 $ Total 198 3,125.00 Target profit per month $ 8,000.00 Sales revenue at breakeven Expected change in volume (56) 5.00% Multiproduct Target profit point: Hn units Sales revenue at target profit Product #1 Product 12 792 395 5 7,917.00 $ 11,875.00 $ Total 1188 19,792.00 Margin of Safety (in $) $ 1,875.00 Margin of Safety 28.00% 37 38 39 40 Daerating Leverage Factar 2.62 Expected change in operatingincame%] 13.35% 41 Business & Directions Orignal Assumptions Advising Client Supplier Cost Increase New Sales Mix Alternative Contract + Ready 100% AutoSave OFF Dojo CVP Excel project- Jake's pet supply business - student file - Saved to my Mac Q Search Sheet Insert Formulas Data Review View Share Home 138 Page Layout foc A 3 C E F G H L M Launch-it $ 1 2 3 4 5 6 6 7 Launch-It $ 6.00 50.00% Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 10.00 ASSUMPTIONS Product #1: Sales price per unit Variable costs per unit: Sales Commission Purchase price Shipping & Handline Total variable cost per unit Product #1 Unit CM CMX Breakeven point -In units -In sales revenue 250 2,500.00 $ $ $ $ $ 1.00 1.00 2.00 4.00 8 Product 2,000.00 $ 800.00 $ 1,200.00 $ Sales Revenue Less: Variable Costs Contribution Marein Less: Fixed Expenses Operatine Income $ $ S Product 12 3,000.00 $ 1,800.00 $ 1,200.00 $ 1 $ $ Total 5,000.00 2,600.00 2,400.00 1,500.00 900.00 Tareet orofit yolume -In units -In sales revenue 10 11 1583.33 15,833.00 Monthly volume 200 units $ 12 13 WACM 28.80% 19.20% 48.00% Treat-time $ 30.00 Treat time $ Product #2: Sales price per unit Variable costs per unit: Sales Commission Purchase Proe Shippine & Handling Total variable cost per unit 12.00 40.00% $ $ | $ $ $ 3.00 7.00 8.00 Product #2 Unit CM Breakeven point -In units - In sales revenue 125 3,750.00 Calculation of Weighted average CM per unit Product Product #2 $ 2,000.00 $ 3,000.00 $ $ 800.00 $ 1,800.00 $ $ 1,200.00 S 1,200.00 $ Total 5,000.00 2.600.00 2,400.00 Sales Revenue Less: Variable Costs Contribution Margin 18.00 $ Monthly volume 100 units Target profit volume -In unit: in sales revenue WACM/unit 5 8.00 791.67 23,750.00 $ S 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Fixed costs per month Work Contract Trade sho Entry Fres Tatal fixed costs per month $ $ 1,000.00 500.00 1,500.00 Multiproduct Breakeven paint: in units Product #1 Product #2 125 63 $ 1,250.00 $ 1,875.00 $ Total 198 3,125.00 Target profit per month $ 8,000.00 Sales revenue at breakeven Expected change in volume (56) 5.00% Multiproduct Target profit point: Hn units Sales revenue at target profit Product #1 Product 12 792 395 5 7,917.00 $ 11,875.00 $ Total 1188 19,792.00 Margin of Safety (in $) $ 1,875.00 Margin of Safety 28.00% 37 38 39 40 Daerating Leverage Factar 2.62 Expected change in operatingincame%] 13.35% 41 Business & Directions Orignal Assumptions Advising Client Supplier Cost Increase New Sales Mix Alternative Contract + Ready 100%
