# Valuation Spreadsheets



## Bill and Ben (11 April 2013)

I'm just wondering if anyone could advice me on where I can get a spreadsheet where I enter relevant financials data and have a range of metrics calculated for me ie ROE, ROIC, EM, P/E, EV, P/B, EPS Growth %, Debt ratio's, Liquidity ratio's etc. If it could include some valuation formula's would be even better ie Gordon Growth model, Graham's method, DCF etc. 


I've tried to knock one up myself, but am having trouble with consistency on stuff like free cash flow, so it's proving to be more trouble than it's worth. And when it comes to the valuation, it's a little out of my excel skill league tbh. I'm willing to pay for the right spreadsheet, so any recommendations at all would be welcome. 

If it could link to online data sources and save me the data entry I'd pay even more. I saw one like this at the Old School Value site, but it's for US markets only.


----------



## DJG (11 April 2013)

Bill and Ben said:


> I'm just wondering if anyone could advice me on where I can get a spreadsheet where I enter relevant financials data and have a range of metrics calculated for me ie ROE, ROIC, EM, P/E, EV, P/B, EPS Growth %, Debt ratio's, Liquidity ratio's etc. If it could include some valuation formula's would be even better ie Gordon Growth model, Graham's method, DCF etc.
> 
> 
> I've tried to knock one up myself, but am having trouble with consistency on stuff like free cash flow, so it's proving to be more trouble than it's worth. And when it comes to the valuation, it's a little out of my excel skill league tbh. I'm willing to pay for the right spreadsheet, so any recommendations at all would be welcome.
> ...





Hi mate,

I'm about 1/2 - 3/4 of the way through making mine. It has most of the basic ratios (such as those mentioned) but not much else like the Gordon Growth Model etc - this you could add though.

Still needs some touching up.

I'm doing 'Business Models with Excel 'at uni so should prove to be helpful.


----------



## CanOz (11 April 2013)

Bill and Ben said:


> I'm just wondering if anyone could advice me on where I can get a spreadsheet where I enter relevant financials data and have a range of metrics calculated for me ie ROE, ROIC, EM, P/E, EV, P/B, EPS Growth %, Debt ratio's, Liquidity ratio's etc. If it could include some valuation formula's would be even better ie Gordon Growth model, Graham's method, DCF etc.
> 
> 
> I've tried to knock one up myself, but am having trouble with consistency on stuff like free cash flow, so it's proving to be more trouble than it's worth. And when it comes to the valuation, it's a little out of my excel skill league tbh. I'm willing to pay for the right spreadsheet, so any recommendations at all would be welcome.
> ...




Put it out to eLance....

Plenty of skills just waiting to be tapped.

CanOz


----------



## skc (11 April 2013)

Kermit345 has a pretty nice looking spreadsheet. I have no idea about it's ability to make you money, however.

https://www.aussiestockforums.com/f...t=22762&page=2&p=636341&viewfull=1#post636341


----------



## Bill and Ben (11 April 2013)

DJG said:


> Hi mate,
> 
> I'm about 1/2 - 3/4 of the way through making mine. It has most of the basic ratios (such as those mentioned) but not much else like the Gordon Growth Model etc - this you could add though.
> 
> ...




That would be great if you could let me take a look when you're done mate.


----------



## Bill and Ben (11 April 2013)

CanOz said:


> Put it out to eLance....
> 
> Plenty of skills just waiting to be tapped.
> 
> CanOz




Nice idea. I've bookmarked the link and it's definitely an option. I'll see how my search pans out. I'd prefer one that's been in use rather than have to go through the testing of a new one, but it might end up being the best way to go. Thanks.



skc said:


> Kermit345 has a pretty nice looking spreadsheet. I have no idea about it's ability to make you money, however.
> 
> https://www.aussiestockforums.com/f...t=22762&page=2&p=636341&viewfull=1#post636341




I don't know if any spreadsheet will make me money per se. I just want an easy way to get these metrics and valuations at hand so I can add them to my considerations. I might send Kermit345 a message and see if we can't wrangle something. Cheers!


----------



## galumay (11 April 2013)

I would also be interested, i have struggled to compile anything that i have any real confidence in, I love the look of Kermit345's workbook, but he made it pretty clear he had no interest in selling/sharing it. (which is perfectly understandable.)

So if people are sharing please pm me and let me know! 

I am happy to customise to suit my strategy and system, its the work of creating a really neat template which is hard for me.


----------



## Ves (11 April 2013)

galumay said:


> I am happy to customise to suit my strategy and system, its the work of creating a really neat template which is hard for me.



You don't really need much of a template to value anything.

The key is knowing how to make assumptions and which valuation method is best to turn those assumptions into a usable valuation.

The maths isn't really that advanced.  You just need to figure out how to use it, then you can build whatever spreadsheets you desire from the ground up.  

Most people make their models way too complicated and over-crowd them with superfluous data and ratios that distract from the big picture.  Valuation isn't supposed to be scientfically accurate... it's putting what you know if you quantitative format to use as a guide for making investment decisions.


----------



## McLovin (11 April 2013)

Ves said:


> You don't really need much of a template to value anything.
> 
> The key is knowing how to make assumptions and which valuation method is best to turn those assumptions into a usable valuation.
> 
> ...




Yeah I agree completely. Not all ratios are relevant to every company, you end up with a really cluttered spreadsheet with lots of irrelevant ratios and calculations. If I'm analysing a company like JBH I'm interested in working capital management, gross margins, leases. When it comes to a TLS I'm more interested in all that PP&E they've got, depreciation rates, capex spend.

No matter how pretty your spreadsheet the law of GIGO still applies.


----------



## doctorj (11 April 2013)

Valuation is more art than science.  A valuation spread sheet is a useful tool, but it needs to be tailored for the company/industry to be useful.  Also, I find the exercise of developing the valuation more informative than the resulting number – you often find that the business is dependent on things you didn’t originally expect…


----------



## kermit345 (12 April 2013)

Hey guys,

Received a PM from Bill and Ben but figured i'd reply here. It's been quite some time since I used the spreadsheet that was linked on here as I haven't been investing or making changes to my portfolio anywhere near as much as I previously was. I've still got a few different versions of it floating around on my computer at home so i'll see what I can find although i'm not sure if the macros or excel add-on still work that allow you to download the data.

It's probably not a bad little project for me to get back into if I have some spare time. I've been spending a lot of time over the last 12 months looking at angles for gambling on race horses would you believe and using excel to download data from various websites and using that data to come up with a rating for each horse in a race to find what horse to bet on. Pretty much the same as you do with a company, download the data, run it through some equations and see if you want to buy or not.

Anyhow if you can find websites that have the data you want and gain some kind of understanding about how web queries and macro's work within excel then it can be pretty easy to bring in all the information you need. As others have said, equations, valuations and other figures won't give you the final answer, you still have to be subjective in some ways, but using the power of computers/excel can certainly save you a lot of time. You must also be wary of data being incorrect from your sources as well though, so always question your outputs and think to yourself 'do those values make sense for this company'.


----------



## DJG (12 April 2013)

McLovin said:


> Yeah I agree completely. Not all ratios are relevant to every company, you end up with a really cluttered spreadsheet with lots of irrelevant ratios and calculations. If I'm analysing a company like JBH I'm interested in working capital management, gross margins, leases. When it comes to a TLS I'm more interested in all that PP&E they've got, depreciation rates, capex spend.
> 
> No matter how pretty your spreadsheet the law of GIGO still applies.




That's the main reason I made my own. Same example as yours, I find inventory turnover more important to JB HI FI than say a mining company. Along with amount of sales from marketing etc.

You can declutter by using groups and bring sections of the spread down to those little + symbol to expand it.
Just need to find time to finish it off.




kermit345 said:


> It's probably not a bad little project for me to get back into if I have some spare time. I've been spending a lot of time over the last 12 months looking at angles for gambling on race horses would you believe and using excel to download data from various websites and using that data to come up with a rating for each horse in a race to find what horse to bet on. Pretty much the same as you do with a company, download the data, run it through some equations and see if you want to buy or not.




Had a look at arb betting mate?


----------



## kermit345 (12 April 2013)

Not really, arb betting seems too rare to find and after you do start making a decent amount off it a few times the betting agencies are pretty quick to limit or close your account. The only other way is to do trading on betfair but then you have to be at your computer which isn't possible if you work OR have a betting bot coded which isn't really my cup of tea either. The other issue is Australian markets are charged a turnover charge if you do too much backing on certain markets due to the load being put on betfair's servers.

Basically that leaves the only option as arb/trading on overseas markets, particularly the UK which can be very profitable if your good at it or have a BOT set up correctly, but then I lose interest because I actually enjoy the gambling/thrill aspect of it as well.

Anyway i've probably said too much, don't want to take the thread off track. If anybody needs help with excel equations etc let me know.


----------



## Bill and Ben (16 April 2013)

kermit345 said:


> As others have said, equations, valuations and other figures won't give you the final answer, you still have to be subjective in some ways, but using the power of computers/excel can certainly save you a lot of time. You must also be wary of data being incorrect from your sources as well though, so always question your outputs and think to yourself 'do those values make sense for this company'.




Thanks kermit, 

I completely agree that you won't find the answers in a spread sheet, but also that it can be very useful and time saving. I have guys who do the data entry from 5-10 years worth of annual reports for me in their downtime and the result gives me a nice snap shot of a company over that period, which in turn helps me understand what else I need to be looking for. It's just another tool.

I've found it very useful, but I've had trouble with the consistency of FCF calculations (I think I keep stuffing up how to accurately calculate CFO from the financial reports); and my EPS Growth percentage when dealing with negative figures is wrong. I'm far from competent with excel, but I think I may have found someone who can help. Maybe I'm overvaluing its usefulness, but I'm surprised I couldn't find anyone selling a basic spreadsheet for the ASX.

As some else said though, just developing this and trying to make it work has been an education in itself. Even if it comes to naught I've learned a lot.


----------



## DJG (16 April 2013)

kermit345 said:


> Not really, arb betting seems too rare to find and after you do start making a decent amount off it a few times the betting agencies are pretty quick to limit or close your account. The only other way is to do trading on betfair but then you have to be at your computer which isn't possible if you work OR have a betting bot coded which isn't really my cup of tea either. The other issue is Australian markets are charged a turnover charge if you do too much backing on certain markets due to the load being put on betfair's servers.
> 
> Basically that leaves the only option as arb/trading on overseas markets, particularly the UK which can be very profitable if your good at it or have a BOT set up correctly, but then I lose interest because I actually enjoy the gambling/thrill aspect of it as well.
> 
> Anyway i've probably said too much, don't want to take the thread off track. If anybody needs help with excel equations etc let me know.




PM coming


----------

