I made a cash flow statement template available for download in a previous post and it’s been a popular download. Recently, I realized the template is in an older file format, so I created templates in multiple file formats and linked to them below.
Similarly, I previously posted a net worth tracking template in an older file format, so I updated the template and linked to multiple formats of the net worth tracking spreadsheet.
I hope these prove as helpful for you as they have been for me!
I previously posted an article regarding cash flow planning and noticed that a lot of the visitors coming to this site are viewing this article. I decided to create a more thorough walk through of creating your own cash flow planning Microsoft Excel spreadsheet. Having learned to cash flow plan through Dave Ramsey’s Financial Peace University (FPU), I use my cash flow plan on a weekly basis, but I remember that I had plenty of questions when I first learned the concept.
Of course, if you’d prefer to just download my already-prepared spreadsheet rather than create your own, you can download it here: Cash Flow Plan
Creating a Cash Flow Plan Spreadsheet
Open Microsoft Excel. I’m using Microsoft Excel 2010, but any version of Excel will suffice, as we’re not using any of the advanced features.
In cell G1, enter your monthly net income. In my spreadsheet, I’ve entered $2,878.59, which equates roughly to an annual salary of $42,979.61, the average wage of a U.S. worker in 2011 according to the Social Security Administration.
In cell A2, type Category. This column will be filled with categories where you will allocate money (for example, savings, housing, etc.).
In cell B2, type Description. This column will be filled with the descriptions of items where you plan to spend your money (for example, rent, renter’s insurance, mortgage, etc.).
In cell C2, type Sub Total. This column will list the amount spend per each item listed in the description column.
In cell D2, type Total. This column will include the total amount allocated for a category.
In cell E2, type % Net. This column will contain calculations indicating how much of your net pay will be allocated per category.
In cell F2, type Actually Spent. This column will indicate how much you actually spent per item and will allow you to see where you underspend and overspend. Your spreadsheet should now look like this:
The next step is to consider major categories that your spending falls into. Examples might be charity, saving, housing, etc. Enter your first category in cell A3. Enter your next category in cell A6. Then enter all other categories, giving yourself a couple of cells of cushion for each. The spacing isn’t critical here, as you can simply cut and paste later in order to give yourself more room. Here is what my spreadsheet now looks like:
You’ll now want to fill in each spending item in column B. For example, under the Charity category, you may list Church, Catholic Radio, Franciscan Friars of the Renewal, or any of your favorite charities. Go through each category with the intention that all of your spending is classified (i.e., every penny you spend should fall under a category and sub-category). Here is what my spreadsheet now looks like:
Your next task is to determine how much to allocate for each item. Certain items are easy to place a number on, like rent, mortgage, and your cable bill. These bills are the same each month. Other items will be more difficult to come up with, as they fluctuate on a monthly basis. After tracking your spending for a few months, you’ll develop a better idea of how much you spend on these items. You can then update your spreadsheet accordingly. Here is my updated spreadsheet:
In cell D57, type the following: =SUM(C4:C54) This will provide a sum of all of the values in column C and will tell you how much you’ve allocated. This should match cell G1.
(Optional) I consider this step optional, but I like to do this so that I can see what proportion of my money I spend on each category. In cell E8, type =(SUM(C4:C7)/G1) This will tell you what percentage of your net monthly pay you have allocated toward the Charity category. If you aim at a 10% figure for tithing, this tells you that you need to allocate more for charity. Do this for each category of spending. My updated spreadsheet:
Next, we’ll want to enter formulas for calculating how much you *actually* spent. In cell F8, type =SUM(F4:F7) At the end of the month, after you go line by line in your cash flow plan and enter how much you have to your diocese, your parish, and so on, cell F8 will tell you how much you actually gave to charity. Do this for each category in your spreadsheet.
Finally, add up how much you actually spent for the entire month. In cell F56, type =SUM(F8,F14,F18,F24,F28,F36,F40,F43,F49,F53) Note that the cells in this formula are the summations of each category. If you added an item or took an item away, you’ll need to adjust your formula accordingly. My finished cash flow plan looks like this:
Your cash flow plan will be a work in progress, especially for the first few months that you practice using it. You’ll find categories to add, items to add, and things to rename. Good luck!
I took Dave Ramsey’s Financial Peace University (FPU) course last fall and learned quite a bit. I entered the class not expecting to learn too much but came out surprised with the many fresh ideas Dave gave me.
One of my favorite lessons focused on cash flow planning. Cash flow planning allows you to plan where your income (in flow) is spent (out flow). Rather than following a “spend as you go” strategy, cash flow planning provides you a plan for the month ahead so you can consciously decide where your money will be spent. This plan gives you visibility into what you truly *need*, what you *want*, and where you are wasting money.
Creating and Following a Cash Flow Plan
To create a cash flow plan, you can use plain old pen and paper or, what I choose to do, use a spreadsheet. A spreadsheet lets me make easy comparisons month to month, use formulas to avoid math errors, and is just plain convenient. Start by listing your take home income at the top of the spreadsheet. Then list categories (like Saving and Philanthropy) and, within these categories, more specific entries (like House Fund, Roth IRA, Catholic Charities, Church):
Allocate every penny of your income to a category. This will result in the total spent amount equaling your take home pay. After following the plan for a month or two, you’ll see where you should plan to spend more or spend less. Initially, I found following the plan very challenging but have grown to like the plan, especially since my most recent vacation was fully funded :) Here is a link to my Cash Flow Plan in Excel 2010 format.
Pay Yourself More and the Credit Card Companies Less
The most significant benefit cash flow planning has brought to my financial situation is that of facilitating paying myself first. Paying yourself first is the practice of taking money out of your paycheck as soon as you receive it and placing the money in savings and/or investing the money. Ideally, you should set up an automated transfer where a given amount is transferred to your savings or brokerage accounts at the beginning of each month. Having set up an automated transfer, I fill out my savings column without fail and avoid the “I forgot to make the transfer at the start of the month and now I don’t have any money left” excuse. Paying yourself first takes money from a location where it’s easily spendable and puts it in a savings or brokerage account where impulse spending cannot easily reach. Also, once you determine a suitable amount to put away, you’ll find that you won’t miss the money.
Another benefit of cash flow planning I’ve experienced is that of saving up for big ticket items (think vacations, car insurance payments due twice a year, etc.) in advance rather than putting them on a credit card and paying off the balance later. Prior to attending FPU, I was in a cycle of putting large expenses on my credit card, paying the balance off over the course of a few months, then finding myself at square one when another large expense came up. Cash flow planning allows you to save small amounts each month for upcoming expenses so you’re not put in the position of having to draw from savings or rely on credit cards when large expenses arise. By eliminating credit card payments, you can then flow this cash into more worthwhile pursuits (saving, investing, vacation fund, etc.).
Dave Ramsey’s web site has a great monthly cash flow plan and even includes Dave’s recommendations regarding how much of your pay check you should allocate to various categories: http://www.daveramsey.com/media/pdf/fpu_monthly_cash_flow_plan_forms.pdf. If you don’t already follow a monthly cash flow plan, I highly recommend creating one and being disciplined about following it. You’ll minimize unplanned spending and put your money to better use.