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

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

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

PowerPivot vs Data Model    1 thanks

Excelcrafter | | Permalink

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."

shurst's picture

Hi Excelcrafter - many thanks

shurst | | Permalink

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.