ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Name of companyNovo Nordisk (NVO)
This and the cost of capital worksheets are the only ones that you have to input. The
2
Date of valuationNov-25
other worksheets will depend on your choices.
3
Country of incorporationDenmark
4
Current Price per share = (all currency in DKK )$314.00Input cell
5
Number of shares outstanding =4443.00
Computed cell (from another inputted item in worksheet)
6
Do you want to capitalize R&D or other expenses?No
If yes, please enter numbers in R&D worksheet
7
Do you want to convert leases to debt?Yes
If yes, please enter numbers in operating lease worksheet
8
Marginal tax rate to use (for cost of debt)20.60%Marginal tax rate
Effective tax rate (high growth period)
Effective Growth rate (stable)
9
Trapped cash balancePatriot Games20.60%20.60%20.60%
10
What tax regime do you want to use in valuation? Wait-it-out Wait-it-out20.60%20.40%20.60%
11
Input pageGo Irish20.60%20.40%30.68%
12
Market inputs for your company$1,395,102.00
13
Market Capitalization =
Measures of profitability, leverage & growth
14
From the balance sheetMost recent yearPrevious yearROE =71.78%
15
Book value of equity =$169,869.00$143,486.00ROIC =47.10%
16
Total Debt Outstanding & Minority Interests$101,214.00$102,787.00Net margin =32.64%
17
Cash, Marketable Securities and Other Cross Holdings$32,973.00$26,708.00Pre-tax operating margin =42.43%
18
From the income statementD/E ratio (book) =59.58%
19
Revenues$315,603.00$290,403.00D/E ratio (market) =7.74%Unadjusted
Adjusted (for R&D & Leases)
20
Operating income (EBIT) =$132,659.00$128,339.00Growth rate (revenue)8.68%Operating Income$132,659.00 $ 133,909.16
21
Interest Expense$8,000.00$1,838.00Growth Rate (EBIT)3.37%Net Income$103,000.00 $ 103,000.00
22
Effective tax rate =20.40%(See tax rate WS)Reinvestment Rate 75.63%See Reinvestment WorksheetInvested Capital$219,565.00 $ 226,290.85
23
Net Income$103,000.00
Growth given current ROIC & Reinv Rate
35.63%Book Equity$143,486.00 $ 143,486.00
24
Your high growth inputs Reinvestment $ 46,757.00 $ 46,757.00
25
Expected growth rate in operating income=11.13%
To solve for the implied growth in your market value, follow these steps:
Debt$101,214.00 $ 107,939.85
26
Do you want to use your current ROIC for future investmentsYes
1. Open the Goal Seek function in Excel (under Tools)
27
If no, enter the ROIC for high growth period9.00%
2. Set cell B31 to the value in cell B1 by changing cell B5
28
Return on Invested capital on growth investments=47.10%
3. You should see the imputed growth rate in cell B5.
29
Length of growth period =7
30
Your stable growth inputs
31
Return on invested capital in stable growth =25.00%
32
Effective tax rate in stable growth =22.00%
33
Your cost of capital inputs
34
Cost of equity =8.41%
Use worksheet for Cost of Capital
35
Cost of capital =7.90%
Use worksheet for Cost of Capital
36
Riskfree rate =2.55%
37
Employee Options (if any)
38
Do you have employee options outstanding?No
39
Number of options outstanding =
40
Average strike price =
41
Average maturity =
42
Standard deviation on stock price =
43
Dividend yield on the stock =3.67%
44
Intrinsic ValueMarket ValueSo really, this stock is overvalued?
45
Value of assets in place =$1,321,615.40Market value of equity$1,395,102.00
46
Value added by future growth =$1,499,282.07Enterprise value$1,463,343.00
47
Intrinsic enterprise value$2,820,897.47
48
 + Cash and Cross holdings$32,973.00
49
 - Debt and Minority Interests$107,939.85
50
 - Tax due on trapped cash brought back$0.00
51
 - Tax due on deferred taxes during high growth$1,701.34
52
Intrinsic equity value$2,744,229.27
53
Value of options (management)$0.00
54
Intrinsic equity value per share $617.65
55
Growth Analysis
56
Price you are paying for growth =$141,727.60
57
Value of this growth =$1,499,282.07
58
Price of growth/ Value of growth9.45%
59
Breakdown of multiples
60
ActualIntrinsic
61
TotalAssets in PlaceGrowthTotalAssets in placeGrowth
62
P/E13.5412.171.3826.7212.1714.56
63
P/BV8.217.380.8316.207.388.83
64
EV/Sales4.424.190.238.944.194.75
65
EV/Invested Capital5.865.550.3111.855.556.30
66
67
If you are interested in the mechanics of the valuation, here are the cash flows (for up to 10 years of high growth… if your growth period exceeds 10 years, this detailed cash flow table will not work)
68
No Growth Scenario
69
Reinvestment Rate =0%
70
01234567
71
After-tax Operating Income$105,596.56$105,596.56$105,596.56$105,596.56$105,596.56$105,596.56$105,596.56
72
 - Reinvestment$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00$0.00
73
FCFF$105,596.56$105,596.56$105,596.56$105,596.56$105,596.56$105,596.56$105,596.56$0.00$0.00$0.00
74
$97,869.19$90,707.29$84,069.49$77,917.43$72,215.56$66,930.95$62,033.06$0.00$0.00$0.00
75
Terminal year reinvestment (in year n+1)$0.00$551,742.97
76
Terminal year FCFF (in year n+1)$103,474.02
77
Terminal Value (in year n)$1,310,525.09
78
Value today$1,321,615.40
79
80
Growth Scenario
81
Reinvestment Rate =23.63%
! Expected growth rate in high growth rate/ Return on Invested capital in high growth
82
Reinvestment Rate (stable growth)10.2000%
! Assume stable growth rate = riskfree rate; return on capital in stable growth = cost of capital
83
01234567
84
After-tax Operating Income$117,349.46$130,410.46$144,925.14$161,055.31$178,980.76$198,901.32$221,039.04$0.00$0.00$0.00
85
 - Reinvestment$24,951.04$27,728.09$30,814.22$34,243.85$38,055.19$42,290.73$46,997.69$22,545.98$0.00$0.00$0.00
86
FCFF-$24,951.04$89,621.37$99,596.23$110,681.29$123,000.12$136,690.04$151,903.64$198,493.06$0.00$0.00$0.00
87
Terminal year operating income$222,119.24
88
Terminal year FCFF$199,463.08
89
Terminal Value$3,731,339.95
90
Value today$2,820,897.47
91
92
93
Value added by growth1,499,282.07
94
1234567
95
Taxes paid (based on effective tax rate for high growth period)$30,074$33,422$37,142$41,275$45,869$50,975$56,648$0$0$0
96
Taxes deferred (based on US marginal tax rate)$295$328$364$405$450$500$555$0$0$0
97
Cumulated Taxes Deferred$295$623$987$1,391$1,841$2,341$2,896$2,896$2,896$2,896
98
99
100