Free template to analyse companies?
19/08/2011 9 Comments
Yes its free! An Excel template for you to analyse industrial type companies, whether they are in Australia, US, Europe, Africa or Asia. It also includes several valuation methods and my own personal company ranking system. You might ask why I’m giving this away, which is a fair question. The main reason is that I can’t analyse every company, I just don’t have the time. By giving out the template, the community as a whole should be able to improve upon it and at some point in the future, we can collect templates for many companies and add them to the site for everyone to share.All you need is to download the annual reports for the companies and armed with this template, you’ll be able to analyse the company’s financials and determine whether it’s a great, good, average or poor company. Once you have the annual reports, just fill in the blank spaces, and let the template calculate all the ratios and valuations for you.
Don’t be daunted by the size of the workbook, the number of sheets or the number of calculations. The minimum you need to do is fill in the blue highlighted cells on the ‘model’ worksheet for the last financial year. You don’t need to enter data right back to 1998 (I started building this model in 2008, so I wanted ten years of historical data).
Future ideas
- A PDF guide for the template containing definitions, examples etc.
- Templates for other types of companies such as banks, property companies, insurance, mining, and oils companies and any other type of company that doesn’t fit into the industrial template.
If you have any questions, or need help with the template, leave a comment on the blog, so other users can use it as a guide.
Click the link below to download the template.
Pingback: Skaffold.com – Yet another black box trading system « Value Analysis
1) awsome template , let me take few points from the excel ( along with the damodaran on valuation book ) for the next feature set that we are working
2) never knew that there are fans out there for Brian McNiven Valuation
3) am sure you would have checked out the following resources if not read them for what ever they are worth …
a) http://pages.stern.nyu.edu/~ekerschn/pdfs/readingsemk/EMK%20NYU%20S07%20Global%20Tech%20Strategy%20Special%20Edition%20Valuation.pdf
b)http://www.grahamanddoddsville.net/wordpress/Files/SecurityAnalysis/Valuation/valuation-multiples-primer.pdf
Revenue+Cost of Sales =Gross Profit ???
Expenses+Cost of Sales+Total Revenue=EBITDA???
These are the first couple of items that I noticed in the spreadsheet when I downloaded it. Perhaps it is just a problem because I downloaded to Open Office. Do these formulas make more sense in Excel?
Nice job with the spreadsheet. It looks like it is great for evaluating a company.
However, by the time I finished inputting the data on one company the Euro crisis will be solved. Any tips on finding consolidated data for companies instead of downloading and trying to interpret all of the financial reports?
Keep up the good work. Your website is very interesting.
Hi Matt,
Revenue + Cost of Sales does = Gross Profit. Cost of Sales is a negative value. Same for EBITDA, Expenses and Cost of Sales will usually be entered as negatives in the Income statement. Generally, Income Statements and Cash flow statements use negative values for expenses. The Balance sheet, on the other hand, uses positive values for liabilities, so most data on the B/S is shown as a positive value.
You can get some consolidated data from Yahoo finance, and maybe Reuters websites. There are also some tools on the web for downloading financial data that I have come across, but they’ve never been able to get data for Australian stocks, so I’ve never looked into them in great detail.
The other option is that I can create a company model for you if you want, for a small fee. It takes me about an hour and a half to setup and finish a model for a company. Let me know if you are interested.
Cheers
mike
Great stuff, I was just going to log on to write an apology for having questioned your spreadsheet when I realised that I needed to put in negative values. Really interesting and useful spreadsheet. I am going to try evaluating TGA first and maybe after doing one example I can get the input time close to yours. However I am no student of finance, just trying to make sure my portfolio doesn’t get wiped out and put cash to better use.
Thanks,
Matt
Hi Matt,
I do have an old model for TGA – its not on the latest spreadsheet, but does have 2011 financials updated. If I get a chance in the next day or so, I’ll post an updated model for you.
Cheers
Mike
Hi Mike,
I did the model for TGA. It has only been listed since 2006 so its not possible to get all of the average calculations working up to 10 years. I did do it up to 5 years and had to fiddle the offset function in the CAGR 5 year cells to offset by -4 columns. I assume if I have a 2007 report and all reports up to 2011 that this is 5 years of data. Therefore I only need to offset by 4 columns to get my 5 year CAGR. Minor point, but I needed to do this anyway to get all of the valuation methods to work.
Little tricky for me to interpret the cash flow section. Other payments row I used for the acquisition in 2011 and I did not include rental purchases in the cash flow as I assumed this was part of the normal operating cash flows.
I also noticed that your cash flow section on your free template download is a little different to your SPT model. (Just minor, but missing the net change in cash and the investing cash flows is in a different row.) Don’t believe it impacts any of the other results.
Very handy little spreadsheet that will take me a few more months to interpret all of the goodies.
Cheers,
Matt
Hi Matt,
Excellent work! and very glad that you like it.
The Compound annual growth (CAGR) formulas have been mostly setup for companies with at least 12 years of data, so you have to play with the formulas if the company has less than that. Partly the reason I added 5 and 10 year CAGR calculations.
With regards to 5 year CAGR from 2007 to 2011, the offset is 5 (function takes 1 away at the end, so it actually uses 4 periods). For TGA I use this function to get CAGR from 2007 to 2011 =((T4/P4)^(1/4))-1.
With regards to the cashflow section in the model, I usually don’t bother with the “Other payments” row. Its not included in any calculations, so doesn’t matter if its not filled in. In fact you can ignore
Proceeds from Borrowings and Repayment of Borrowings rows as well. They aren’t used in any calculations either. I only added them so I could see for some companies their history of borrowings.
My Spotless model was done on an newer model, but you are right, it doesn’t affect any other results.
I’ll update the template this week with my newer model (Net change in cash is the only new row in the new model, and moving the “Cash flow from investing activities” row)
Have you had a look at the MikeRatings sheet as well? I find that’s pretty handy as to the companies quality. For TGA I get 7.08 / 10, which is pretty good (as an example, Spotless scores 2.08 /10).
Cheers
mike
I ended up with Mike Rating of 8.33. I think I need to fiddle a bit with my inputs because it didn’t balance correctly. The -1 for the CAGR formula above is to convert the ratio to % isn’t it?
Anyway, will be busy in the new reporting period trying to get my records up to date. Hope it is a good one for you!
2012 is looking like a very strange year that could go either way great pain or a big muddle through. Hopefully I am wrong and it is a great gain!