Tutorial: Sales analysis in Excel 2010/13, part 1

Partner
The Knowledge Base
Share this content
2

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

Please Login or Register to read the full article

Replies

Please login or register to join the discussion.

PowerPivot vs Data Model

Simon,

This series of tutorials has been an excellent introduction to the more powerful and robust data analysis options in Excel 2010/2013. I liked you rather charitable comment regarding the lack of PowerPivot in Excel 2013 (except in Office 2013 Professional Plus): "Perhaps in order to compensate for this, Excel 2013 does include as standard the data model that is a key element of PowerPivot."

Thanks (1)

Hi Excelcrafter - many thanks for your comments - much appreciated. I'm glad you are finding the series of interest. I think putting the Inquire add-in into the Professional Plus edition only (which is I think the case) is an even worse decision. Every single spreadsheet user could probably benefit from the auditing and documentation capabilities of Inquire, so it's a real shame Microsoft see it as relevant only to Professional Plus.

Thanks (0)