Sales Analysis With Excel

The CAPITAL Series 7 reporting system can export the contents of many reports as data files which can be read into other applications, such as Microsoft Excel. Headings, subtotals and totals are automatically stripped from these data files in order to avoid problems when manipulating them in external applications. Subtotals and totals can be added back into the data using tools such as Excel. This article describes some of these basic techniques.

This short tutorial refers to Microsoft Office 97.

How To Load Your Data Into Excel

1. Start Excel and select Open from the File menu.

2. Where you see "Files of Type" scroll through the list until you see "dBase Files".

If this option does not come up, you may need to install some additional "filters" from your original Excel CD. This depends on the version of Microsoft Office that you are running.

When you print reports to file and intend to import the resulting data into Microsoft Excel or any application that supports DBF (dBase) files, you should select "dBase" as your export option. DBF files are easier to work with than ASCII files if you have the option to work with either.

How To Sort Your Data In Excel

1. Decide how you want to sort the report. Click on the column heading that you want the report sorted by. The entire column should be highlighted after doing this.

2. From the menu select Data|Sort.

3. Excel will prompt you to expand the selection. You do want to do this. This option should already be selected so just press the Sort button.

Keep in mind that Excel allows you to sort and then subsort multiple columns. So you could sort a sales analysis report by stock group and then by every location within that group.

4. At this stage we'll just sort by one column so click on OK.

The spreadsheet is now sorted.

How To Subtotal Data In Excel

1. Click on any cell in your spreadsheet in order to unselect any selected columns.

2. From the menu select Data|Subtotals.

3. Where you see "At each change in" change this to the column you wish to subtotal on, when the contents of the column changes.

In other words, if you want subtotals by stock group, you would click on the stock group column. Each time the contents of the stock group changes, Excel will insert a subtotal. Be aware that this only makes sense if you have also sorted your spreadsheet by stock group. So your subtotal column selection is almost always the same as your sort column selection.

4. Where you see "Add subtotal to" untick all ticked entries you are not sure of. Excel guesses, but it often guesses wrong. Tick the columns you would like to see subtotals for.

5. Press OK.

Your sales analysis data is now sorted, subtotalled, and totalled.

21/06/2001