I've just finished designing and testing the stock analysis spreadsheet I had in my mind for some time now. I wanted a tool to support investing decisions with high-quality, relevant and on-time financial data. I also wanted to have real-time quotes so I could evaluate the attractiveness of an investment in a fast, systematic way. Maybe it could also be possible to perform additional calculations including non-financial leading indicators and a portfolio tracker (but I left these to a newer version).

All this for a large list of international stocks, using multiple currencies and no plugins. Just an Excel table and a financial Dashboard. The result is quite satisfying and I think this will be a fantastic investing decision supporter, so I decided to test it using the most popular companies of our time: Apple.

One of the things I always had to explain to non-financial people when analysing a business is that profit doesn't necessarily mean cash. You can have a profitable business and run out of cash (you can actually go bankrupt because of this) or you can have a loss-making company and lots of cash. You just need to know the basics of accounting to understand this. Following the cash is the single most important financial analysis in any company, and you can do it using a cash flow statement or a calculation using a profit and loss and two consecutive balance sheets. Here's how...

First of all, I would like to give you a short explanation of name ranges. Every cell in excel has its own name, which contains a letter and a number. But you can also give your own name to any cell, or range of cells. In some situations, this makes things easier and faster. The great thing about it is that you can make the whole process totally dynamic, and that can be very useful to write formulas or to use a list of data. Dynamic name ranges are also nice as input ranges for pivot tables as they make the source data changeable. The best way to create dynamic name ranges is to use a combination of OFFSET and COUNTA functions. Let's see how to do this.

Excel offers us very good possibilities for gathering information from the Web. You can use a Web query to create refreshable data that is stored on the Internet. Then you can analyse the data by using the tools and features in Excel. For example, you can create a Web query to import stock market information to excel, and update it with the latest data on the Web site. One way to do this is to use one of the default installed Web queries, the other is to create your own Web query. In this article, I will show you both.

We continue with the example from the previous article. This time, we will use the Offset function to simulate different scenarios in a business model. The results will be the same, of course, but you can learn how handy this function can be.