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