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

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.

3) Use the model developed in (1) to test the implications of Christina Whitmans Proposal to Drive Revenue" (pg. 5). Please use a 50% cannibalization rate for the Premium Product and a 15% cannibalization rate for the Basic Product. Show your NPV and IRR results. Now, repeat this step but use

a 57.5% cannibalization rate for the Premium Product and a 15% cannibalization rate for the Basic Product. Show your NPV and IRR results.

a 65% cannibalization rate for the Premium Product and a 15% cannibalization rate for the Basic Product. Show your NPV and IRR results.

4) Use the model developed in (1) to test the implications of Margaret Tans Proposal to Minimize Cannibalization (i.e. raising the price to $23 dollar and minimize the cannibalization rate to 45% - pg.6) Show your NPV and IRR results. And Summarize your results for part (1) to (4) using the summary of scenarios template provided in the excel file.

5) Going back to the base case( question (1) & (2) ), assuming for base case, there is a 30% probability for 50% cannibalization rate for the Premium Product, a 40% probability for 55% cannibalization rate for the Premium Product, and a 30% probability for 60% cannibalization rate for the Premium Product, calculate Expected NPV, Standard Deviation of NPV, and Coefficient Variation of NPV. Assuming P&J generally accepts project with a coefficient variation range between 0.3 to 0.5, should P&G accept the Premium Product?

Going back to Christina Whitmans Proposal to Drive Revenue" ( question (3 ), assuming for Proposal to Drive Revenue" , there is a 40% probability for 50% cannibalization rate for the Premium Product, a 35%probability for 57.5% cannibalization rate for the Premium Product, and a 25% probability for 65% cannibalization rate for the Premium Product, calculate Expected NPV, Standard Deviation of NPV, and Coefficient Variation of NPV. Assuming P&J generally accepts project with a coefficient variation range between 0.3 to 0.5, should P&G accept the New Product now ?

(6) Monte Carlo Simulation Analysis - Conduct 1,000 trial MC simulations use the base case (question (1)) and the following assumption:

Normal distribution for cannibalization rate of the Premium Product :

o Mean = 50%, Standard deviation = 5%

Normal distribution for per unit revenue of the New Product

o Mean = $23, Standard deviation = $2

Report the following statistics from your trial: Mean, Standard deviation, Maximum, Minimum, Median, Probability of NPV > 0, Coefficient of variation. Also Provide a Histogram of your simulated NPV.

Assuming P&J generally accepts project with a coefficient variation range between 0.3 to 0.5, should P&G accept the New Product now given your simulation results ?

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

			THE PROCTER AND GAMBLE COMPANY: CREST WHITESTRIPS ADVANCED SEAL
			Summary of Scenarios
Scenario		AS units	Prem units	Basic units	Gross margin	Incremental revenue	NPV (M)	IRR
Base case	50% cannibalization
Base case	55% cannibalization
Base case	60% cannibalization
Drive revenue	50% cannibalization
Drive revenue	57.5% cannibalization
Drive revenue	65% cannibalization
Min. cannibalization	45% cannibalization