Powered By Blogger

Wednesday, May 2, 2012

Application of Pascal’s Triangular Plus Monte Carlo Analysis to Calculate the Risk of Expected Utility


Introduction

Following to the article of “Application of Pascal’s Triangular in Corporate Financial Strategy” on link: http://emfps.blogspot.com/2012/04/application-of-pascals-triangular-in.html and the article of “Monte Carlo Analysis on Case of Nike, Inc.: Cost of Capital” on link: http://emfps.blogspot.com/2012/01/monte-carlo-analysis-on-case-of-nike.html,  the purpose of this article is to use a new simulation model which has been made by the combination of the Pascal’s Triangular and Monte Carlo method for calculating the risk of Expected Utility. The most important finding is to track and control the changes of the risk and Expected Utility when we change our trials. Another finding is to improve our primary estimation about the probability for Utility function extracted from some macroeconomic indicators such as inflation rate, unemployment, CPI, PPI, GDP, interest rate and so on. As the matter of fact, I have proved that by increasing of the trials, we will have the constant Expected Utility but the risk of Expected Utility will decrease. Finally, I am willing to tell you that one of the best ways to solve the complex problems (Multi Dimension problems) by using of Monte Carlo Simulation Model (due to the limitation of CPU of our PC) is to breakdown the problem and to utilize the combination of many methods such as Markov Chain, Fuzzy Logic and so on accompanied by Monte Carlo Simulation Model. I think that this simulation model can be used by someone who have been involved in business of  Saving Energy, Stock Markets, Forex trading, Shopping malls such as Carrefour, Giant Hypermarket, IKEA, AEON, Mydin, Parkson, SOGO, Tesco and so on but the most important usage is to estimate the risk of deficit financing  in macroeconomic where it was implied in article of “Case Analysis of GAINESBORO MACHINE TOOLS CORPORATION (CON): A New Financial Simulation Model” on link: http://emfps.blogspot.com/2012/04/case-analysis-of-gainesboro-machine.html
Expected Utility Theory
In the reference with Wikipedia on below link: http://kleene.ss.uci.edu/lpswiki/index.php/Expected_Utility_Theory
The definition of Expected Utility Theory, Utility and Expected Utility Calculations are as follows:
Expected utility theory is a tool aimed to help make decisions amongst various possible choices. It is a way to balance risk versus reward using a formal, mathematical function.
When faced with a number of different choices, expected utility theory recommends that you calculate the expected utility of each choice and then choose the one with highest expected utility.
Utility
Utility is simply a measure of a person's preferences amongst different things. From these preferences (if they are rational!) we can deduce a utility function which represents preferences by order relations between numbers.
This only works if a person's preferences are, in a certain sense, rational. If someone prefers the Angels to the Dodgers, then they shouldn't also prefer the Dodgers to the Angels. And, if they prefer the Angels to the Dodgers, and the Dodgers to the Giants, then they shouldn't prefer the Giants to the Angels. We also require that, for any two things, a person prefers one to the other, or is indifferent between the two.
If a person's preferences are rational in the above sense, then we can define a utility function as follows:
u() is a function that assigns numbers to things (represented by variables x,y,z,…)
u(x)>u(y) if and only if this person prefers x to y
u(x)=u(y) if and only if this person is indifferent between x and y
The numbers assigned by u() should also match how much one thing is preferred to another. If someone assigns u(Angelswin)=100 and u(Dodgerswin)=1, then they'd prefer to see an Angels win 100 times more than a Dodgers win.
Example of Utility Functions
Suppose we want to create a utility function for a fan of the "Planet of the Apes" movies. There were five movies in the series: "Planet of the Apes", "Beneath the Planet of the Apes", "Escape From the Planet of the Apes", "Conquest of the Planet of the Apes" and "Battle for the Planet of the Apes".
Our fan likes "Escape" the best out of the five, prefers "Escape" to "Beneath," is indifferent between "Beneath" and "Conquest," prefers "Beneath" to "Planet," and prefers "Planet" to "Battle." Here is a utility function that could represent our fan's preferences:
u(Escape)=10,u(Beneath)=u(Conquest)=8,u(Planet)=5,u(Battle)=1
One way to think of utility is in terms of how much you would pay for each of these things, or how much these things are worth to you.
Expected Utility Calculations
How appealing a certain choice is depends not only on the payoffs of that choice, but how likely those payoffs are. The multi-million dollar payoff of a lottery is certainly appealing, but it is so unlikely that buying a lottery ticket is virtually a waste of money. Expected utility calculations are meant to balance risk versus reward.
We think of an act (like buying a lottery ticket) as having a number of possible outcomes (in this case, winning or losing). Given a person's utility function (see above) and their degrees of belief in each of the possible outcomes, we can figure out the expected utility of any act. This is done as follows:
Let the act in question be labelled A. Let o1,o2,…,on be the various possible outcomes of A (there needs to be at least one outcome, but there could be many).
To each outcome oi is an associated probability Pr(oi) which measures how likely that outcome is, and a utility u(oi) which measures that outcome's spot in this person's preference relation.
The expected utility of A is:
E(A)=u(o1)Pr(o1)+u(o2)Pr(o2)+…u(on)Pr(on)
Now, when faced with a choice between multiple acts A1,A2,…An, expected utility theory says that a person should choose the act with the highest expected utility. That is, calculate E(A1),E(A2),…E(An) and then choose the act with the highest associated utility.
Therefore, if we obtain the probability and Utility function for each outcome, we are able to calculate Expected Utility for each action.

Methodology

The methodology to make this simulation model has been explained step by step as follows:
1) To define the acts
2) To determine the numbers of the total trials or the numbers of the possible outcomes
3) To make the appropriate trials of Pascal’s Triangular in reference with the numbers of the total trials (the total possible outcomes) by using of Excel spreadsheet (referred to step 2)
4) To calculate the total amount of outcomes for each possible outcome
5) To calculate the total sum of outcomes for the total trials
6) To divide step 4 to step 5 for each possible outcome to obtain accumulative probability
7) To find out the consequence of all possible outcomes (which is the same the Binomial Probability Distribution) by using of accumulative probability (step 6)
8) To find a range for Utility function by using of marketing research
9) To estimate a primary probability for each Utility function (primary range) by using of macroeconomic indicators such as PPI, CPI, GDP, interest rate, unemployment rate, inflation, exchange currency and so on
10) To calculate Cut-offs or accumulative primary probability (which is utilized to conduct a Monte Carlo analysis)
 11) Using of the Rand formula = Rand () on our excel spreadsheet for all Utility functions
12) Using of the VLOOKUP formula = VLOOKUP (Rand cell, Cut-off, 2) for all Utility functions
13) To multiply each probability of the possible outcome to each Utility function
14) To sum all results obtained from step 13
15) The increase iterative calculations by using of a two –way Table just like Sensitivity analysis in which row is the numbers of 1 to 10; column is the numbers of 1 to 200 or 400 or 1000 and independent variable is the result of step 14
16) To calculate the standard deviation (STDEV), coefficient of variation (CV) and the average of Utility function.

Finding and discussion

In this case, I started by an example as follows:
1) I defined the acts as The Purchasing and Selling
2) Referring to the article of “Application of Pascal’s Triangular in Corporate Financial Strategy” on link: http://emfps.blogspot.com/2012/04/application-of-pascals-triangular-in.html, I considered the total trials or the total possible outcomes in four levels as follows:
Ø  999 trials (possible outcomes) where the total number of the people (participants) were 997 plus Purchasing and Selling
Ø  500 trials (possible outcomes) where the total number of the people (participants) were 498 plus Purchasing and Selling
Ø  100 trials (possible outcomes) where the total number of the people (participants) were 98 plus Purchasing and Selling
Ø  10 trials (possible outcomes) where the total number of the people (participants) were 8  plus Purchasing and Selling
Here is the consequence of the outcomes for each level in the format of binomial probability distribution curve as follows:

-The level of 999 trials:


- The level of 500 trials:


-The level of 100 trials:


- The level of 10 trials


3) I considered the primary range for Utility function and probability for Purchasing and Selling as follows:
Probability of Purchasing
U (Oi)P
0.1
30
0.15
35
0.35
40
0.4
45



Cut offs
U (Oi)P
   30
    0.1
   35
   0.25
   40
   0.6
   45



Probability of Selling
U (Oi)S
0.1
45

0.15
40

0.35
35

0.4
30






Cut offs
U (Oi)S

     0
   45

    0.1
   40

   0.25
   35

   0.6
   30


As you can see, the probability for Purchasing is exactly vice versa with Selling.

The Results

I calculated the standard deviation (STDEV), coefficient of variation (CV) and the average of Utility function for each level of trial as follows:

-The level of 999 trials:
Purchasing
STDEV (Ave.)
0.657515

CV (max)
0.017365

CV (average)
0.016342

E (Pur.) Total Ave.
40.23528

Selling


STDEV (Ave.)
0.649111

CV (max)
0.019577

CV (average)
0.018688

E (Pur.) Total Ave.
34.73502


- The level of 500 trials:

Purchasing
STDEV (Ave.)
0.784547
CV (max)
0.02199
CV (average)
0.019497
E (Pur.) Total Ave.
40.23969

Selling
STDEV (Ave.)
0.784866
CV (max)
0.024444
CV (average)
0.022575
E (Sellig) Total Ave.
34.76781

-The level of 100 trials:

Purchasing

STDEV (Ave.)
1.175161
CV (max)
0.03183
CV (average)
0.029202
E (Pur.) Total Ave.
40.24228

Selling
STDEV (Ave.)
1.143864
CV (max)
0.034977
CV (average)
0.032905
E (Sellig) Total Ave.
34.76331

- The level of 10 trials:

Purchasing

STDEV (Ave.)
2.066192
CV (max)
0.053684
CV (average)
0.051285
E (Pur.) Total Ave.
40.28859

Selling
STDEV (Ave.)
2.043727
CV (max)
0.063607
CV (average)
0.058879
E (Sellig) Total Ave.
34.7068

I compared the changes of risk for each level of possible outcomes where this finding has been included in below diagram:


As you can see, the risk will decrease if we increase the level of possible outcomes while the Expected Utility will be the constant for each level of possible outcomes.


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.”
 


Wednesday, April 25, 2012

Pressure drop field


You can review below link which is about System Advisor Model (SAM):

https://sam.nrel.gov/content/pressure-drop-field

Here is also very fascinating report for who are interested in working on Energy Saving field:

http://www.unene.ca/un803-pwp/UN0803_System_Thermal_Hydraulics_Written_Report.pdf

Tuesday, April 17, 2012

A New Financial Simulation Model for Case Analysis of GAINESBORO MACHINE TOOLS CORPORATION


Introduction

Following to the article of “Application of Pascal’s Triangular in Corporate Financial Strategy” on link: http://emfps.blogspot.com/2012/04/application-of-pascals-triangular-in.html and the article of “Case Analysis of GAINESBORO MACHINE TOOLS CORPORATION: The Dividend Policy” on link: http://emfps.blogspot.com/2012/03/case-analysis-of-gainesboro-machine.html
In this article, I am willing to introduce you a new financial simulation model which is based on EXHIBIT 8 (Projected Sources – and Uses Statement Assuming a 40% Payout Ratio) of the case: GAINESBORO MACHINE TOOLS CORPORATION. In fact, the template of this new simulation model is EXHIBIT 8 whereas I have developed this template by adding new assumptions and new components. I can say that this simulation model is the combination of Exhibit 8 and Discounted Cash Flow Analysis plus some new components. The purpose of this simulation model is to analyze simultaneous impact two independent variables which are the Cost of Capital (WACC) and Dividend payout ratio on one dependent variable which is the Stock Price. My final result will be to find the appropriate the Cost of Capital (WACC) and Terminal Value Growth Rate for two given data in this case which are dividend payout ratio and the Stock Price. Of course, maybe you think that I should depict dividend policy which is the issue of the case. Yes, but I will analyze it in my next article. Here, I will show you that I have still the problem with the calculation WACC of Gainesboro Machine Tools Corporation. Anyway, I think that I have obtained the best estimation for WACC.

Methodology

As I told you, I used from EXHIBIT 8 as my template and I entered all data on my spreadsheet then I did following actions step by step:
Step1: I added below assumptions to Exhibit 8:
Ø  Depreciation growth rate
Ø  CAPEX growth rate
Ø  Change in NWC growth rate
Ø  Cost of Capital
Ø  Terminal value growth rate
In the result, we will have 8 independent variables.
Step 2: To complete my spreadsheet (template), I should get the growth rate of CAPEX and Change in NWC and Depreciation growth rate.
Firstly, I checked the amounts obtained from Exhibit 2 (Balance sheet) for CAPEX and Change in NWC replaced on Exhibit 8 for 2005 year.

Note (1): I can tell you that Change in NWC (19.5) on Exhibit 8 is not true because we cannot consider Bank loan as current liability. In the meanwhile, the growth rate of CAPEX for 2009 year had been considered 3.5%!!!!
 Please see my true and false calculation as follows:


On the other hand, I calculated CAPEX as follows:



You can see on Exhibit 8 the amount of CAPEX is equal to 43.8 (2005 year) while the true CAPEX is equal 39.63
I could not find any rational reason behind a 10.5 % increase on CAPEX.
Anyway, if you have any time, you can contact to writers (Robert F. Bruner and Sean Carr) or publisher (University of Virginia Darden School Foundation, Charlottesville, VA) about these problems.

Step 3: Following to step 2, I worked on the calculation of the cost of capital and terminal value growth rate as follows:

Ø  Cost of Debt




I considered the cost of debt equal to 4.3% in the reference with Exhibit 3 and Ten – year Treasury note yield of 2004 year.

Ø  Cost of Equity
The analysis of the cost of equity in this case is very hard. I use from three methods as follows:
1) Cost of Equity by using the Constant - Growth Valuation (Gordon) Model. Since the cost of equity by using of this method is calculated approximately equal to 1.34% which is less than the cost of debt, I assumed the cost of equity more than 4.3%.
            2) Using of Stock valuation formula. This method is wrong way
            3) The compare shareholders' expected return and cost of debt. In this method,
            I assumed that the cost of equity is always less than the shareholders' expected return
            and more than the cost of debt consequently we have 23% < Ke < 4.3%. I considered
            the average of it as the cost of equity equal to 13%.

Here is my details calculation:









As you can see, finally I considered a range between 4% and 11% for WACC.
One of the most crucial problems to use the discounted cash flow analysis is to find the appropriate the Terminal Value Growth Rate because this methodology is very sensitive to TVGR. There are many ways to estimate TVGR as follows:

-Historic growth rates
-Forecast 3- year growth rates
-Terminal capital expenditure
-Competitive advantages among the firms
-Current and future market cap (refer to BGC matrix in Strategic Management)
-Porter’s five forces such as competitions on barriers to entry
-Macroeconomic indicators such as inflation, interest rate, GDP and so on

The based on Exhibit 3 and the growth rate of CAPEX for final cash flow, I assumed the range between 0 and 3 for Terminal Value Growth Rate.

Step 4: I have added some components to my simulation model as follows:

-After dividend Excess cash
-Terminal value
-Total excess cash(borrowing )
-Plug: excess cash(borrowing)
-Present value of flows
-Enterprise value
-Borrowing needs
-FV of borrowing needs
-Plug: FV of borrowing needs
-New borrowing needs
- Current outstanding debt
-Total outstanding debt
-Equity value
-Current shares outstanding
-Equity value per share
-Current share price

I used from formula = IF (PLUG < 0, - PLUG, 0) and =IF (PLUG > 0, +PLUG, 0) for below parameters:

-Plug: excess cash (borrowing)
-Borrowing needs
-Plug: FV of borrowing needs
Here is this part of my simulation model:








Note (2): I think this part of my simulation model is very important for Macroeconomic analysis because we can examine the risk of deficit financing where the final cash flow will lead us to a NPV > 0 or a huge economic collapse throughout the world. 

Step 5: In this step, I used two ways table of sensitivity analysis in which I analyzed the impact of the cost of capital and dividend payout ratio as independent variables on the stock price as dependent variable. The findings are below cited.

Finding and Discussion

The final result of my sensitivity analysis is as follows:






In the reference with Exhibit 5, the average current stock price of Gainesboro Machine Tools Corporation is equal $29.15
As you can see on above sensitivity analysis, there are four points which show us the current situation of Gainesboro as follows:

-WACC = 5% and Dividend payout ratio = 35%
-WACC = 7% and Dividend payout ratio = 20%
- WACC = 9% and Dividend payout ratio = 5%
- WACC = 9% and Dividend payout ratio = 3%

In this analysis, I chose Terminal Value Growth rate equal to 3%.
Now, please look at Exhibit 1 (Income statement). You can see the dividend payout ratio for 2003 year is equal 35.7%

Dividend payout ratio = Total dividend payout / Net income
Total dividend payout = 0.25 * 18,600,000 = $4,650,000
Net income = 12,993 * 1000 = $12,993,000
Dividend payout ratio = (4,650,000 / 12,993,000) *100 = 35.7%

In the result, I can reserve my assumptions for Terminal Value Growth Rate = 3% and Cost of Capital = 5 % as my primary data because of current situation of Gainesboro. As the matter of fact, in my next article, I will explain you how we can make decision for dividend payout ratio (Dividend Policy) where the basic of our assumptions for WACC will be equal 5 % and Terminal value growth rate will be equal 3%.

To be continued……

Note:  “All spreadsheets and calculation notes are available. The people, who are interested in having my spreadsheets of this simulation model 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.”