Create your Financial Forecast

As a working class person, I believe that it is important to look at your finances on a regular basis.  To be honest, this holds true no matter who you are. Therefore, it is important that everyone creates their own Financial Forecast.

When you have an understanding of your finances it helps in your everyday choices because you no longer have to worry about whether you can afford it – you will already know!

Workingclassfinance isn’t here to tell you stop ALL unnecessary spending – it is here to help you be more in control of your financial life by looking into the future based on current spending habits.  We only live once, so whilst I want everybody to enjoy life, I would rather everyone enjoy their whole life, rather than enjoying it now and paying for it hard later.

It is important for everyone to know where their current financial trajectory is taking them, because it is better to know of any issues in advance.  Of course nobody can predict the future, but by forecasting as accurately as possible, you will have a better chance of being prepared for it.

The way I feel best is to take a few steps:

  1. Analyse all your current Income and Expenses and Debts – not HOW you want them to be, but what they ACTUALLY are at the moment, the time to amend them is later.
  2. Forecast your current expenses and spending trend over the next few years at least (I go for 5 years) – I feel it is important to forecast it over a few years so that you can see if you are having a future issue or not.
  3. Once you have your current forecast – you can then take the necessary steps to amend your forecast to make it workable.  This can involve eliminating expenses, reducing expenses and increasing income. Also, timing is important, if you can delay something or bring it forward to fit into your forecast, then do it.

I use Microsoft Excel, but there are also free options such as Google Sheets or other spreadsheet documents.  I will walk you through how to set up your own forecast, and also provide free templates for you to amend for yourself.  If anything, you will get some spreadsheet experience by doing this.

The great thing about this, is that once you have it set up, it literally forecasts your future finances.  So, if you overspend, your whole future finances are changed to reflect this.  

Please note: this is not financial advice – this is framework for you to track your future finances by yourself.


Step 1: Get your Forecasting spreadsheet set up

This is likely to be the most time-consuming part of this whole process, but it is important to get it right and be confident with using spreadsheets.

I am using Excel, but feel free to use any other spreadsheet software you may wish to use.

I have all of my income/expenses coming out of just one bank, but you may wish to have multiple tabs for different accounts and link them together.

It is important to realise that everyone will have different finances, and so some parts of this section will be relevant, and some wont be.

1.1 Setting up the Tabs

Alongside the Main Bank tab, I have multiple additional tabs for Savings, Debts, Mortgages and a Summary page.

Tabs
Required Tabs

You can however just choose to have one tab if you just have one bank that you run your finances through.

1.2 Main Bank tab

This tab is the main working tab where the majority of your income and expenses will be tracked.

1.2.1 Headings

Across the top I have the following Column headings:

Day No
Day
Date
Bank

Wages/Salary
Other Income1
Other Income2

Savings
Credit Cards
Loans
Mortgage/Rent
Council Tax
Gas/Elec
Water
Mobile Phone
Telephone/Broadband
House Insurance
Veh Insurance
Veh Tax
Fuel
Veh Repairs/MOT
Groceries
Travel Costs
Holidays
Gym/Exercise classes
Prescriptions
Dentist/Opticians
Socialising
Eating out/Takeaways
Shopping
Hobbies
Clothes
Haircuts
Other spending
Birthdays
Christmas
Special Occasions

You can choose to add/delete columns as necessary to fit your own situation.

Column Headings
Column Headings

Note: I have change the text orientation in the column headings so that it is easier to fit on one page, but if you are happy to scroll across then I would suggest leaving the text as it is so that you can read it easier.

1.2.2 Day number, Day and Date

Once I have my column headings in place I add in the day number, Day and Date and drag it down for however many years I want to forecast for. In this case I have started at 1 January 2020 (Wednesday) and finished at 31 December 2024.

So start off by populating the cell under the date column with the date you want to start off with. Then add the day of the week under the Day in a 3 letter format (i.e where Wednesday is Wed and Sunday is Sun).

To get the day number in, you will need to add the formula =day(c4) into cell A3. This will pull the day number of the month, and so therefore adjusts for longer/shorter months. This is useful so that you are able to filter for specifc days of the months were an expense may be paid (i.e a direct debt that comes out on the 15th of each month).

Day number formula
Day number formula


Now that you have got the first line of day number, day and date set up, you can just highlight all 3 cells and click on the little notch that comes up in the bottom right hand of the cells, click and hold it whilst you drag down the page. Stop at whatever forecast length you want, i.e in this case it is 5 years time, so 31 December 2024. You will now have a fully populated day number, day and date range. Have a quick check to make sure that the correct date has the right day and day number, but it should be fine.

Populated dates and locked screen
Populated dates and locked screen

It is often useful to lock the top rows and left hand columns so that no matter where on the page you are, you can always see the titles.

I lock it on cell E3, which means that it will always show the column headings and the cells to the left (dates and main bank total). To lock it, click on cell E3 and click on View along the top menu bar, and click on “Freeze Panes”. You can now move up and down, left and right, and always have the top column headings and dates in your view.

1.2.3 Adding the opening bank balance and carrying it forward

In cell D3 I add my closing bank balance from the day before, this way, I know that my bank balance is correct and any adjustments I make will carry forward when I add in new income and expenses.

Next, I add in a formula that will calculate my closing bank balance for each day. To do this I make it grab the previous day’s balance and add in all of the income and expenses for the current day.

To do this for the first day, and in this instance I am starting in cell D4, so this is the cell where I will type the formula in.

=D3+SUM(F4:AN4)

The above formula grabs yesterdays closing balance and adds in all of the income and expense columns too. This needs to be amended depending on your own columns, as they could end on a different column letter.

Bank Formula
Bank Formula

To carry this down to all of the dates, you can click on the cell, and double click the little notch in the bottom right hand corner. This will automatically populate each cell below due to you already having populated the date columns. If this doesn’t work, you can just grab the notch and pull it down manually which will copy and update the formula. The bank will now automatically update when you add in income and expenses in part 2.

1.3 Savings Tab

This section is to show all of your savings accounts in one tab.

1.3.1 Savings Headings

In the savings tab, we have a similar daily set up as to the main tab (as we will for all the other tabs). The easiest way to get this, is to highlight columns A-C in the Main tab by clicking and holding on column A and dragging across to Column C, then Copy and paste on to Column A in the Savings tab. You could also do this for the Debt, Mortgage and Summary Tabs if you want to.

You should now have the same days in the same rows for both tabs.

From here, I will add a column heading in column D for total savings.

Next, this is where you will add as many different savings accounts as you have/want. For each savings account I have the following headings:

Opening Balance – the amount already in savings
Deposit – how much you add in (+)
Interest – how much interest or bonus you receive (+)
Withdraw – how much you take out of your savings (+)
Closing Balance – all of the above added together

In column F1 I will add a savings description, in this case just Savings 1, then in F2 I will add the Opening balance column heading, and then going across to the right I will add the remaining column headings.

Once I have the savings headings in place I can add more savings accounts if necessary, by simply copy and pasting the headings next to it, whilst leaving a space.

In the below screenshot you can see I have added 3 savings accounts

Savings headings
Savings headings

1.3.2 Savings – Adding the opening bank balance and carrying it forward

Next we need to add the opening savings balance. This can obviously vary depending on whether you already have savings or not. I will add the value in cell F4.

To calculate the closing balance, we simply add together the Opening, Deposit, Interest received and Withdraw amounts. To do this we use the below formula in Cell J4:

=SUM(F4:I4)

The next stage is to carry this closing balance forward to the next day. In cell F5 (opening balance) I will grab cell C4 (closing balance from previous day). I do this using the below formula:

=C4

Now I can the opening balance and closing balance formula down. Make sure that you drag the formula for the opening balance from cell F5 down, because if you grab it from F4 you will only have the original opening balance which is a keyed in number.

Once this is all dragged down you should have a simple functioning tracking sheet for that savings accounts. Just repeat the same for all of the other savings accounts.

Savings accounts
Savings accounts

1.3.3 Total Savings

The final part in setting up your savings spreadsheet is to total all of your savings into one overall amount. You want to add the closing balance of each savings account together.

In the case of the template I am going to use the below formula in cell D4:

=J4+P4+V4

Total Savings Formula

Next just highlight cell D4 and double click the notch in the bottom right hand corner of the cell or drag the notch down to populate the rest of the cells below.

You now have your Savings tab set up.

1.4 Debt tab

The set up for the Debt tab is similar to the savings, and will vary by person depending on the total number of debts that you have. In this instance I will set up 3 debts, but make sure when doing your own forecast that you add all of your debts.

1.4.1 Debts Headings

In the Debts tab, we have the same daily set up as to the main tab (as we will for all the other tabs). The easiest way to get this, is to highlight columns A-C in the Main tab by clicking and holding on column A and dragging across to Column C, then Copy and paste on to Column A in the Debts tab.

You should now have the same days in the same rows for both tabs.

From here, I will add a column heading in column D for total Debts.

Next, this is where you will add as many different Debt accounts as you have. For each Debt I have the following headings:

Opening Balance – the amount already in Debt
Purchases(or Loan amount) – how much you spend (or total new Loan) (+)
Interest Charged – how much interest the company has added (+)
Payment – how much you pay off your Debt (-)
Closing Balance – all of the above added together

In column F1 I will add a Debt description, in this case just Credit Card 1 (but you could name it what the Credit Card is), then in F2 I will add the Opening balance column heading, and then going across to the right I will add the remaining column headings.

Once I have the Debt headings in place I can add more Debt accounts if necessary, by simply copy and pasting the headings next to it, whilst leaving a space.

In the below screenshot you can see I have added 3 Debt accounts.

Debt headings
Debt headings

1.4.2 Debts- Adding the opening bank balance and carrying it forward

Next we need to add the opening Debt balances. This can obviously vary depending on whether you already have Debt or not. I will add the value in cell F4.

To calculate the closing balance, we simply add together the Opening, Purchases, Interest charged and Payments amounts. To do this we use the below formula in Cell J4:

=SUM(F4:I4)

The next stage is to carry this closing balance forward to the next day. In cell F5 (opening balance) I will grab cell C4 (closing balance from previous day). I do this using the below formula:

=C4

Now I can the opening balance and closing balance formula down. Make sure that you drag the formula for the opening balance from cell F5 down, because if you grab it from F4 you will only have the original opening balance which is a keyed in number.

Once this is all dragged down you should have a simple functioning tracking sheet for that savings accounts. Just repeat the same for all of the other savings accounts.

 Debts carry forward
Debts carry forward

1.4.3 Total Debt

The final part in setting up your Debt spreadsheet is to total all of your Debts into one overall amount. You want to add the closing balance of each Debt account together.

In the case of the template I am going to use the below formula in cell D4:

=J4+P4+V4

Next just highlight cell D4 and double click the notch in the bottom right hand corner of the cell or drag the notch down to populate the rest of the cells below.

You now have your Debts tab set up.

1.5 Mortgage tab

The mortgage tab may or may not be needed depending on the person and whether you have purchased your own home or not.

Most people will likely only have the one mortgage, but of course some people own more than one home and so therefore could have more than one mortgage.

The exact same process as the debt tab is used to set this up, so you could just copy that tab and rename it and change the headings.

As a main basis, I would have the below headings:

Opening Balance
Additional Mortgage
Interest Charged
Fees Charged
Payment
Over-payment
Closing Balance

Mortgage
Mortgage

I have just added the one mortgage, but if you have more, you can just copy and paste the columns to add more mortgages and amend the the total mortgage formula to add them all together.

1.6 Summary tab

The summary tab is a simple tab the brings together all of the other tabs into one snapshot of your finances by day.

Again, we will have the same set up of the days, which you may have already done.

The headings that we will need for the Summary tab just include the other tab names, so Main Bank, Savings, Debts and Mortgage.

I then link the total for each date/column to the tab. In F4 I have got the Main bank total for the first day, which in this case I get using the below formula:

=’Main Bank’!D4

This is then done for the other column headings and tabs.

Finally I add a total column in Column D which adds up each of the totals of the 4, and again drag it down.

Summary
Summary



You can now see how your totals change over the years.

Many people may just have a liquid total, which includes just totalling the Main Bank, Savings and Debt. Most people are not likely in a position to pay off their mortgage, but could easily be in the position to pay off their other debts.

Step 2: Analyse your Income and update Forecast spreadsheet

This step is where you populate your spreadsheet based on known amounts, and will show you where you are now. It will also give you a glimpse into your financial future. You have already set up the working part of your spreadsheet, now you just need to add in the detail.

Start off by highlighting row 2 (which should have your column headings) and clicking on the filter icon. This will add a filter to all of the headings. This filter will allow you to easily pick up regular days or day numbers and quickly add in the income (or expense later).

2.1 Update your Income

Most people know how much they get paid each month, so it shouldn’t be too difficult for you to update this section.

To start off with you will go into your Main Bank.

Make sure the figure you put in is after tax, and is put in on each pay day.  So, if you are paid on the last working day of the month, you go through each month end on the spreadsheet and put your net wage/salary figure into it.  Or if you are paid every Friday – again, filter your spreadsheet day on Friday and put your wage/salary figure into it.

In this case, I have put a £300 a week salary being paid every friday. To get this I just filtered column B on friday, which brings up every single friday. I then add £300 into column F for each cell.

If you have any other regular income, such as benefits or a known amount of cash coming in, then put them in as well. It all makes your forecast more accurate.

Your Forecast spreadsheet should now show a nice large figure – now is the time to move onto your expenses.

Step 3: Analyse your Expenses and update Forecast spreadsheet

For most people, this section will be the more detailed as most working class people have just one or two incomes, but lots of expenses.

You should have already added in your main expenses when setting up your spreadsheet, but below is the process.

The good thing is that most expenses are generally on a regular day or date. You can use the day filter to add your weekly grocery shopping every Saturday if that is your shopping day. If your mortgage comes out on the 3rd of every month, then you can use the day filter to filter on 3 and then add your mortgage payment for every day 3 in your forecast.

I tend to split my expenses into the following main sections and then breakdown further:

  • Savings
  • Debt
  • Home
  • Vehicle
  • Groceries
  • Travel
  • Health and Fitness
  • Shopping and Fun Money
  • Special Occasions

Each of the above will have various expenses which are individual to each person, for example some people may have a mortgage whilst others will rent and some people may pay keep. 

Analysing your current expenses in an honest and full way is the best way to be, because you are kidding yourself if you omit an expense because you are embarrassed you spend that much (don’t be embarrassed, be honest).

Most expenses are regular, whether they are weekly, fortnightly, monthly or annually, or any mix inbetween.   It is important that you input ALL of your regular and irregular expenses into the forecast.

 The best place to start is your direct debits, as these will be regular.  Go through your bank statements and add in all your expenses.

Got a large purchase planned?  A new car, a holiday, a wedding?  Pop that into your forecast to see how it affects.  If you are planning on taking out a loan or using a credit card – add a new form of debt onto the spreadsheet so that you can track that too and factor in the repayments to your forecast to the best of your knowledge – then update once you have firmer figures.

As soon as you know that an expense has changed, you will need to change this in your forecast going forward if necessary – the whole idea of this forecast is that it isn’t rigid because things change for the better as well as worse.  A fluid forecast is different than a fixed budget, but both need to work together to help you stay on track, but also to take advantage of changes in situation.           

Step 4: Reducing Expenses

Now that you have your current and forecast spend in your Financial Forecast, you can look item by item at how you can reduce your expenses.

The larger the expense, generally the better chance you have of saving money.  Check out our Expenses articles for help on where to look and how to reduce these costs.

Even if you have a sustainable spending habit, there is nothing wrong with ensuring you get the best deal on what you spend and pocketing the difference. 

This extra cash in your pocket can help you save more money for your emergency savings pot (you have one right?) or allow you to pursue a hobby or save for retirement.  £10 saved a month is £120 a year, which is a nice amount for you to spend on something nice for yourself.

Step 5: Update your forecast regularly

It is best practice to update this as soon as things change – so if you get more money than you were expecting, reflect it in your forecast.  You really do need to get into the habit of staying on top of your finances, so at any time, you can know how much money you have.

An up to date forecast is the best piece of information you can have in your arsenal, and it is better than a budget alone.

Hopefully you stuck with me throughout this, and I can assure you that this will help you have better control over your finances and give you more confidence on this aspect of your life.

Once you have created your own forecast, and got into the habit of updating it, you will naturally begin to amend it and create better spending and earning habits.  It will likely mean that you reduce your expenses, and so therefore you will have less to track over time.

Conclusion

You should now have your own fully functioning tracker of your everyday finances. Make sure that you keep it updated regularly and you will have an amazing tool at your fingertips and be able to plan for the future better.

Be aware that things change quickly, and so therefore it is important to stay on top and amend your Forecast as necessary. If you lose your job for example, or if interest rates change things can go south quickly.

Promotions and pay rises can help too, try not to let the positives allow a Lifestyle Creep, but instead use the additional cash for better purposes that fill you with a greater sense of fulfillment.

WorkingClassFinance Financial Forecast template

If you want a headstart on your own Financial Forecast, you can get the ready made WorkingClassFinance Financial Forecast template by signing up to our newsletter here.