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

1.