Around two weeks ago I decided that I am going to build a heavily automated discounted cash flow stock valuation model using Microsoft Excel. Why you ask? Simply because my current process of evaluating stocks is very tedious and time consuming.

Today I use Excel to value stocks before making an investment decision. However the entire process of valuing a single stock can take hours. I manually copy and paste financial statement data into Excel from the companys financial statements. This can be a pain in the $%# sometimes when the formatting doesnt work properly. From there I have to manipulate the financial statement data to a point where I can start to perform some calculations. Since this process is so tedious I fear that I will make mistakes or potentially cut my analysis short because it takes so long to complete leading to suboptimal investment decisions.

I feel there must be a better way do this. There must be a way to automate the investment analysis process and make it more efficient and reliable. And thats what I have set out to do.

Model Objectives

• Substantially reduce the time it takes to value a stock
• Improve the accuracy of my stock valuations

Model Requirements

• The model must be build based on a discounted cash flow valuation analysis.
• The model must be able to value the majority of TSX, NYSE & NASDAQ listed companies.
• The model must be able to automatically retrieve financial statement data from the internet based on a ticker symbol as input. This includes annually & quarterly income statements, balance sheets and cash flow statements.
• The model must also automatically retrieve price information regarding the stock including the most recent available market price.
• The model must be able to automatically calculate certain financial ratios and display trends in a summary report or graph.

Why use a Discounted Cash Flow Model?

In my opinion, the discounted cash flow analysis framework is the most robust and accurate method when it comes to valuing a stock and determining its intrinsic value. This type of analysis is highly coveted by Benjamin Graham and Warren Buffet. It does take a lot of work to do a thorough DCF analysis and there is a lot of room for error, but in the end I feel the effort pays off through better investment decisions.

Why build the model in Excel?

You might be wondering why I choose Excel to build my model? Other alternatives include Google Docs or web based programming languages (HTML, PHP, JS, ASP etc.). Heres why I choose Excel.

• Able to execute complex calculations through formulas and existing functions
• Able to import data from the internet into a pre-formatted table
• Easy Graphs and charts
• Back end programming language in VBA to automate particular processes
• Software has a large user base and is widely distributed

Will You Share?

Yes, I intend to provide a free download of my model to readers of this blog. Ive been working on the model for over two weeks now and am very close to an initial release. Ill have something to share soon. Stay tuned.