Anymatters: July 2006

A fascinating conversation between Tony Blair and George Bush has been caught by the microphones at the G8, when the two men didn't think they were being overheard. It tells us a lot about the relationship between the two men, about the US-UK special relationship and the two men's views on the Middle East. Here's a transcript as best as we can make out. (via Adam Boulton/SKYNews)

>>> watch the video of the conversation >>>

Bush: Yo Blair How are you doing?
Blair: I'm just...
Bush: You're leaving?
Blair: No, no, no not yet. On this trade thingy...[inaudible]
Bush: yeah I told that to the man
Blair: Are you planning to say that here or not?
Bush: If you want me to
Blair: Well, it's just that if the discussion arises...
Bush: I just want some movement.
Blair: Yeah
Bush: Yesterday we didn't see much movement
Blair: No, no, it may be that it's not, it maybe that it's impossible
Bush: I am prepared to say it
Blair: But it's just I think what we need to be an opposition
Bush: Who is introducing the trade
Blair: Angela
Bush: Tell her to call 'em
Blair: Yes
Bush: Tell her to put him on them on the spot.Thanks for the sweaters it's awfully thoughtful of you
Blair: It's a pleasure
Bush: I know you picked it out yourself
Blair: Oh, absoultely, in fact I knitted it myself
Bush: What about Kofi [inaudible] his attitude to ceasefire and everything else ... happens
Blair: Yeah, no I think the [inaudible] is really difficult. We can't stop this unless you get this international business agreed.
Bush: Yeah
Blair: I don't know what you guys have talked about but as I say I am perfectly happy to try and see what the lie of the land is but you need that done quickly because otherwise it will spiral
Bush: I think Condi is going to go pretty soon
Blair: But that's that's that's all that matters. But if you, you see it will take some time to get that together
Bush: Yeah, yeah
Blair: But at least it gives people...
Bush: It's a process, I agree. I told her your offer to...
Blair: Well...it's only if I mean... you know. If she's got a..., or if she needs the ground prepared as it were... Because obviously if she goes out, she's got to succeed, if it were, whereas I can go out and just talk
Bush: You see, the ... thing is what they need to do is to get Syria, to get Hezbollah to stop doing this shit and it's over
Blair: [inaudible]
Bush: [inaudible]
Blair: Syria
Bush: Why?
Blair: Because I think this is all part of the same thing
Bush: Yeah.
Blair: What does he think? He thinks if Lebanon turns out fine, if we get a solution in Israel and Palestine, Iraq goes in the right way...
Bush: Yeah, yeah, he is sweet
Blair: He is honey. And that's what the whole thing is about. It's the same with Iraq
Bush: I felt like telling Kofi to call, to get on the phone to Bashad [Bashir Assad](9a and make something happen
Blair: Yeah
Bush: [inaudible]
Blair:
Bush: We are not blaming the Lebanese government
Blair: Is this...? (at this point Blair taps the microphone in front of him and the sound is cut.)

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.

JKSE

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.

ASII

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.

Beta

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.

WACC

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.

Value

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

Bond

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.

Option

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.

Bush and Blair's Chat Caught on Microphone

Dynamic Company Valuation - Excel Model

Mama Mia... 2 Goals 2 Large Pizzas

Prepare for European Semifinal

Flickr

Blog Archive

Links

Blogroll

Surfing Blogs

Indonesia Context

New Zealand Context

North America Context

Particular Context

Finance Context

Photoblogs