Excel Template–Enhancements

I have made few major enhancements to my excel template for my own benefits (and also hopefully will benefit you as well).

Changed presentation of presenting the financial data

The following table consists of the raw financial data, and linked to SQL Server database. This is the data source for all calculations and analysis in my excel template. I still maintain this table in my excel template, but I have made it hidden.

Sometimes, when I want to understand why CROIC or Cash Conversion Cycle of a company getting so high (or so low), I will refer to this table to find out the root cause. The issue is this table is very long, and I have to scroll here and there to find the relevant data. This is quite time consuming.


So, to make my life easier, I have created three more worksheets: Income Statement, Balance Sheet and Cash Flow.

Income StatementBalance SheetCash Flow

Few important things you may need to aware:

  • I follow classification used by MorningStar in my financial statement.
  • This means some of naming standard that I use here is not exactly same with annual report.
  • For some classifications, I added few items in annual report together. For example, “Investment/Asset Impairment Charges” in “Net Cash From Operations” is sum of “Property, plant and equipment written off”, “Allowance for inventories write down/(back)”, “Inventories written off” and “Impairment loss on receivables”.
  • The reason I need to use these classifications is I need to make some standardization so that I can store financial data in database without creating too much overhead maintenance.
  • You should also notice many percentage values in my financial statement. This is structural analysis or common-size analysis financial statements. This analysis helps me to easily identify large or drastic changes in a firm’s financials. Rapid increases or decreases will be readily observable, such as a rapid drop in reported profits during one year.
  • This format of financial statement is not suitable for banks though. I will design a format specifically for banks.

Staging Worksheet for Calculated Values/Ratios

I have created a staging worksheet for all calculated values or ratios. The purpose of this worksheet is also to help me easily identify components that attributed to (poor or good) company performance. I believe that you will also gain some benefits as you can easily understand how I derive out calculated values or ratios.

I also incorporated hyperlink in the workbook. If you want to understand the calculation of “Owner Earnings”, just click the link and it will take you to the calculation.


Price vs Intrinsic Value

This chart is to help me to gauge whether effectiveness of DCF in valuing a stock, and also better illustration whether the stock is undervalued or overvalued.


EY% Chart

The concept of EY% chart is actually from EquitiesTracker. It is quite simple to use this chart. A buy signal will be triggered if EY% rises above or close to the green line; a sell signal will be trigger if EY% drops below or close to the red line.


There are few more minor handy enhancements, but not really significant to mention here.


2 thoughts on “Excel Template–Enhancements

  1. Hi LC,
    How to intepret the Price vs IV chart from the rising and falling of the IV line under different circumstances?


    • Price vs IV chart is mainly for gauging whether DCF analysis is suitable for a particular stock.

      For instance, if the stock price is going up over the years, but the IV got no increment, this may indicate DCF analysis is not suitable for the stock.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s