# Free spreadsheets



## sinner (6 July 2015)

Hi,

Thought I would throw up some recent spreadsheets I made in the process of my normal researching. Usually before I turn an idea into a proper program I try to make a spreadsheet first to understand it better.

Now I am posting this stuff just in the spirit of sharing, none of the below should be considered endorsement of a particular trading strategy, nor am I suggesting the below be used in any serious fashion without a lot of refinement and work. But perhaps you will spot a mistake I made, or have an interesting observation or find it useful in inspiring your own ideas.

They are .ODS (Open Document Spreadsheet) format so should be compatible with Excel/OpenOffice/LibreOffice/Google Spreadsheet.

Fill in the orange sections to see the results in bold.

View attachment files.zip


1. Return Forecast

Given:
* Investment duration (years, should never be less than 10)
* Forecast business growth (EPS)
* Forecast annual dividend yield 
* Long term dividend yield average
* Current valuation (can be whatever you like, e.g. Price/Sales, CAPE, Market Cap/GDP, etc)
* Long term valuation average (average value of entire valuation dataset)
* Current risk free rate (this can be your discount rate, or it can be the current risk AAA rate or whatever you like)
* Forecast volatility (I use std dev of last 63 days daily return, but you can use Implied Volatility or GARCH/ARIMA/whatever inputs you like).

Outputs:
* Forecast annualised return for the investment duration based on growth, valuation mean reversion (valuation and dividend models) and dividend yield.
* A Sharpe ratio exposure metric based on forecast annualised return, assumed risk free rate and forecast volatility. The idea here is to scale your exposure by the Sharpe ratio on a daily/weekly/monthly/quarterly/yearly basis.

This concept is based on my understanding of some work from John Hussman.

Here is an example of the S&P ASX 200 index:



2. Intrinsic Value calculator

I am not sure where I got the idea for this one but I was reading something which described what is essentially a crude intrinsic valuation calculator and this was my attempt to implement.

Given:
* Current EPS
* Current DPS
* Your chosen discount rate
* Forecast EPS growth for each year (by default the spreadsheet assumes homogenous EPS growth)
* Forecast DPS growth for each year (by default the spreadsheet assumes homogenous DPS growth)

Outputs:
* "Intrinsic Value" price of the security today if the market values the security at a P/E of 10 in 10 years.
* "Intrinsic Value" price of the security today if the market values the security at a P/E of 15 in 10 years.
* "Intrinsic Value" price of the security today if the market values the security at a P/E of 20 in 10 years.

Example:



3. FS_SCORE calculator

In the book "Quantitative Value" an improved version of the Piotroski score is presented. Now I am an owner of this book and I really like the methodology presented within. Full disclosure, I own some of the NYSE listed ETF which uses this methodology (QVAL). Now if you don't own the book, don't fret, since every part of the book is available as free blog posts (see example below).

Piotroski score: http://www.investopedia.com/terms/p/piotroski-score.asp
FS_SCORE: http://blog.alphaarchitect.com/2012...y-high-quality-stocks-continued-part-3b-of-4/

Given: 
* Net income (current and prev year)
* Total assets (current and prev year)
* CapEx (current and prev year)
* Cashflow from Operations (current and prev year)
* Long term debt (current and prev year)
* Current assets (current and prev year)
* Current liabilities (current and prev year)
* Equity purchase $ amount
* Equity issuance $ amount
* Cost of goods sold (current and prev year)
* Revenue (current and prev year)

Outputs:
* Free cash flow (current and prev year)
* Accruals
* Gross margin (current and prev year)
* Return on Assets (current and prev year)
* FCF on Assets (current and prev year)
* Leverage (current and prev year)
* Liquidity (current and prev year)
* Margin (current and prev year)
* Turnover (current and prev year)
* FS_SCORE (rates the company out of 10 on 3 areas)

Example:


----------



## prawn_86 (7 July 2015)

Great work, this will be of use to a heap of people i am sure


----------



## skc (7 July 2015)

Thanks Sinner. I haven't spent the time looking at them in any detail, but coming from you I am sure it's top shelf.

Very generous of you for posting them.


----------



## craft (7 July 2015)

It looses the formula's when I open with excel.

Looks like you have discounted the dividend back to front in the second spread sheet.


----------



## Deplenthion (7 July 2015)

Nice work Sinner

Always good to see another trader/programmer 

Regards
John


----------



## craft (7 July 2015)

craft said:


> It looses the formula's when I open with excel.
> 
> Looks like you have discounted the dividend back to front in the second spread sheet.




The P/E terminal values are also being discounted at 8.27% not 7.64% in that example. Discount rate is your required return, I'm guessing you are applying your 7.64% to the closing balance which is not correct.


----------



## sinner (7 July 2015)

craft, tomorrow (when I am back in front of the correct computer) I will post up an Excel version and use your comments to try to see what I did wrong with the second spreadsheet, please stay tuned as I'm sure your input will help me understand this stuff a little better.


----------

