IT zone

The Northwind Trader tutorials. By David Carter

If there was one series of tutorials on AccountingWEB that I would like people to work through, it is this one - Northwind Traders.

Most of the other pivot table tutorials are artificial - the data has been specially prepared so that you can use pivot tables on them straight away. In my terminology, the data is "analysis-ready",

But in real life it isn't like that. In real life all your time is spent in getting the data out of the accounts package and into a usable format. Producing the reports themselves takes just a few minutes.

Northwind Traders is as close to real life as we can get. It's a real order processing and stock control system written by Microsoft in Access, containing over 2000 records. Microsoft have generously allowed us to make it available as a download for the tutorial. So you are practising on a data set which is pretty close to the real thing, not the massaged data in the other tutorials.

If you ever get involved in creating a data warehouse in your own organisation, you will follow the same basic procedure as in these four tutorials.

1. Work out a data map of your accounts/ERP package
The first tutorial shows how to download the contents of the Northwind data files, and make a data map of the fields they contain. Basically, it's a searching exercise: you are preparing a register of all the fields you've got to play with.
Improving sales reports at Northwind Traders: Tutorial #1.

2. Construct an analyis ready file using MS Query
Northwind being a transaction processing system, you will find that the pieces of data you need for your sales reports have been "normalised", that is, spread around in various different tables. Your task is now to pull them together into a single table from which you can produce reports.

This is the analysis-ready file, here called a Daybook. You now create it by making joins between the various tables in MS Query. Query is a basic, SQL-like database that comes with Excel.
Northwind Traders Tutorial #2. Creating the Daybook.

3. Analyse the sales data with pivot tables
With the daybook created, the hard work is done. We can now set up this Daybook in Excel on any manager's desktop within the organisation. This tutorial shows how easy it is, and how you can produce fantastic reports in seconds with pivot tables.
Northwind Traders Tutorial #3: Create sales reports with pivot tables.

4. Add data that isn't there
When producing reports from an accounts/ERP system you will always find that there are items of data you need which aren't stored anyhwhere on the database. In the case of Northwind, the Access database doesn't store the cost price of a product, so we can't calculate profit on sales. This last instalment uses VLOOKUP to pull a Cost Price field into the Daybook, so we can finally produce those profit reports.
Northwind Traders Tutorial #4 : Add cost prices and margins.

Summary: List of the Northwind tutorials:
Improving sales reports at Northwind Traders: Tutorial #1.
Northwind Traders Tutorial #2. Creating the Daybook.
Northwind Traders Tutorial #3: Create sales reports with pivot tables.
Northwind Traders Tutorial #4 : Add cost prices and margins.

http://en.wikipedia.org/wiki/Edgar_F._Codd