Financial Key Ratios by Industry

There are so many different financial key ratios. Ever wonder how to select relevant financial key ratios when analyzing a company?

I have compiled information from Moody’s into this document: Financial Key Ratios by Industry (Non-financial).

You can use this reference to select relevant financial key ratios for a specific industry. For more details, you can click the link to view respective document.

 

 

A New Year Gift for 2017

To my blog readers, here is a gift from me in conjunction of New Year 2017.

  1. If you use Office 2010 and above, click this: https://lcchong.files.wordpress.com/2016/12/new-year-gift-2017-plantation1.xlsx
  2. If you totally don’t use Office, click this: https://1drv.ms/x/s!AmIq2xtAX2vZhus2coILlceOtxAicA

It is very tedious for me to downgrade the excel to lower version. After all, this excel has been my personal homework for so many years, so it has been very tailored made for my own usage and convenient.

new-year-gift-2017-plantation

This excel contains operating statistics of CPO companies in KLSE. The stats are as at 26 Dec 2016. I update this file twice a year for my own homework in selecting good CPO companies.

I tried my best to make sure integrity of the data, but if you spot a mistake, please feedback to me.

Lastly…

new-year-2017-images-with-quotes

What-if Analysis – Data Table

One reader asked me how to create sensitivity table as following:

sensitivity-table

Before I start the tutorial, do one thing first.

  1. Download and open the latest excel.
  2. Go to “HAIO Value” worksheet.
  3. Navigate to cell C277 and change font color to red (or any color you like)
    sensitivity-table-formula
  4. Note that 0.209 is linked to L57.
  5. Highlight D278 to L286 and press Del button. No worry, you can always download my excel again if you screw up something.

Here is the step by step. This feature is available in most of the version of Office. Here, I use Office 365.

  1. When you create your own sensitivity table, make sure you construct the table by following format:
    Sensitivity Table - Structure.PNG
  2. Highlight C277 to L286.
  3. Navigate to Data, then select What-if Analysis.
  4. Click Data Table. You will see a small window (a lousy designed window).
    data-table-window
  5. You have to select cells that define the FY2018 MLM Growth Rate and FY2017 MLM Growth Rate. Here you are:
    data-window-cell-selection
  6. Click OK.
  7. Press F9 on your keyboard. This is important!
  8. Scoll down and see the results.

Alright! Usually, cool stuffs come with payoff (like exploded Note 7 and overpriced iPhone 7).

  1. If you have many data table built in your excel, calculation of your excel will be slower. If the calculation is complicated and you keep adding new data table, this will make situation even worse.
  2. By default, to reduce calculation overhead, Excel already enabled “Automatic Except for Data Tables”.
    Default Calculation Mode.png
  3. This means Excel won’t recalculate your data tables by default.
  4. You have to press F9 to calculate the active worksheet.

You can try to change the option to “Automatic” if you only have very few data tables and simple calculation. I tried before where everytime I change a value, Excel will be unresponsive for 10-20 seconds to recalculate 20++ data tables at one time. Think twice before you change the option.

To learn how to format cells colors, please Google “Conditional Formatting”.

Complete Company Valuation – A Long Overdue

I have shared my framework of fundamental analysis (as below) with some people before.

fundamental-analysis-lc

 

If you ever study my excel thoroughly, you probably notice that I do not publish one part in my analysis. That is projection of future earnings and book value, which is part of Company Valuation.

projection-missing

The main reason I did not publish the projection is because I did not format the worksheet properly, and sometimes I just use calculator and paper to do projection. For reduce administrative overhead, I will attempt to incorporate my projection model for each company (if possible) in my analysis. This is a long term on-going project where I will gradually publish projection model for each company in my portfolio.

BTW, I hope that you can differentiate the difference between Company Valuation and Fair Value Estimation. Please check out the following illustrations.

valuation-vs-fair-value-estimate-1

valuation-vs-fair-value-estimate-2

Discounted Cash Flow – Fine Tuned 2

Further from Discounted Cash Flow – Fine Tuned, I attached an excel with the new DCF model.

I also made one change to my reverse-engineering DCF (RDCF).

For the very first time, I recorded a video to explain this change. I am novice in making video, so the video is not without flaws. 🙂

 

Discounted Cash Flow – Fine Tuned

Recently, I have made some fine tuning to my DCF model. I have planned this for months, but I wanted to get the concept right before I make any change.

DCF Fine Tuned 1

Stub Period Fraction

DCF Fine Tuned 2

  1. Previously, I use integer for discount period – 1, 2, 3, 4, etc… What’s wrong with the “Normal” Discount Period – 1, 2, 3, 4, etc. are not the best representations of cash flow because companies generate cash flow over the course of the year… not all at the end! Also, we might be valuing the company mid-way through the year after Q1, Q2, etc. have already passed.
  2. Solution #1: The Mid-Year Convention – Rather than using 1, 2, 3, etc., you can use 0.5, 1.5, 2.5, etc. to better represent cash flows arriving throughout the year – “on average,” they are generated “in the middle” of the year instead.
  3. Solution #2: Stub Periods – Even if you use 0.5, 1.5, 2.5, etc. that still assumes that you’re valuing the company at the beginning of the year since cash flows arrive exactly 50% of the way through… but what if some time has passed? We can use the DAYS function in Excel to estimate this:=DAYS(Next_Year,Valuation_Date)/DAYS(Next_Year,Hist_Year)Gives us the fraction of the year remaining between now and the end of the year – e.g., 0.25, 0.75, 0.63… and then in future periods, you just take that stub period from the first year and add 1, 2, 3, etc.: So 0.25, 0.75, and 0.63 would become 1.25, 1.75, 1.63, then 2.25, 2.75, 2.63, etc.
  4. Combining the Mid-Year Convention with Stub Periods – Split it into Year 1 and the years afterward:Year 1: Just divide the stub period by 2 – so 0.5 becomes 0.25, 0.75 becomes 0.375, etc.Following Years: Take the “normal” discount period for the year and subtract 0.5 each year. Why? Because we get cash flows midway through THAT FUTURE YEAR – not the stub period in the first year!

    Here, for example, it’s 1.147 in FY15 because 0.647 is the remaining period in Year 1… and we don’t get any Year 2 cash flows in Year 1! And then we just add 0.5 from Year 2, to approximate the cash flows arriving midway through Year 2.

Implied Enterprise Value and Implied Equity Value

DCF Fine Tuned 3

I have outlined the calculation into line items, so you will be able decode the formula easily. Besides, I have used different terminology: Implied Enterprise Value and Implied Equity Value.

Before I explain further, let’s get one thing clear. What does “Implied” mean?

Imply means to express (something) in an indirect way : to suggest (something) without saying or showing it plainly

So, definition of “Implied Enterprise Value” and “Implied Equity Value” here is not exactly the same with “Enterprise Value” and “Equity Value”.

Implied Enterprise Value – Think of “cash flow” as a way to pay investors in the company. At the top, before you take out interest expense and debt repayments, that cash flow is available to everyone – both equity and debt investors. What metric represents both equity and debt investors? That’s right, Implied Enterprise Value.

Implied Equity Value – After you have got this “cash flow available to everyone,” you then pay debt investors by making the required interest payments and principal repayments to them. Now that they’ve been paid, that remaining cash flow is only available to equity investors, and you can “pay” those equity investors by issuing dividends or repurchasing shares from them. Since this cash flow is only available to equity investors, when you use it in a DCF you calculate the company’s Implied Equity Value.

Morningstar for KLSE Stocks

This article is not about Morning Star candlestick pattern.

INSAGE, EquitiesTracker, Reuters, Bloomberg and few other platforms (such as Winvest and Grandpine) are well known paid services in providing financial data for companies listed in KLSE. INSAGE and EquitiesTracker provide free edition (with limited features) if you sign up with certain brokers.

If you are looking for a free and reputable data provider for Malaysia stocks, you can consider Morningstar (www.morningstar.com) free edition. You can subscribe to Morningstar paid edition for full features, but I think the free edition provides rich features which I think sufficient for analysis.

  1. Morningstar provides tailored format of financial statements specifically for banks and REIT.
  2. You can view 5 years of financial data in the free edition.
  3. You can compare reported data and restated data.
  4. You can export 5 years of financial data and key ratios into excel, and it is FREE!!!
  5. Morningstar provides comprehensive analysis on Insiders and Ownership. Their ownership analysis provides rating for institution funds (that covered by them). This data is very important for you to determine quality of institution funds that owned the stock you interested.
  6. There are few more nice features you can toy around.

How about stock filtering by criteria? You can access this feature in the paid edition. Hold on! Thanks to Microsoft. MSN Money provides stock filtering powered by Morningstar: http://www.msn.com/en-us/money/stockscreener.

One catch though. How to search KLSE stock in Morningstar? You can try the search engine in Morningstar, but it doesn’t work now (it was working few weeks ago). Thanks to Google. If you want to search Public Bank in Morningstar, type “morningstar public bank” in www.google.com.

I am still not a paid subscriber of Morningstar because I am still in the middle of verifying its data.

By the way, I have no affiliation with Morningstar