Calculate the Drive Revenue Proposal and fill in the information...

Year 0 Year 1 Year 3 Year 4 Revenue Adv Seal Premium + Basic Incremental Revenue Gross Profit Adv Seal Premium + Basic Incremental Gross Profit Incremental Ad Expense SG&A Expenses Depreciation Incremental EBIT Taxes NOPATNet working capital Net PP&E Free Cash Flow NOPAT + Depreciation - Capital expenditures - Investment in NWC Free Cash Flow Free Cashflow Discounted Value IRR NPVFinancial Planning and Strategy MGMG-518 Case 22 The Procter 8:. Gamble Company Hocter&Gamble Case Background In May 2008, the Procter 8: Gamble (P&G) North America Oral Care group is considering launching a new Crest Whitestrips line, Advanced Seal. The product improves on existing athome tooth enamel whitening treatments, providing better adhesion to the teeth and thus better whitening results. The group is concerned about the willingness of customers to adopt the new line and the effect the new line will have on existing P&G whitening products. Assumptions 1. Sales volume forecast of 2 million units for Advanced Seal 2. $2M increase in Advertising Cost per year 3. S4M for Capital Expenditure with 5-year accelerated depreciation schedule 4. $1M per year for incremental selling, general, and administrative expenses 5. $1.5M One-Time Development Expense 6. 8% Discount Rate and 40% Tax Rate 9. 9 NWC ratio Base Case 1. Price for Advanced Seal Crest Whitestrips at $22 per unit 2. $12 per unit for cost 3. 2 million units for units sold 4. $6M Advertising Expense per year 6. 70% Cannibalization Rate from Existing P&G Sales 7. 55% cannibalization rate on Premium units 7. 15% cannibalization rate on Basic units Base case Valuation (DCF model Method) Unit: Million 5 Operating Revenue 2008 2009 2010 2011 2012 Product Sales Advance Seai - 2M units*$22 44.00 44.00 44.00 44.00 Product Sales Premium - 2M units*$18/ 55% cannibalization rate 36.00 16.20 16.20 16.20 16.20 Product Sales Basic - 2M units*$13/ 15% cannibalization rate 26.00 22.10 22.10 22.10 Total Revenue 62.00 82.30 82.30 82.30 82.30 - NWC - NWC ratio is 9 6.89 9.14 9.14 9.14 9.14 Operating Expenses I COGS Advance Seal -2M unit5*$12 24.00 24.00 24.00 24.00 COGS Premium -2M unit5*$7 14.00 6.30 6.30 6.30 COGS Basic-2M unit5*$6 12.00 10.20 10.20 10.20 Total Opeating Expenses 26.00 40.50 40.50 40.50 40.50 - Gross Prot 36.00 41.80 41.80 41.80 41.80 Overhead - SG8LA 1.00 1.00 1.00 Advertising 4.00 6.00 6.00 6.00 6.00 Depreciation Expense tota! 54m 0.00 0.80 1.28 0.77 0.46 Development cost -51.5M One-Time 1.50 Total Overhead Costs 5.50 7.80 8.28 7.77 7.46 Earning Before Income Tax 30.50 34.00 33.52 34.03 |34.34 Income Taxes (40%) 12.20 13.60 13.41 13.61 13.74 Net Income 18.30 20.40 20.11 20.42 20.60 Depreciation Expense -total $4m 0.0 0.80 1.28 0.77 0.46 Operating Cash Flow [Depre excluded] 18.30 21.20 21.39 21.19 21.06 Change in NWC 2.26 Capital Spending 5.50 Total cash flow 19.63 19.81 19.62 19.50 NPV 70.80 Drive Revenue 1. Increase in Advertising Budget from $6M to $7.5M on year 1 2. Reduces price to $21 for Advanced Seal 3. Because of the reduced price, annual sales volume will increase up to 1.25 million up to 3.25 million units per year 4. 60% cannibalization rate on Premium units Drive Revenue case Valuation (DCF model Method) Unit : Million $ Operating Revenue 2008 2009 2010 2011 2012 Product Sales Advance Seal - 3.25M units*$21 58.25 68.25 68.25 68.25 Product Sales Premium - 2M units*$18 / 60% cannibalization rate 36.00 14.40 14.40 14.40 14.40 Product Sales Basic - 2M units *$13 / 15% cannibalization rate 26.00 22.10 22.10 22.10 22.10 Total Revenue 62.00 104.75 104.75 104.75 104.75 NWC - NWC ratio is 9 5.89 11.64 11.64 11.64 11.64 Operating Expenses COGS Advance Seal -3.25M units*$12 39.00 39.00 39.00 39.00 COGS Premium -2M units*$7 14.00 6.30 6.30 5.30 6.30COGS Basic -2M units*$6 12.00 10.20 10.20 10.20 10.20 Total Opeating Expenses 26.00 55.50 55.50 55.50 55.50 Gross Profit 36.00 49.25 49.25 49.25 49.25 Overhead SG&A 1.00 1.00 1.00 1.00 Advertising .00 7.50 6.00 5.00 6.0 Depreciation Expense -total $4m 0.00 0.80 1.2 0.7 0.46 Development cost -$1.5M One-Time 1.50 Total Overhead Costs 5.50 9.30 8.28 7.77 7.46 Earning Before Income Tax 30.50 39.95 40.97 41.48 41.79 Income Taxes (40%) 12.20 15.98 16.39 16.59 16.72 Net Income 18.30 23.97 24.58 24.8 25.07 Depreciation Expense -total $4m 0.00 0.80 1.28 0.77 0.46 Operating Cash Flow [Depre excluded] 18.30 24.77 25.86 25.66 25.53 Change in NWC 4.75 Capital Spending 5.50 Total cash flow 22.94 23.95 23.76 23.64 NPV 84.03 Minimize Cannibalization 1. Raising price at $23 2. Reduces advertising budget to $5M 3. Decline in unit sales to $1M per year 4. 45% cannibalization rate on Premium units Minimize Cannibalization case Valuation (DCF model Method) Unit : Million $ Operating Revenue 2008 2009 2010 2011 2012 Product Sales Advance Seal - 1M units*$23 23.00 23.00 23.00 23.00 Product Sales Premium - 2M units*$18 / 45% cannibalization rate 36.00 19.80 19.80 19.80 19.80Product Sales Basic - 2M units *$13 / 15% cannibalization rate 26.00 22.10 22.10 22.10 22.10 Total Revenue 62.00 64.90 64.90 64.90 64.90 NWC - NWC ratio is 9 6.89 7.21 7.21 7.21 7.21 Operating Expenses COGS Advance Seal -1M units*$12 12.00 12.00 12.00 12.00 COGS Premium -2M units*$7 14.00 7.70 7.70 7.70 7.70 COGS Basic -2M units*$6 12.00 10.20 10.20 10.20 10.20 Total Opeating Expenses 26.00 29.90 29.90 29.90 29.90 Gross Profit 36.00 35.00 35.00 35.00 35.00 Overhead SG&A 1.00 1.00 1.00 1.00 Advertising .00 5.00 5.0 5.00 5.00 Depreciation Expense -total $4m 0.00 0.80 1.28 0.77 0.46 Development cost -$1.5M One-Time 1.50 Total Overhead Costs 5.50 5.80 7.28 6.77 6.46 Earning Before Income Tax 30.50 28.20 27.72 28.23 28.54 Income Taxes (40%) 12.20 11.28 11.0 11.29 11.42 Net Income 18.30 16.92 16.63 16.94 17.12 Depreciation Expense -total $4m 0.00 0.80 1.28 0.77 0.46 Operating Cash Flow [Depre excluded] 18.30 17.72 17.91 17.71 17.58 Change in NWC 0.32 Capital Spending 5.50 Total cash flow 16.41 16.59 16.40 16.28 NPV 59.85