Friday, 17 March 2017

Spreadsheet 101: How to Make Your Own Accounts System for FREE! (Part 2)

In this post I'll show you how to finish creating your simple spreadsheet for accounting at home.
This is a great way to manage your accounts whether you are self employed or just need a way to help budget your finances.

Today we add a 'Summary' to our previous sheet and make it look pretty!

If you haven't seen part 1 of this post, visit that one first, then come back here.
(it will make more sense, I promise.)
Spreadsheet 101: How To Make Your Own Accounts System for FREE! (Part 1)


Colouring the background.
To colour any part of the sheet you need to drag and highlight a selection the choose the background colour button. This will give you a dropdown colour chart that you can then pick your colours from.
Add borders in a similar way to above. Highlight selection, then choose the type of border you need. You can put a border anywhere. I tend to like it as simple as possible because you can find yourself stuck in front of these sheets, pondering as to where your money went, for a few hours. Lots of lines make my eyes hurt after a while.

Heres my colour scheme and borders. You can see i use shades of colours to help separate different sections. It helps if you need to quickly find a heading. For example within all of my spreadsheets I have ever made the income is always in yellow. Having this colour scheme means, even on a large scale spreadsheet, sections, headings, and subheadings are always easy to find. This comes in especially handy when you are booking in mountains of receipts. Or if you have to introduce a new employee to the job.

From here you are nearly done.
Testing is an ABSOLUTE MUST!
There is nothing worse than finally using a spreadsheet and finding out you have missed something. Be it a formula
(like I did above) or that you need another column somewhere. Typically, whenever I have updated the larger accounts system I use for my fathers business, I have trialled it for 3 months January - April, using it along side any older versions. just to make sure i don't loose any data along the way.

Simple fix. I added the new formula at the bottom of the 'Expenses - other' column. Dont forget to update the 'Total Expenses' formula if needed.

This green section on the side is used for a summary. The summary is designed as a one-stop-shop for all the information on the spreadsheet.

At the bottom of the summary we need 3 headings.
'Total Expenses' (from the purple section)
'Total Income' (From the yellow section)
and 'Total Profit'

We use our litte formula from the previous post '=cell#'
For example to get the 'Total Expenses' number to appear in CELL 'F35' (purple arrows) we type the formula '=E37'
We use the same formulae for 'Total Incomings' (yellow arrows) '=K37'

TOP TIP: If the cell you need is part of a merged cell always use the furthest left Cell number.
Once this information arrives in these boxes
 (the purpose of our test data was so we knew we had done it right here!)
To find our 'Total Profit' we need this formula to take Our 'Total Expenses' from our 'Total Incomings'
'=SUM(Total Incomings - Total Expenses)'
Then we find profit!
If you ever need to view in full screen you can use the shortcut "Ctrl+Shift+J" or select 'fullscreen' from the 'view' dropdown menu.

And thats it!

You have created - perhaps - your first fully functional spreadsheet. 
At the moment it's only one month at a time - so before you use it save a blank one. 
"Accounts BLANK 2016_2017" 
then you always have one as you need it. Everytime you open it to start a new month
'File' -  'Save As'  "Accounts March 2016_2017" and obviously switch out 'March' for whatever month it is, Before you start filling in information!

Remember you can change any of the headings to suit your needs. Thats the beauty of a spreadsheet - Personalisation.

With colours I prefer to stay muted and traditional to my father's first ever spreadsheets but you could make it bright pink, bright green, bright orange. Whatever makes you happiest whilst mulling over your accounts.


Do let me know if you likes this 2 part 101 - How To Series. I'm always getting asked how I created my Father's Spreadsheet so I'm sharing the knowledge this way :)


Happy Computing,

Post a Comment