I have completed the first segment of this project. All info for the last 3 questions in provided in the first 2 snips of the filled out excel. Please complete the 3 questions on empty excel file and detail the formulas you used. Thank you!
Launch-it Launch-it $ 10.00 6.00 60.00% 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: Sales commision Total variable cost per unit Product #1 Unit CM CM % Breakeven point: -in units -in sales revenue 250 Total $ $ $ $ 2,500.00 1.00 2.00 1.00 4.00 Product #1 2000 800 1200 Product #2 1 3000 1800 1200 sales: Variable Costs: Contribution margin Fixed expenses: Net Income: $ Target profit volume: -in units -in sales revenue 5000 2600 2400 1500 900 1583 15,833.00 Monthly volume 200 $ WACM % 48% Treat-time $ 30.00 Treat-time 12 00 Product #2: Sales price per unit Variable costs per unit: product cost: Shipping: Sales commision Total variable cost per unit $ $ $ $ Product #2 Unit CM CM % Breakeven point: -in units -in sales revenue 7.00 8.00 3.00 18.00 Total Calculation of Weighted average CM per unit Product #1 Product #2 1 0.67) 0.33 sales mix CM per unit | 3,750.00 Monthly volume 100 Target profit volume: -in units -in sales revenue WACM/unit 792.00 23,760.00 Fixed costs per month: work contract Sales Booth Total fixed costs per month $ $ $ 1,000.00 500.00 1,500.00 Product #1 Multiproduct Breakeven point: -in units Sales revenue at breakeven 125 Product #2 | Total 63 188 18903140 Target profit per month $ 8,000.00 1250 Expected change in volume (%) 5% Multiproduct Target profit point: -in units Sales revenue at target profit Product #1 792 7920 Product #2 396 11880 Total 1188 19800 0.4 Total product cost: Shippine Sales commision Total variable cost per unit $ $ $ $ 7.00 8.00 3.00 18.00 CM% Breakeven point: in units -in sales revenue Calculation of Weighted average CM per unit Product #1 Product #2 1 0.67 0.331 6 12 125 3,750.00 sales mix CM per unit $ 18 Monthly volume 100 Target profit volume: -in units -in sales revenue WACM/unit A 4.0 4.0 8.0 $ $ 792.00 23,760.00 Fixed costs per month: work contract Sales Booth Total fixed costs per month $ $ $ 1,000.00 500.00 1,500.00 | Multiproduct Breakeven point: -in units Sales revenue at breakeven Product #1 125 1250 Product #2 63 1890 Total 188 140 Target profit per month $ 8,000.00 3 Expected change in volume (%) 5% Multiproduct Target profit point: -in units Sales revenue at target profit Product #1 792 7920 Product #2 396 11880 Total 1188 9800 1 Margin of Safety (in $) $ 1,860.00 Margin of Safety % $ 0.37 Operating Leverage Factor 2.67 Expected % change in operating income (%) 13% NEW ORIGINAL Change Operating income 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. Brief explanation: WACM percentage MOS% 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 Brief explanation: Operating income 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 WACM/unit "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) Units to earn target profit will Jake need to sell to earn his target profit? Briefly explain your findings to the client. Operating income per 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: $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. Operating leverage factor Expected % change in op inc
