In the first part of this Working with accounting data tutorial series we looked at ways to apply some of the new features in Excel 2010 and 2013 to management reporting situations
We showed how the inclusion of the PowerPivot ‘Data model’ within Excel 2013 itself could replace the use of Excel Lookup formulae to give easier, more automatic and potentially more reliable results. The second tutorial looked at how to work with trial balance data data to create flexible monthly reports. Here in part 3, we are going to move away from a trail balance and start looking at sales invoice transactions. We will use the Invoices data from Microsoft’s sample database: Northwind.
Getting at the data
Our data is held in an Access database, so Excel can easily create a dynamic link to the data allowing us to refresh the link to the data manually or automatically, thereby updating all our reports based on the linked data. If you are going to use Excel for data analysis, particularly if you want to work with large volumes of data and complex data structures, then you might find the PowerPivot add-in very useful.
As we have mentioned in the previous articles...
Further reading and tutorials
- Tutorial 1: Working with accounts data in Excel 2010 and 2013
- Tutorial 2: Create a flexible P&L in Excel 2010/2013
- Excel 2013: Where the productivity gains are
- Excel 2013: Up close and personal
- Excel 2010: Simon Hurst’s greatest tips
- Excel FAQs: Working with accounts data
- Improve your reporting skills with self-teach tutorials
- Improving sales reports at Northwinds Traders (first of a 2007 tutorial series by David Carter)
- AccountingWEB pivot table tutorial archive