Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I feel so confuse how to do the discounted cash flow on excel. Can someone show me how along with the excel formulas? I feel

I feel so confuse how to do the discounted cash flow on excel. Can someone show me how along with the excel formulas? I feel that I am not getting the right numbers with the following case study information. Also, it doesn't work out if I drag the numbers across does it?

FOR YEAR 4, OPTION 1-3 NET WORKING CAPITAL SHOULD BE SET TO ZERO.

Does the project appear economically viable? Use the base case assumptions to build a four-year discounted cash flow model for Advanced Seal.

Calculate the NPV and IRR from the DCF for Option 1 on Excel:

Selling price of Advanced Seal Crest Whitestrips: $22 per unit

Cost per unit: $12

Sales volume: 2 million units per year

Incremental advertising: $2 million

Advertising budget per year: $6 million

Cannibalization from existing P&G sales: Advanced = 70%, Premium = 50-60%, Basic = 15%

Calculate the NPV and IRR from the DCF for Option 2 on Excel:

Selling price of Advanced Seal Crest Whitestrips: $21

Cost per unit: $12

Sales volume: 3.25 million units per year

Incremental advertising: $3.5 million

Advertising budget for year 1 = $7.5 million

Cannibalization from existing P&G sales: Advanced = 70%, Premium = 55%, Basic = 15%

Calculate the NPV and IRR from the DCF for Option 3 on Excel:

Selling price of Advanced Seal Crest Whitestrips: $23

Cost per unit: $12

Sales volume: 1 million units per year

Incremental advertising: $3.5 million

Advertising budget per year = $5 million

Cannibalization from existing P&G sales: Advanced = 70%, Premium = 45%, Basic = 15%

THE PROCTER AND GAMBLE COMPANY: CREST WHITESTRIPS ADVANCED SEAL
Base Case Valuation
Assumptions
Advanced Seal Premium Product Basic Product
Per unit revenue and costs
Revenue $22 $18 $13
COGS $12 $7 $6
Gross profit $10 $11 $7
Cannibalization rate 50% 15%
Year 0 Year 1 Year 2 Year 3 Year 4
Volume
Adv Seal 2,000
Premium -1,000
Basic -300
Incremental 700
SG&A expenses 1,000
Advertising (Adv Seal) 6,000
Foregone advertising (Prem + Basic) 4,000
Capital investment 4,000
Startup costs 1,500
Depreciation schedule 20.0%
Net working capital turnover 9.0
Tax rate 40%
Discount rate 8%
Revenue
Adv Seal 44,000
Premium+Basic -21,900
Incremental revenue 22,100
Gross profit
Adv Seal 20,000
Premium+Basic -13,100
Incremental gross profit 6,900
Incremental advertising exp 2,000
SG&A expenses 1,500 1,000
Depreciation 800
Incremental EBIT -1,500 3,100
Taxes -600 1,240
NOPAT -900 1,860
Net working capital 2,456 2,456 0
Net PP&E 4,000 3,200
Free Cash Flow
NOPAT -900 1,860
+ Depreciation 800
- Capital expenditures 4,000
- Investment in NWC 2,456 0
Free cash flow -7,356 2,660
NPV
IRR

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Green Energy Audit Of Buildings A Guide For A Sustainable Energy Audit Of Buildings

Authors: Giuliano Dall’O’

2013 Edition

1447161726, 978-1447161721

More Books

Students also viewed these Accounting questions