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

Crude Oil Update (9 Oct 2016)

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

crude-oil-full1116-future

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.

Excel

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!