Tracking your income and expenses can be a stressful part of your business, especially if you do not keep on top of it. Having an easy to use accounts system can make life much easier. This guide looks at how you can you spreadsheet to record your genealogy business accounts.
NOTE: This guide requires a basic understand of how to use and Excel spreadsheet.
Most business use specialised accounting software to keep track of their income and expenses, but these can be expensive to buy and may need some specialism to set up and maintain. When I first started my business, I immediately chose to use the spreadsheet software I already had as part of my Microsoft suite, as I could customized it to match my own requirements. Other companies also produce their own spreadsheet software, and some of these are available to be used ‘in the cloud’ such as Google Docs.
Personally I prefer my software on my own PC, as I have more control over its access, and do not need to remember passwords, or worry over being hacked.
Step 1: Set Up
When you first set up your spreadsheet, it is worthwhile researching the expenses you need to record for your tax return. This is research you need to do yourselves rather than relying on 3rd party information.
A basic workbook will have a number of separate worksheets which can all be automatically cross referenced. The first sheet can be a debit sheet to list all of your expenses, the 2nd a credit summary sheet to list your incoming payments (each client will only have one listing per year on this page), a sheet for each client detailing the type of work, costs and payments, and I keep one sheet to record mileage.
For those of you who may not be familiar with spreadsheet software, each box is called a cell, and each cell has a reference number in the same format as map references. The first cell will be A1, the one next to it will be B1 and the one underneath B1 is B2. Calculations in Excel also start with an = sign rather than it appearing at the end.
Step 2: Expenses
Before creating an expenses or debit sheet, I have found it useful to have the basic breakdown of profit/loss etc. at the top of the first page. This helps you to see your total expenses, credit and overall profit or loss. Keeping totals at the top of the sheet rather than at the bottom as with traditional books, ensures it is easy to access, and prevents lots of scrolling downwards. (See Fig 2.)
You only need to use the headings that are relevant to you, and your tax office. For instance in the UK there are tax headings for the cost of renting/purchasing, running and maintaining business premises, as I work from home these are not relevant to me and so do not appear on my spreadsheet. The general headings I use are: advertising, web/email, subscriptions, professional memberships, payment fees (from PayPal etc), supplies (includes, paper, ink, binders etc.), mileage and CPD. You can always add to this later if necessary.
Above each column heading create a Total using the sum formula =SUM(D5:D350) (where D is the column, and the numbers are the relevant rows). To make sure I have plenty of room for adding expenses, I prefer to make the last cell quite far down (i.e. 350).
In the example below the total spent on advertising is £159.20. One a formula has been set you can replicate it to other columns or row by dragging the bottom right hand corner of the completed cell.
The total debit is £414.70 which is the total of each column, and is calculated using the formula =SUM(D3:K3). If you do add further columns at a later stage remember to change the K3 to the correct cell reference. (Credit and Profit will be covered later in the guide.)
How you personalise your spreadsheet is up to you. I prefer to have the headings in bold and colour code them: red for debit total, green for credit and profit, and blue for expenditure headings.
You will see on the example that it appears to have been divided into quarters. This is a system called ‘freeze panes’ which will allow you to scroll down and across the sheet without losing the headings or the details of the expenditure sheet. To format your sheet in this way click into the first cell that can contain a price (in this case D5), then click on the Freeze Panes option. In MS Excel 2010 this is on the View toolbar (See fig 3).
Now you will find that part of the area in the example below will stay in place but the rest can be scrolled around. (Note that the rows jump from 4 to 8 and the columns from C to E).
Step 3: Mileage
Charging for mileage and associated travel expenses is often one area that can be forgotten. If you have included it with your research price, then you do not need to have a separate record, however if you charge for mileage, road tolls and car parking in addition to the research cost, you should include it in your expenditures. This section can also be used if you have accommodation stays.
Working out the cost per mile depends on different factors such as engine size,
The UK has a standard mileage price guide for employees using a company vehicle which is dependent on the fuel type and engine size, and can be found at www.gov.uk/government/publications/advisory-fuel-rates/how-advisory-fuel-rates-are-calculated, professional researchers in other countries will need to research their own guidance.
In a new sheet called mileage or travel, you can created a record for each journey detailing the date, start and end points and the total number of miles per journey. At the top of the sheet remember to include your totals, and here you can make a note of the price per mile you are charging.
In a column for the total mileage cost of each journey use a formula that will multiply the total number of miles by the price per mile. In the example below this is: =D4*$E$1 where D4 is the cell for the total mileage for that journey and E1 is where the price per mile is. The * symbol means multiply. The $ signs around E1 ensures that E1 is always used in the calculation even when you replicate the formula for other cells. This is called Absolute Referencing.
To copy (replicate) this formula to the cells below it, simply drag the
It is not recommended that you change the price per mile during a financial year, but if you have to add the new price into a different cell and use the new cell in future formulas. If you simply change the original cell then all of the previous calculations will automatically change to the new price.
To include the total travel cost calculated on this sheet onto the first sheet, type = into the relevant cell on the 1st sheet, and then click into the total price cell on the travel sheet. The formula will be similar to =mileage!I1 where mileage is the name of the sheet and I1 is the cell reference. (See fig 6). This cell will now be updated automatically when the mileage sheet is added to.
Step 4: Client Accounts
I recommend that you have one sheet per client and each sheet should detail the financial transactions as they occur.
As with all of the other sheets, the totals are at the top of each sheet. This sheet can also contain a total owing cell you can quickly see if that client has paid in full or not. I also include the invoice or receipt number alongside each relevant charge or payment.
If you wish you can create a client template sheet which contains all of the formulas, and which you can copy for each new client. I find this easier than copying an existing client sheet and then removing all of the information. To create a copy of the template right click on the tab and choose ‘move or copy’. Then decide where you want the new sheet to be placed and make sure you tick the create copy box. You can then change the name of the tab by double clicking on it.
How you order your sheets is your choice, but I prefer to keep my existing clients towards the front of the sheets and the completed work further along, as it makes it easier to access the relevant record. You can drag worksheet tabs to your preferred location, using ‘click and drag’.
Step 5: Income
The sheet should be before your clients’ pages for easy access, and I prefer it after the debit and mileage sheets, and it will list the basics for your income.
I prefer to breakdown this into how payment was made – cash, bank transfer/cheque or PayPal etc., as each payment type determines where the money was deposited. Being on the income sheet also makes it easy to see at a glance if the bill for each client has been fully paid, rather than trawling through the individual sheets.
Most of the formulas on this sheet are linked to other sheets in the same way as the mileage/travel link does. The final bill amount is linked to cell C1 and the received payment is linked to cell D1 (see Figure 9). This means that once the links are in place for a client you simply need to update the client’s sheet and the changes will be reflected on the income sheet.
The Outstanding Bill in each row will be =D6-SUM(F6:H6), where 6 is the row number. This can be inserted once and then replicated down as required. (See below).
Incomes from one off presentations/magazine articles etc. do not need a client sheet so are added straight to the income sheet.
In the example below the final bill and payment totals for the two clients are linked to the totals on their sheets, whereas the presentation and teaching incomes have been added in manually.
The most complex part of this sheet is the paid column which uses a formula and an automatic conditional formatting system. This will allow you to see the words Yes, No and if No then the cell is colored in red. This cell also needs to copied to the one beneath it ever time you enter a new income source.
The formula to show Yes or No is =IF(D6=SUM(F6:H6), “Yes”, “No”) where D6 is the relevant final bill and F6:H6 (F6, through to H6, includes G6) is the payments already made. (Note: the quotation marks tell the system it is a word.)
Conditional formatting (on the Home menu, choose ‘New Rule’) on the cell tells the software that if “No” appears in the cell then the font should be in bold and the cell coloured pale red. Use the Format button at the bottom of the dialog box to make your selections.
New Financial Year Accounts
The start of a new financial year can be a stressful time, and having to create a new accounting spreadsheet will only add to the stress. So rather than re-inventing the wheel every year, all you need to do is copy and paste the current workbook, change its name to the next year, remove all of the unwanted information from the debit, mileage, and income sheets and delete the clients sheets. You will then have a new workbook just for one financial year rather than one spreadsheet that tries to cater for every year.
If you would like a copy of a blank template, you cna contact me via any of the options on my contacts page.
Excel Tutorial: A Comprehensive Guide to Excel for Anyone https://www.youtube.com/watch?v=8L1OVkw2ZQ8
Help with Excel Functions and Formulas. www.excelfunctions.net
Microsoft Excel Help. https://support.office.com/en-us/excel
Microsoft Topics. https://edu.gcfglobal.org/en/topics/excel/