Business Description

After taking business classes, Jake, an avid dog-lover, decided to start selling unique pet supplies at trade shows. He has two products:

Product 1: "Launch-it"- a tennis ball thrower that will sell for $10.

Product 2: "Treat-time"- an automatic treat dispenser that releases a treat when the dog places his paw on the pedal. The treat dispenser will sell for $30.

Costs: Jake has hired an employee to work the trade show booths. The work contract is $1,000 per month plus a commission equal to 10% of revenue. Jake will also spend $500 per month on trade-show entry fees. Jake is purchasing the products from a supplier in Mexico. Launch-its cost $1 each; Treat-times cost $7 each. Shipping and handling on the Launch-its will cost $2 each; Shipping and handling on the Treat-times, which are heavier, will cost $8 each. The shipping and handling costs will be paid by Jake, not the customer.

Assume Jake expects to sell 200 Launch-its and 100 Treat-times during his first month of operations (June).

Jake's financial goal is to earn an operating income of $8,000 per month. He believes volume may grow at a rate of 5% a month.

Following is the original assumption:

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 20%. 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.

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.

6. 00 0.60 ASSUMPTIONS Product #1: Launch-it Sales price per unit $10 Variable costs per unit: Sales comm ($10*10%) $1 Launch it purchase cost per $1 Shipping & handing cost per $2 Total variable cost per unit $4 Product #1 Launch-it Unit CM $ CM % $ Breakeven point: -in units $ -in sales revenue $ 250.00 2,500.00 Target profit volume: -in units -in sales revenue $ $ 1, 583.00 15, 833.00 Monthly volume $200 Treat-time $30 Product #2: Sales price per unit Variable costs per unit: Sales comm($30*10%) Treat it purchase cost per Shipping& handing cost per Total variable cost per unit $3 $7 $8 $18 Product #2 Unit CM CM % Breakeven point: -in units -in sales revenue Treat-time $ $ $ $ 12.00 0.40 125.00 3, 750.00 Monthly volume $100 Target profit volume: -in units -in sales revenue $ $ 792.00 23, 750.00 Fixed costs per month: Salary per month Trade show entry fees Total fixed costs per month $1,000 $500 $1,500 Target profit per month $8,000 Expected change in volume (%) 5% Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 Sales revenue Total variable cost Contribution margin Fixed expenses Operating income Product #1 $2,000 $800 $1,200 Product #2 $3,000 $1,800 $1,200 Total $5,000 $2,600 $2,400 $1,500 $900 WACM % 28. 80% 19. 20% 48% per unit Calculation of Weighted average CM Product #1 Sales $2,000 Less: Variable Costs $800 Contribution Margin $1,200 Product #2 $3,000 $1,800 $ 1,200 Total $5,000 $2,600 $2,400 WACM/ unit $6 $12 $8 Multiproduct Breakeven point: -in units Sales revenue at breakeven Product #1 125 1250 Product #2 63 1875 Total 187.5 3125 Multiproduct Target profit point: -in units Sales revenue at target profit Product #1 792 7917 Product #2 396 11875 Total 1187.5 19792 Margin of Safety (in $) 1875 Margin of Safety % 38% Operating Leverage Factor 2. 67 Expected % change in operating income 16% NEW ORIGINAL Change Operating income Brief explanation: WACM percentage MOS% Operating income Brief explanation: WACM/ unit Units to earn target profit Operating income Brief explanation: Operating leverage factor Expected % change in op inc 6. 00 0.60 ASSUMPTIONS Product #1: Launch-it Sales price per unit $10 Variable costs per unit: Sales comm ($10*10%) $1 Launch it purchase cost per $1 Shipping & handing cost per $2 Total variable cost per unit $4 Product #1 Launch-it Unit CM $ CM % $ Breakeven point: -in units $ -in sales revenue $ 250.00 2,500.00 Target profit volume: -in units -in sales revenue $ $ 1, 583.00 15, 833.00 Monthly volume $200 Treat-time $30 Product #2: Sales price per unit Variable costs per unit: Sales comm($30*10%) Treat it purchase cost per Shipping& handing cost per Total variable cost per unit $3 $7 $8 $18 Product #2 Unit CM CM % Breakeven point: -in units -in sales revenue Treat-time $ $ $ $ 12.00 0.40 125.00 3, 750.00 Monthly volume $100 Target profit volume: -in units -in sales revenue $ $ 792.00 23, 750.00 Fixed costs per month: Salary per month Trade show entry fees Total fixed costs per month $1,000 $500 $1,500 Target profit per month $8,000 Expected change in volume (%) 5% Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 Sales revenue Total variable cost Contribution margin Fixed expenses Operating income Product #1 $2,000 $800 $1,200 Product #2 $3,000 $1,800 $1,200 Total $5,000 $2,600 $2,400 $1,500 $900 WACM % 28. 80% 19. 20% 48% per unit Calculation of Weighted average CM Product #1 Sales $2,000 Less: Variable Costs $800 Contribution Margin $1,200 Product #2 $3,000 $1,800 $ 1,200 Total $5,000 $2,600 $2,400 WACM/ unit $6 $12 $8 Multiproduct Breakeven point: -in units Sales revenue at breakeven Product #1 125 1250 Product #2 63 1875 Total 187.5 3125 Multiproduct Target profit point: -in units Sales revenue at target profit Product #1 792 7917 Product #2 396 11875 Total 1187.5 19792 Margin of Safety (in $) 1875 Margin of Safety % 38% Operating Leverage Factor 2. 67 Expected % change in operating income 16% NEW ORIGINAL Change Operating income Brief explanation: WACM percentage MOS% Operating income Brief explanation: WACM/ unit Units to earn target profit Operating income Brief explanation: Operating leverage factor Expected % change in op inc