Excel tips: Creating financial statements series
- Part 1 – Creating an extended trial balance
- Part 2 – How to avoid errors when using the sort functions
- Part 3 – Using basic functionality when creating financial statements
Working with spreadsheets can make accounting tasks much more efficient and reliable once we have learnt to use the required functionality. However, in order to achieve that we have to combine accounting knowledge with spreadsheet skills and for many of us, bringing both areas together is a real challenge. In this article we’re going to work through how to use basic Excel functions when creating an extended trial balance (ETB). It is easy to focus our attention on Excel’s advanced functions and formulas and underestimate the value of having a good working knowledge of the basics. Download the raw trial balance (TB) for GM Consultancy, which has been exported at year end from accounting software. In the first tab we can see that the TB balances and there is no suspense account to clear. In the second tab we find some year-end adjustments that need incorporating. Therefore, we can start by formatting three more pairs of columns for the adjustments, Statement of Profit or Loss (SoPL) and Statement of Financial Position (SoFP). We can use the copy and paste functions to replicate the debit and credit headings already present. Note that once we have copied cells B5 and C5 they are highlighted with a dashed green border,
and whilst this is still highlighted we can paste multiple times:
Once the paste has been applied to cells in columns H and I, press the Esc button, on your keyboard, to clear the copy. The Esc button can be used to clear any cells highlighted in this way and is really useful to remember when you click on a cell by mistake in the middle of writing a formula and it all starts to go wrong!
Merge and centre can be used to add overarching headings:
Wrap text can be used to make the text fit when it is too long for the cell width:
However, be aware that the combination of merging cells and wrapping text will result in the wrap not appearing to work or the cell height being inappropriate. Therefore don’t be put off by making manual adjustments.
The height and width of rows and columns can be adjusted by clicking below the row in question or to the right of the column, the cursor will change to a black cross with arrow heads that indicate the direction in which changes can be made, drag the cursor and the change will occur once you stop:
Double clicking, when the cursor is in the same position, will automatically adjust the height or width of rows and columns to fit the contents of the cells affected. However, again be aware that this will not work if the text in the cells is wrapped.
If you click on cell A16 then you will notice that a number of boxes on the Home ribbon become highlighted:
This tells us what formatting has been applied to this cell, including Wrap Text. Therefore we need to click on Wrap Text to remove it, then double click to the right of column A to automatically adjust the column width and stop the text from overflowing into column B.
Now we have created a basic structure for our ETB, additional rows can be added for the year-end adjustments. Cells can be inserted, deleted and formatted in a number of ways using the buttons on the Home ribbon. However, if you highlight the row below where additional one(s) are required, a simple click or clicks on the Insert button produces the desired result. In this case we need five rows for the adjustments and profit/loss calculation.
Making any adjustments to the pre-existing text, after copying and pasting, can be quicker than re-typing the account names required.
At this stage it is often useful to scroll the screen to a sensible point, we don’t need to see the titles, and then freeze some rows as it is really useful to always be able to see the headings. In this case, the most appropriate options is clicking in A6 then selecting freeze panes:
Now we can enter the year-end adjustments linking cells wherever possible. This is because if we need to make changes in the future, a change to the original figure will then automatically occur in all the cells it is link to and will consequently improve accuracy and efficiency. This is achieved through direct cell referencing and can be combined with linking sheets.
To add the adjustment for the irrecoverable debt:
- Type = in D26
- Click into the Year-end adjustments tab and select B1
- Press your Enter button to return (Do not try to manually return to the TB tab and remember the Esc button if you need to start again!)
- Type = in E7
- Click on D26 and press Enter
As we all know the totals of the adjustment columns should balance. There are already totals in place for the TB columns that use SUM formulas we can drag along the row and apply to the other columns:
Be mindful that formulas may not cover the full range of cells required if you have inserted rows at the end of the original data range, as we did in this example. Therefore the formula in B31 needed amending from = SUM(B6:B25) to = SUM(B6:B30) before it was dragged.
Direct cell referencing and formulas should be used to extend the TB into the financial statement columns:
Once the extension is complete, the formatting of the worksheet can be improved because currently there is a mixture of raw data and Excel’s default settings.
The whole of a worksheet can be selected by clicking on the triangle in the left hand corner where the rows and columns meet:
From there you can change the font type and size. Changes made in this way might effect cells that you would prefer to be different, such as the titles or headings but it can be quicker to apply mass changes and then re-format cells to be highlighted, than do each area separately.
Now let’s delete all the 0.00 entries and reformat the cells containing numbers to currency but without any pence:
Lastly a formula should be used to calculate the profit or loss for the year,
and double entered into the SoFP using a link:
In the next article, we will look at avoiding errors using the sort functions within Excel to produce the financial statements from our ETB. In the meantime download my finalised spreadsheet and compare it to yours.
Read more tips on Excel here
Browse the full range of AAT study support resources here
Gill Myers is a self-employed accounts consultant. She has taught AAT qualifications since 2005 and written numerous articles and e-learning resources.
