• This service has been inactive since 2010 as my new role in TM1 consulting.
  • Please disregard any commercial offers as this website has been archived and transformed into a blog rather than a commercial website.
  • Please contact me for more info in BI and Financial Modelling consultancy.

AstraWatch: Week - 20 Nov 06

PT Astra International Tbk. (ASII)

BETA= 1.447045614
WACC= 27.87%
Equity value (in IDR ,000,000,000)= 46,635.60
Value per share (in IDR) = 11,519.64

last trade
ASII= 15,400.00
JKSE= 1,717.73
INDO-17= 6.248

Initial Capital Investment

As every business must start with an initial capital investment, KBF builds its plant and office with own equity and bank loans. KBF's equity and loan from bank may first deposit or transfer to savings before being spent for capital expenditures.

To do this, the simple way is to create journal directly:
go to command centre > accounts list

There some new accounts we want to create here:
1. KBF equity
2. KiwiBank Term Loans
3. KiwiBank Savings
For KBF equity, go to equity > press new

Fill in the form something like this:

Same process for KiwiBank Term Loans with account type: liability and KiwiBank Savings with account type: asset.

Then, just record the journals as KBF's equity and loan from bank being transfered or deposited to savings. Go to record journal entry.
Equity

Loan

From here, KBF can establish the biofuel plant and buying office equipment and vehicles. Go to purchase > enter purchases. Please use GST tax code - Standard (S) with 12.5% GST. Then pay or close all open bill with electronic clearing account.

Finally, by checking the account list (go to accounts > accounst list), it can be found the balance sheet position something like this.

How about the inventory and production? Does KBF need more funding to start production? Stay tuned.

It's better to build your own accounts

Start with creating a new company file:

Then, follow the instructions until the accounts list menu. In this menu, it is recommended to build your own accounts list as you might need some flexible adjustments when recoding journals.

Kiwi Biofuel Ltd

Kiwi Biofuel (KBF) Ltd is a fictional manufacturing company used in this MYOB simulation. KBF produces liquid biofuel from kiwi fruits. With $100,000 assets raised from $50,000 equity and $50 loan, KBF's production capacity is 1,000 litre per day or 300,000 litre per year. It employs 2 labours, 1 engineer and 1 office assistant.

To produce 100 litre, it needs:
5 tray kiwi fruits @$6.00 per tray
2 hours labours @$12.50 per hour
50 kWh electricity @$0.10 per kWh
Total = $60 per 100 litre

Selling price = $1.00 per litre

One year budget:
  • Sales = $300,000
  • Cost of sales = $180,000
  • Advertising = $10,000
  • Salaries = $60,000
  • Office = $10,000
  • Maintenance = $ 10,000
  • Other expenses = $10,000
  • Operating profit = $20,000
  • Tax = $6,600
  • OPAT = $13,400
  • ROA = 13.40%
  • ROE = 26.80%

The Market Deviates?



There are some reasons why the market price deviates from the valuation of Astra International. If the valuation is correct, the market is currently trying to drive the price up until some level to conduct short selling as the future expectation is long (buy) when the price is low as of the valuation. Otherwise, the valuation is wrong.

AstraWatch: Week - 6 Nov 06

PT. Astra International Tbk. (ASII)

BETA= 1.446220312
WACC= 27.63%
Equity value (in IDR ,000,000,000) = 48,100.64
Value per share (in IDR) = 11,881.53

last trade
ASII= 14,500.00
JKSE = 1,664.84
INDO-17= 6.207%

AstraWatch: Week - 30 Oct 06

PT. Astra International Tbk. (ASII)

BETA= 1.447344401
WACC= 27.28%
Equity value (in IDR ,000,000,000)= 50,441.23
Value per share (in IDR)= 12,459.68

last trade
ASII= 13,950.00
JKSE= 1,612.95
INDO-17= 6.413%

AstraWatch: Week - 16 Oct 06

PT. Astra International Tbk. (ASII)

BETA= 1.447796665
WACC= 27.02%
Equity value (in IDR ,000,000,000)= 52,269.56
Value per share (in IDR) = 12,911.31

last trade
ASII= 13,500.00
JKSE= 1,572.85
INDO-17= 6.47%

AstraWatch News: Week - 7 Aug 06

Astra-Toyota Establish Multifinance Company
from Bisnis Indonesia (www.bisnis.com)
PT Astra International Tbk and Toyota Motor Corporation are in cooperation to establish multifinance company PT Toyota Astra Financial Services (TA Finance) with the total investment of IDR2 trillion.

The financing company is aimed to control the credit on Toyota cars credit worth IDR600 billion - IDR700 billion to finance 6000 units of car financing.

The President Director of TA Finance Buntoro Muljono said that such integration is aimed at boosting the sales of Toyota cars in Indonesia through Toyota business chain.

"Yes, it is aimed at boosting the sales of Toyota. Due to the high application, we are optimistic to meet the target of 6000 units of cars," he said during the launching of the company.

With such service, he said, the buyer could get one stop service.

Buntoro said that to establish the company, Toyota has acquired PT KDLC Bancbali Finance, from Korean company Korea Development Leasing Corp. and Bank Bali [presently Bank Permata].

The company acquired the multifinance company as the government has stopped granting the license to open multifinance company.

"We have acquired a multifinance company, KDLC Bancabali Finance, and then changed it to TA Finance."

The two shareholders have injected IDR500 billion this year, and the rest IDR1.5 trillion will be injected if the company needs additional liquidity.

=====

Toyota Astra Finance

PT. Toyota Astra Financial Services (TA Finance) is a joint venture between Toyota Financial Services Corporation (TFSC) and PT. Astra International Tbk (AI)., whose relationship has spanned for over 50 years. Memorandum of Understanding was signed in September 2005, an initiative which aims to be the most preferred and ultimately, the only financing solution for Toyota vehicle ownership.

Dynamic Company Valuation - Astra Watch

This is a financial model for live-updated valuing PT. Astra International Tbk, say it as 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.

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.

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.

Why AstraWatch?

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 can affect the analysis.

The relevant market data may include INDO17 risk-free rate, ASTRA share price and JSX Composite market index that are able to indicate changes in value. The main goal is to adjust Beta and WACC for calculating the live-updated equity value, most importantly, the equity value per share or share fair value of the Indonesia's giant automotive.

Contact: email AstraWatch