Investalks 301 @KL – Financial Modelling and DCF Valuation for Non-Financial Companies

Date: 25-26 Feb 2017

Time: 9:00am to 5:30pm.

Venue: Investalks Academy, 12-1, Jalan Perubatan 3, Pandan Indah, 55100 KL.

Course Fee:

  1. Investalks Student: RM680.00
  2. Public: RM750.00

Registration:

  1. Bank in:
    1. Bank: Maybank
    2. Account No.: 514383562062
    3. Account Name: Ivestalks Enterprise
  2. Send the payment receipt to investalksacademy@gmail.com
  3. Register here: https://docs.google.com/forms/d/1Hn9uLNAl8dKldiy_LkG1Ac7mPBTz3SmraXmyqFLe-OA/edit

Overview

This course aims to provide participants with a thorough understanding of how to build a robust financial modelling and valuation model from start to finish. Calculations cover revenues, operating and maintenance costs, capital expenditure, depreciation, debt and equity financing and taxation, leading to the build-up of integrated financial statements for the entity in question. The model is dynamic in nature, with the ability to run different scenarios and adjust the timing of key events.

During the course, participants also gain an insight into how to interpret the results and run sensitivities, as well as perform some degree of testing to reduce the incidence of modelling errors.

With the valuation model, participants will learn how to do estimate fair value by using Discounted Cash Flow model. Last but not least, participants will learn how to determine margin of safety by using systematic method.

Course Outline: https://drive.google.com/file/d/0ByPvDH6SQ5tHTVFtaEM3YzF5d3M/view?usp=sharing

Duration: Two days

Pre-course work: The instructor will send pre-course work to the participants few days before the training. The participants are encouraged to finish and study pre-course work before attending the training.

Class size: The recommended class size is a maximum of 12 participants. This is so that each participant can obtain sufficient one-on-one attention and support from the course instructor.

Laptop: Participants should bring a laptop computer, so they can take part in the hands-on exercises. The laptop can be Windows or OSX based. The following software must be installed prior to the training:

  1. Microsoft Excel 2010/2013/2016
  2. Microsoft Word 2010/2013/2016
  3. Adobe Reader 6.0 or above

Format
The course is highly interactive, comprising of a mix of theory, group discussions, instructor-led demonstrations and Excel-based exercises for participants to undertake.

Participants are provided with a comprehensive slide pack, an illustrations booklet covering key Excel formulae, instructions to modelling exercises and exercise solution files. These will be used during the course and will serve as valuable reference material following the course should participants wish to refresh their skills at a later date.

Key objectives

The course is designed to cover the following key objectives:

  • Appreciate the difference between what makes a good model and a bad one
  • Follow a logical, structured and disciplined approach towards model building
  • Build a model (or significant parts of one) from start to finish
  • Learn how to translate key financial and commercial aspects into Excel
  • Understand better how to tailor the outputs of the model towards end users and interpret the results
  • Improve knowledge of Excel functionality
  • Learn ways to reduce the incidence of modelling errors
  • Learn Discounted Cash Flow model to estimate fair values.
  • Learn systematic method to determine margin of safety.

Target Audience

The course is ideal for those looking to achieve the following:

  • Gain an understanding of leading approaches towards company valuation, in order to build models that are robust and user friendly in nature
  • Be able to use existing models more competently, interpret the results and have greater comfort over the integrity and accuracy of the model’s calculations
  • Be able to use Discounted Cash Flow to estimate fair values.

The course has been tailored specifically for retail investors. Financial modelling and DCF valuation could be very complex, but can be extremely simple in some circumstances. The course strives the balance by providing sufficient practical knowledge and tips to retail investors, in order to build sensible financial modelling and DCF valuation in short time frame.

Prerequisites
Some prior knowledge and experience is assumed. For example, participants should have:

  • The ability to navigate easily around Excel’s menu options
  • Working knowledge of financial statements and rudimentary accounting
  • A basic understanding of leading approaches towards company valuation
  • A basic understanding of valuation ratios, such as P/E, Earning Yield, Dividend Yield, etc.
  • (Nice to have) A basic understanding of valuation models, such as Graham’s Formula, EBIT Multiples, Katsenelson P/E Model, etc.

Modules

Module 1: Financial Modelling

  1. You will learn exactly what financial modelling is and why we care about it.
  2. The overall model development process and items to cover during the design phase.
  3. Revenue and Expense Projections
    • Learn how to project a company’s revenue based on its average customer value by segment, pricing growth, and customer…
    • Understand the 3 different methods of projecting revenue, in general, and how to ensure that your numbers are not wildly off the mark in future periods.
    • Learn to project a company’s Income Statement expenses.
  4. Balance Sheet and Cash Flow Statement Drivers
    • Learn how to project Current Assets, Current Liabilities, and key Cash Flow Statement line items based on the company’s historical trends and future cash flow profile.
    • Learn which items are projected independently on the Balance Sheet, and which items flow in from changes on the Cash Flow Statement.
  5. Debt Schedule
    • Learn how to add a simple debt schedule – including how to factor in additional draws and repayments, interest, and the impact on the company’s cash balance.
  6. Share Issuance and Buy Back
    • Learn how to project share issuance and buy back, and the impact on the company’s Balance Sheet and Cash Flow Statement.
  7. Linking the Statements
    • Learn how to link the financial statement projections properly, tie together all the items, and ensure that the Balance Sheet balances.

Module 2: Valuation

  1. The Time-Value of Money
    • To understand concept of time-value of money by using real life example.
  2. What Money is Worth?
    • Learn about different ways to measure the value of money and make investment decisions in this lesson, including the Present Value (PV), Net Present Value (NPV), the Internal Rate of Return (IRR), and the Weighted Average Cost of Capital (WACC)
  3. Putting DCF into Action
    • Learn what the key concept behind a DCF, as well as the intuition for how you value a company based on its Free Cash Flows, your selected discount rate, and the company’s Terminal Value
    • Learn the major steps in doing DCF valuation.
    • Estimate Fair Value by DCF model.
  4. Free Cash Flow
    • Learn how to calculate Unlevered Free Cash Flow and Levered Free Cash Flow, and understand their differences and impacts in DCF.
  5. Discount Period
    • Learn how to optimize discount period rather than using the “normal” discount period, such as 1, 2, 3, etc.
  6. Discount Rate
    • Understand the concept of WACC and how to calculate it by the standard formula.
    • Learn alternate way to calculate WACC.
  7. Terminal Value
    • Learn the two main methods for approximating Terminal Value and how to cross-check the output from both methods.
    • Learn more about what makes it tricky to select appropriate multiples and/or long-term growth rates in this calculation.
  8. Equity Value & Enterprise Value
    • Learn how Equity Value and Enterprise Value differ, the basics of how to calculate them.
  9. Uncertainty Risks of Fair Values
    • Learn systematic way to determine margin of safety before investing.
  10. Optimizations.

WELLCAL – FY16 Q4 (2 Feb 2017)

Before CNY, I have accumulated some units of WELLCAL. Here is my analysis file: https://doc.co/JhXzES.

I believe that with the recovery of US economy and rise of construction projects, demand for industrial rubber hoses will continue to see a gradual recovery. Meanwhile, the additional capacity from Plant 3 supports WELLCAL in further strengthening its position in the market with a wider range of industrial hose.

In terms of valuation, at the level RM2.0, I think WELLCAL valuation is quite attractive, even if in the bad scenario.

wellcal-fy16-q4-valuation

 

 

 

HAIO – FY17 Q2 Updates (22 Jan 2017)

I would like to provide some updates about my action plan on HAIO. Since Mar 2016, HAIO has been very astonishing, increased from 2.4 to 4.28, close to 78% increase.

haio-22012017-chart

This is beyond my expectation. Now, I have a happy problem to solve. That is I have to decide whether I should continue to accumulate the stock, sell the stock or hold the stock.

To make decision, I have to look into three aspects. Firstly, the growth drivers that support the price performance. Secondly, challenges that will probably suppress good story of HAIO. Lastly, the valuation. Is it fully valued, overvalued or undervalued?

Growth Drivers

  1. Based on the recent quarterly results, for MLM division, revenue and pre-tax profit increased by about 70% and more than double to RM77.5 million and RM 16 million respectively as compared to FY16 Q2. Additional sales from new products and higher recurring sales of consumer products from member customers and the increase in monthly recruited new members had helped to boost revenue and profit for the division. The new members recruited had increased by more than double to approximately 40,000 which had contributed to a higher sales to the MLM division.
  2. 6 Sep 2016 – The group aims to expand to the Middle East, China, India, Sri Lanka and Europe via partnerships to explore the market for products for women, such as fashion, accessories and cosmetics.
  3. A short term (possible) boost – As Chinese New Year festive season is approaching, the Retail division will carry out an extensive CNY promotion campaign and is expecting to bring in more revenue in the next quarter. As reported in recent quarterly results, Currently, the Retail division has started recruiting more Chinese physicians for its outlets to enhance its image as a healthcare service provider and to attract more crowd. Whereas for the MLM division, it will continue to intensify its new members recruitment program and to roll out more new products in the second half of the year.

Challenges

  1. Performance of the wholesale and retail division still weak.
    1. Wholesale division – Revenue decreased by about 30% to RM 11.7 million as compared to FY16 Q2 of RM 16.8 million. The decrease in revenue was mainly attributable to lower sales generated from duty-free goods and Chinese medicated tonic as most of the medical halls had reduced their stockholding level since the increase in selling price early of this financial year.
    2. Retail Division – Despite continuous weak consumer sentiment in the domestic market, revenue decreased marginally by about 2.6% to RM 9.3 million as compared FY16 Q2. The pre-tax profit declined by approximately 31% mainly due to lower A&P income subsidy from suppliers and a drop in sales of the higher margin house-brand products.
  2. The Wholesale division will continue to be affected by the weakening of RM currency as about 40% of its purchases are imported. The division sees that its profit margin will be adversely affected in the near future and has looked into more trade settlement using RMB currency instead of USD with the traders in China.
  3. Weak purchasing power of consumers.

Malaysia Consumer Confidence Q3 2016.PNG

Valuation

Refer to my updated financial modelling, I opined that HAIO is currently (but not extreme) overvalued at 4.28 based on the following blended valuation (DCF, earnings and dividends)

haio-scenario-fy17-q2

What is market expectation on HAIO at 4.28? My guess: High dividend payout and resilient company – HAIO committed to give 100% payout, but yet to become a policy. I think that due to uncertainties, investors are looking for conservative and dividend stocks. Some investors are probably willing to pay premium for HAIO. If just looking at valuation from dividend perspective,

  1. Baseline – 3.9-4.1
  2. Good – 4.0-4.5

Action Plan

Refer to the above table, my holding price is in the range of 2.1 and 2.3 (the ugly scenario). Besides, my dividend yield is around 9%.

Protected by high dividend yield and good scenario (3.9 – 4.2), I will continue to hold and monitor HAIO. Another qualitative factor is ever since Mr. Tan Kai Hee (or the old senior management team) let go responsibilities in managing company to the younger management team, I feel that the new management team demonstrated higher energy and more active leadership. I am OK to give HAIO the “benefit of doubt” where I underestimate their future earnings and cash flow.

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

Investalks 301 @KL – Financial Modelling and DCF Valuation for Non-Financial Companies

Date: 10-11 Dec 2016

Time: 9:00am to 5:30pm.

Venue: Investalks Academy, 12-1, Jalan Perubatan 3, Pandan Indah, 55100 KL.

Course Fee:

  1. Investalks Student: RM680.00
  2. Public: RM750.00

Registration:

  1. Bank in:
    1. Bank: Maybank
    2. Account No.: 514383562062
    3. Account Name: Ivestalks Enterprise
  2. Send the payment receipt to investalksacademy@gmail.com
  3. Register here: https://docs.google.com/forms/d/1Hn9uLNAl8dKldiy_LkG1Ac7mPBTz3SmraXmyqFLe-OA/edit

Overview

This course aims to provide participants with a thorough understanding of how to build a robust financial modelling and valuation model from start to finish. Calculations cover revenues, operating and maintenance costs, capital expenditure, depreciation, debt and equity financing and taxation, leading to the build-up of integrated financial statements for the entity in question. The model is dynamic in nature, with the ability to run different scenarios and adjust the timing of key events.

During the course, participants also gain an insight into how to interpret the results and run sensitivities, as well as perform some degree of testing to reduce the incidence of modelling errors.

With the valuation model, participants will learn how to do estimate fair value by using Discounted Cash Flow model. Last but not least, participants will learn how to determine margin of safety by using systematic method.

Duration: Two days

Pre-course work: None required

Class size: The recommended class size is a maximum of 12 participants. This is so that each participant can obtain sufficient one-on-one attention and support from the course instructor.

Laptop: Participants should bring a laptop computer, so they can take part in the hands-on exercises. The laptop can be Windows or OSX based. The following software must be installed prior to the training:

  1. Microsoft Excel 2010/2013/2016
  2. Microsoft Word 2010/2013/2016
  3. Adobe Reader 6.0 or above

Format
The course is highly interactive, comprising of a mix of theory, group discussions, instructor-led demonstrations and Excel-based exercises for participants to undertake.
Participants are provided with a comprehensive slide pack, an illustrations booklet covering key Excel formulae, instructions to modelling exercises and exercise solution files. These will be used during the course and will serve as valuable reference material following the course should participants wish to refresh their skills at a later date.

Key objectives

The course is designed to cover the following key objectives:

  • Appreciate the difference between what makes a good model and a bad one
  • Follow a logical, structured and disciplined approach towards model building
  • Build a model (or significant parts of one) from start to finish
  • Learn how to translate key financial and commercial aspects into Excel
  • Understand better how to tailor the outputs of the model towards end users and interpret the results
  • Improve knowledge of Excel functionality
  • Learn ways to reduce the incidence of modelling errors
  • Learn Discounted Cash Flow model to estimate fair values.
  • Learn systematic method to determine margin of safety.

Target Audience

The course is ideal for those looking to achieve the following:

  • Gain an understanding of leading approaches towards company valuation, in order to build models that are robust and user friendly in nature
  • Be able to use existing models more competently, interpret the results and have greater comfort over the integrity and accuracy of the model’s calculations
  • Be able to use Discounted Cash Flow to estimate fair values.

The course has been tailored specifically for retail investors. Financial modelling and DCF valuation could be very complex, but can be extremely simple in some circumstances. The course strives the balance by providing sufficient practical knowledge and tips to retail investors, in order to build sensible financial modelling and DCF valuation in short time frame.

Prerequisites
Some prior knowledge and experience is assumed. For example, participants should have:

  • The ability to navigate easily around Excel’s menu options
  • Working knowledge of financial statements and rudimentary accounting
  • A basic understanding of leading approaches towards company valuation
  • A basic understanding of valuation ratios, such as P/E, Earning Yield, Dividend Yield, etc.
  • (Nice to have) A basic understanding of valuation models, such as Graham’s Formula, EBIT Multiples, Katsenelson P/E Model, etc.

Modules

Module 1: Financial Modelling

  1. You will learn exactly what financial modelling is and why we care about it.
  2. The overall model development process and items to cover during the design phase.
  3. Revenue and Expense Projections
    • Learn how to project a company’s revenue based on its average customer value by segment, pricing growth, and customer…
    • Understand the 3 different methods of projecting revenue, in general, and how to ensure that your numbers are not wildly off the mark in future periods.
    • Learn to project a company’s Income Statement expenses.
  4. Balance Sheet and Cash Flow Statement Drivers
    • Learn how to project Current Assets, Current Liabilities, and key Cash Flow Statement line items based on the company’s historical trends and future cash flow profile.
    • Learn which items are projected independently on the Balance Sheet, and which items flow in from changes on the Cash Flow Statement.
  5. Debt Schedule
    • Learn how to add a simple debt schedule – including how to factor in additional draws and repayments, interest, and the impact on the company’s cash balance.
  6. Share Issuance and Buy Back
    • Learn how to project share issuance and buy back, and the impact on the company’s Balance Sheet and Cash Flow Statement.
  7. Linking the Statements
    • Learn how to link the financial statement projections properly, tie together all the items, and ensure that the Balance Sheet balances
  8. Assumptions, sensitivities and scenario cases.
  9. Optimizations on Financial Modelling.

Module 2: Valuation

  1. The Time-Value of Money
    • To understand concept of time-value of money by using real life example.
  2. What Money is Worth?
    • Learn about different ways to measure the value of money and make investment decisions in this lesson, including the Present Value (PV), Net Present Value (NPV), the Internal Rate of Return (IRR), and the Weighted Average Cost of Capital (WACC)
  3. Free Cash Flow
    • Learn how to calculate Unlevered Free Cash Flow and Levered Free Cash Flow, and understand their differences and impacts in DCF.
  4. Discount Period
    • Learn how to optimize discount period rather than using the “normal” discount period, such as 1, 2, 3, etc.
  5. Discount Rate
    • Understand the concept of WACC and how to calculate it by the standard formula.
    • Learn alternate way to calculate WACC.
  6. Equity Value & Enterprise Value
    • Learn how Equity Value and Enterprise Value differ, the basics of how to calculate them.
  7. Terminal Value
    • Learn the two main methods for approximating Terminal Value and how to cross-check the output from both methods.
    • Learn more about what makes it tricky to select appropriate multiples and/or long-term growth rates in this calculation.
  8. Putting DCF into Action
    • Learn what the key concept behind a DCF, as well as the intuition for how you value a company based on its Free Cash Flows, your selected discount rate, and the company’s Terminal Value
    • Learn the major steps in doing DCF valuation.
    • Estimate Fair Value by DCF model.
  9. Uncertainty Risks of Fair Values
    • Learn systematic way to determine margin of safety before investing.
  10. Optimizations on DCF Model

PADINI – Fundamental Analysis (26 Oct 2016)

Research Report (FY16 Q4): Word Document

Data and calculation: Excel

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