Excel – recurrent

When I moved from the west coast of Australia to the east coast, all alone, with only a few dollars in bank accounts, an engagement ring in my pocket and a strong desire to marry a girl, I knew I had to get serious with my finances. If I was going to get married I had to show this woman that I knew how to take control. The best way I found to help get my financial situation sorted was by starting a budget. A budget in its simplest form is just a plan - a plan that involves money and time. It should tell you what you're going to receive in the form of income, what you're going to be paying, and when these two aspects will occur. To provide further assistant to your plan you could write some notes against each line to help classify what it is, and/or why it is happening. By doing a budget you will see how much money you have either in surplus or in deficit each week, and whether the decisions you're making are helping you to achieve your financial goals. The first step in getting a budget up is to structure it so it makes sense. You could go and grab a free weekly budget template off from the internet today, but what I want you to focus on right now is the basics of what goes into a cash flow budget. Cash Flow Budget Format So with a basic fundamental understanding behind what a budget is, how do we structure or lay this information out? Is there a right or wrong way in displaying this data? Having created many different types of budgets both personally and professionally in my career over the last decade there are some formats that can help make the design of the budget a little easier to understand. Time Period Before getting started with the actual transactions, establish what time period you would like your budget to cover. Are you thinking about what will be happening over the next 12 months? Or maybe just a week? If you're just getting started with budgeting start with a smaller time period such as next week (the next 7 days). With that time period in mind, or whichever one you decide upon, you can now begin to start casting your mind forward on what will be happening in your bank account over that time period. To start, let's begin with money that will come in to your account, this is known as: Cash inflows The best way to start structuring your budget is to write down all the transactions you can foresee happening where money will be received. This would be things like: Salary or wagesBank interestDividends from sharesGiftsSale of anything you own (assets) Write these down on separate lines to help separate what they are for easy reference. Once you've listed all your cash inflows then total up all the items and their corresponding dollar amounts. For example, your cash inflow list might look a little like this: Cash Inflow TypeAmount ($)Salary1,000.00Bank Interest3.50Total Cash Inflows1,003.50Cash Inflows from 1/04/2021 to 7/04/2021 Gross salary or net salary? A good question to consider when doing a budget is whether the salary and wages line in the cash inflow area should be the gross amount before taxes are taken out, or net after taxes have been taken out. If you're just starting out with your first budget write down the net amount you will receive in your bank account as this will be the easiest to follow and track. As you become more experienced with budgeting you'll begin to work on strategies to help maximise your take home pay, and then you will want to list your gross salary amount to gauge the impact of items salary sacrificed. Once we've listed all our cash inflows over the coming week ahead (or whatever time period you've elected to choose), we can then move to the second stage of our cash flow budget by listing money that will be withdrawn, or taken out of, our bank or savings account. Cash outflows Out of the two broad categories cash outflows can definitely take the longest to complete. There are many ways we can spend our money and just as we tried to foresee what money was entering our bank account, we now need to cast our mind into seeing what things will we be spending our money on over the coming time period. Some ideas to help ponder, would include items such as: Food (groceries, fast food/takeaway, coffee/cafe)Loan payments (mortgage payments, credit card, car payments)Rental payments (home, car)Car expenses (fuel/petrol, mechanic, rego)Utility bills (electricity, gas, water, rates, phone, internet)Tuition fees (school, books, uniform)Insurance (car, home & contents)Activities (travel, sports)Online Services (movies, storage)Donations (church, tithing, charity) I hope some of those categories helped provide enough prompting for you to consider what you’re likely to be spending on these areas over the time period in consideration. With a smaller time frame you should be able to capture most of the expenses occurring. Either you’ve been sent a bill in the mail, or you can have a quick look at the transactions in your bank account to see what will be coming. The larger the timeframe in perspective the easier it will be to miss certain expenses. Some expenses which may occur only annually or even longer than annually include: Driver’s licencePassportProfessional licences/membershipsInsurances (car, car rego, home & contents)Credit card annual feesMortgage fees For some of these items you’ll need to check when they expire and see they don’t occur during your budget window. If they do, then don’t forget to include them in your cash outflows section. When you’ve finished setting all your cash outflow items in order during the time period you should have something like this, as an example: Cash Outflow TypeAmount ($)Groceries300.00Phone55.00Water280.00Fuel50.00Total Cash Outflows685.00Cash outflows from 1/04/2021 to 7/04/2021 By summing all the cash outflows together at the bottom you should have a fair idea of what will be withdrawn from your cash account during the same budget period. Net Cash Flow Underneath our cash inflow section and cash outflow section is the main area where we calculate how much we’re likely to increase or decrease during the time period of our budget. To arrive at out net cash flow figure we simply subtract the cash inflow total by the cash outflow total. The formula being: Net cash flow = Cash inflow - Cash outflow If we have a positive value this represents more cash inflow than cash outflow. If we have a negative value this represents more cash outflow during the period than cash inflow. One other further aspect that is often placed in the Net Cash calculation section is the Opening Balance of the bank account at the start of the time period. By inserting the opening balance we can perform one more calculation to determine what our closing bank balance will be at the end of the budgeted time period. The formula for calculating the closing bank balance is as follows: Closing balance = Opening balance + Net cash flow By seeing the resulting closing bank balance it can provide a quick reference to compare against when we look at this value against our actual bank account amount. Net Cash FlowAmount ($)Net Cash Flow318.50Opening Bank Balance (1/04/2021)4,165.13Closing Bank Balance (7/04/2021)4,483.63Net cash flow & closing balance at end of budget period Using a Spreadsheet The easiest way to perform all these necessary calculations is to write them up in a spreadsheet. By having them placed in a spreadsheet you can have the computer perform the calculations and you would only need focus on the inputs. As you become more confident in using a spreadsheet with your budgeting purposes, as I did when I began doing our budget, you will modify the spreadsheet to help speed up the process. Over time you don't want to have to keep entering the same inputs for each time period, you want to be able to write the input once, enter when the transaction will start, and when it will stop (if it does) and then enter the frequency for when the transaction occurs (for example, monthly, weekly, fortnightly, etc). When you have this process down to a fine art you realise you can budget daily, weekly, fortnightly, monthly, annually or whatever your heart desires! You can begin to see as far into the future as you want depending upon the level of detail you've inserted into your budget spreadsheet. This is why I have shared my cash flow budget spreadsheet which does exactly that - it allows you to enter your cash inflows and outflows by simply entering into the respective sections the following details: Description (optional)Category (optional)Start Date of transactionEnd Date of transaction (optional)Frequency of transactionAmount of transaction per frequencyType (inflow or outflow) At the very top of the spreadsheet you the enter what you would like to see reported. You enter the start and end date of the reporting period and the frequency of each column. The most common report is an annual report, so you would enter a date range spanning over the course of a year, for example 1/01/2021 to 31/12/2021, with the frequency of the time periods within that period set to monthly. If you wanted to remain on a weekly budget you could leave the date range as the year, but change the frequency of the time periods within the year to weekly. Here's a snapshot of what the cash flow forecast spreadsheet looks like when filled out with some data and displayed in a weekly format: Weekly cash flow spreadsheet Summary A budget is a plan listing our financial commitments and ideas on what we think will happen in the future. If you’re just getting started with budgeting then I’d highly recommend finding a format that works for you. In this article we’ve looked at the most common approach to creating a personal cash flow budget, but this is by no means the only approach. Start by forecasting the week ahead and write down what you think you’ll be receiving in income, and then write down what you think you’ll be spending over the same time period. If you get paid weekly, then doing a weekly budget, or if you get paid fortnightly or monthly, then do a budget over that same time period. It's generally easier to do a budget based on the cycle of when you get paid for your own salary or wages. As you become more familiar with the budgeting process and want to start automating the task, or expanding your time horizons from weekly to monthly then annually you can then begin to use a spreadsheet to help capture these transactions to help you see how your personal finances will progress over time. By getting started with a budget you’ll begin to take control of your finances and be more responsible with your life - just what happened to me before I proposed to the girl who eventually became my wife!