Month: April 2013

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

1. 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.
2. 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.
3. In cell A2, type Category. This column will be filled with categories where you will allocate money (for example, savings, housing, etc.).
4. 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.).
5. In cell C2, type Sub Total.  This column will list the amount spend per each item listed in the description column.
6. In cell D2, type Total.  This column will include the total amount allocated for a category.
7. In cell E2, type % Net.  This column will contain calculations indicating how much of your net pay will be allocated per category.
8. 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:
9. 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:
10. 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:
11. 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:
12. 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.
13. (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:
14. 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.
15. 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!