I grew up in Africa, and I remember advising a business man who ran a successful retail business but continually experienced cash flow problems. When I probed him about how he managed his cash flow, he said that, when it came time to replenish the stocks in his retail store, he never seemed to have the cash in his bank to pay for the costs of restocking his store. After some deeper probing, it became clear that the source of his problem was that, when he saw all the cash coming in from sales, he felt rich and began spending the money without thinking that he would need some of the sales proceeds later.

This is a problem. Not only for a businessman in Africa, but for many who feel rich when they receive their paycheck and start spending it with little thought for projected cash flow requirements. That’ why, many years ago, I developed a solution.

It’s simple, really. In fact, it’s a simple spreadsheet. I use it to this day, both in my personal finances and for my business finances, and I have shown it to others who tell me they still use it and pass it to their friends and colleagues. I set a recurring weekly task in my Outlook calendar to review my finances. This entails updating Quicken to get an updated bank balance (the balance reflected by my bank may not reflect checks I have written but not yet been processed). I enter the updated bank balance in the spreadsheet (see more below) and then review the impact on my projected cash flows. I scour Quicken for my personal financial record-keeping and certainly find it useful, especially for recording all my expenses, setting up a budget and comparing actual expenditure vs. budgeted expenditure. But this is the historical part of my financial records. I never found its cash flow projection tool to be user friendly, and it’s not easy to adapt to my ongoing financial cash flows. That’s why I still rely on my simple spreadsheet system.

I have attached a sample of the spreadsheet tool I use. (The specific spreadsheet cells I mention relate to the illustration spreadsheet and may be different for the way you set up your spreadsheet) Please take note of the following within the spreadsheet:

1. I personally set up my spreadsheet for weekly income and expenditure, but you can set it up for bi-weekly or even monthly data. Set this information in line 1 by editing line 1. I recommend weekly data for tighter control, but it obviously requires more effort and attention.
2. There are formulas in lines 1 except for B2 (see below), 8, 43, 45 & 47. You can edit these formulas. Select the cell in which there is a formula and the formula will show in the formula bar of the spreadsheet.
3. Enter your opening bank balance in B2 and enter all your own income and expenditure. The formulas will take care of the rest. If you don’t see a line item for an expense you have simply insert a row and enter the name of your expense.
4. Each week, at the end of the week, I enter my updated bank balance in line 2 for the next week and I delete the column for the completed week, so that the current week is always in the first data column. Further, because I enter the updated bank balance, the projections are always based of the most recent details.
5. I keep a very close watch on row 2, because it shows me an ongoing projected net cash position. If this runs into the negative in the future, such as in column Z in the attached illustration, I know I will be running into cash flow problems and I look into what I can do to avoid this. In this example, I could reduce my vacation savings to resolve the shortfall toward the end of the year.
6. I keep on using the same template year after year. You can use it to run “what-if” scenarios, such as, what impact would it have if I bought a new car and added a monthly payment.

Cash flow management can be such a stressful process. I have found that using this tool gives me much-needed comfort by ensuring me that, if I stick to my projected costs, I will have less possibility of running into cash flow problems.

Feel free to view a sample spreadsheet and download a blank version of the tool and use it to bring greater peace of mind in managing your personal finances. I hope it helps you as much as it has helped me. And don’t forget to factor in some vacation savings – everyone needs a break now and then.