Excel bookkeeping is usually suitable only for small, simple businesses; but with the cost of a standard software package usually exceeding £300, spreadsheets are often worth the time spent in designing them and the added flexibility. This section will demonstrate how to
analyse bank transactions using Microsoft Excel.

Example of Bookkeeping by Excel

Bank transactions In the following example, a business with an opening bank balance of £8,657.33 has four bank payments and three bank receipts as follows:

Payments

Goods for resale 1,000.00


Salaries 3,000.00

Rates 344.50

Goods for resale 500.00

Receipts

Sales 4,500.00

Interest 45.00

Sales 2,000.00

It designs a spreadsheet with three worksheets: bank payments, bank receipts and bank summary.

The bank payments sheet is set out as follows:

Excel Bookkeeping for a bank voucher

 

Cell C5 contains a formula which is copied to all the other cells in the range C5 to E8: =IF($85=C$2,$A5,0)

The effect of this is that the number entered in column 8 dictates the column (C, D or E) into which the expenditure is analyzed. The formula is saying, ‘If 85 is the same figure as C2, I will copy A5 to C5; if not, I will enter zero.’ 

The ‘$’ sign in a formula is a useful feature when copying and pasting the formula to other cells. To give a simple example, if cell J6 contained the formula: =J3+J4

and this was copied and pasted to L 10, the formula in L 10 would normally read: =L7+L8

If, however, J6 contained the formula: =J$3+J$4

and this was copied and pasted to L 10, column J would change but rows 3 and 4 would not, resulting in: =L$3+L$4

The bank receipts sheet is set out as follows and on the same principles: 

bank receipts sheet in excel

Finally, the bank summary sheet would look like this: 

the bank summary sheet

The receipts and payments totals are taken from formula linked to the respective worksheets.

 

Other information

The information gathered from the bank transactions could feed into an extended trial balance which is the basis for the final accounts. 

Spreadsheets could also include a summary of VAT transactions, accruals and prepayments, tax computations, schedules of fixed assets, and information to be included in the statutory financial statements such as an analysis of loans and lease agreements, staff costs and segmental analysis. They may assist with ratio analysis, which is important not only for applications for bank borrowing but also for the directors’ report, which for larger companies must now give details of ‘key performance indicators’. These may include the ratios
discussed in Chapter 10; if the financial statements are compiled on a spreadsheet, the ratios can automatically be calculated and updated. Graphs are also a useful feature of most spreadsheets, though the skills needed to compile them are quite advanced. Formulae and figures can also be copied across different worksheets. To get full benefit from using spreadsheets, it would be advisable to take a short course, or read a book such as Teach Yourself Excel/2007 by Moira Stephen.

Questions to test your
understanding of Accounting:

1- Why might the following journal entry be automatically questioned by a good computerized bookkeeping package?

DR VAT control account 30.60 CR Sales ledger control account 30.60

2- Which numbers in Iticale would you expect the following nominal accounts to begin with?

  1. Plant and machinery
  2. Bank loans 
  3. Loans to employees
  4. Cost of raw materials 
  5. Pension contributions
  6. made by company for employees 

3. If VAT code’S’ were selected in Iticale, how much VAT would be added to an invoice for £3,000?

4. Which one of the following will an Excel spreadsheet not normally do?

  1. Round figures to the nearest whole number. 
  2. Find the cells which are affected if a number is entered in a particular cell.
  3. Link to Revenue and Customs’ website and automatically update for changes in tax law. 
  4. Calculate the number of days between two dates.
ACCOUNTING INSTANT TIP

When entering an item into the computer, it is easy to get everything else right and then post the item into the wrong accounting
period. This will make not only this year’s accounts wrong, but also last year’s accounts.