# Building a Fundamental Analysis Spreadsheet – FA 5

Today we’re going to go back through the financial statements, and try to build a Fundamental Analysis Spreadsheet to take some of the work out of calculating all those ratios.

Contents

###### The Balance Sheet

The first step in our Fundamental Analysis Spreadsheet is to build up the balance sheet. You’ll remember that the balance sheet shows what a company owns and what it owes.

As with all of the financial statements, I’ll be using the Tesco examples from Phil Oakley’s ShareScope articles that we used in the original posts.

Creating this sheet in Google Sheets was pretty straightforward.

I’ll just spend a few moments to go through the colour coding:

- Lines in black are numbers that I’ve typed in myself (we’ll look at streamlining data input in a later post)
- The cells shaded in blue are calculated directly by the sheet (I didn’t need to enter anything)
- The lines in purple are the numbers that are used in the ratio analysis

I’ve also used a Google Finance function to look up the number of Tesco shares in issue, so that we can carry out per-share calculations:

`Shares (M) = GOOGLEFINANCE(LON:TSCO,"shares")/1000000`

Luckily, everything balances. So far so good.

###### Balance sheet ratios

This part didn’t go quite so well.

- The current ratio matches the ShareScope number
- I’ve used conditional formatting to shade this cell pink as the current ratio isn’t good enough at 0.7

- The quick ratio also matches
- The quick ratio is also bad and shaded pink automatically

- But the debt to capital ratio (in red) doesn’t match
- And so on down the list
- 6 of the ratios match and four don’t

- I’ve used the number of shares to automatically calculate NAV and NTAV per share
- I’ve excluded for now a number of ratios that either need data from one of the other financial statements, or use two years of data to calculate an “average” figure for the year:
- Working capital to turnover
- Stock days
- Average stock days
- Debtor days
- Average debtor days
- Creditor days
- Average creditor days
- Working capital cycle
- Avg. Working capital cycle

###### Income statement

Assembling the income statement went fairly well, though I find the layout used by ShareScope very confusing.

The figure for pre-tax profit doesn’t match – the sheet calculates a different figure from that stated by SharePad.

- I’ve marked that “wrong” figure in red again.

As you can see, a lot of the income statement is calculated (it’s mostly addition, subtraction and sub-totals).

###### Income Statement ratios

Another mixed bag here. The profit margins match, but nothing else:

- dividend cover matches if I use the “stated” pre-tax profit, but not if I use the calculated figure from the sheet
- interest cover doesn’t match at all
- I don’t have the detail in the income statement to calculate the fixed charge cover

###### Cashflow statement

The construction of the cashflow statement went pretty well.

The problem here is that I expected that many of the numbers would be taken from the other financial statements – that the cashflow statement restates the other numbers to show the cash effect.

This doesn’t seem to be the case:

- only five items from the cashflow statement appear in the other statements
- only dividends paid and operating profit match
- I’ve marked these in blue

- depreciation, taxation and interest paid don’t match the other sheets
- I’ve marked these in red

###### Spreadsheet Key

Here’s a quick re-cap of what all the colours mean in the spreadsheet:

###### Cashflow ratios

The four cashflow ratios calculate nicely, though it should be remembered that the underlying numbers for taxation, depreciation and interest don’t match those from the other financial statements.

- operating cash conversion % is slightly different to the number in the original cashflow post because of a typo in the original calculation
- free cash dividend cover of 0.26 is shaded pink because that’s a very bad result

Free cashflow conversion, free cashflow margin, FCFf margin, capex to depreciation and capex to turnover haven’t been calculated here as they each need a number from the income statement.

- Instead they are calculated a couple of sections further down the page

###### Two-year balance sheet

In order to calculate a number of “average” ratios (which use values at the start and end of a year to give an average), we need to put together a two-year balance sheet.

Here it is:

###### Multi-sheet ratios

The last step for today is to calculate the ratios that need data from more than one sheet:

Building this section went pretty well with the exception of debtors / creditors to turnover, which don’t match the ShareScope numbers.

###### Conclusions

It’s been a frustrating day overall.

- We have something that looks like a set of consolidated financial statements
- We’ve removed duplication between and within sheets, to automatically calculate as many of the numbers in the sheets as possible
- We’ve also worked through all the ratios that we met when we considered each of the financial statements in turn, and these are also being calculated automatically
- So we have a minimum dataset of what we need to examine a company’s accounts

But:

- several items are inconsistent between the financial statements
- other items can’t be calculated properly from the data in the statements
- a significant minority of the ratios don’t match the results from ShareScope

Today was meant to be a “back to basics” session, but I don’t think we’ve gone back far enough. The lack of progress is disappointing, but Rome wasn’t built in a day.

Next week I’ll look at putting together the same consolidated sheet from the company’s real financial statements, by downloading a copy of the Tesco annual report.

I’m not sure how many of the ratios we’re interested in will be calculated by Tesco themselves, so we’ll probably also need a free source of these ratios.

Until next time.

*Share this with , Twitter, Google+, Pinterest, LinkedIn, Tumblr, Reddit and StumbleUpon.*