Preparing Financial Statements using Pivot Tables

I am often asked the question of whether it is possible to prepare an income statement and a balance sheet in Excel. I always reply that of course it is simple to do that providing you know the basic layout of those statements.

This set of notes illustrates that all you really need to prepare an income statement and a balance sheet using Excel are:

· The data: a list of revenues, expenses, assets, liabilities and capital are all you need. In these notes, I will be dealing with the income statement and balance sheets separately

· A Pivot Table: well, in this set of notes, two Pivot Tables but the homework at the end of the notes is for you to combine everything into one list and one Pivot Table

I am assuming that you already know the basics of

  • · Financial statements
  • · Preparing and using a Pivot Table

This is a lengthy note so I have put it into this PDF file for you to download … prep_fin_state_pivtab: work through it at your own pace.

What you are aiming at here is to prepare these Pivot Tables from lists of data

The Excel spreadsheet i used to create the above was corrupted beyond repair by Excel 2010 after I gave it the massively onerous task of adding headers to each worksheet. After all, after only 25 years of development, we’ve got to give Gates and his tribe a fair crack of the whip and admit that adding a header is one of the more advanced functions built into Microsoft Office’s 2010 suite.