Monthly Cash Flow Worksheet
This spreadsheet is for people who don't like the word budget but still want to get a grip on their finances. Basic personal finance is mostly about managing cash flow which means tracking and planning how money is entering and leaving your real and virtual pockets. This worksheet can be used for tracking your spending as well as creating a budget.
Monthly Cash Flowfor Excel
This worksheet was created for use in a book about personal finance and getting free from debt (a work in progress). It includes a fairly comprehensive set of expense categories and was designed to be easy to understand and simple to customize. It's mainly for people who don't already use Quicken (you can generate a report like this from Quicken very easily via the Reports menu).
Although there are some really cool things that can be done with Excel to make this worksheet more useful, I have intentionally kept this template simple. Quicken is my personal tool of choice when it comes to personal money management. If you want to use a free spreadsheet with the ability to enter transactions in account registers like you do in Quicken, you can try the Money Management Template.
Keep in mind that spreadsheets allow you to make more mistakes than software like Quicken, because spreadsheets use formulas and calculations that you may mess up by accident.
You can edit the labels for the various categories in column B as needed. You may want to add more specific categories or delete some you don't need. Do this by inserting or deleting entire rows. When inserting rows, make sure to copy the formulas in the Total and Average columns.
The formulas used for the column totals are set up to make it easy to add and delete rows without messing up the template, as long as you insert new rows within the range of rows referenced by the totals. You should still verify the formulas used for the Totals if you insert rows.
Average Monthly Expenses
The Average column divides the Total by the number of columns to give a monthly average. This can be particularly helpful if you have an annual, quarterly, or other periodic expense and want to figure out what monthly amount to set aside for that upcoming expense.
Creating a Monthly Budget that is the same each month requires that you use averages for variable expenses (fuel, food, etc.) and periodic expenses (insurance, tuition, subscriptions, etc.). A yearly cash flow analysis like this one can help you figure out what those averages are.
TIP: One of the benefits of using a spreadsheet for a report like this is that you can add comments to a cell to provide more information about a particular expense (Right-click > Insert Comment). For example, you might add a note to explain why you spent $200 on subscriptions in November. That may help you later if you are looking for ways to cut back your expenses.
Biweekly and Weekly Instead of Monthly
A cash flow analysis or budget can be defined over any period that you want. The dates at the top of this worksheet don't affect any of the other numbers in the worksheet, so you could change these dates to whatever you want them to be.
HINT: If you unhide the columns to the right of the worksheet, you'll find a cell that lets you change the date period to biweekly or weekly. If you change to biweekly periods, the Average will represent a biweekly average.
Removing and Adding Columns
If you wanted a 6-month cash flow, simply delete the last 6 columns before the Total column. Adding columns is also possible, but it will require more experience working with Excel.
If you wanted to add columns for weekly or biweekly cash flow reports, you could copy and insert copied columns, but you'd need to insert them BEFORE the current 12th column so that the Total and Average formulas remain correct (If you insert them immediately before the Total column, the Total and Average won't include your inserted columns). You'd also need to fix the formulas in the summary rows at the top of the worksheet (copy the formulas used for the 2nd column to the right).
The purpose of the beginning/ending cash balance section at the top of this spreadsheet is to show how much cushion you have in your spending account.
For the Beginning Balance, enter the sum of the amounts in your spending accounts on the chosen start date. A checking account, your wallet, and/or savings account might be considered a "spending account" if money leaves your possession from that account (like paying bills from a savings account or paying a cab driver with money from your purse).
What is NOT a spending account? Your Retirement Fund, Emergency Fund, College Fund, etc. Do NOT include the balance in any of the categories listed under "Allocations" in the Outflows section. Why? Because we are treating transfers to these types of savings as Outflows from cash accounts. If you included your Retirement Fund in the cash balance, then it wouldn't make sense to include "Retirement Fund" as an Outflow. Note again that this is meant to show how much cushion your spending account has.
Is a credit card account a spending account? If you are paying the balance off each month, then yes. You would monitor your credit card statement and allocate each transaction to specific expense categories. The transfer of money from your checking account to your credit card account would NOT show up in the cash flow statement in this case.
On the other hand, minimum payments on credit card balance(s) are included as "Credit Card Payments" in the Debt section of the outflows.
TIP: It is MUCH easier to keep savings separate from spending if you are using separate bank accounts for each.