Cash Flow Management 102 — the practical tools
Welcome back to Fairlo’s guide to smarter money management and cash flow!
This week we are introducing a simple yet efficient tool — the DIY cash flow statement. This online sheet will help you assess your income and expenses by the month, see averages and patterns instantly.
Let’s dive right in!
How to Use a Cash Flow Statement
First access the DIY Cash Flow Statement here.
You can use the sheet to structure your financial movements from the previous months.
We recommend using this tool once the month is finished and you have tallied up all your accounts.
To be able to identify patterns and opportunities to improve keep on using the cash flow tool monthly. We advise to schedule some time to create your statement each month, for example every first Sunday to complete the information. It could take about 1–2 hours to fill the sheet out, depending on the complexity of your finances. Consider it as making an appointment with your own CFO!
In the Excel sheet provided we already have some smart formulas in the green fields, which serve as the brain of your cash flow statement and will calculate for you automatically. Please do not remove these formulas if you want them to do the work for you.
Cash flow Statement Ingredients
- Cash Flow Worksheet — Fairlo has created an easy version for personal cash flow management. Please see the file on this link and create your own copy by going to File — Make a Copy or Download.
- Bank Statement — collect all of your bank statements from each account you have, and have had some movement in the previous month. Many banks offer the option to download your detailed transactions in an Excel or CSV (comma-separated versions to be used in ExcelNUM0 which could be a great help in putting together your cash flow statement. If you have this option, please open your account movements in a separate Excel sheet. If you do not have this option, feel free to use the paper version of your bank statement.
- Balance before the period- Check what your balance was at the beginning of the period and at the end. For example, if we are creating the cash flow statement for May 2019, your starting balance is the end-of-day balance on the 30th of April, and the ending balance of the 31st May.
How to Complete the Cash Flow Statement
1. Set up your Cash Flow Worksheet
a) Add in the day you start assessing your cash flow in cell C3. Watch out — this is not when you are filling out the worksheet, but from when you are calculating your movements, ideally the first of a given month.
b) Fill your starting cash balance in C5- all the amount of cash or debt you currently have. Lines 3–6 should now automatically recalculate, and you should not worry about it any further.
c) Delete the example figures — remove the numbers we have added as a demonstration in the sheet.
d) Adjust the worksheet according to your lifestyle and work- our incomes and expenses differ greatly: we can earn a fixed salary, profit shares from our business, have children or travel more often than others. Go through the categories in the worksheet, delete irrelevant lines for you and insert the ones you need. You can easily do this by selecting the number of a line with a right-click.
2. Add your income
a) Find all the positive transactions from your bank statements
b) Categorize them according to the type of income you have — whether it is a salary, business revenues, scholarship, benefit or any other.
3. Categorize your expenses
According to how comfortable you are with Excel, you can choose to categorize within the program, or manually.
a) Excel categorization
i) After importing your bank statement into your Excel file, add an extra column to the sheet, and name it categories.
ii) Start categorizing each item by entering the name of the given category.
iii) When you have categorized each line of expense use the filter function on the first line.
iv) Select through the filter one category at a time and check their total.
v) Add the amount to the cash flow statement of the selected category.
b) Manual categorization
i) Note down a category next to each line of expenses in your cash flow statement
ii) Add the expenses up according to their category
iii) Insert the total in the cash flow statement
4. Check for accuracy
a) Ideally, the ending cash balance should be matching your bank balance on the last day of the period.
b) If the ending balance is not matching do not panic — double check your calculations through the categories for typos and miscalculations.
5. Review your balances and check for improvements
Pro-tip: Pivot categorization
If you feel very comfortable with excel, you can automate categorization using a pivot table. After inserting your bank statement, open one more empty column, name it as “Month” and add the name of the corresponding month. Complete the categorization as above, but instead of filtering for individual categories, you insert a pivot table. The pivot table selects the month as the column category, and calculates the sum of all transactions per category.
The good thing about using a pivot table is that you can collect each month in one sheet. When the new month comes, you just insert and categorize your bank statement in the same sheet and recalculate the pivot table.
Here you go — this is all you have to do to create your very own cash flow statement!
This exercise, only taking a couple of hours each month, can give you a great overview of your finances, increase awareness for your spending, and help you achieve your financial goals.
