CVP Excel project_Completed_

CVP Modeling Project The purpose of this project is to give you experience creating a multiproduct profitability analysis that can be used to determine the effects of changing business conditions on the client's financial position. Your goal will be to use Excel in such a way that any changes to the assumptions will correctly ripple through the entire profitability analysis. If executed properly, the client should be able to use this spreadsheet over and over, using different "what if" assumptions. 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. Directions You have be operating in spreadshee assumption rather than assumption FORMATTIN - Round all function. - Show all M rather than - Show all p - Right justi 1) Complete separately 2) Complete (Treat -time Check figur 3) Complete product line totals for al company. Check figur 4) Calculate 5) Use the THEN, calcu with this vo 6) Use the second gray calculate sa Check figur 7) Calculate as a percen determine t 8) Change n 9) Make sur 10) Go to th

CVP Model Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 Product #1 Product #2 Total Sales Revenue $ 2,000.00 $ 3,000.00 $ 5,000.00 less variable expenses Cost of Goods Sold $ 200.00 $ 700.00 $ 900.00 Shipping Expenses $ 400.00 $ 800.00 $ 1,200.00 Employee Commision Expense $ 200.00 $ 300.00 $ 500.00 Total Fixed Cost $ 1,500.00 WACM % 48% Operating Income $ 900.00 Calculation of Weighted average CM per unit Product #1 Product #2 Total Sale Price Per Unit $ 10.00 $ 30.00 $ 16.67 Variable Cost Per Unt $ 4.00 $ 18.00 $ 8.67 Number of Units 200 100 300 CM % 60% 40% 48% WACM/unit $ 8.00 Multiproduct Breakeven point: Product #1 Product #2 Total -in units 125 63 188 Sales revenue at breakeven $ 2,083.33 $ 1,041.67 $ 3,125.00 Multiproduct Target profit point: Product #1 Product #2 Total -in units 792 396 1188 Sales revenue at target profit $ 13,194.44 $ 6,597.22 $ 19,791.67 Margin of Safety (in $) $ 1,875.00 Margin of Safety % 38% Operating Leverage Factor 2.67 Expected % change in operating income (%) 280%

CVP Model ASSUMPTIONS Product #1 Launch-it Product #1: Launch-it Unit CM $ 6.50 Sales price per unit $ 10.00 CM % 65% Variable costs per unit: Breakeven point: Cost of Goods Sold $ 1.00 -in units 308 Shipping Expense $ 2.00 -in sales revenue $ 2,000.00 Commission $ 0.50 Total variable cost per unit $ 3.50 Target profit volume: -in units 1,538 Monthly volume 200 -in sales revenue $ 15,384.62 Product #2: Treat-time Sales price per unit $ 30.00 Product #2 Treat-time Variable costs per unit: Unit CM $ 13.50 Cost of Goods Sold $ 7.00 CM % 45% Shipping Expense $ 8.00 Breakeven point: Commission $ 1.50 -in units 148 Total variable cost per unit $ 16.50 -in sales revenue $ 4,444.44 Monthly volume 100 Target profit volume: -in units 741 Fixed costs per month: -in sales revenue $ 22,222.22 Trade- Show Entry $ 500.00 Work Contract $ 1,500.00 Total fixed costs per month $ 2,000.00 Target profit per month $ 8,000.00 Expected change in volume (%) 5%

CVP Model ASSUMPTIONS Product #1 Launch-it Product #1: Launch-it Unit CM $ 6.00 Sales price per unit $ 10.00 CM % 60% Variable costs per unit: Breakeven point: Cost of Goods Sold $ 1.00 -in units 250 Shipping Expense $ 2.00 -in sales revenue $ 1,500.00 Commission $ 1.00 Total variable cost per unit $ 4.00 Target profit volume: -in units 1,583 Monthly volume 125 -in sales revenue $ 15,833.33 Product #2: Treat-time Sales price per unit $ 30.00 Product #2 Treat-time Variable costs per unit: Unit CM $ 12.00 Cost of Goods Sold $ 7.00 CM % 40% Shipping Expense $ 8.00 Breakeven point: Commission $ 3.00 -in units 125 Total variable cost per unit $ 18.00 -in sales revenue $ 3,750.00 Monthly volume 175 Target profit volume: -in units 792 Fixed costs per month: -in sales revenue $ 23,750.00 Trade- Show Entry $ 500.00 Work Contract $ 1,000.00 Total fixed costs per month $ 1,500.00 Target profit per month $ 8,000.00 Expected change in volume (%) 5%

CVP Model Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 Product #1 Product #2 Total Sales Revenue $ 1,250.00 $ 5,250.00 $ 6,500.00 less variable expenses Cost of Goods Sold $ 125.00 $ 1,225.00 $ 1,350.00 Shipping Expenses $ 250.00 $ 1,400.00 $ 1,650.00 Employee Commision Expense $ 125.00 $ 525.00 $ 650.00 Total Fixed Cost $ 1,500.00 WACM % 44% Operating Income $ 1,350.00 Calculation of Weighted average CM per unit Product #1 Product #2 Total Sale Price Per Unit $ 10.00 $ 30.00 $ 21.67 Variable Cost Per Unt $ 4.00 $ 18.00 $ 12.17 Number of Units 125 175 300 CM % 60% 40% 44% WACM/unit $ 9.50 Multiproduct Breakeven point: Product #1 Product #2 Total -in units 66 92 158 Sales revenue at breakeven $ 1,425.44 $ 1,995.61 $ 3,421.05 Multiproduct Target profit point: Product #1 Product #2 Total -in units 417 583 1000 Sales revenue at target profit $ 9,027.78 $ 12,638.89 $ 21,666.67 Margin of Safety (in $) $ 3,078.95 Margin of Safety % 47% Operating Leverage Factor 2.11 Expected % change in operating income (%) 222%

CVP Model Jake's Pet Supplies Pro Forma Contribution Margin Income Statement For the month ending June 30 Product #1 Product #2 Total Sales Revenue $ 2,000.00 $ 3,000.00 $ 5,000.00 less variable expenses Cost of Goods Sold $ 240.00 $ 840.00 $ 1,080.00 Shipping Expenses $ 400.00 $ 800.00 $ 1,200.00 Employee Commision Expense $ 200.00 $ 300.00 $ 500.00 Total Fixed Cost $ 1,500.00 WACM % 44% Operating Income $ 720.00 Calculation of Weighted average CM per unit Product #1 Product #2 Total Sale Price Per Unit $ 10.00 $ 30.00 $ 16.67 Variable Cost Per Unt $ 4.20 $ 19.40 $ 9.27 Number of Units 200 100 300 CM % 58% 35% 44% WACM/unit $ 7.40 Multiproduct Breakeven point: Product #1 Product #2 Total -in units 135 68 203 Sales revenue at breakeven $ 2,252.25 $ 1,126.13 $ 3,378.38 Multiproduct Target profit point: Product #1 Product #2 Total -in units 856 428 1284 Sales revenue at target profit $ 14,264.26 $ 7,132.13 $ 21,396.40 Margin of Safety (in $) $ 1,621.62 Margin of Safety % 32% Operating Leverage Factor 3.08 Expected % change in operating income (%) 324%

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. EXCEL of the you ha

f sales volume changes, the operational income for o increase by $450 because treat-times have a The weighted average cost margin is expected to fall , the number of units required to earn your desired y 188. This occurred because you earn more money you do per Launch-it. the cost of goods is raised by the supplier by 20%, me for the month is likely to reduce by $180, and e cost margin is likely to decline by 4% since your ll be greater as a result of the increase in variable ur profit margin of safety will be reduced by 5% as a the contractor increases the flat price to $1500 but ission to 5%, your operational income is likely to Operating Leverage Factor rises by 1.41 points. This les improve, he is projected to make less commission ecast, which will benefit you with bigger quantities of worksheet, put a + in the cell where you o the original worksheet, put your cursor on

CVP EXCEL MODEL GRADING RUBRIC POSSIBLE POINTS Original Assumption worksheet Formatting conventions followed: units 5 monetary amounts 5 percentages 5 right justified 5 ALL figures used formulas and cell references except in blue box 20 All figures are correct 10 Advising Client worksheet Supplier cost increase (green boxes) Correct comparison figures 5 Explanation (proper grammar, complete sentences, professional) 5 New Sales mix (yellow boxes) Correct comparison figures 5 Explanation (proper grammar, complete sentences, professional) 5 Alternative contract (purple boxes) Correct comparison figures 5 Explanation (proper grammar, complete sentences, professional) 5 Data on worksheet follows formatting conventions 5 Other 3 Worksheets Other 3 worksheets properly labeled 5 All figures in the blue 'ASSUMPTIONS' boxes are correct 10 Total 100