Managing Expenses and Cash Flow – Part 2: Spreadsheet


Managing Expenses and Cash Flow 10 ActualChartBy using the Cash Flow method and the spreadsheet included here, you can simplify the tracking of your expenses with a few minutes of work each month. You can also plan your income and expenses by month, prioritize your expenses, show your cash flow to ensure you have enough money when big bills are due and compare the actuals to the plan. While it is recommended that you buy everything using plastic or mobile payments as indicated in Part 1, you can also enter any Transactions that you want.

This is Part 2 of a four-part post.

  • Part 1: Why – The reasons for managing your expenses and why it is difficult to do so are given.
  • Part 2: Spreadsheet – An explanation of how to use the Cash Flow spreadsheet is provided. You may download it by clicking on Download Spreadsheet.
  • Part 3: FinanceBase – Only so much can be done with a spreadsheet. A more robust and full-featured application as an alternative to the spreadsheet is discussed.
  • Part 4: Design – A summary of the design of some of the features of the spreadsheet and the posts is presented.

Summary Of Why You Should Use Cash Flow

The Cash Flow approach is different from “budgeting” because the later now indicates that it is a difficult process and requires rigid planning and compromises in how you live whereas the former provides more flexibility, takes less time and has proven to be less stressful.

The approach is to show your income less expenses over a year on a chart to see where and when you have an excess or a deficit. This is called a cash flow and is used to highlight when you need extra money for major expenses and if your normal spending is as expected.

The key to using the cash flow approach is that you can make trade-offs between expense categories when spending on one category exceeds your plan. You can also only use categories that make sense to you and provide you with as much detail as you want to track. If you overspend one month in one category from the plan, you can adjust the plan and/or adjust what you spend on other categories the next few months. Usually, underspending on some categories is accompanied with overspending on one or two. The stress level drops significantly when you are not locked-in to a plan that no longer is reasonable for the circumstances.

Planning and tracking your expenses is simplified if you use the spreadsheet provided here and buy everything on plastic. You then do not have to keep track of every purchase yourself as the financial institution does it for you. Once you have access to this data it can be copied and pasted into the spreadsheet to generate the actual cash flow and compare it to the plan. Transactions can also be added manually. Adjustments can then easily made so that the overall financial objectives for the year are meet.

You should be able to load the transactions from your accounts and update the spreadsheet in less than 15 minutes. Do this once a month, or more often if you want to keep on top of impulse spending and manage your “vices”. This is much less than the time required by many other approaches that require you total your receipts by category and enter them for each month. You also get the fine control needed with the spreadsheet.

Overview of the Spreadsheet

Please download the Excel spreadsheet by clicking on

Download Managing Expenses and Cash Flow Spreadsheet.

It has no data in it so that you can import your own transactions and enter the plan as you read the instructions that follow. If you are not familiar with the layout of an Excel spreadsheet, you can find many references by searching on the internet or refer to (1) or (2) in the resources section below.

The spreadsheet has 3 sheets that you can view by clicking on the Sheet Tabs at the bottom of the Excel spreadsheet: Plan & Actual, Accounts and Transactions. The spreadsheet has a lot of tables and charts on it and if you want more detail, you can make use of them, if you wish.

However, you only need to enter data into

  • a) the Income and Detail Expenses Plan area of the Plan & Actual sheet and
  • b) paste data into the Transaction sheet.

There are 3 charts that you should use to monitor how you are doing during the year, all the rest provide more detail, if you want. They are all on the Plan & Actual sheet:

  • 1) Cumulative Cash Flow by Month in the top-left,
  • 2) Group and Category Expenses to a specified month starting at Column AP, and
  • 3) Income, Expenses and Cash Flow by Month starting at column BM.

Printing – You must print each sheet separately. The Print Areas and Page Breaks have been set so that the Plan & Actual sheet will print on 4 pages, the Accounts sheet on 1 page and the Transactions on a minimum of 4 pages for the first 200 transactions.

Saving – After you have filled in the spreadsheet, you should Save As with you own name and the year.

Archive Versions – You can add the current date when you Save As, if you want to have archived versions.

Backups – If you do not already have an automatic backup (such as Time Capsule for Mac OS X), then you should copy your spreadsheet regularly to a backup disk or USB drive. Having to recreate all of your transactions would be difficult and time consuming.

Next Year – When you are ready to start a new spreadsheet for next  year, all you have to do is modify the Plan values, clear the Account values and delete all of the Transactions. If you want to use last year’s actuals as the plan, before you delete the Transactions, go to the Actual area on the Plan & Actual sheet, select the Detail Expenses amounts and copy them. Then scroll to the Plan area, select the first cell in the Plan Detail expenses and, using the Past Special item on the Edit menu, select the Values radio button and click on the OK button. This will paste only the values and not the formulas. If you want, do the same for the Actual Income values and Paste Special the Values onto the Plan income. Then Save As the spreadsheet with the new year as part of the name.

Showing Screenshots in Full Size – Depending on your screen size, the screenshots may be too small to see. You can view them in a larger size by using the “contextual menu” to open a new Window with the screenshot. Hover over the screenshot and right-click if you are using Windows or control-click if you are using a Mac.

How Much Time Does It Take To Start?

I filled the spreadsheet for Jan, Feb and Mar of 2015 using transactions from a chequing account and 4 charge card accounts to see how long it took. It may take you a little longer, but it is really a simple task.

In all, there were almost 200 transactions and it required about 40 minutes as follows: It took about 20 minutes to access, then copy and paste the data from the accounts into the Transactions page of the spreadsheet. As I pasted each account’s monthly transactions, I bulk entered the Month. It took another 10 minutes to assign each transaction to a category using a shortcut: 1) I sorted the transactions by Description so that they were in groups. 2) I then bulk entered the Category for each group of Transactions with the same Description. Adding the plan required another 10 minutes as I copied all of the Actuals for the 3 months and pasted the values only into the plan area. I then either copied a category’s values to the end of the year or added some for those that happened a few times a year .

Plan & Actual Sheet

The Plan & Actual sheet is shown when the spreadsheet opens with the Plan area showing. It has been split into horizontal and vertical panes and then frozen so the Cumulative Cash Flow By Month chart in the top left corner is always visible no matter which way you scroll.Managing Expenses and Cash Flow 01 Cum

The example used here has a plan for the complete year and actuals from January to April. March has $2,300 more expenses than income so the cumulative cash flow goes negative. Money from another source than the cash flow is required. There is also another shortfall in Nov, but there should be enough cash to cover it, if the excess cash flow is kept until then. Notice that the Actual line is above the plan because actual expenses are less than the plan.

Scroll to the right to see the other 3 areas: Monthly Plan and Actual, Year Totals and Track Expenses and Actual. Scroll down to see more of Expenses.

Enter data only into the outlined cells. Most of the other cells have formulas and any change to them will cause lots of problems with the totals and charts. Because you are able to insert and delete rows to change the Categories and Groups that you use, the spreadsheet cannot be locked, so please be careful. See the Making Changes section later on which describes the proper way to make any changes.

There are many charts included on the sheet that change automatically each time a change is made to any value in the spreadsheet.

Plan Area

Across the top in the Plan area are 3 charts: Plan Income and Expenses (which are updated whenever you make a change to any amount in the Plan area), Income by Month (Plan and Actual are shown to let you see when how the actuals are doing against the plan), and Detail Expenses by Month (again to show how the actuals are doing against the plan) charts. The last 2 charts will only show the plan initially until some actuals have been included on the Transactions sheet.Managing Expenses and Cash Flow 02 PlanIncomeExp

For the example, the first chart shows that the expenses exceed income in Mar and Nov. These must be planned for to ensure there is enough cash around to pay for the shortfall.Managing Expenses and Cash Flow 03 InAndExp

The left chart show that the Actual Income for Jan to Apr are as expected but the right chart shows that Expenses are a little less than the plan.

1. Start by entering the Year at row 3.

2. Continue by entering the Income Categories that you want at row 18. The defaults are Take Home 1, Take Home 2 and Other Income so that a couple does not have to make any changes. Just replace the generic names with your own. Go to the Making Changes section below on deleting and adding new rows. To the right starting in column F, you can enter the expected income for each income category for each month. The Yearly Plan amount in column D will be updated each time you enter a new monthly amount as will the totals on row 22. See below for bulk entering of values.Managing Expenses and Cash Flow 04 PlanIncomeTable

Only the first four months of the example are shown. There is an increase in Apr for the second income because payday is every second week, so there are 3 paydays in Apr of $900 each.

Where are the Deductions? They are not included in the Detail Expenses on the spreadsheet as a default. If you want to include the total income instead of the take home pay, make sure that you include the deductions in the Detail Expenses section. You should also add them to a separate Group, such as Deductions.

3. Examine the Detail Expenses categories starting at row 28. Make any changes to both the Category or Group to suit your needs, or leave until you have imported some Transactions and have a feel for what categories are important to you. Enter any monthly values you want starting at column F for each category you want. Inserting and deleting rows and bulk entering of values are dealt with in the Making Changes section below. The Yearly Plan and Left columns will be updated automatically. The Left column starts with the total from the Yearly Plan in the income section and is reduced each time a Detailed Expense is entered into a month. It will go negative and become red when there is not enough income for the expense. You can reorder the rows to prioritize the Categories as indicated below.Managing Expenses and Cash Flow 05 PlanExpensesTable

For the example, all Expense Categories are shown so that the Yearly Plan and Left amounts can be completely shown. The final Left amount equals the Dec value on the Cumulative Cash Flow chart.

Which Categories should be included? The number of Detail Expense categories has been deliberately kept to less than 20 so as not to make the task of assigning Transactions too long. However, you can add or delete any that you want to customize the spreadsheet to your liking. However, to decide on what Categories you should have, consider the following:

  • – If a Category has more than 10% of the total and it consists of many different expenses, it should be subdivided.
  • – If it is one of your known frivolous expenses that you want to track and control, it should have its own row.
  • – If you want finer detail on specific expenses, make a separate row. For example, a category for Vacation is included with a group called Extras. Also, if you have to clear off credit debt that you had before, create a category called Credit Repayment and use the Credit group.
  • – There should be at least a separate Group and possibly separate categories for credit charges (bank fees, interest, outstanding balance payments) if you believe that you are paying too much for them or you want to ensure that they are visible to you.

Use a Separate Category for Major Expenses – If you have a major expense that is discretionary or one that can be incurred at your discretion, you should create a new category for it. You can then move the expense from one month to another depending on when you have excess funds. It will show on the Cash Flow chart. Some examples are a vacation where you can decide which month to make the initial payment. Another is major house purchases such a fridge, stove or repairs. Again, you can adjust the month to one where you have a positive cash flow.

4. Scroll down to the Group Expenses and change the Group names to match those of the Detail Expenses, if you made a change there. Do not enter any values into the month columns as they are automatically calculated from the Detail Expenses area. The total of the Year Plan column should match that of the Detail Expenses or else you are missing a Group. You can delete or add Groups as indicated below.Managing Expenses and Cash Flow 06 PlanGroup

Notice that the chart is for the full year for the Plan and only the first four months for the Actuals. While Housing is a higher percent than expected, most of the others are lower, thus distorting the percentages somewhat.

Why is there a Group? Using the Group helps to collect like expenses together to make it easier to see how much you are spending in major areas. You can then compare your spending with recommendations you find on the internet or with national averages. There are three good resources given below (3)(4)(5) for the USA and Canada. There are 5 graphs on the spreadsheet that you can use for these comparisons. The one to the left of the Group Expenses gives the expenses as a percent for the year for the Plan and the year-to-date (YTD) for the Actual. The other 4 charts give the totals as a dollar value start at column T.

Cash Flow – At the bottom of the Plan area are 2 rows that show the cash flow monthly and as a cumulative amount. It is the cumulative cash flow row that is plotted in the chart in the upper-left of the window.Managing Expenses and Cash Flow 07 PlanFlowTable

For the example, the excess expenses for the 2 months are very obvious as they are a negative red number.

Month Plan and Actual Area

If you scroll to the right to column S, you will see the monthly breakdown of the Plan and Actual for the Group and each Category. The default months are Jan, Feb and Mar. To change to other months, at row 24, column Y, enter the month you want.

This area provides the detail comparison for 3 consecutive months so that you can see what was planned and what was actually spent. The results are shown as charts and tables. The rows match those of the Categories for the Plan area. Any reordering of the Categories to prioritize them will automatically be shown in this area. Any changes to the name will be shown on the charts.

Depending on your screen size, not all of the three months may show. Just scroll to the right to see any that are not showing.

It is in this area where the difference between the plan and actual are easy to see. Depending on what works best for you, the Group chart and the Category chart indicates the relative differences while the table shows the exact different.Managing Expenses and Cash Flow 08 Month

For the example, the Plan-Actual column shows that there have been savings in 7 categories and overspending in 4 for a total saving of $244.

Year Totals and Track Expenses Area

Scroll to the right to column AO and you will see the Yearly breakdown of the Plan and Actuals to a specified month for the Group and each Category. To change to any month, at row 24, column AU, enter the month you want. You may need to adjust other Categories for the rest of the year. The charts and table are the same as shown above for a month, but are from Jan to the month specified.

I consider this to be one of the most important views of how you are doing to manage your expenses. If checked at the end of each month and the latest month is entered, it will let you see how each category is doing against the plan. Make any changes to the plan for future months or adjust the plan use the actual values. This is easy to do as you only need to go to the Plan area (described below), select the months and categories desired, copy them and paste the values (not the equations) over the same plan months/categories.

Starting at row AV is a set of 4 charts that you can use to examine in detail any 4 categories. The charts show the Cumulative Detail Expenses by Month for the Plan and Actual-to-date. These charts give you a good perspective of how your actuals are doing.

You can change which Categories are charted by finding the “Enter Detail Expense Category:” text and putting the cursor into the cell next to it. Then enter = and select the Category from column B that you want. Then press Tab. The chart will change to the new Category.Managing Expenses and Cash Flow 09 CumExp

For the example, the Car Gas expenses are less than planned to April.

Actuals Area

Scroll to the right to column BL and you will see the chart for the Income, Expenses and Cash Flow for each month. It includes Actual Income and Actual Expenses and the Plan values plus the Cumulative Cash Flow. This put almost all of the key charts into one chart. This has been done because most of the other charts are quite small in size, whereas this one extends the complete width of the table and hence provides a good view of how the Actuals compare to the Plan.Managing Expenses and Cash Flow 10 ActualChart

The Income, Detail Expenses and Group Expenses tables below the chart are calculated from the Transactions sheet. Do not change anything is this area.Managing Expenses and Cash Flow 11 ActualTables

 

At the bottom of the page are the Monthly Actual and Cumulative Actual rows. The later is used in the Chart on the upper-left of the page. For comparison, the Plan values are also shown.Managing Expenses and Cash Flow 12 ActualFlowTable

For the example, the Actual Expenses have been set so that each month they are less than the Plan Expenses so that the Actual Cash Flow will always be better than the Plan Cash Flow. This is shown on the chart as well as on the table above. While it may be a bit hard to read in the above screenshot, by Apr the Actual Cumulative Cash flow is $669 while the Plan is $60 for a saving of $609.

Making Changes

It is easy to customize the spreadsheet to what works for you, but you must follow a few rules so that the equations are not affected.

Changing the name of a Category or Group – Just select the cell and enter the new text.

Bulk Entry of Values – Entering values into each month for the Plan for Income and Detail Expenses can be tedious and prone to error if the numbers are the same for each month. 1) One method to bulk enter numbers is to enter one number, tab to the next cell, select the entered cell, copy it and then paste into all of the other months. 2) Another is to enter one number, tab to another cell, select the first cell again and drag the handle that is in the lower right corner of the cell to the other months. 3) A third method is to select all the cells that are to have the same value, enter the value and then press enter while holding down the Command key for Mac OS or Cntl key for Windows.

Deleting a Row – Only delete a row that has text for Income, Category or Group. Do not delete any of the headers or those with a gray fill as they are used for the charts. 1) To delete, select the complete row by clicking on the row number along the left. This will highlight the entire row. 2) Next click on the Edit menu and select Delete. The row will disappear and the totals and charts will change.

Inserting a Row – 1) Select any row that has text for Income, Category or Group. This will highlight the entire row. Do not select any of the headers or those with a gray fill as they do not have the equations needed. 2) Then copy the selected row by clicking on the Edit menu and selecting Copy. The row will be highlighted and have a flashing dotted line around it. 3) Now, select a row where you want the new row to be inserted and click on the Insert menu and select Copied Cells. A copy of the flashing dotted line row will be pasted above the row you selected. This will ensure that the equations needed in all the areas are not lost. 4) Now all you have to do is change the text of the Income, Category or Group to ensure that you do not have a duplicate. Also, change any of the values for the months as you probably do not want the ones that were there for the original row.

Moving a Row – This is most useful when the priority of a Detail Expense’s Category is to be raised or lowered so that it is not negative in the Left column, but it can be done for Income and Group Expenses as well. 1) Select the row to be moved. This will highlight the entire row. 2) Then cut the selected row by clicking on the Edit menu and selecting Cut. The row will be highlighted and have a flashing dotted line around it. 3) Now, select a row where you want the row to be moved to and click on the Insert menu and select Cut Cells. The row will be deleted from its original location and will appear above the selected row. This preserves the equations used in other areas.

Transactions Sheet

The Transactions sheet is shown by clicking on the Transactions tab at the bottom of the page. The top pane is frozen so that only the bottom scrolls. The sheet has the Cumulative Cash Flow By Month in the top pane that is on the Plan & Actuals sheet so that you can see the changes as they are made. There are 7 columns: Date, (no title), Description, Out, In, Month and Category.

To enter data into this sheet, you need to login to your financial institution and list all of the transactions for the month. Copy all of the transactions and paste them into the sheet starting at Row 15, Column A.

I have tried a number of charge card accounts and found that all of them are tab delimited and put the expenditures into the Out column.Managing Expenses and Cash Flow 13 Transactions

For savings and chequing accounts, the Debits fall into the Out column and the Credits fall into the In column. If they do not, just select them and drag them to the correct column. The Balance falls into the Month column so select and clear them using Edit > Clear > Contents. For some charge cards, there are two dates.

Do not worry about what is supplied for the Date or Description as you will only use it to help identify the Category and they are not used anywhere else on the spreadsheet.

If the data is not tab delimited, you can still get it into the columns by using Text to Columns on the Data menu. Follow the instructions on the dialog box that opens.

Once you have the account transactions on the sheet, bulk enter the Month using the 3-letter abbreviation used in the Plan area and for each transaction enter the Category it falls into. If many are the same, select the rows that contain the transactions and sort them by the column that has the description. Then bulk enter the Categories.

Continue with each account at the next empty row. At line 200 there is a note to “Insert Enough Rows above this row to add any Transactions for the Month“. Make sure you do this or else the tables and graphs will not include those that over-write the row with the instructions.

Each time you enter both a Month and Category, the tables on the Plan & Actual sheet and all charts will automatically be updated.

If it helps, here is a shortcut I use. I copy all of the categories into the top pane next to the chart so that I can see which ones are used. I had to break it into 2 columns so that it all stays in the top pane. Then I can copy and paste the one I want without retyping and making a mistake. If you add new categories, make sure you update this list.

What Should You Do About Cash? There will always be times when you need to use cash. If it is taken from your chequing account or is a cash advance (hopefully you never do this as the charges are very high), it will be one of the transactions. Enter it as a Cash category (group = Miscellaneous). If you want to itemize it, just insert new rows and enter the amount and remove the Cash category from the withdrawal (or reduce it by the amounts you itemize).

Accounts Sheet

The Accounts sheet is shown by clicking on the Accounts tab at the bottom of the page. It is a key component to managing your finances as it helps you manage your cash accounts and credit liabilities so that you know what deposits are required for you to meet your expenses each month. There is also a section for managing all your accounts so that you view your worth at the end of each month.

Section A is where you determine if how much is available in the cash accounts for the next month’s expenses.

1) First, you must enter what Cash Account Assets that you will be using to pay for your expenses. There are default entries for Chequing and Savings. Change the Name to whatever you want and insert new rows if desired as indicated previously. At the end of the current month, enter what is in the account. The amount in column C (End Dec) is for the last year.

2) Next, for the Charge Account Liabilities, change the Name and delete or insert new rows if needed. The defaults are Line of Credit, Charge Card 1 and Charge Card 2.  At the end of the current month, enter what is to be paid next month from this month’s charges, not the balance owing. If you have made an effort to pay off the charges incurred during the month before the end of the month, this value will be zero. If there are still some left to pay, use this value. The amount in column C (End Dec) is for the last year.

The Available for Start of Next Month row is calculated from the above by subtracting 2 from 1. This amount is what you have available for the expenses at the start of the next month. Warning: If negative (red), check Section B below to see if you will have enough at the end of the next month.Managing Expenses and Cash Flow 14 AccountsA

For the example, the chequing account starts with $1,000 and, for the first four months, all actual (not planned) income and expenses are added to or spent from this account. There is no savings amount. By the end of March, the large $800 House insurance and $2,000 Property taxes expenses have almost deleted the account so that with the outstanding $300 line of credit liability, there is a shortfall of -$23 for the start of April. Because April has an excess of income over expenses (see Section B below), at the end of April, there is a $1,669 balance. This is why there is a warning to check Section B when there is a negative.

Section B helps you determine the Expected Cash Assets at the end of the month. To do this, the Plan Income, Expenses and Cumulative Cash Flow for each month are copied from the Plan area. This will help you see what has been planned for the next month. Below this are 3 rows that calculate what will be available at the end of the month.

The Plan Income – Detail Expenses line is calculated by subtracting the Expenses from the Income. If negative it will be red. The Available at Start of Month are the values calculate in Section A, but shifted by one month to the right.

The final row of Section B is the Available Expected at End of Month and is the addition of the two rows above. If negative (red), you may be short for the month. Warning: Check next month and if still negative, transfer enough into the Cash Accounts to make it positive. If Chequing is expected to be below the minimum for no bank fees, decide if it is worth transferring funds from savings or LOC.Managing Expenses and Cash Flow 15 AccountsB

For the example, note that all but March has more Income than expenses and March is -$2,340 due to the two large bills mentioned above. This would have forced the Available Expected at End of Month to be -$257, but because there were savings made in the Actuals, it is really a little less (-$23). As mentioned in Section A above, the negative number (-$23) was a warning to check this line to see if for the next month (April) to see if it is still negative. Because it is not, no infusion of money is needed. Care would have to be taken during April to ensure that there Income as deposited before money is withdrawn.

Section C is a Summary of Actual\s which is provided for comparison purposes so that you do not have to switch to another sheet. The Actual Income and Actual Detail Expenses are copied from the Plan & Actual sheet. The difference between the Income and Expenses is shown. Rather than you having to make a manual comparison, the Actual – Plan (Income-Expenses) line does it for you. If the Actual is saving you money from the Plan, it is positive and black and if not it is negative and red.Managing Expenses and Cash Flow 16 AccountsC

For the example, as expected by viewing the Cash Flow chart on the Plan & Actual sheet, the last two lines show that savings where made from the plan for all but one month and that by April the cumulative savings are $609.

Section D permits you to determine your worth at the end of each month. This is not used anywhere else on the spreadsheet and is provided as a help in showing how your finances are changing over the year. There are 2 areas where you need to enter values and one chart that shows you worth.

3) Enter all of your other assets that are not included in 1 above at the end of each month. If you miss a month or two, include then when you can. Change the Name as desired and delete or insert rows as indicated in the Making Changes section above. The All Assets row is used to calculate the worth and is plotted on the chart.

4) Enter all of your other liabilities that are not included in 2 above at the end of each month. If you miss a month or two, include then when you can. Change the Name as desired and delete or insert rows as indicated in the Making Changes section above. The All Liabilities row is used to calculate the worth and is plotted on the chart.

The Worth row subtracts the liabilities from the income. It is shown on the chart.

The chart labeled All Assets and Liabilities by Month uses columns for Assets and Liabilities and a line for Worth.Managing Expenses and Cash Flow 17 AccountsD

 

For the example, arbitrary amounts have been entered to produce values that can be charted as shown below.Managing Expenses and Cash Flow 18 AccountsChart

Go to Part 3: FinanceBase

Resources

(1) Screen Elements Mac Excel 2011 – St. Edward’s University return

(2) Parts of the Excel 2010 Screen – About.com, About Tech return

(3) How Americans Spend Their Money – Consolidated Credit return

(4) Budget breakdown: Here’s how Canadian households spend their money – BuzzBuzzHome News, M. Warzecha, January 22, 2015 return

(5) How Much Money You Should Spend on Living Expenses – Budgeting Guidelines for Income – Credit Counselling Society return