Developing a Basic Financial Model - Part III: The Cash Flow Statement

Excel is probably the most popular spreadsheet in use today, and certainly a mainstay of investment banks, private equity firms and hedge funds. It offers a tremendous amount of flexibility to develop a wide array of financial computations, ranging from simple, static calculations to complex, dynamic analyses. In order to effectively develop financial models for use in valuation analyses or forecasting, it is important to understand how companies show their information.

For the sake of simplicity, let us assume that the only components on a company's balance sheet at December 31, 2007 is cash of $100, accounts receivable of $200, accounts payable of $100, and equity of $200. At the end of December 31, 2008, the company shows accounts receivable of $350, accounts payable of $150 and equity remained $200. What would the cash balance be? First, you look at the change in accounts receivable, and if that balance increases, that is a use of cash (and vice versa for a decrease in the balance). So, given the information above, it is clear that there was a use of cash of $150, meaning that the cash from the balance from the year prior would be decreased by that amount. Why does this happen?

A similar process occurs for payables, except in an opposite process. The accounts payable have increased by $50, so that increases the cash amount. Think of this as deferring a payment due today until some time in the future, and in keeping with the financing discussion above, a third party is providing financing for you, and thus, this becomes a source of cash. In this example, the $150 increase in accounts receivable offset against the $50 increase in accounts payable nets to a cash use of $100. With equity remaining the same, cash from the prior period would be reduced by $100. In short, cash balance would be zero at December 31, 2008.