This is my last project for live-updated valuing PT. Astra International Tbk, that's why I call it a Dynamic Company Valuation. Anyone can be the insider! This is an Excel model with macro, where some VBA codes are used to manipulate key data for updating Beta and WACC via Yahoo Finance and Bank Indonesia websites.
Go to AstraWatch blog >>>
Download Dynamic Company Valuation >>> Excel Model with Macro
Introduction
Company valuation model for analysis can be developed into a broader scope of modelling techniques. This is not really due to the usage of a more sophisticated model, but quite related to the implementation of financial planning function in a company.
Since the key parameters in valuation model are mostly influenced by market behaviour, then it is important to update some relevant market data in regular basis to see how they affect the analysis. The relevant market data may include risk-free rate, stock price and market index that are able to indicate changes in value.
This model introduces manipulation of data by automatically changing some market data via the tool of external data import in Excel and some VBA simple codes for refreshing the Web Query. The main goal is to adjust calculation in Beta and WACC in the framework of company valuation and also for Option and Bond analysis.
In projecting financial statement, the historical data of the last two year operation is used to find the assumptions in the form of financial ratios that are normally corresponded to Sales. Then, the average of the last two year ratios can be used as the basic assumptions. Sales Growth ratio can be used as a look-up parameter in sensitivity analysis.
Sensitivity models are used by utilising two important tools in Excel, i.e., Data Tables and Graphs. Both tools are set to show visual changes in value after altering the growth ratio to some levels. This model also introduces sensitivity analysis of changes in financial ratios for the goal of predicting the possibility of bankruptcy. Traditional financial ratio techniques used are for Altman Bankruptcy model and Chesser Loan Surveillance model to be visually compared with the equity value and stock price resulted from the Free Cash Flow valuation.
The key analysis in this model is the Free Cash Flow valuation that has been as the important technique explained in the course and from the text. The method follows the that has been explained in the class using the No-Negative Cash and Debt as the Plugs. Some modification is tried to be applied by using Bonds as the plug, where the Bank Loans is calculated based on the bank loans to bonds ratio. The sum of both is the Long-term Debt.
Using bonds as the plug, the plan is to set of a Bond Portfolio for covering future financing in the projection. Then, the analysis refers to Net Present Value and Portfolio Duration that may anticipate in the future. As a bond issuer, the company should expect the lower value and duration.
Finally, based on the previous Employee Stock Option Scheme, this model also tries to do some option valuation. Because of memory shortage stored in the computer, the simulated Return is only done to 100 simulations. The other option model uses a bootstrap technique, which is worked faster in the other single worksheet. The slowing down of memory processing is influenced mainly by a huge number of iterations particularly in Data Tables calculations for sensitivity analysis.
Technical Notes
INDO17
- USD 1 billion Dollar-denominated global bonds issued by the Ministy of Finance maturing in 2017 (INDO 17) rated B2 (positive) by Moody’s and B plus (positive) by Standard and Poor’s.
- Daily date to be updated via Central Bank of Indonesia webpage to the blue colour range.
- If the latest updated date from web query - 2 days less than today's date, no need to update.
- It assumes 2 days lag from website update, however if computer is on, it can be updated automatically.
- To update WACC sheet for risk-free rate assumption.
- If number of days to be updated = 0, no need to update rate.
- Jakarta Stock Exchange (JSX) Composite Index consists of all stocks in the bourse. JSX opened in 1912 under the Dutch colonial government, closed during WW and re-opened in 1977.
- Instead of those via Yahoo Finance, there are 18 JSX indices quoted by Bloomberg. The other popular indices: LQ-45, JSX Agri, JSX Mining, JSX Islamic, JSX Manufacture. Weekly date to be updated via Yahoo Finance webpage to the blue colour range.
- If the latest updated date from web query - 7 days - 2 days less than today's date, no need to update.
- It assumes 2 days lag from website update, however if computer is on, it can be updated automatically.
- To update BETA sheet for index prices column in the lower bound, reversing position.
- If number of weeks to be updated = 0, no need to update stock index.
- PT. Astra International Tbk is a group company of the largest conglomeration in Indonesia with total asset of USD 5 billion in 2005 at USD/IDR 9,500 and sales of USD 6,4 billion.
- Has been undertaken a loan restructuring program in 1999 and now owned 47% by Jardine Cycle and Carriage Ltd Singapore and 53% by public listed in JSX since 1990.
- Weekly date to be updated via Yahoo Finance webpage to the blue colour range.
- If the latest updated date from web query - 7 days - 2 days less than today's date, no need to update.
- It assumes 2 days lag from website update, however if computer is on, it can be updated automatically.
- To update BETA sheet for stock prices column in the lower bound, reversing position.
- If number of weeks to be updated = 0, no need to update stock price.
- The blue range "ASII" and "JKSE" are used to store price and index data updated from ASII and JKSE sheets.
- The key reference numbers to find the lower bound of columns are the row numbers in the left and right hand sides. Data is in reversing position from the original data, the latest one is in the bottom.
- Using the average market return from the BETA sheet, the market risk premium is 22%. This shows that the high volatility in JSX market makes the investors more love to hold stocks.
- The market value of equity is calculated from the average price produced in the BETA sheet.
- WACC of 28.63% is high, but normal in emerging country like Indonesia.
- Quarterly update of input is recommended.
- The first part consists of Balance Sheets, Income Statement and Financial Ratios, which is processed to create projection from 2006 - 2010. Based on the raw data of 2004 and 2005, assumption of financial ratios is created according to the average of the two years ratios.
- Then, projection is created based on the average ratios. There are two plugs: Bonds and Cash.
- The second part is Bankruptcy Prediction model and its sensitivity analysis. However, this still borrows some parameters resulted in Free Cash Flow Valuation, such as equity value and stock price. The reason of doing this is because of ASII past experience in loan restructuring.
- Even there has been a significant changing of ownership, the big size of the company needs to be alerted based on growth and value. Moreover, it politically matters as such a national asset.
- The third part is the main part, Free Cash Flow valuation. Based on the assumption of 18% sales growth (average of 2004 and 2005), the equity value is IDR 39,329,000,000,000 or at USD/IDR 9,500 is around USD 4 billion, with stock price of IDR 9,715 (USD 1.023).
- Calling the plug in balance sheet projection as ending balance, this model tries to work out the additional and repayment bonds. The additional is assumed as face value or book value of bond issued. In 2006, all 2005 ending balance is assumed to be paid in 2006.
- Then, additional is calculated as ending - (-repayment) - beginning, which indicates there is a new bond issued. Then, the coupon of the new bond is calculated as repayment in 2007.
- By repeating this process, it can be found a series of additional bonds to produce as a bond portfolio and the cash flows that consist of PV and coupon. Increase in Bond Price means loss.
- In accounting system, the bonds PVs may go to cash and the coupons may go to interest expenses.
- The yield is assumed 100 bps above the government yield and the coupon rate is 8% annually.
- Portfolio duration is calculated based on weight of bonds' PV.
- Sensitivity analysis consists of effect yield on bond portfolio NPV to 2010, portfolio duration and coupon effect on portfolio duration. As issuer, company chases a lower duration.
- This is based on the previous employee stock option scheme that is assumed to be continued with different level of stock price, volatility and risk-free yield.
- There are two way in generating payoff simulation, by generating random return using NORMINV in a table and by recording one-by-one into the table. The second one does not work properly due to too many iterations for several Data Tables calculations in the worksheet. Should be separated in different workbook.
0 comments:
Post a Comment