create the following worksheets for Target Corporation Company from the year 2014 to 2016:

1. discounted cash flow worksheet and analysis

2. pro forma worksheet and analysis

Annual Financials for Target Corp. Cash FLow Analysis

Operating Activities

Fiscal year is February-January. All values USD millions.	2013	2014	2015	2016	2017	5-year trend
Net Income before Extraordinaries	3B	2.69B	(1.64B)	3.36B	2.74B
Net Income Growth	-	-10.17%	-160.73%	305.56%	-18.61%
Depreciation, Depletion & Amortization	2.14B	2B	2.13B	2.21B	2.3B
Depreciation and Depletion	2.12B	1.98B	2.11B	2.19B	2.28B
Amortization of Intangible Assets	22M	20M	22M	23M	18M
Deferred Taxes & Investment Tax Credit	(14M)	58M	7M	(322M)	41M
Deferred Taxes	(14M)	58M	7M	(322M)	41M
Investment Tax Credit	-	-	-	-	-
Other Funds	164M	(718M)	3.79B	145M	574M
Funds from Operations	5.29B	4.03B	4.29B	5.4B	5.65B
Extraordinaries	-	-	-	-	-
Changes in Working Capital	34M	2.49B	150M	445M	(214M)
Receivables	(217M)	157M	-	-	-
Accounts Payable	199M	213M	777M	534M	(543M)
Other Assets/Liabilities	(101M)	2.62B	(115M)	227M	36M
Net Operating Cash Flow	5.33B	6.52B	4.44B	5.84B	5.44B
Net Operating Cash Flow Growth	-	22.44%	-31.92%	31.65%	-6.98%
Net Operating Cash Flow / Sales	7.26%	9.15%	6.11%	7.92%	7.82%

Investing Activities

	2013	2014	2015	2016	2017	5-year trend
Capital Expenditures	(3.28B)	(1.89B)	(1.79B)	(1.44B)	(1.55B)
Capital Expenditures (Fixed Assets)	(3.28B)	(1.89B)	(1.79B)	(1.44B)	(1.55B)
Capital Expenditures (Other Assets)	-	-	-	-	-
Capital Expenditures Growth	-	42.45%	5.30%	19.48%	-7.58%
Capital Expenditures / Sales	-4.47%	-2.65%	-2.46%	-1.95%	-2.23%
Net Assets from Acquisitions	-	(157M)	(20M)	-	-
Sale of Fixed Assets & Businesses	66M	70M	95M	1.9B	46M
Purchase/Sale of Investments	102M	130M	106M	24M	28M
Purchase of Investments	-	-	-	-	-
Sale/Maturity of Investments	102M	130M	106M	24M	28M
Other Uses	-	(1.55B)	(321M)	-	-
Other Sources	254M	3.12B	-	19M	-
Net Investing Cash Flow	(2.86B)	(271M)	(1.93B)	508M	(1.47B)
Net Investing Cash Flow Growth	-	90.51%	-610.70%	126.38%	-389.96%
Net Investing Cash Flow / Sales	-3.89%	-0.38%	-2.65%	0.69%	-2.12%

Financing Activities

	2013	2014	2015	2016	2017	5-year trend
Cash Dividends Paid - Total	(869M)	(1.01B)	(1.21B)	(1.36B)	(1.35B)
Common Dividends	(869M)	(1.01B)	(1.21B)	(1.36B)	(1.35B)
Preferred Dividends	-	-	-	-	-
Change in Capital Stock	(1.52B)	(1.46B)	-	(3.44B)	(3.49B)
Repurchase of Common & Preferred Stk.	(1.88B)	(1.46B)	-	(3.44B)	(3.71B)
Sale of Common & Preferred Stock	360M	-	-	-	221M
Proceeds from Stock Options	-	-	-	-	-
Other Proceeds from Sale of Stock	360M	-	-	-	221M
Issuance/Reduction of Debt, Net	(88M)	(4.35B)	(166M)	(85M)	(664M)
Change in Current Debt	(530M)	(890M)	(80M)	-	-
Change in Long-Term Debt	442M	(3.46B)	(86M)	(85M)	(664M)
Issuance of Long-Term Debt	1.97B	-	1.99B	-	1.98B
Reduction in Long-Term Debt	(1.53B)	(3.46B)	(2.08B)	(85M)	(2.64B)
Other Funds	(16M)	456M	373M	369M	-
Other Uses	(16M)	-	-	-	-
Other Sources	-	456M	373M	369M	-
Net Financing Cash Flow	(2.49B)	(6.36B)	(998M)	(4.52B)	(5.5B)
Net Financing Cash Flow Growth	-	-155.79%	84.32%	-352.51%	-21.72%
Net Financing Cash Flow / Sales	-3.39%	-8.93%	-1.37%	-6.12%	-7.91%
Exchange Rate Effect	8M	26M	-	-	-
Miscellaneous Funds	-	-	-	-	-
Net Change in Cash	(10M)	(89M)	1.52B	1.84B	(1.53B)
Free Cash Flow	2.05B	4.63B	2.65B	4.41B	3.89B
Free Cash Flow Growth	-	126.27%	-42.75%	66.08%	-11.73%
Free Cash Flow Yield	-	-	-	-	6.85%	NA

Target Corp., free cash flow to the firm (FCFF) forecast

USD $ in millions, except per share data

Year	Value	FCFFt or Terminal value (TVt)	Calculation	Present value at 7.14%
01	FCFF0	4,262
1	FCFF1	4,498	= 4,262 (1 + 5.53%)	4,198
2	FCFF2	4,656	= 4,498 (1 + 3.51%)	4,056
3	FCFF3	4,725	= 4,656 (1 + 1.49%)	3,843
4	FCFF4	4,701	= 4,725 (1 + -0.52%)	3,568
5	FCFF5	4,581	= 4,701 (1 + -2.54%)	3,246
5	Terminal value (TV5)	46,149	= 4,581 (1 + -2.54%) (7.14% -2.54%)	32,696
Intrinsic value of Target's capital				51,607
Less: Debt (fair value)				13,570
Intrinsic value of Target's common stock				38,037
Intrinsic value of Target's common stock (per share)				$68.94
Current share price				$53.23

Weighted Average Cost of Capital (WACC)

Target Corp., cost of capital

1 USD $ in millions

Equity (fair value) = No. shares of common stock outstanding Current share price = 551,708,283 $53.23 = $29,367,431,904.09

Debt (fair value). See Details

2 Required rate of return on equity is estimated by using CAPM. See Details

Required rate of return on debt. See Details

Required rate of return on debt is after tax.

Estimated (average) effective income tax rate = (32.70% + 32.50% + 33.00% + 36.50% + 34.90% + 34.30%) 6 = 33.98%

WACC = 7.14%

FCFF Growth Rate (g)

FCFF growth rate (g) implied by PRAT model

Target Corp., PRAT model

		Average	Jan 28, 2017	Jan 30, 2016	Jan 31, 2015	Feb 1, 2014	Feb 2, 2013	Jan 28, 2012
	Selected Financial Data (USD $ in millions)
	Net interest expense		1,004	607	882	1,126	762	866
	Discontinued operations, net of tax		68	42	(4,085)
	Net earnings (loss)		2,737	3,363	(1,636)	1,971	2,999	2,929
	Effective income tax rate (EITR)1		32.70%	32.50%	33.00%	36.50%	34.90%	34.30%
	Net interest expense, after tax2		676	410	591	715	496	569
	Add: Dividends declared		1,359	1,378	1,273	1,051	903	777
	Interest expense (after tax) and dividends		2,035	1,788	1,864	1,766	1,399	1,346
	EBIT(1 EITR)3		3,345	3,731	3,040	2,686	3,495	3,498
	Current portion of long-term debt and other borrowings		1,718	815	91	1,160	2,994	3,786
	Long-term debt and other borrowings, excluding current portion		11,031	11,945	12,705	12,622	14,654	13,697
	Shareholders' investment		10,953	12,957	13,997	16,231	16,558	15,821
	Total capital		23,702	25,717	26,793	30,013	34,206	33,304
	Ratios
	Retention rate (RR)4		0.39	0.52	0.39	0.34	0.60	0.62
	Return on invested capital (ROIC)5		14.11%	14.51%	11.35%	8.95%	10.22%	10.50%
	Averages
	RR	0.48
	ROIC	11.61%
	Growth rate of FCFF (g)6	5.53%

2017 Calculations

2 Net interest expense, after tax = Net interest expense (1 EITR) = 1,004 (1 32.70%) = 676

3 EBIT(1 EITR) = Net earnings (loss) Discontinued operations, net of tax + Net interest expense, after tax = 2,737 68 + 676 = 3,345

4 RR = [EBIT(1 EITR) Interest expense (after tax) and dividends] EBIT(1 EITR) = [3,345 2,035] 3,345 = 0.39

5 ROIC = 100 EBIT(1 EITR) Total capital = 100 3,345 23,702 = 14.11%

6 g = RR ROIC = 0.48 11.61% = 5.53%

FCFF growth rate (g) implied by single-stage model

g = 100 (Total capital, fair value0 WACC FCFF0) (Total capital, fair value0 + FCFF0) = 100 (42,937 7.14% 4,262) (42,937 + 4,262) = -2.54%

where: Total capital, fair value0 = current fair value of Target's debt and equity (USD $ in millions) FCFF0 = last year Target's free cash flow to the firm (USD $ in millions) WACC = weighted average cost of Target's capital

FCFF growth rate (g) forecast

Target Corp., H-model

Year	Value	gt
1	g1	5.53%
2	g2	3.51%
3	g3	1.49%
4	g4	-0.52%
5 and thereafter	g5	-2.54%

where: g1 is implied by PRAT model g5 is implied by single-stage model g2, g3 and g4 are calculated using linear interpoltion between g1 and g5

Calculations

g2 = g1 + (g5 g1) (2 1) (5 1) = 5.53% + (-2.54% 5.53%) (2 1) (5 1) = 3.51%

g3 = g1 + (g5 g1) (3 1) (5 1) = 5.53% + (-2.54% 5.53%) (3 1) (5 1) = 1.49%

g4 = g1 + (g5 g1) (4 1) (5 1) = 5.53% + (-2.54% 5.53%) (4 1) (5 1) = -0.52%