How it works

Nowadays we can use the electronic pages of a spreadsheet program if we have access to a computer. As long as you know how to write on them you can simply follow the instruction in the chapters of this book in the same way as you would for paper pages.

There are various spreadsheet packages on the market, but the differences in the way they work are not great. If you can use one you will be able to grapple with another. The examples used here relate to Microsoft Excel.

Writing on spreadsheet pages

Just as you move to the appropriate spot on a paper page and write on it with a pen, with a spreadsheet page you move to the spot with the direction keys or a mouse and type the information through the keyboard.

It’s as simple as that.

Adding them up

You will need your standard and formatting toolbars showing. If they are not, click on the view menu and choose the toolbars option. Next, click in the standard and formatting boxes and then click ‘OK’.

When you come to adding the columns up draw the lines of the answer boxes, using the buttons on the formatting toolbar. Use the arrow key to select a single line for the top and a double line for the bottom. The column will add itself up if you click on the answer box and then click the Σ button on the standard toolbar.

Calculating the c/d balance

Where you have both debit and credit columns you will need to calculate c/d balance. To do this you have to add both sides and take the smaller figure from the larger. Then you enter the difference in the smaller column. That means you’ve got to jot the two totals down somewhere. Here’s how you do that. First make room for a c/d balance by inserting a row if necessary above the answer boxes.

Next insert a column next to the debit column being added, by clicking on the insert menu and choosing the columns option while the cursor is to the right of the column. Enter in it the instruction to sum the column immediately above and to its left. To do this click on the Σ button. ‘= SUM ()’ will appear in the box. Enter in the brackets the pair of cell references which bound the column you wish to add. Separate them with a colon. Example: E3:E6.

Do the same with the credit column. Example: L3:L6. Here you will find you have to replace a cell reference already showing.

Instructions don’t show up on the page – they’re invisible – but the answers which they make do. This one is just a jotting though; you don’t want it to show, so you must deliberately hide it. You can do this by clicking on the format menu and selecting the columns option, then clicking on hide, while the cursor is in the column concerned.

Next, enter the ‘If’ command. To do this click on the f* button on the toolbar and choose the ‘If’ button while the cursor is in the last space above the total box in the debit column. Click the button labelled Next.

Type the first of the two cell references in which you put the column addition formula (i.e. the first of the hidden cells), followed by a < sign, and this is followed by the second of those cell references. Example F7 < M7. In the second box down, marked ‘value if true’ type the reverse of this. This time the two cell references should be separated by a minus sign instead of a < symbol. In the third box down simply type a space (press the space bar once). Click the button labelled Finish.

Now repeat this on the credit side in the cell above the answer box, reversing the formula.

There is no point in showing zeros in these cells, so if they do appear click on the tools menu and select options. Make sure Zero values is not ticked and then press the key labelled OK.

Next, click in the first of the actual answer boxes and then click on the Σ button twice. Do the same in the right hand answer box.

The balance c/d is then transferred to the opposite side after the total box, as the opening figure (Balance b/d) for the next month. To do this on the spreadsheets just type in the space below the total box on each side the formula which has been entered in the c/d balance box on the opposite side.

Click in the last available space in the particulars column (above the answer box line) and then click the function key. Select the If option and then, in the dialogue space, type the co-ordinates in which the subtotals are stored in the hidden columns, separated by a ‘<’ symbol, (e.g. F7 < M7). In the second dialogue space type ‘Balance c/d’. In the third dialogue box type a space (just press the space below once). Click on finish. Now do the same in the space adjacent to this one on the credit side of the sheet, reversing the formula. Lastly, enter the b/d balance narratives. Using the function key, simply enter in the particulars column, below the totals boxes on each side, the exact formula you entered in the diametrically opposite position (i.e. the space above the total box in the opposite column), but substitute the term c/d with the term b/d.

As you type in the formulae they will appear temporarily in the boxes, but will disappear as soon as you press finish in each case.

Configuring a spreadsheet page for day books

It is easy to configure pages which will add themselves up and cross balance for day books. All you have to do is draw in the answer boxes, as you did for the ledger pages. Click on each answer box to highlight it and then click on the Σ button on the toolbar.

Making things easy for yourself

Now you don’t have to go through this each month. You can keep this specimen page without any actual monthly figures in it as a template.

Four steps for creating a template

• 1.Create a single sheet workbook.
• 2.Format it with the titles and formulae.
• 3.Save as a template.
• 4.Enter the folder in which you wish to store it.

Speeding up ledger posting

You can keep all accounts of a single ledger division (e.g. all customer accounts) on the same sheet, one after the other, as the placing of automated summing and balancing instructions will ensure that the accounts do not get mixed up. Each ledger division becomes a different sheet (e.g. sheet 1 = Sales daybook, sheet 2 = Nominal ledger and sheet 3 = Sales ledger, and so on.) A big advantage of doing this is that you can make posting from daybooks to ledger sheets easy, by putting all the sheets involved on the screen at once. The larger your screen the easier this will be. For example:

• Sales daybook.
• Nominal ledger.
• Sales ledger.

Then you can simply use copy and paste across the boundaries of the sheets to do your positing. For example, to post a transaction from the Sales daybook to the relevant ledger sheets, just follow these steps:

• 1.Call up all the relevant sheets on the screen at once.
• 2.Click on the gross invoice value for each entry on the Sales daybook sheet.
• 3.Press ‘Alt’ ‘E’ ‘C’.
• 4.Scroll down the Sales ledger sheet to the personal account of the customer concerned.
• 5.Click on the next available space in the debit column.
• 6.Press ‘Alt’ ‘E’ ‘P’.
• 7.Enter the date in the date column.
• 8.When all the entries have been posted to the Sales ledger accounts, proceed as follows.
• 9.Click on the net total in the Sales daybook.
• 10.Press on ‘Alt’ ‘E’ and ‘C’.
• 11.Scroll to the next available space in the credit column of the Sales account in the Nominal ledger.
• 12.Press ‘Alt’ ‘E’ ‘P’.
• 13.Click on the VAT total in the Sales daybook and press ‘Alt’ ‘E’ ‘C’.
• 14.Scroll to the next available space in the credit column of the VAT account in the Nominal ledger and press ‘Alt’ ‘E’ ‘P’.

Automating depreciation calculations

Asset depreciation calculations can be done swiftly and simply, using Excel’s built in functions.

Straight line method

Click on the ‘*f’ tab on the menu bar (this is the toolbar which always shows at the top of the screen).

• 1.Select the ‘Financial’ option.
• 2.Click, then, on the ‘SLN’ option.
• 3.The following dialogue boxes will appear on the screen: asset value; estimated salvage (scrap) value; estimated useful life.
• 4.Enter the relevant figures and click ‘OK’ to find the annual depreciation figure.

Diminishing balance method of depreciation

• 1.Click on the ‘Insert’ tab on the menu bar.
• 2.Select the ‘f*’ option.
• 3.Then select the ‘DDB’ option.
• 4.The following five dialogue boxes will appear. cost; estimated salvage value; estimated useful life; start of the period; end of the period.
• 5.Enter the relevant figures and click on ‘OK’ to find the depreciation for the asset.

Sum of the years (or sum of the digits) method of depreciation

Follow the same procedure as for the diminishing balance method, selecting the ‘SYD’ instead of the ‘DDB’ option.