David Carter's tutorial uses a sample database enabling companies to learn how to improve their sales orders and stock control which runs through an accounts package written in Access by live-linking Excel direct to their Access database. His analysis also utilises pivot tables. This is an introduction to a series of four tutorials.

The fact is that companies are still reliant on Excel to run sales and forecasting. The finance director would love to pull all these elements of business technology together and make the system work for him.

Here I show how to pull data together using pivot tables (and a series of independent tutorial on those too) and an Access database. It is based on a fictional company, Northwind Traders and all the software for the tutorials is available for free.

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 2,000 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. You can access the first tutorial in Boardroom immediately and the other three will follow in successive weeks.

1. Work out a data map of your accounts/ERP package [1]

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.

Continued on next page

Continued

In the following weeks these tutorials will become available:

2. Construct an analysis-ready file using MS Query [2]

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.

3. Analyse the sales data with pivot tables [3]

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.

4. Add data that isn't there [4]

When producing reports from an accounts/ERP system you will always find that there are items of data you need which aren't stored anywhere 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.