Excel tips: Creating financial statements series part 3 – Using basic functionality

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 create financial statements from an extended trial balance (ETB) using Excel’s basic functions, because it is easy to underestimate their value and role as a platform to building on, when we come to use advanced functions and formulas.

Click to download the ETB we sorted in the last article.

The first tab contains the ETB, the second, the year-end adjustments and the third, blank financial statements. 

Hiding columns to prevent errors

There is one final change we can make to our ETB so that its presentation is actually of practical help to us in terms of creating the financial statements. That’s to hide the TB and adjustment columns to ensure that we can’t accidentally link to unadjusted cells.

Highlight columns B to E, right click and select Hide from the drop down menu:

To unhide columns look for a gap in the alphabetical order of the columns, highlight the columns either side of the missing ones, right click and select Unhide from the drop down menu:

Linking up the correct cells

Now we can finally start to create the statements, which is mainly a matter of linking to the correct cells in the ETB and amending the generic proformas to suit our specific needs. 

Start by using direct referencing to link the sales and cost of sales figures between the sheets, as we did in the ETB article:

Then write formulas to calculate the cost of goods sold and gross profit:

Then, add or delete rows as necessary in order to customise the other income and expenses sections. You may want to make another copy of the worksheet so that the SoFP layout is retained.

You can also link the account names and their corresponding amounts then drag both columns references in the SoPL.

Be careful not to include the inventory and to link the office expense, repairs and maintenance account seperately.

Free Excel webinar

Learn how to present effectively in Excel from expert Deborah Ashby. To view the recorded webinar please register your details below

View webinar

AutoSum is a really useful tool to use when adding up a list such as the expenses. Click on the cell you want the total in and then press the AutoSum button on the HOME ribbon. The cells that Excel suggests may not be the ones you want:

So, select the ones you do want:

Check the profit figure on the statement matches the ETB figure before using the same skills and functionality to create the SoFP.

The appropriate lines can be altered by using the borders function:

Highlighting can be maintained though the fill colour function. The selection of no fill can be really helpful if deleting cells has resulted in formatting that no longer works:

Check the closing capital and net assets figures match and then tidy up the rest of the formatting, as in the previous article, with regards to cell size, font size, number and decimal formatting and use of comma separators. Lastly name the tab by right clicking on it and selecting rename:

Use the same process to delete any worksheets that you made copies of but no longer need. 

In summary

You’ll know whether your statements are correct or not based on whether they reconcile with the ETB but you can also click here to download my finalised spreadsheet and compare it to yours.

Further reading:

• Read more tips on Excel here
• Tricky Topics Deciphered: Discounted cash flow
• Study tips: How to prepare closing inventory for financial statements

Gill Myers is a self-employed accounts consultant. She has taught AAT qualifications since 2005 and written numerous articles and e-learning resources.