On this page we’re going to break-down how to read the spreadsheets that I’ve uploaded for each company. Find the spreadsheets below – there’s different spreadsheets depending on how far back the company’s results go.
Rows 1-8
- Current Stock Price – here is the current stock price of the company. I’ll try and update these as often as I can, but if it’s incorrect then simply type in the correct current stock price into the yellow box.
- Current FCF and Sales Multiple – this tells you what the current FCF and sales multiples the company is currently trading at based on the current stock price.
- Last Year’s Growth, Average Growth over Last 3yrs, Long-Term Average Growth – these are just the growth rates of the company over the periods mentioned.
- Current ROIC – this gives the ROIC of the company.
- Your Chosen Margin of Safety – input your chosen margin of safety here and the spreadsheet will apply it to all our calculations.
- Fair Stock Prices by Method – this gives the calculated stock price for our company by each method that we have available to use; multiples from similar companies, multiples from company’s history, multiples from company’s growth, and the low-growth formula. These are calculated further down the spreadsheet, but are written as summaries up here.
- Our Target Stock Prices by Method – this includes the margin of safety that we’ve chosen to calculate the stock price using our four main methods as above. These are calculated further down the spreadsheet, but are written as summaries up here.
- Recommended FCF or Sales Multiple – this gives a conservative recommendation of the FCF or sales multiples you should use to value the company.
Row 9-24
This gives the balance sheet numbers in complete detail. Each item (cash, inventory, etc) also has either ‘% of assets’ or ‘% of liabilities’ columns next to them. This is so we can see when an individual item is increasing or decreasing more than everything else, which will come in use to spot red flags and green flags.
Row 28-46
This gives the income statement numbers in complete detail. Each operating expense will also be calculated as a percentage of sales (‘% Rev’) in the column next to it. This again allows us to see when an individual expense is increasing or decreasing more than everything else, which will come in handy to see why a company is becoming more profitable (decreased expenses) or less profitable (increased expenses).
Underneath each item I have also calculated its average. Below that I’ve calculated the long-term growth of each expense so we can see if any particular expense increases or decreases faster than the sales do. This will also come in handy.
Row 49-66
This gives the cash flow statement numbers in complete detail. Note that ‘Normalised FCF’ aims to calculate the FCF by using the average capital expenditures (as a % of sales) over time. Capital expenditures can fluctuate significantly from year-to-year and are often the main reason why FCF may also vary significantly from year-to-year. By finding the average capital expenditure (as a % of sales), we are able to find the normalised FCF. The column ‘Buying Companies’ just shows you how much the company spent on buying other companies.
Row 69-85
This gives three groups of ratios;
- Company’s Quality (ROE, ROIC) – these tell you how high-quality (or efficient) the company is. If these percentages are lower than 5% then it’s a bad company, if 5-10% then average, if 10-15% then above-average, and if 15%+ then it’s a great company.
- Likelihood of Survival (Quick, Current) – these tell you how likely the company is to run out of cash in the near-future. If the current ratio is below 1.5 then the company is at-risk of bankruptcy, and if the quick ratio is below 1 then the company is again at-risk of bankruptcy.
- Amount of Debt – this tells you how much debt the company has, giving an idea of the risk of going bankrupt in the long-term. We want debt to be less than 40% of the company. Interest coverage tells you how many times higher the company’s profit is above their mandatory debt repayments (interest). We want the company’s profits to be at least 2 times higher than these interest payments, otherwise the company is at-risk of bankruptcy.
Row 89-106
This section tells you how fast the company is growing. Growth in sales is our main indicator of growth, but there are others, including; earnings per share, equity per share, liquidation value, and free cash flow. Each metric is calculated as a long-term average growth (CAGR), or a year-to-year growth (yoy). CAGR is a more realistic calculation.
Row 109-125
This section allows you to value the company by valuing each segment of it individually, then adding them altogether to find the company’s total value. A common way to find an investment opportunity is when a company has multiple segments and one of them is doing very well, but this is hidden in among the average (or poor) performance of the rest of the company.
Row 128-145
This section values the company using multiples from similar companies. Insert the details of the similar companies (you can fit up to 4 of them) and the company’s total value will be calculated for you.
Row 149-162
This section shows you the stock price of the company for a variety of given FCF multiples. You can then pick which multiple suits your company best.
Row 164-180
This section calculates the total value of the company using the company’s historical multiples. It does this for sales multiples, NOPAT multiples (which you won’t use much), and FCF multiples.
Row 182-190
This section calculates the total value of the company using the company’s growth. The company’s growth rate will automatically insert as their growth in sales last year.
Row 194-200
This section calculates the total value of the company using the low-growth formula. This approach will be valuing the company with a 3%pa long-term growth rate.
Row 204-247
This section calculates the total value of the company using a Discounted Cash Flow Analysis. Use this approach to value those high-growth companies which are losing money.