## Building a Financial Model

Piotroski and Net Net Working Capital

As I prepare, update and upgrade my work I come across new things to think about and old things to improve. For a few years now I have been using a spreadsheet filled with data on BP, Repsol and Royal Dutch Shell to encourage delegates and others to set up and build a financial analysis spreadsheet and model and to consider the most efficient way of doing that

This week I decided to update that spreadsheet file with two measures that are not new to me but that are new to the file:

• Piotroski F Score Model
• Net Net Working Capital Model

Neither model is complex and both of them are used by stock market analysts and investors as a way of trying to find good value shares to invest in or advise on.

For further reading on these two measures, take a look at this very informative web site: http://www.oldschoolvalue.com. Some of what follows is based on Jae Jun’s work but the spreadsheet programming and ideas are our own.

Piotroski F Score Model

The Piotroski F Score Model is around 13 years old now and was invented by a Professor of Accounting. No surprise, then, that its component parts are accounting measures and it comprises 9 measures or ratios:

 Name of Measure Formula for Measure Analysis 1 Return on assets (ROA) Net income before extraordinary items for the year divided by total assets at the beginning of the year. Score 1 if positive, 0 if negative 2 Cash flow return on assets (CFROA) Net cash flow from operating activities (operating cash flow) divided by total assets at the beginning of the year. Score 1 if positive, 0 if negative 3 Change in return on assets Compare this year’s return on assets (1) to last year’s return on assets. Score 1 if it’s higher, 0 if it’s lower 4 Quality of earnings (accrual) Compare Cash flow return on assets (2) to return on assets (1) Score 1 if CFROA>ROA, 0 if CFROA 5 Change in gearing or leverage Compare this year’s gearing (long-term debt divided by average total assets) to last year’s gearing. Score 1 if gearing is lower, 0 if it’s higher. 6 Change in working capital (liquidity) Compare this year’s current ratio (current assets divided by current liabilities) to last year’s current ratio. Score 1 if this year’s current ratio is higher, 0 if it’s lower 7 Change in shares in issue Compare the number of shares in issue this year, to the number in issue last year. Score 1 if there is the same number of shares in issue this year, or fewer. Score 0 if there are more shares in issue. 8 Change in gross margin Compare this year’s gross margin (gross profit divided by sales) to last year’s. Score 1 if this year’s gross margin is higher, 0 if it’s lower 9 Change in asset turnover Compare this year’s asset turnover (total sales divided by total assets at the beginning of the year) to last year’s asset turnover ratio. Score 1 if this year’s asset turnover ratio is higher, 0 if it’s lower

In spite of my own advice to spreadsheet modellers to PPP (Paper, Pencil, Plan), we plunged into our programming and devised a table that does what it was supposed to do: arrived at the Piotroski F Score for each year for BP from 2011 to 2006 based on the rules given above. Here is a screenshot of our table:

There you are: everything labelled, numbered, explained, workings clearly shown and the final result for each year is conveniently located right at the top of the table: so there is nothing to search for. The only thing we need to add is the decision rule that the higher the F Score the better since low value F Scores tend to highlight companies in trouble.

OK! So what’s wrong with this table? Well, we can see that the score for ROA for 2011 is 1, meaning that it’s positive; negative for 2010 and then positive all the way back to 2006. And? And … what is it? Is the raw ROA 5%, 10% … 0.00001% each year?

We can say the same with all nine measures or ratios: whilst we have found the F Score, we cannot drill down in any way to get any kind of understanding of what a weak company might have to do to turn itself around.

The second problem with this table that we cannot see from this screenshot is this formula relating to the gross margin:

=IF((Inc_State_and_CFS_BP!B10-Inc_State_and_CFS_BP!B11)/Inc_State_and_CFS_BP!B10>(Inc_State_and_CFS_BP!C10-Inc_State_and_CFS_BP!C11)/Inc_State_and_CFS_BP!C10,1,0)

Do you really want to audit or review the value of 1 or 0 in a cell based on that formula? We thought not.

By reorganising this work sheet we have now programmed the previous mega formula with this formula:

=IF(B19>C19,1,0)

By the way, you might tell us that the sheet names are making our formulas too long and you would be partly right. However, that’s the way they are: let’s assume the names of the sheets cannot be changed since that can happen!

How did we achieve this productivity improvement? We created a separate table in which we list all of the variables and the results of the nine formulas called on by the Piotroski F Score Formula:

The worst looking formula in that table is again for gross profit and it looks like this:

=Inc_State_and_CFS_BP!B10-Inc_State_and_CFS_BP!B11

Again the work sheet names are long and, at least so far, we have not set up any range names that could make our work even easier and more productive.

Going back to the previous mega formula, =IF(B19>C19,1,0) refers to this new table and for 2011 says =IF(2011’s Gross Margin is greater than 2010’s Gross Margin, put 1 in this cell otherwise put 0 here)

Could we improve matters even more? We think so but not everyone will like this change:

Note: you can see blank cells, zeroes and a #DIV/0! error in the above tables: they are there deliberately as a further learning point for my delegates … ask yourself, however, how I might get rid of them!

What we have done here is to separate out the data gathering (from the income statements, balance sheets and cash flow statements) from the ratio calculations that Piotroski calls for.

Assuming that we are able to use the whole of this spreadsheet file as a template now, which my future delegates will certainly be working towards, we now have a Piotroski F Score working model that is better fit for purpose!

In the worksheet we have been working on there are also

• sparklines for the ratios
• a correlation matrix in which we compare the ratio results with each other

Net Net Working Capital

Another investor ratio that most accountants probably do not use is the measure of Net Net Working Capital (NNWC). What this measure does is to tell us the potential value of a company’s working capital under some rather severe restrictions. Look at the formula to see what that means:

NNWC = 100% cash and short term investments + 75% of debtors (receivables) + 50% of stock (inventories) – 100% of total liabilities

The thinking behind this measure is:

• cash and short term investments are worth 100% of its value
• accounts receivables should be taken at 75% of its stated value because some might not be collectible
• take 50% off inventories, due to discounting if close outs occur
• all liabilities will have to be paid in full

As before, without PPP, we prepared the following table, again for BP:

Good! Now we know that NNWC for BP for 2011 is \$(121,414) million. We can also see the results for earlier years.

Then we realised that this measure on its own is rather limited as most, if not all, of the people we work with will never have heard of NNWC and therefore will have no point of reference for it. Therefore, we added this table:

Now we can see the more familiar unadjusted working capital values for BP as well as a statement of NNWC as a percentage of WC: a basic comparison of the old and the new measures.

Again, our spreadsheet contains sparklines for the NNWC, the WC and the comparison of NNWC and WC results.

Finally, the above results relate to BP but this model is ready to be used as a template for any company at any time now:

• type in the data in the data gathering table if you wish
• copy and paste your data into the supporting worksheets if you wish

It will work!

Duncan Williamson

### 7 Responses to “Building a Financial Model”

1. GJA Says:

Duncan,

This is a good article. I have been working on similar models in Excel myself, but I have a few questions on implementation, as I am new to using advanced functionality in Excel (I tend to use Matlab much more often). I am having trouble pulling balance sheet/income statement data from the web in Excel- I have had very mixed results with a Stock Market Function add-in that I downloaded online. Do you have recommendations on how to do this, or could you show how you did this in this spreadsheet? I am also curious as to what site provides free data going back 7 years from the present. I did not see a link on this site to try out the spreadsheet and dig into the logistics of it.

Thanks!

2. Peter Says:

Hi Duncan do you have an example of this spreadsheet described above that I can download, will help with my current cima studies

3. Jeffrey Says:

4. […] Here is the list of criteria used to calculate the Piotroski Score. This and the calculation in the sheet is based on  Building a Financial Model […]

5. […] Here is the list of criteria used to calculate the Piotroski Score. This and the calculation in the sheet is based on  Building a Financial Model […]

This site uses Akismet to reduce spam. Learn how your comment data is processed.