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
		NPV with minimum Premium cannibalization (50%)

Assumptions Advertising Increase Per Year Capital Expenditures One-Time Development Expenses 5-Year Depreciation Schedule Discount Rate $2,000,000 $4,000,000 $1,500,000 8% 40% 15%% ax Base Case Cannibalization (Basic) Base Case Cannibalization (Premium) | (50% + 60% / 2) 55% Base Case Cannibalization (Advanced) | (65% 75% / 2-70% Base Case Marketing Per Year 6,000,000 Assumptions Advertising Increase Per Year Capital Expenditures One-Time Development Expenses 5-Year Depreciation Schedule Discount Rate $2,000,000 $4,000,000 $1,500,000 8% 40% 15%% ax Base Case Cannibalization (Basic) Base Case Cannibalization (Premium) | (50% + 60% / 2) 55% Base Case Cannibalization (Advanced) | (65% 75% / 2-70% Base Case Marketing Per Year 6,000,000