What-if Analysis – Data Table

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


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)
  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).
  5. You have to select cells that define the FY2018 MLM Growth Rate and FY2017 MLM Growth Rate. Here you are:
  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”.

Crude Oil Update (9 Oct 2016)

Let see whether oil can break the resistance zone. This is the 3rd or 4th attempt.


New Format of Research Report

I have uploaded a new analysis for HAIO (FY17 Q1). There are two files in that post.

Research Report

You can go through the report and understand how I make decision for an investment. You will see how I justify my investment thesis for a stock.

Structure of report:

  1. Synopsis (or Investment thesis)
    • My investment decision, and what do I think the company/asset is really worth? We need a pricing imperfection to make money.
    • The stock is priced imperfectly because of these 2-3 key factors. The market has not factored them in because…. But you believe they’ve been overlooked, and that there’s a chance to gain significantly by longing/shorting this stock. Or, on the flipside, the market has incorrectly factored in certain things that do not matter that much.
  2. Company Background
    • What are its products and operations?
  3. Performance
    • Performance analysis based on the stock’s historical data
    • Projection model
  4. Valuation
    • For a long, you need to show that there’s a good chance that the stock is undervalued in some way (e.g., right now it’s trading at $25, but there’s a reasonable chance it’s worth $35-$40) by showing your public comps, precedent transactions, and DCF analyses; for a short or a hold, you do the opposite and show why the stock is overvalued.
  5. Catalysts
    • And certain key events in the next 6-12 months will cause the market to “realize” this pricing imperfection, resulting in a correction and the potential to make money. Key events might be new product launches, acquisitions, earnings announcements, competitors’ tactics changing, divestitures, positive clinical trial data, and financing activities such as share repurchases or issuing debt / equity.
  6. Risks
    • What are the main reasons why you might be wrong? And yes, everyone is wrong sometimes. You have to lay out the top 2-3 market and company-specific reasons why your investment thesis might be wrong, and then explain what you can do to hedge against these risks… even if you’re wrong, could you at least limit your losses? In many cases, the risk factors will be directly related to the catalysts you cited above.


In the excel file, you will see a new worksheet called “HAIO Value”. This worksheet contains projection model for Income Statement, Balance Sheet, Cash Flow, Key Ratios, DCF, Fair Value Estimation and Sensitivity.

Basically, the idea is

  1. You make projection for the business growth
  2. Links up 3 financial statements (yes, you need solid knowledge how 3 financial statements link together)
  3. Then you can easily get projection of EPS, DPS, BVPS and Free Cash Flow
  4. With the Free Cash Flow, you can DCF analysis on it.new-dcf
  5. Then estimate fair values
  6. Then do sensitivity analysis
    1. We will verify whether the valuation is realistic or not.
    2. The good, bad and ugly scenario

So, this is the new format of my research report.

HAIO – Fundamental Analysis (7 Oct 2016)

Research Report (FY17 Q1): Word Document

Data and calculation: Excel

Blog Posting

I will change my strategy in maintaining my blog.

  1. By default, I will post updated analysis of stocks in my portfolio for financial year-end results.
  2. Previously, I always post updated analysis of stocks in my portfolio for each quarterly result. From now on, I won’t do this anymore unless there are significant changes in the company. I may post my view in short description in this group.
  3. With time saved, I can post analysis of some interesting stocks (if there is any).
  4. To further reduce maintenance overhead, I will change format of my analysis. Forums won’t like that kind of new format, but no big deal for me as traffics from forums are not big. For my serious blog followers and learners, I think you will like the new format.

Thanks for your support and stay tuned!

Complete Company Valuation – A Long Overdue

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



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.


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.



AIRASIA – Fundamental Analysis (18 Sep 2016)

Analysis for Q2 2016 Financial Report (29 Aug 2016):

  1. Summary
  2. Detailed Analysis