One of the most useful financial models for a business is the cash flow model. A business can be trading profitably but still, fail due to a lack of cash. Sometimes the cash flow model is linked to an income statement and balance sheet to form an integrated financial model.
Cash flow is hard to model because the timings of cash outflows and inflows have a critical impact on the results. Our cash flow model needs to be flexible and should allow us to change the timings from our Input Datasheet and assess the impact. It should also allow us to change assumptions.
Let’s look at that now.

The Cashflow Model: Input Data and Timings
In a Cashflow Model, the timing of cash inflows and outflows is crucial, and it can be difficult to build a model in a way that allows the user to adjust the timing of cash inflows and outflows.
In the examples with this course, I use the HLOOKUP function with a table that gives a number to each month (or time period) and then shows the number of the month that payment will be received or made. I’m using HLOOKUP to ensure that the Model will work in older versions of Excel. If you have an Office365 subscription, then you can use the new XLOOKUP function instead. I would use XLOOKUP by preference but, as I say, I want to ensure that you can use this approach no matter what version of Excel you have.
This is what I do.

In the Input data sheet, I have user-definable assumptions that allow the user to set the period for credit sales and credit purchases. I’ve set it up as a drop-down list:


Then in the Input Data Sheet I have an area where cash sales and credit sales each month are input as well as credit purchases and other costs.
I have an area of this table which shows the month number and the number of the month in which the cash from credit sales is received and when cash is paid for credit purchases. It’s very simply – it just adds the credit period the user has set to the current month number. In a real life model you might want to hide this area out of sight.


If the user changes the credit sales receipt delay in the assumptions table then the month number of the credit sales received also changes. Similarly, for credit purchases.

In the Calculation Sheet, I use an HLOOKUP formula to pick the amount received or paid according to the month number displayed.
Thus, for cash received from credit sales in month two has the following formula: = HLOOKUP(D24,$C$12:$E$16,5, FALSE)
•The formula looks up cell D24 which is the month number of the current month.
•C12 to E16 is the array which contains the data to lookup; Row 12 showing the month number when the cash from the credit sale is received, and row 16 showing the amount received. The $ symbols fix the cell references to allow copying of the formula.
•To avoid “#N/A” errors, I have wrapped the HLOOKUP formula in an “IF” statement using the function ISNA
If I change the user-definable assumption for the number of months of credit, then the cash flow model changes accordingly.
This is a simple approach to the issue which avoids the need for macros. It could become quite cumbersome where there is a lot of data with different timing parameters. Therefore, when building a real model, I suggest using an “Intermediate Calculations” tab to contain these look-ups to avoid confusing users.

In the next lesson, I’ll use an example to illustrate the cash flow model further.

Lesson 11 Example Cashflow Model
Let’s return to the Trumpton Town Café for our example.
Suzi and Sam, owners of the Trumpton Town Café have asked you to help them prepare a simple cash flow model. They have opted for the option to open at 8.30 am and offer both breakfast and morning coffee promotions. They believe that the figures they have put together are reliable.
They have agreed with their catering supplier that, for the first 12 months of the extended operation, all catering supplies will be on 2 months credit. There are no additional capital costs.
The following assumptions have been agreed with Suzi and Sam:
Read list


Let’s look at the Model.

The Assumptions sheet contain all the assumptions for the Model which can be changed by the user.

The Input Datasheet shows all the inputs, including current cash sales and the additional cash sales for the new project. Credit sales, food costs, wages and so on.
You might want to study the Trumpton Café Model in the spreadsheet that accompanies this course.

Then we have the Calculation Sheet in blue showing the cash flow model. It uses the HLOOKUP function to slot the cash from credit sales and cash paid for credit purchases into the right month.
The Model extends to 16 months to ensure that all debtor and creditor cash flow s are covered.
Given the potential complexity of cash flow modelling, this Model remains relatively simple. Of course, applying different periods of credit to different elements of income and expenditure would increase the complexity, but Intermediate Calculation sheets could be used to build up sub-elements allowing the core calculation model to remain relatively simple.
For the Trumpton Town Café, I have also created an Income Statement and Balance Sheet
Both the income statement and balance sheet are driven by the cash flow statement, and I have created a section that shows the elements of the cash flow model and income statement that are used in the Balance sheet.
Examine the spreadsheet that comes with this course for more information.