One of the basic methods to examine the share price of a company is to utilize the Discounted Cash Flow Analysis (DCF). The Purpose of this article is, applying Monte Carlo method for a DCF to reach at least standard deviation (STDEV). I again chose financial case of Nike, Inc: Cost of Capital because this case has a ready Discounted Cash Flow analysis that we can use it as a good template just like to below spreadsheet:
Note: By using above template, you will be able to make your own DCF analysis for any company, if you have at least two years the Balance sheet and Income statement of the company. These data have been included in annual report of each company.
In the reference with my previous post of http://www.emfps.org/2011/06/case-analysis-of-nike-inc-cost-of.html
Note: By using above template, you will be able to make your own DCF analysis for any company, if you have at least two years the Balance sheet and Income statement of the company. These data have been included in annual report of each company.
In the reference with my previous post of http://www.emfps.org/2011/06/case-analysis-of-nike-inc-cost-of.html
I stated: “…But I am willing to
tell you that it can be a complex case in which we can doubt about sensitivity
analysis done by Kimi Ford (portfolio
manager) too. Because her assumptions such as Revenue Growth Rate, COGS /
Sales,
S &A / Sales, Current Assets / Sales, and Current Liability /
Sales have been adopted from previous income statements and balance sheets from
1995 to 2001. Perhaps, we can take new assumptions.”
As we know, the most crucial thing to bear in mind for a true
financial analysis is to reach to the accurate and reasonable assumptions. We
usually use from five years of annual reports to gather data from income
statements and balance sheets as the sources of our assumptions. This is only
an internal analysis and maybe it will be enough for small size companies. But to
analyze the big size companies, we should not only have an internal analysis
but also external analysis such as PEST and Porter’s Five Forces to find out
competitive advantages. In this case, I have only examined the influence of the
economic indicators included in Macroeconomic as driving forces but we as well
as know to take a good external analysis, we should analyze the impacts of
Political issues, Society-Culture, Technology and prepare a SWOT analysis
compatible with value chain (value- added) and Porter’s five forces. This is
only a sample of external – internal analysis for Case of Nike, Inc. in which I
would like to expand a Monte Carlo Analysis on this case. How can we do our
analysis?
In this article, I am willing to tell you the method of Monte
Carlo Analysis done on the case of Nike, Inc.: Cost of Capital step by step as
follows:
Ø
At the first, we should make a
spreadsheet just like EXHIBIT 2 (Discounted Cash Flow Analysis) made by Kimi
Ford. This spreadsheet will be our basic platform of the simulation model
(Monte Carlo).
Ø
We should have so many scenarios on
assumptions such as Revenue Growth rate (%), COGS / Sales (%), S & A /
Sales (%), Tax rate (%), Current assets / Sales (%), Current liabilities/ Sales
(%), Terminal value growth rate (%) and merge all scenarios to find out what
the share price is most sensitive to assumptions. In fact, we would like to
know which assumptions have most impact on enterprise value or share price.
Ø
To make the scenario analysis on
your spreadsheet (Excel 2007), please go to Data – What-If Analysis –
Scenario Manager – Add and write the name of scenario – Changing Cell – write
the range of your assumption – Protection –Hide – OK.
Ø
I already made so many scenarios
and I merged them together where I found that the assumptions of COGS / Sales
(%), S & A / Sales (%) have most impact on share price and enterprise
value.
Ø
Kimi Ford considered a range of COGS
/ Sales between 0.58 and 0.6, and a range of S & A / Sales between 0.25 and
0.28. These ranges could be compatible with five years income statement which
is an internal analysis.
Ø
But to take an external analysis,
we should find the economic indicators which are driving forces on COGS / Sales
and S & A / Sales. Then we should consider the probability distribution for
each range of COGS / Sales and S & A / Sales in accordance with data
collected from economic indicators.
Ø
Firstly, the timing of our external
analysis is very important. We should bear in mind that we are on July 5,
2001(the date of the Case). Therefore. We should collect and select economic
indicators data before 2001 year to expand our projection of probability
distribution for next 10 years until 2011year.
Ø
The economic indicators, which are
affecting on COGS / Sales and S & A / Sales, are Unemployment rate,
Inflation rate, PPI (Product Price Index), CPI (Consumer Price Index), and
Economic growth rate.
Ø
I collected and selected the economic
indicator data from below links:
Ø The relationship between inflation rate and unemployment rate is vice
versa. It means that in the period of high inflation rate, the rate of
unemployment will decrease. But a high inflation rate will increase the price
of goods sold including the cost of hiring the workers. The employers have to
pay the demand of workers for higher wages during the period of low
unemployment rate. If we have a high unemployment rate but during the period of
high inflation, the Consumer Price Index will be increase in which the price of
goods in stores will go up. Whereas the employers are able to hire the cheaper
workers in the period of high unemployment rate, but if the workers cannot
receive the enough wages or any loan to purchase the goods, the stores will
have to decrease the price of their goods where it will lead to a lower
inflation. It is the same first relationship mentioned between inflation rate
and unemployment rate which is Vice Versa.
Ø According to the data selected by me from above links, the unemployment
rate fell down from 1995 to 2000 year while we had an increase on PPI and
inflation rate. Therefore, my assumption for probability distribution on COGS
/ Sales and S & A / Sales referred to the outcomes are as follows:
Outcomes
|
Probability
|
S&A / sales
|
COGS/sales
|
|||
Stagnant
|
0.1
|
0.25
|
0.58
|
|||
Slow growth
|
0.3
|
0.26
|
0.59
|
|||
Average
growth
|
0.35
|
0.27
|
0.6
|
|||
Rapid growth
|
0.25
|
0.28
|
0.61
|
|||
Ø Now, I can start the Monte Carlo Analysis as follows:
Ø I considered the formula = Rand() for COGS / Sales and S &
A / Sales to generate the random numbers.
Ø I obtained the cut-offs table for COGS / Sales and S & A / Sales separately as
follows:
Cutoffs
|
S&A / sales
|
|
0.25
|
||
0.1
|
0.26
|
|
0.4
|
0.27
|
|
0.75
|
0.28
|
|
cutoffs
|
COGS/sales
|
|
0.58
|
||
0.1
|
0.59
|
|
0.4
|
0.6
|
|
0.75
|
0.61
|
|
Ø
Then, I replaced the formula =
Vlookup instead of numbers 0.6 and 0.28 for 2002 year in spreadsheet and copy
& paste them for all years.
Ø
Finally, I made a Two –Way Data
Table where the column was included the numbers of 1 to 1000 and row was
included as variable of discount rate and the impact of column and row
variables were on share price. When I run this sensitivity analysis, the
calculation was repeated for 1000 times from random numbers of COGS / Sales and
S & A / Sales. You can see this model on following GIF s.
Ø
The result of mean and standard
deviation for share prices in related to the Cost of Capital have been sorted
in below table:
Mean
|
STDEV
|
|
WACC
|
Share price
|
Share price
|
12%
|
30.15
|
2.14
|
11.50%
|
32.48
|
2.39
|
11.17%
|
34.03
|
2.62
|
11%
|
35.02
|
2.64
|
10.50%
|
37.43
|
2.96
|
10%
|
40.63
|
2.93
|
9.50%
|
44.34
|
3.48
|
9%
|
48.79
|
3.97
|
8.50%
|
54.20
|
4.68
|
8%
|
60.16
|
5.33
|
As we can see, above table shows us that if the cost of capital of
Nike increase more than 9.8%, its share price will be overvalue and it will not
be valuable. But, if we refer to my previous analysis mentioned on link: http://www.emfps.org/2011/09/case-analysis-of-nike-inc-cost-of.html, we can find that WACC = 7.92% consequently to purchase
the share price is valuable.
Now, let me return back today and see Close share price of Nike
extracted from Yahoo. Finance from 2001 to 2011 sorted on below table:
Average
for each year
|
||
Year
|
Share price
|
|
2001
|
51.08
|
|
2002
|
50.97
|
|
2003
|
56.21
|
|
2004
|
76.93
|
|
2005
|
83.59
|
|
2006
|
86.10
|
|
2007
|
70.50
|
|
2008
|
61.01
|
|
2009
|
55.40
|
|
2010
|
74.79
|
|
2011
|
87.93
|
|
What do you think about my economic
analysis? Is it true or wrong?
Nowadays, Nike, Inc.’s cost of capital
should be approximately 7%.
All researchers
and individual people, who are interested in having this model, don’t hesitate to
send their request to below addresses:
