I Need Help with the Attached Valuation Worksheet; I Need You to Use Publicly Available

I need help with the attached valuation worksheet; I need you to use publicly available information to evaluate/value the Pfizer company. Simply enter the needed information in the highlighted cells - I'm just super busy and on a business trip till 4/28 and don't have time to do the research.

Page 1
INPUTS FOR VALUATION
Current Inputs
Enter the current revenues of the firm =
Enter current capital invested in the firm =
Enter the current depreciation =
Enter the current capital expenditures for the firm =
Enter the change in Working Capital in last year =
Enter beginning shareholder's equity
Enter the value of current debt outstanding =
Enter the number of shares outstanding =
$12,406
$10,000
{ As a nave estimate, you can use BV of debt + BV of Equity)
$233
$298
$115
$20,000
$10,000
1,500.00
High Growth Period
Your Inputs
Enter the growth rate in revenues for the next 5 years =
25.00%
What will all operating expenses be as a % of revenues in the fifth year? 70.00% (Operating expenses include depreciation: This is equal to (1-Pre-tax Operating Margin))
How much debt do you plan to use in financing investments?
Enter the growth rate in capital expenditures & depreciation
50%
25.00%
Enter working capital as a percent of revenues
7.50%
Enter the tax rate that you have on corporate income
36.00%
What beta do you want to use to calculate cost of equity =
1.25
Enter the current long term bond rate =
6.50%
Enter the market risk premium you want to use =
5.50%
Enter your cost of borrowing money =
8.50%
Stable Period
Enter the growth rate in revenues =
6.00%
Enter operating expenses as a % of revenues in stable period =
Enter capital expenditures as a percent of depreciation in this period
75.00%
200.00%
How much debt do you plan to use in financing investments?
5.00%
Enter interest rate of debt in stable period =
7.50%
What beta do you want to use in the stable period =
1.10
ESTIMATED CASHFLOWS
Base
1
2
3
4
5
6
7
8
9
10
Growth in Revenue
25.00%
25.00%
25.00%
25.00%
25.00%
21.20%
17.40%
13.60%
9.80%
6.00%
Growth in Deprec'n
25.00%
25.00%
25.00%
25.00%
25.00%
21.20%
17.40%
13.60%
9.80%
6.00%
Revenues
$12,406
$15,508
$19,384
$24,230
$30,288
$37,860
$45,886
$53,871
$61,197
$67,194
$71,226
Operating Expenses
% of Revenues
70.00%
70.00%
70.00%
70.00%
70.00%
70.00%
71.00%
72.00%
73.00%
74.00%
75.00%
- $ Operating Expenses
$8,684
$10,855
$13,569
$16,961
$21,202
$26,502
$32,579
$38,787
$44,674
$49,724
$53,420
EBIT
$3,722
$4,652
$5,815
$7,269
$9,086
$11,358
$13,307
$15,084
$16,523
$17,471
$17,807
Tax Rate
36.00%
36.00%
36.00%
36.00%
36.00%
36.00%
36.00%
36.00%
36.00%
36.00%
36.00%
$22,382
$25,359
$29,081
$33,733
$39,549
$46,818
$55,334
$64,988
$75,563
$86,744
$98,140
$2,382
$2,977
$3,722
$4,652
$5,815
$7,269
$8,517
$9,654
$10,575
$11,181
$11,396
+ Depreciation
$233
$291
$364
$455
$569
$711
$862
$1,012
$1,149
$1,262
$1,338
- Capital Expenditures
$298
$373
$466
$582
$728
$909
$1,263
$1,616
$1,969
$2,322
$2,675
- Change in WC
$115
$233
$291
$363
$454
$568
$602
$599
$549
$450
$302
$2,202
$2,664
$3,329
$4,162
$5,202
$6,503
$7,514
$8,451
$9,206
$9,671
$9,756
Ending equity
EBIT (1-t)
= FCFF
Terminal Value (in '05)
$167,813
COSTS OF EQUITY AND CAPITAL
1
2
3
4
5
6
7
8
9
10
Cost of Equity
13.38%
13.38%
13.38%
13.38%
13.38%
13.21%
13.05%
12.88%
12.72%
12.55%
Proportion of Equity
50.00%
50.00%
50.00%
50.00%
50.00%
59.00%
68.00%
77.00%
86.00%
95.00%
5.44%
5.44%
5.44%
5.44%
5.44%
5.31%
5.18%
5.06%
4.93%
4.80%
50.00%
50.00%
50.00%
50.00%
50.00%
41.00%
32.00%
23.00%
14.00%
5.00%
9.41%
9.41%
9.41%
9.41%
9.41%
9.97%
10.53%
11.08%
11.62%
12.16%
Cumulative WACC
109.41%
119.70%
130.96%
143.28%
156.76%
172.39%
190.54%
211.66%
236.26%
265.00%
Cumulative Cost of Equity
113.38%
128.54%
145.73%
165.22%
187.32%
212.07%
239.73%
270.61%
305.02%
343.29%
Present Value
$2,435
$2,782
$3,178
$3,631
$4,148
$4,359
$4,435
$4,349
$4,093
$67,008
Value of Firm
$100,417
After-tax Cost of Debt
Proportion of Debt
Cost of Capital
FIRM VALUATION
- Value of Debt
$10,000
Value of Equity
$90,417
Value of Equity per Share
$60.28
1995
Value of Firm by year
$ Value of Debt
1996
1997
1998
1999
2000
2001
2002
2003
2004
$100,417
$107,200
$113,956
$120,514
$126,649
$132,061
$137,716
$143,766
$150,491
$158,314
$50,208.61
$53,600
$56,978
$60,257
$63,325
$54,145
$44,069
$33,066
$21,069
$7,916
Page 1
DAE Valuation
EBIT (1-t)
- WACC (CI)
Abnormal Earnings
Terminal AE
PV
PV of AE
+ Capital Invested
+ PV of Chg Capital in Yr 10
= Firm Value
Indicated Price per share
Base
$2,382
5
$7,269
$1,580
$5,690
6
$8,517
$1,661
$6,855
7
$9,654
$1,771
$7,882
8
$10,575
$1,904
$8,671
9
$11,181
$2,054
$9,128
$1,499
$1,957
$2,459
$3,013
$3,629
$83,800
$10,000
$3,651 This reconciles the assumptions on stable growth, ROC and Capital Invested
$3,977
$4,137
$4,097
$3,863
Cumulated Cost of Equity
2
$3,722
$1,379
$2,342
3
$4,652
$1,432
$3,220
4
$5,815
$1,498
$4,318
10
Terminal Year
$11,396
$12,080
$2,216
$3,636
$9,180
$8,444
$137,016
$55,169
$97,450
$64.97
Cost of Equity
ROC
Capital Invested
Calculation of Capital Invested
Initial
+ Net Cap Ex
+ Chg in WC
Ending
1
$2,977
$1,338
$1,640
13.38%
29.77%
$10,000
$10,000
$10,000
13.38%
36.09%
$10,314
13.38%
43.45%
$10,706
13.38%
51.94%
$11,197
13.38%
61.55%
$11,810
13.21%
67.72%
$12,576
13.05%
71.09%
$13,579
12.88%
71.54%
$14,782
12.72%
69.23%
$16,151
12.55%
64.53%
$17,661
$10,000
$81
$233
$10,314
$10,314
$102
$291
$10,706
$10,706
$127
$363
$11,197
$11,197
$159
$454
$11,810
$11,810
$198
$568
$12,576
$12,576
$401
$602
$13,579
$13,579
$604
$599
$14,782
$14,782
$820
$549
$16,151
$16,151
$1,060
$450
$17,661
$17,661
$1,338
$302
$19,301
109.41%
119.70%
130.96%
143.28%
156.76%
172.39%
190.54%
211.66%
236.26%
265.00%
Page 2
0.00%
41.69%
$28,975 (Adjusted to reflect terminal ROC)
DAROE
1
2
3
4
5
6
7
8
9
10
Terminal Year
Forecast ROE
0.13302861 0.146762194 0.159974529 0.172390131 0.183802011 0.181907476 0.174459731 0.162720074 0.147971435 0.131376997
Cost of Equity
13.38%
13.38%
13.38%
13.38%
13.38%
13.21%
13.05%
12.88%
12.72%
12.55%
Abnormal ROE
0.07%
1.30%
2.62%
3.86%
5.01%
4.98%
4.40%
3.39%
2.08%
0.59%
0.59%
Present Value of AROE
0.06%
1.01%
1.80%
2.34%
2.67%
2.37%
1.87%
1.29%
0.71%
0.18%
1.44%
Total DAROE
115.60%
Beginning Equity
$20,000
$23,120
Firm Value
Indicated value per share $15.41
Buffett Valuation Worksheet (January/February 1998, Computerized Investing,
Enter values into shaded cells
Date of Analysis: 12/31/1997
Current Stock Data
Company: Nike, Inc.
Ticker: NKE
Price: $48.25
EPS: $2.77
DPS: $0.48
BVPS: $11.38
P/E: 17.4
Earnings Yield: 5.7%
Dividend Yield: 1.0%
P/BV: 4.2
Seven Year Averages
Return on Equity: 22.8%
Payout Ratio: 15.9%
P/E Ratio-High: 18.4
P/E Ratio-Low: 9.5
P/E Ratio: 14.0
Sustainable Growth 19.2%
(ROE * (1 - Payout Ratio))
Gv't Bond Yield: 6.0%
Historical Company Data
Year
Year 8
Year 7
Year 6
Year 5
Year 4
Year 3
Year 2
Year 1
EPS
0.80
0.94
1.07
1.18
0.99
1.36
1.88
2.68
DPS
0.09
0.13
0.15
0.19
0.20
0.24
0.29
0.38
BVPS
2.62
3.39
4.35
5.33
5.77
6.68
8.28
10.63
Price
High
8.70
11.98
18.94
22.56
22.31
19.13
35.19
64.00
P/E Ratio
High
3.20
10.9
6.00
12.7
8.78
17.7
13.75
19.1
10.78
22.5
11.56
14.1
17.19
18.7
31.75
23.9
Low
Low
4.0
6.4
8.2
11.7
10.9
8.5
9.1
11.8
ROE
30.5%
27.7%
24.6%
22.1%
17.2%
20.4%
22.7%
25.2%
EPS
DPS
BVPS
High Price Low Price
Annually Compounded Rates of Growth (7 year)
[(Year 1 / Year 8) ^ (1/7)] - 1
18.9%
22.8%
22.1%
33.0%
38.8%
Annually Compounded Rates of Growth (3 year)
[(Year 1 / Year 4) ^ (1/3)] - 1
39.4%
23.9%
22.6%
42.1%
43.3%
Projected Company Data Using Historical Earnings Growth Rate
Year
Current
Year 1
Year 2
Year 3
Year 4
Year 5
Year 6
Year 7
Year 8
Year 9
Year 10
EPS
$2.77
3.29
3.91
4.65
5.53
6.57
7.81
9.28
11.03
13.11
15.58
DPS
0.44
0.52
0.62
0.74
0.88
1.05
1.24
1.48
1.76
2.09
2.48
15.58 Earnings after 10 years
13.29 Sum of dividends paid over 10 years
$217.43 Projected price (Average P/E * EPS)
$230.72 Total gain (Projected Price + Dividends)
16.9% Projected return using historical EPS growth rate
[(Total Gain / Current Price) ^ (1/10)] - 1
Projected Company Data Using Sustainable Growth Rate
Year
Current
Year 1
Year 2
Year 3
Year 4
Year 5
Year 6
Year 7
Year 8
Year 9
Year 10
BVPS
$11.38
13.57
16.17
19.28
22.98
27.39
32.66
38.93
46.41
55.32
65.94
EPS
2.60
3.10
3.69
4.40
5.25
6.26
7.46
8.89
10.60
12.64
15.06
DPS
0.41
0.49
0.59
0.70
0.84
1.00
1.19
1.42
1.69
2.01
2.40
15.06 Earnings after 10 years (BVPS * ROE)
12.72 Sum of dividends paid over 10 years
$210.23 Projected price (Average P/E * EPS)
$222.96 Total gain (Projected Price + Dividends)
16.5% Projected return using sustainable growth rate
[(Total Gain / Current Price) ^ (1/10)] - 1
Payout
Ratio
11.3%
13.8%
14.0%
16.1%
20.2%
17.6%
15.4%
14.2%