Question
1) Use the base case assumptions (pg. 4) as well as the information presented in the case to build a four-year discounted cash flow model
1) Use the base case assumptions (pg. 4) as well as the information presented in the case to build a four-year discounted cash flow model for Advanced Seal given a 50% cannibalization rate for the Premium Product and a 15% cannibalization rate for the Basic Product. What are your NPV and IRR results? Please use the Basic Template from the excel file provided for the project.
2) Calculate (1) again using a 55% cannibalization for the Premium Product and a 15% cannibalization rate for the Basic Product. Show your NPV and IRR results. Also, calculate (1) again using a 60% cannibalization rate for the Premium Product and a 15% cannibalization rate for the Basic Product. Show your NPV and IRR results.
You must use scenario manager function as well as simulation from EXCEL for this Project. (Also please show me the formulas you've used on excel)
SUPPORTING DOCUMENT: https://imgur.com/a/Yy2NNF1
| 0 | THE PROCTER AND GAMBLE COMPANY: CREST WHITESTRIPS ADVANCED SEAL | ||||
| Discounted Cash Flow 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 | |||||
| Premium | |||||
| Basic | |||||
| Incremental | |||||
| SG&A expenses | |||||
| Advertising (Adv Seal) | |||||
| Foregone advertising (Prem + Basic) | |||||
| Capital investment | |||||
| Startup costs | |||||
| Depreciation schedule | |||||
| Net working capital turnover | |||||
| Tax rate | |||||
| Discount rate | |||||
| Revenue | |||||
| Adv Seal | |||||
| Premium+Basic | |||||
| Incremental revenue | |||||
| Gross profit | |||||
| Adv Seal | |||||
| Premium+Basic | |||||
| Incremental gross profit | |||||
| Incremental advertising exp | |||||
| SG&A expenses | |||||
| Depreciation | |||||
| Incremental EBIT | |||||
| Taxes | |||||
| NOPAT | |||||
| Net working capital | |||||
| Net PP&E | |||||
| Free Cash Flow | |||||
| NOPAT | |||||
| + Depreciation | |||||
| - Capital expenditures | |||||
| - Investment in NWC | |||||
| Free cash flow | |||||
| Discounted value | |||||
| NPV | |||||
| IRR |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Advanced Accounting
Authors: Joe Hoyle, Thomas Schaefer, Timothy Doupnik
10th edition
0-07-794127-6, 978-0-07-79412, 978-0077431808
