About 10 years ago I had a problem. I had tracked my finances with Quicken for many years and I had a good understanding of my financial situation. My balance sheet was always an accurate representation of our net worth, and the income statements always told me how well we were doing financially. But my wife and I always seemed to be short of cash, even though our financial statements showed our income and our net worth steadily growing.
I knew what the problem was. My retirement plan was a huge part of our overall assets and on-going income, but the increases in the value of the retirement plan did nothing to assist us with our day-to-day finances because it was for the future and wasn’t to be used now. The retirement plan was hugely important to us so I couldn’t remove it from my Quicken financial reporting, but I needed to find a way to remove its influence from analyzing our day-to-day finances.
After much thought and even more trial and error, the Cash Flow Analyzer was what I created to solve the problem. It has worked like a charm ever since. The central idea behind it was to set it up to only include the money we lived on and the money we spent now, which is about 95% of the transactions and income and expense categories in my Quicken books.
In addition I knew that I needed to get control of our spending so we spent our money the way we wanted to and so our day-to-day spending would not exceed what I was bringing in day-to-day. The way to do this was to identify the most important spending categories to us, determine how much we wanted to spend in each category and then compare our actual spending to this budget in a manner that would allow me to easily keep track of it all every day if I wanted (remember, I’m an accountant).
I have tweaked it from time to time, but the essence of it is as you see it now, and if you use it properly it will allow you to stay totally on top of your day-to-day cash flow without much effort.
What follows now is a Tutorial Example for you to use in understanding and/or setting up the Cash Flow Analyzer for yourself. It shows you where my Cash Flow was at, at the end of November, 2010, so you can make and see the same kinds of analysis that I would have done at the time. But more than that you can see how I created my budget and how the numbers get transferred from the monthly Quicken income statement to the proper line on the Cash Flow Analyzer.
I hope you find it useful, and it encourages you to begin taking better control of your own finances if you are not already doing so.
This example contains the following three spreadsheets:
2) The November 2010 Income Statement (from Quicken)
The 2010 Budget
- The numbers for 2008 and 2009 are the actual income and expense for those years
- I used them as a guide for determining what to budget for 2010.
- The Taxes budget for 2010 came from the Income Tax Analysis spreadsheet (which isn’t shown)
- The Total Cash In Monthly Budget ($8,833) and each Cash Out Budget number can be found on the Analyzer at the bottom line labeled “Monthly Budget”
The November 2010 Income Statement from Quicken
- This report is coming straight out of Quicken.
- I have included every income and expense account in the report
- The Total Income number and each Expense number are input into the Analyzer on the November line, with the following exceptions:
- Expense Reimbursement and Reimbursable Expense – We often pay for things for relatives for which we are reimbursed. I never include these amounts in the Analyzer.
- Interest Expense – Because I am tracking cash flow as opposed to income and expense in the Analyzer I need to also add in the debt reduction portions of all payments on loans to the interest expense that I am tracking in Quicken. This is why the number plugged into the Analyzer for Home Interest, Principal and Taxes is $2,787 not $1,564.50. The extra $1,222.50 represents paying down our 1st mortgage for the month and the entire debt service paid on a Retirement Plan loan.
- Investment Expense – This represents the costs associated with the Retirement Plan. Other than the loan debt service we paid into the Retirement Plan I do not include any Retirement Plan transactions in the Analyzer. The Retirement Plan is not something I am depending on right now to live on so it doesn’t affect the cash flow that we are living on.
The 2010 Cash Flow Analyzer
- This is the actual report from 2010 which includes three pages.
- Note the Months Completed is set at 11. This gives you the proper comparison of your MONTHLY SPENDING AVERAGE vs. your Monthly Budget.
- Before I started inputting December data I changed the Months Completed to 12.
- At the bottom you can see the FINAL DOLLARS LEFT in each of the categories along with the Total YTD dollars in and out.
- Also at the bottom you can see the Projected $ at year-end based on the spending you have done so far for the year.
- Finally at the far right you can see the Net Cash In or Out for each month, plus the highlighted number in bold at the bottom right shows you how well you are doing overall for the year.
The following is an estimate of how much time this takes me:
The initial setting up of Quicken can take as long as a day. Afterwards it takes me 5 to 15 minutes a day to enter data into it, or an hour or two once a week.
Inputting the data from Quicken to the Analyzer typically takes 5 to 10 minutes each time I do it. I like to check it throughout the month, but you could just input the monthly totals into it once you have completed your month in Quicken.
Once a year, typically in late December or early January I go through the Budget process. Initially it took me about 8 to 10 hours to do this, but in recent years as I have understood my cash flows much better it takes only 3 to 5 hours.