Monday, 13 March 2017

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

If you have your own business you know how tedious it can be tallying up all the company incomings/outgoings, working out your tax return, and other similar accounting tasks. Perhaps you've searched online to look for a simple books program and found the cost far to much to validate using it for your small growing business?

I have a solution that I have been using for the last 10 years or more.

Since I was a young teen I've always helped My dad with the accounts for his own small business and over the years created booking in systems, that are far from the streamline paid-for apps of today, but do the job for us very well. A simple spreadsheet design means you can do it at home on the software you already have and its completely customisable to your businesses needs, 
and IT'S FREE!

This is the final result we are working towards. It's a fully functional spreadsheet for one month. At a later date this can be incorporated into a bigger spreadsheet that does a lot more than simple sums.

Of course I'm no expert myself! I still find things on the spreadsheet software that I never knew existed and I'm basically dragging up years of tinkering and a long forgotten GCSE in ICT, but the computing world is ever-changing so you're never going to know it all. 
Just learn the bits you need.

I will highlight a few ideas below to get you started, with a general outline of how to go about building your own spreadsheet. So if you have no idea where to start hopefully this will get you into the swing of things.

____________________________________

First things first.
Do you have spreadsheet software?

The two main ones I have always used are "Microsoft Excel" and "Apache Open Office".

"Microsoft Office - Excel": I used to use in school and on my older home computers that have it built in. Of course now computers don't come with it installed unless you pay a massive premium, so I stick with the freebies.

"Apache OpenOffice - Calc": Is free downloadable software. It comes as a whole office software just like "Microsoft" (including a word processor, powerpoint etc.) and it does a good job as a replica.


TOP TIP: Whatever you choose to use, if you have more than one computer/laptop It's best to have the same program across all of them - then you dont have to worry about file converting so all the programs know how to open it. Also the following processes are essentially the same whether you have either of the above mentioned programs. I will be working on "Apache Open Office 4".

____________________________________

Apache - OpenOffice Software.

This software comes with everything you need for typical daily use including a Word Processor and the Spreadsheet - which we will be using now.

Once opened its actually called 'Calc'.

I start off by making the basic layout of the spreadsheet. If you hover your mouse between the line of two columns you can drag to enlarge or decrease the width of the column. The same down the left side.
To make a large heading I merge cells together. Do this by highlighting the boxes you want to merge then finding the merge button like the one above. 
I like all the words centred too. Again highlight like before, then select this button.
Now we have the basic layout, two large headings and narrow columns for our monthly dates.
Type in the numbers 1 to 31. These will be our days of the month, of course not all month have 31 days This can be changed at a later date when you come to save all the months or just left blank.
TOP TIP: Instead of writing out all the numbers you can write the numbers 1 to 5 then highlight them all. Place your mouse at the bottom right of the highlighted section and you should be able to drag it down. This will auto fill wherever you drag to. (31)

Once you have done the above step you will be able to save yourself time by copy and pasting the dates as a whole column. If you right click the column header (in my case it was 'A') you will get the option to 'copy'.
From there you can right click where you want more dates (in my case this is column 'G') and select 'paste'. This will save you lots of time and effort. Paste wherever you need them - I did it twice more. ('L' & 'P')
Showing result of copy & paste of dates.
You do not need this many dates if you find it 'clunky' however its nice to be able to easily see what date when you are booking in data so try to have at least one visible on the screen at all times.
Add Sub-headings as you need them. This is a very simple spreadsheet, it would be ideal for a small company that does not have many places they purchase items. For example; A Landscaping business has 3 times the amount of columns as this.  (bear in mind you can build past the screen along to the right if you have lots of headings - for larger or more complicated businesses)
Merge boxes as and when needed - if you do this you will need to merge each box in the column underneath too.
If your screen does not show your whole spreadsheet and you need to scroll down at any point, it is handy for the headings to stay put. To do this we can 'Freeze' the selection. Start by highlighting the rows you want to freeze - including a row underneath - select the 'Window' drop down from the top bar and select 'Freeze'
You'll get something like this, with a line cutting across the screen. Now if you scroll up or down (for example scroll down to the date 31(st) and the headings should follow the screen down.
____________________________________

Thats your super simple layout done. From here we are adding formula which sounds scary but it's easy. Trust me!

____________________________________

Here we are adding a formula which is going to add up our entire column. From day 1 to day 31.
we place it at the bottom of each column that we want the total of so we know how much we have spent on that particular section every month.
_____________________

For those of you who are new to spreadsheet: Each cell has a 'coordinate' (Letter then Number)
For Example - The heading 'Employee', in the above photo, is in cell 'C2'.
Once we know the cell 'coordinate' we can calculate lots of things.
_____________________

The most common formula we are using in this super simple spreadsheet will always look something like this
'=SUM(B3:B33)'
This formula tell you the SUM of that range of cells. Basically it adds up the whole column from day 1 to day 31 (another way to explain it is like this: '=B3+B4+B5+B6+B7+B8+B9...' But that's the long, long, long, long, way.

We add the formula at the bottom of each column that we want to add up. From the cell that begins on day 1 to cell with day 31. You can type the whole formula.
Another way to write formulas is to use your mouse once you've written '=SUM(' when click on a cell it will automatically write itself into the formula you are writing.
'=SUM(Cell#:Cell#)'
If you get half way through you design and decide you need another column. there is a simple way to do it. Simply right click the heading of a column and select 'Insert Column'. This will insert a brand new column in on the left of where you selected.

Remember to do a formula at the bottom of the column if needed.

___________________________
You may notice that you 'Money' columns dont have an automatic (£$) money sign. This is a simple fix. Highlight all the columns that need to be currency. Then select the button show above.

___________________________

For each main section you will want to know how much you have spent in that month. The example above shows the formula for adding all the expenses section together. It's the same formula as before except
 we are travelling across a row instead of a column.
'=SUM(B35:D35)'
Do the same for the 'Income' Section. On this example it doesn't need to do any maths so we simply want this cell to equal the one above
 '=K35'
___________________________

That's the main structure done!

Its almost a fully functional spreadsheet and with a few more tweaks it will be a work of art.
For now I hope this helps you even if its just understanding the basics of a spreadsheet so you can move onto bigger and better sheets.

I'll be back to show you how to finalise the spreadsheet, make it pretty and create a summary of the month down the right hand side. I will link it HERE once its posted.

Perhaps If this was helpful to you would you like if I show you how to create a bigger spreadsheet with 12 months and a finalised year-to-date summary that will even work out your tax for you?
___________________________

For now,
Happy computing!
J4ZZ
x

P.S. In the meantime Wiki - 'Apache' have some great information on spreadsheet self teaching. A quick hunt around the internet and I found this site that will help you wil basic understanding of formulae.

Post a Comment