Discounted Cash Flow Analysis plus Monte Carlo Method to Analyze Share Price of a Company

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

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:

 http://data.bls.gov/pdq/SurveyOutputServlet

              http://www.bls.gov/ppi/

              http://www.bls.gov/bls/newsrels.htm#OEUS

              http://www.infoplease.com/ipa/A0104719.html

              http://data.bls.gov/pdq/SurveyOutputServlet

              http://www.usinflationcalculator.com/inflation/historical-inflation-rates/

              http://www.bea.gov/

              ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt

              http://inflationdata.com/inflation/consumer_price_index/historicalcpi.aspx

              http://inflationdata.com/inflation/inflation_rate/historicalinflation.aspx

Ø  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	0.25
0.1	0.26
0.4	0.27
0.75	0.28
cutoffs	COGS/sales
0	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:

https://t.me/Allisnumber

Efficient Portfolio of Assets: Markov Chain & the Constant Eigenvector

Following to my previous article of “EMFPS: Efficient Portfolio of Assets (CON): Application of Markov Chain” posted on the link of “http://emfps.blogspot.com/2011/11/emfps-efficient-portfolio-of-assets-con.html“, I am willing to continue my debate about Zero –Risk (Risk free). How can we find the zero- risk on portfolio of assets?

At the first, we should bear in mind that there is the fundamental issue of Markov Chain which is as follows:

“If Matrix (A) is the result of Markov chain, Vector [ai1] will be the constant Eigenvector of Matrix (A), Matrix (A') and Matrix (A^k) in which all elements of vector [ai1] are the same or vector [ai1] is the Scalar Multiplication of unit vector. It means that: A* c* [ai1] = (lambda)*c*[ai1] where:  ai1 = 1, Eigen value (lambda) = 1, Scalar Multiplication = c”

For example, assume we have:

 Matrix (A) =

0.3	0.12	0.18	0.2	0.15	0.05
0.1	0.2	0.3	0.05	0.17	0.18
0.2	0.1	0.12	0.21	0.18	0.21
0.1	0.18	0.15	0.25	0.1	0.2
0.1	0.13	0.25	0.16	0.14	0.25
0.2	0.09	0.13	0.17	0.25	0.15

Matrix (A) ^2 =

0.179	0.138	0.186	0.186	0.151	0.159
0.16	0.1293	0.187	0.163	0.177	0.184
0.168	0.1337	0.181	0.183	0.164	0.171
0.16	0.1414	0.182	0.177	0.165	0.175
0.161	0.1289	0.173	0.178	0.176	0.184
0.165	0.1328	0.188	0.182	0.16	0.173

Unit Vector (a) =

1

1

1

1

1

1

And, C =12

[Matrix^2] * C * Unit vector (a) = (lambda) * C * Unit vector (a)

12

12

12

12

12

12

Where: lambda = 1

Now, let me turn back to the article of “EMFPS: Efficient Portfolio of Assets (The Optimization for Risk, Return and Probability)” on link: http://emfps.blogspot.com/2011_10_09_archive.html

In that article, we wanted to decrease the risk of portfolio assets in given expected return rate.

If we assume the matrix of assets sorted by annual return and the time is completely compatible with Matrix of Markov chain, we are able to reach the zero – risk (Risk Free).

 As I already stated that it is clear, our assumptions are not exactly accurate. But to solve any problem, we should be able to simplify a complicated problem into boundaries conditions in which we need to sure if our assumptions are reasonable. In fact, there is the fundamental difference between the accurate and the reasonable.

I think the assumption of total sum return rate of all assets for each given time just equal to 100% could be considered the reasonable because we can increase varieties of the assets into our portfolio where the total return rate will be equal to 100%. This is the most important assumption to apply Markov chain in this article.

Therefore, the steps of reaching to Zero – Risk are as follows:

-Total sum return rate of all assets is obtained just equal to 100% for the given time for instance one year.

-Referring to above mentioned, Matrix (X) which is included the proportions of each asset will be equal to Matrix (P) which is Expected Portfolio Return Annually (Rp). In this case, we should not only consider the number of years that we are anticipating but also we should make a square matrix. In the result, the elements of both matrixes will be equal to:

(100 / number of years) %

-The most important step is to be equal all related probabilities distribution for outcomes. In other word, it is to be fixed Expected Portfolio Return Annually (Rp) during the period of one year. The access to the constant return rate is very challenging. As I mentioned in my previous article of “Efficient Portfolio of Assets: Application of Markov Chain (CON)” on below link: http://emfps.blogspot.com/

“This is a game and maybe the application of the Game Theory will help us to find the best analysis. Application of the Game Theory after PEST, Industry and SWOT analysis will guide us to find how much percentage of the shares and which ones should be purchased or sold in which the total action will be affected on Pr and Pe.”

Here is an example:

Assume we have the portfolio of assets as follows:

Year	A	B	C	D	E	F
2012	0.15	0.19	0.16	0.25	0.08	0.17
2013	0.18	0.16	0.20	0.21	0.10	0.15
2014	0.16	0.14	0.16	0.19	0.17	0.18
2015	0.18	0.20	0.16	0.15	0.14	0.17
2016	0.18	0.18	0.20	0.12	0.16	0.16
2017	0.21	0.18	0.23	0.14	0.18	0.06

The elements of matrixes of (X) and (P) will be equal to:

100 / 6 = 16.7 %

In the result, we have the Standard Deviation of expected portfolio returns (Qr) and Coefficient of Variation (CV) approximately equal to zero. Of course, the easiest status (without any challenge) to minimize the risk is to assume a low return rate as expected return or expected value. As you can see, the return rate on a U.S. Treasury bill is the free risk.

Now, let me expand this application for another field. I would like to refer you to my article of “Where money goes? Where power comes from?” On below link:

http://emfps.blogspot.com/2011_02_20_archive.html

Where I stated: “If a function of w=f(x,y,z,t) wants to move far from a point (p), the change of amounts for this function depends on its direction”

Assume:   Grad “f’ = ai + bj+ ck

If we have: a+b+c = 1

Then, the directional derivative on function “f” in which the point P(x1, y1, z1) in the space is moving on direction of unit vector always will be equal 1.

Now, we consider that we have so many functions such as “f, g, w, h…

If we include all directional derivatives of the functions into matrix (X) as follows:

Matrix (X) =

Grad “f”

Grad “g”

Grad “w”

Grad “h”

There is a Eigenvector equal to unit vector which is responding to Eigen value equal to 1 (lambda = 1)

Note:  “All spreadsheets and calculation notes are available. The people, who are interested in having my spreadsheets of this method as a template for further practice, do not hesitate to ask me by sending an email to: soleimani_gh@hotmail.com or call me on my cellphone: +989109250225.   Please be informed these spreadsheets are not free of charge.”

 

To be continued ………

Efficient Portfolio of Assets: Application of Markov Chain (CON)

In this article, I am willing to explain why I wrote previous article of “Efficient Portfolio of Assets: Application of Markov Chain”. The reasons are as follows:

1) As you can see, the most crucial thing to apply the model of Markov Chain for the transactions in finance is to assume the constant rate for Risk and Expected Return. In fact, key factors are to be fixed them. But how can we secure a constant rate for risk and expected return? It can be done by injecting the money as fuel. Let me bring you a real example about the transactions of Crude Oil as an asset during the period of 24 hours on May 7, 2010 as follows:

	Crude Oil Price
	May 7, 2010
Hour	Minute	Second	Pr (USD)	Ra
11	43	19	75.41
12	1	20	76.18	1.02%
12	14	32	75.23	-1.25%
12	55	30	76.04	1.08%
23	40	35	75.41	-0.83%

Where we have:

Real Price (USD) = Pr

Expected Price (USD) = Pe

Return Rate (%) = Ra

Expected Return (%) = Re

Referring to above mentioned, we have variable return rate on each exact time. If we want to have the constant return rate (Re), the Expected Prices (Pe) will be changed as follows:

Pr	Pe	Re
75.41
76.18	76.18	1.02%
75.23	76.96	1.02%
76.04	76	1.02%
75.41	76.815	1.02%

In fact, in the staring of deals, the price is $75.41 and after the first transaction, the return rate will be revealed by the price of $76.18. If we want to control and have a constant returning rate, we should take the action as follows:

We have formula of return rate:

Re = (Pe -76.18) / 76.18

Regarding to above table, to fix expected return rate and to have a constant Re, we have two states as follows:

-Sometimes Pr > Pe: Our strategy in action will be the injection and investment of the money on others assets to fall down the real prices

-Sometimes Pr < Pe: Our strategy in action will be the injection and investment of the money on Crude Oil asset to increase of the real prices.

Of course, this is a game and maybe the application of the Game Theory will help us to find the best analysis. Because we have the limited internal resource and are not be able to purchase so many numbers of the shares to increase or decrease of the share prices (Which asset is the better to buy? How much percentage should we buy?).

Application of the Game Theory after PEST, Industry and SWOT analysis will guide us to find how much percentage of the shares and which ones should be purchased in which the total action will be affected on Pr and Pe.

As the result of this model, a fluctuation will be raised among the whole of the stock markets when there is the lack of the Value Chain throughout the world in which it can be linked the scarce resources. In this case, we have not enough money (Energy) to handle the model of Markov Chain. As the matter of fact, it means there is the lack of liquation on assets to reach the critical points.

This model can be also expanded for corporate strategy where the shareholders move among the Corporations.

2) Referring to above mentioned (Reason 1), we are speaking about the constant rates (numbers) such as Risk and Expected Return, the types of the states (Assets), money, liquation and critical points.

Now, I can remind you about my first article of “Actually, what is the problem?” that I sent this article on https://www.xing.com/net/pri46ffacx/mathe/general-interest-remarks-and-links-5223/actually-what-is-the-problem-14781292/on Oct 25, 2008 (link of this blog: http://emfps.blogspot.com/2010/10/actually-what-is-problem-part1.html).

My question was: Can we join Gibbs’s formula (F = C – P + 2) and phase diagrams in thermodynamic to all of systems in the world?”

The answer is yes. Because of below cited:

-In above model of Markov Chain, we have three variables of the Time, Risk and Expected Return

- In phase diagrams, we have three variables matched by Risk to Pressure and Expected Return to Temperature and the time

-We have the money matched to the Energy, the types of the states for both of them, material matched to assets, liquation and critical points for both of them.

- The most important thing is that there are the critical points for each two systems in which the variables will be the constant numbers (boundaries)in this critical points.

You can see both systems are completely matched together.

3) Referring to Reason (2), I found an example or application for my first article after 36 months. Three days ago, I posted the article of “The Constant Issues, Universal Laws and Boundaries Conditions in Physics Theory” on the link of “http://emfps.blogspot.com/2011/10/constant-issues-universal-laws-and.html “and I told that I am working on a new constant natural number. Maybe I will find an example or application during the period of the next 36 months???

4) What will be the strategy in action?

Referring to Reason (1), it can be proved that we need to discover new resources of the energy other than the existing resources to eliminate the fluctuation on the stock markets.

One of the ideas to discover new resources of energy is to save the energy during the period of time accompanied by the constant conditions (refer to above model and the constant rate). In this case, each 1 KJ of energy saving will be just equal to 1 KJ discovered energy. What are the constant conditions? The methods, which are applied by energy –saving, should not be suffering the whole of the people in the world. In the other word, the people should feel that they are convenience before and after energy –saving. This is the constant conditions. Of course, this strategy needs that the people in the world cooperate to abandon their wrong habits. I had already brought an example of energy saving in article of “Saving Techniques: Optimization in Boiling Water Consumption on the link of “http://emfps.blogspot.com/2010/10/saving-techniques-part-1-optimization.html”

As you can find on Recommendation of this paper:

“What is the Influence of energy saving by the people on environment and business in the world?

International Energy Outlook 2010 (IEO) stated that total world energy consumption rises by an average annual 1.4% from 2007 to 2035. If we assume only 61% population of these 39 countries care about energy saving on the case of boiling water, AE can be calculated just equal to 1.4%. It proves that this method as well as works”.

Note:  “All spreadsheets and calculation notes are available. The people, who are interested in having my spreadsheets of this method as a template for further practice, do not hesitate to ask me by sending an email to: soleimani_gh@hotmail.com or call me on my cellphone: +989109250225. Please be informed these spreadsheets are not free of charge.”