Setting up your finance tracker is quite straight forward, just block out a night with no distractions to help you get your head in the right frame of mind.
Start by setting up your month chart like the below image, making sure you have clear column headings to keep you right.
See the below colour guide to help you find your way round your spreadsheet:
- Blue = Account balance before pay day
- Green = Salary
- Yellow = Account balance after pay day and before expenditure
- Red = Expenditure total
- Grey = Account balance after expenditure
Now it’s time to enter your numbers. It’s best to start this process at the end of the month (after payday has passed but before your bills for the next month come out). This will allow you to have a clear starting point that will save you a brain fry and ensure correct calculations.
Once you’ve entered all your numbers, it’s time to formulate your sheet. Use the below formulas to get your cells calculating right:
- Adding your salary to your account balance: enter your current bank balance to cell D3 and enter =SUM(D2+D4)
- Adding up your expenditure: click on cell B13 and enter =SUM(B3:B12)
- Deducting your expenditure from your account balance: click on cell D13 and enter =SUM(D3-B13)
Now you should have your first month chart set up with income and expenditure, adding up your figures correctly. Best to double check with a trusty calculator, technology isn’t always our friend.
Copy your chart and paste copies one below the other, leaving a gap in between, to take you to the end of the year (remember and change each charts month to keep you right). Your spreadsheet should auto-detect that each chart corresponds to the next and all will calculate your finances. Play around with your sheet and make sure all formulas are adding up as they should.
Once you’ve set out all your charts and they’re all adding up, it’s time to go back to your starting month and populate with your variable spend (i.e. food shops, lunches out and the occasional shopping trips). Your spend will be added in and accounted for in your end of month figure which will then show the impact your spend on the following month.
IMPORTANT: Every time you add a figure to a column, make sure that it’s accounted for in the formula – you don’t want to miss any figures out so you are set back.
You might find it useful to set yourself a personal budget within your expenditure column. I set aside £130 each month to cover my food shop, petrol and social spending and works well for me. I have copied this figure into all months so it is accounted for and allows me to see how much ‘spare’ money I can put into my savings.
When I was younger, my mum would save all her receipts and I mean the ALL. I would always think “What a pointless exercise” but now I know that she did this to keep track of her spending – now I do the same.
Save up your receipts from the week and put them into your spreadsheet, once they’ve been entered they can be binned. I find it helpful to sit down with my spreadsheet every Monday night and update it, this way I don’t have a huge mountain of receipts to plough through.
You’ll make mistakes and miscalculate to start with but you’ll soon get used to tracking your spend and keeping up to date.
Remember, show your spreadsheet love by keeping it up to date and in return it will show your bank balance some love.