David Carter's first tutorial on pivot tables was the most visited part of AccountingWEB last year, proving the value of an in-depth knowledge of this indispensable Excel tool.
His second guide looks at importing sales data in a Text file format using the software's Import wizard. As before, the tutorial will give you practical tips on how you can use pivot tables on a daily basis. [If you want the Excel 2000 or Excel 2003 version of this tutorial, click here ].
Simply follow the instructions below to fully understand the power of the pivot. Tell your friends and colleagues about our excellent guide - and watch out for future tutorials on AccountingWEB, the everyday resource for accountants worldwide.
* * *
INTRODUCTION TO THE SALES ANALYSIS TUTORIAL
In this tutorial you will learn how to import data in a "Text" file using Excel's Import wizard, then analyse it via pivot tables.
To get started, you should first download the STARTUP file. This is a Word file (one page). When it appears on your screen, select File - Print to print a hard copy, then return to this point in Accountingweb. To download this file now, double click on Startup.
Using the instructions in STARTUP, now download the two files which you will be using in the tutorial. They are the Word file and SALANAL.TXT. If you cannot download the file, e-mail Richard Norton on [email protected].
If your own accounts package has a Report Generator program, you should be able to use it to export similar data into a Text file, which Excel can then read.
'Text' is a lowest common denominator format which all computers can recognise because it contains no special characters, except the universal ones such as Carriage return, Tab, or Commas. Files in Text format are sometimes also called 'CDF' (Comma Delimited Files), or 'CSV' (Comma Separated Values) or 'ASCII' files.
Don't worry too much about the technical terms. Excel recognises all these formats and can 'import' them automatically.
You will see that pivot tables allow you to analyse the sales invoices in pretty well any way you want - sales by customer, sales by product group, sales by period, gross margins by product group, etc etc.
If you have ever heard of the term 'Data Warehouse', then you might be interested to know that that is what you are going to be doing for the next half hour or so ' setting up and interrogating a data warehouse.
This tutorial has been developed using Excel 97, and tested in Excel 2000 and Excel 5. Variations from Excel 97 are noted in square brackets [ ] when they occur. It should take you about 30-45 minutes. If possible, it is a good idea for two people to work through it together, one reading the instructions, the other operating the keyboard.
1. IMPORTING THE TEXT FILE INTO EXCEL
Start up Excel. Then: File - Open
Files of Type: (at the bottom) change from Microsoft Excel Workbook to Text Files
Choose SALANAL [Excel 5 = SALANAL.TXT]
The Text Import Wizard, Step 1 of 3 appears.
Excel should have determined that the Data Type is Delimited, not Fixed Width. If it has, click Next.
Text Import Wizard - Step 2 of 3 appears.
There should be vertical lines between the columns (Excel has identified the tab marks as delimiters).
In addition, you can see the field names at the head of each column ' TRANSN, INVNO, DATE, ACCNO etc.
Click Next. Step 3 of 3 appears.
The TRANSN column is highlighted. At the head of the column is the format in which it will be imported ' the 'General' format. Excel assumes GENERAL for all fields.
In the top right of the screen are the possible Column data formats - General Text Date & Skip (i.e. do not import).
General format is OK for all your fields except DATE. You will need to change the date format.
In the Column Data Format box the 'Date' option should have the default value DMY against it. This means that Excel is expecting that any Date fields it finds in the text file will be in Day/Month/Year format, which is the UK standard.
If DMY is not the default offered, select DMY from the list so that it is.
[If your default value is not DMY but MDY, this means that your PC is set to express dates in month/day/yearUS format. If you are a UK user you need to change this now to UK format. Cancel the Import wizard and come out of Excel. Close down all other programs, because you will have to reboot your machine. Then select:
Start - Settings - Control Panel - Regional Settings. Change from English (United States) to English (United Kingdom).
Reboot the PC as or if instructed. Start the tutorial again. This time the Import wizard should offer DMY as default]
Click on the 'General' heading of the third column, DATE. The column is highlighted
At the top right click Date. The heading of the DATE column should now read DMY.
Click on Finish. Wait a moment. The file is imported into Excel.
2. TIDYING UP THE FILE
A Text file imported from another system will have lost most of its formatting. Therefore it first needs to be tidied up:
Highlight the first row of the spreadsheet by clicking on the 1 to its left. Then click B on the toolbar to make the column headings Bold.
Highlight the entire sheet by clicking on the top LH grey brick above the 1 and to the left of A.
Double click between column headings A and B. All the columns are widened to their best fit.
Highlight the second row of the spreadsheet by clicking on 2. Then select: Window - Freeze panes
Before going any farther, copy the file into a suitable directory and convert it to Excel format, as follows:
File - Save As. Leave the file name as SALANAL but below the filename change as follows.
Save as Type: Change from Text (Tab delimited) to Microsoft Excel Workbook. Save.
3. WHAT THE FIELDS MEAN
This is a set of records taken from the sales invoice_detail file within the accounts package. The fields are:
TRANSN = Transaction Type (i.e. Invoice, or Credit Note in this case)
INVNO = Invoice or Credit Note number
DATE = Invoice or Credit Note Date
ACCNO = Customer Account Code
PRODGROUP = Product Group
QTY = Quantity Invoiced
S.P. = Item Selling Price
NET = Line Net Value (i.e. Qty x S.P.)
C.P. = Item Cost Price
COST = Line Cost (i.e. Qty x CP)
MRGN = Line Margin (i.e. NET ' COST)
SALES EXEC = our Sales Representative
So, taking the first two lines as an example, on 4th April 1995 we issued sales invoice number SIN001649 to customer no. R001, Rolls Royce Ltd, for two products ' HP51625A Colour Print Cartridge, quantity 2, at £18 each, and a US Robotics Sportster 28.8 External modem, quantity 1, at £228 each.
4. MOVING AROUND THE FILE
Scroll down through the first 100 or so records to see the sort of data the file contains. Then:
Go to the bottom of the file: (tip: keyboard shortcut = hold down Ctrl key, then press Down arrow)
There are 4,188 rows (i.e. 4,188 sales invoice detail records)
Go to the bottom of column L, the NET column.
Autosum the column ' remember to press return. It should come to £5,442,458
Make a note of the column total. Then remove the total..
Return to the top of the file, cell A2. (keyboard shortcut = hold down Ctrl, then press 'Home' key.)
5. GENERATING A PIVOT TABLE
The cell pointer should be on A2 (or anywhere within the data will do)
We'll build up the pivot table step by step. From the main menu at the top of the screen, select :
Data - Pivot Table Report [Excel 2000 = Pivot Table and Pivot Chart Report, Excel 5 = Pivot Table]
The Pivot Table Wizard, Step 1 of 4 screen appears. Click on Next
Step 2 of 4 The Range box should say: $A$1:$P$4188.
[Earlier versions of Excel sometimes didn't pick up the range automatically. If the Range box is blank or says 'Database' type in $A$1:$P$4188].
Click on Next.
Step 3 of 4: The COLUMN-ROW-DATA box appears. To the right are all the column headings of your spreadsheet
[note if you are using Excel 2000, you must first click on Layout]
Drag and drop these headings as follows:
PRODGROUP into the ROW area
NET into the DATA area
[When dropped into the DATA area, NET should now say 'Sum of NET'. If it says 'Count of NET', double click on Count of NET. A box appears. Change 'Count' to 'Sum']
Now click on Finish. [if you are using Excel 2000, OK, then Finish]
A list of Product Groups appears with a total beside each one. At the bottom of the sheet is the total you noted down.
From the 4,188 records in the SALANAL worksheet Excel has generated a pivot table summarising them by Product Group.
Look at the bottom left of your screen. To create the pivot table, Excel has generated a new worksheet to the left of SALANAL. This sheet is probably named Sheet1.
Click back onto the SALANAL worksheet to confirm that the original sales invoice database is still there.
Now right click back onto the new sheet that contains the pivot table. Rename this worksheet PIVOT.
6. SORTING THE PIVOT TABLE
You would like to rank the product groups in order of value. To do this:
Click anywhere in the Total column, eg on cell B10
Click on the ZA icon. The Product groups are sorted into Descending order, with PC's at the top.
[if you don't have the ZA icon, use the AZ icon instead]
Now sort the pivot table back into Product Group order, as follows:
Left click on the grey PRODGROUP field button in cell A2. The column is highlighted. [not in Excel version 5.0]
[Note to Excel 2000 users. Cell references within pivot tables are usually two rows lower in Excel 2000 compared with other Excel versions. Cell A2 therefore usually signifies cell A4, and so on].
Click on the AZ icon. The product groups are sorted into alphabetical order, ascending.
7. ANALYSING SALES BY CUSTOMER
You would now like to view the data differently, this time looking at sales by customer.
Click anywhere within the pivot table, then right click the mouse.
A menu appears, with 'Format Cells' either at the top or in the middle.
From the menu, select Wizard. [in Excel 5 or Excel 7 = Pivot Table]
The COLUMN-ROW-DATA box re-appears. [Excel 2000, select Layout first]
Drag and drop PRODGROUP anywhere out of the ROW area.
Drop ACCNO into the ROW area.
Then drop CUSTNAME below ACCNO in the ROW area. Then Finish.
The pivot table now displays sales by Customer account number and name. But the screen is not very clear as the ACCNO is totalling. These totals need to be removed:.
Double right click on the grey ACCNO field button, cell A2. The menu appears
Select: Field [Excel 2000 = Field Settings; Excel 5 or Excel 7 = Pivot Table Field].
In the Subtotals area on the left, change from Automatic to None. OK
That looks better. Note that the total is again 5,442,458.
Now left click again on the ACCNO field button in cell A2. The column is highlighted. [not in Excel 5]
Centre the account codes by clicking the Centre icon.
8. BREAKING DOWN SALES BY MONTH ('GROUP AND OUTLINE')
The sales invoices were dated from April to September. Now to break the totals down by month:
Right click within the pivot table. The menu appears. Select:
Wizard [Excel 5 or 7 = Pivot Table] The COLUMN-ROW-DATA box re-appears. [Excel 2000, select Layout first]
Drag and drop DATE into the COLUMN area. Finish. The pivot table is recalculated.
Across the top there is now a series of dates. The dates need to be grouped by month. To do this:
Double right click on the grey DATE field button in cell C1. The menu appears.
Select: Group & Outline - Group The 'Grouping' Dialogue Box should now appear.
[If it doesn't, but you get the message 'Cannot Group That selection', see the paragraph below].
Select Months and Years (at the bottom of the list under Quarters). OK.
The pivot table now displays the Net sales by month.
[Error message - 'Cannot Group That Selection' The Group command is temperamental. I get more trouble trying to group date fields than all other problems put together. And if it does fail, the only feedback you get is 'Cannot Group This Selection'.
There are two likely sources of error. First, your default date format in Windows is set to US month/day/year format, whereas the date fields in your imported data are expressed in UK format day/month/year. To check this, take a closer look at the way your dates are currently formatted in Excel.
If they are US, with the month first, you need to reset your Regional Seetings in Control Panel, as described in section 1 above, then restart the tutorial over again. The second possibility is that one or more date fields within your data are either blank or not formatted correctly as dates.
First thing to do is to go into the SALANAL worksheet, click anywhere on the DATE column BUT NOT THE COLUMN HEADING ITSELF, then click the AZ icon. This sorts the database into date order. Look at the first and the last dates in the worksheet as this is where faulty dates will appear. Correct any errors, then Refresh Data.
Second, check that the Range is not looking at any blank rows, which will contain blank date fields. Do this via Wizard ' Back, which should show you the Range $A$1:$P$4188. If it says $A$1:$P$4189, then you need to change it to $P$4188.
Even when you've corrected all the errors, when you Refresh Data you may find you still get the 'Cannot Group This Selection' message. In this case delete the pivot table and build it again from scratch and it may work this time. Finally, I sometimes find that when I put a Date field in the Row area, it will not Group, but if I put it in the Column area, it will.
If dates won't group in the Row area, but will in Column, group them first in Column, then go into the wizard and move Years/Date out of Column and into Row. You will find that the Grouping stays OK.]
9. FORMATTING NUMBERS WITHIN A PIVOT TABLE
The sales figures would be easier to read if thousands were separated by commas. Therefore:
Right click on any number within the pivot table, then from the menu select: Field.
[or Field Settings, or Pivot Table Field]
The 'Pivot Table Field' dialogue box appears. From the list of options on the right, select:
Number. The 'Format Cells' box appears.
From the 'Category' list, select: Number [Excel 5 ' select format #,##0; [Red]-#,##0]
Then: Decimal Places = 0 - Use 1000 separator, YES - -1234 in red OK OK
The pivot table now includes commas separating the thousands.
10. PRINTING THE PIVOT TABLE
Let's print the pivot table showing monthly sales by customer.
First, click the Print Preview icon to see the finished report on screen [or from the menu, File ' Print Preview]
This report would look better if printed in Landscape. Therefore:
Select: Setup - Page - Landscape - OK Then,
Select: Setup - Sheet - check the Gridlines box - OK
Print to print.
11. ANALYSING SALES BY MANUFACTURER
So far we have analysed sales by product group and sales by customer. Now we will analyse them by manufacturer, then see which manufacturer's products are selling the best.
Right click within the pivot table, then from the menu select Wizard [Excel 5 or 7 = Pivot Table]
The COLUMN-ROW-DATA box re-appears.
Remove ACCNO and CUSTNAME by dropping them anywhere outside the table.
Drag and drop MANUFR into the ROW area.
In addition, drop PRODGROUP onto the PAGE area.
The pivot table is recalculated, this time showing sales by manufacturer.
[The list of manufacturers should be in alphabetical order, starting with 3COM. However, sometimes DEC appears first ' don't ask me why. If it does, sort the manufacturers alphabetically as follows:
Left click on the grey field button MANUFR in cell A5. The column is highlighted.
Click on the AZ icon. The column is sorted and DEC goes to its proper position.
12. ADDING A FURTHER DIMENSION WITH PAGE FIELDS
So far the data is presented in two dimensions, by rows and by columns. However, you can add a third dimension of analysis by using the PAGE field.
For example, we might wish to see which brand of PC is selling the best. Therefore:
PRODGROUP is the Page field in cell A1. Click on the down arrow in the right of cell B1.
(All), COMMS, INTERNET, MEMORY, MONITORS etc appear. Highlight PC'S and click.
The pivot table now shows only those manufacturers who sell PC's.
Total PC sales were £2,813,885. The best seller was DEC, with sales of £1,026,424.
Now click on the B1 down arrow again and look at the sales for PRINTERS.
The total value of sales by manufacturer is shown (£243,132). But which models sold best?
To find out, right click anywhere within the pivot table. From the menu select Wizard
Drag PARTNO and drop it under MANUFR in the ROW area.
Drag DESCRIPTION and drop it under PARTNO in the ROW area
In the ROW area, double click on PARTNO. The Pivot Table Field box appears
Change Subtotals from Automatic to None. OK Finish
The pivot table is redisplayed, but this time shows sales of the individual models of printer. Widen column B if necessary.
[note: if you get a message 'Microsoft Excel cannot make this change because there are too many row or column items', your machine does not have enough memory to accommodate both the PARTNO and DESCRIPTION fields within the pivot table. Try just adding PARTNO only and see if that works.]
13. SHADING SUBTOTAL LINES AND COLUMN HEADINGS (Excel 97 and Excel 2000 only)
In Excel 5 and Excel 7 any formatting applied to a pivot table was lost as soon as you recalculated the pivot table or refreshed the data. It was only with the introduction of the 'Select' option in Excel 97 that formatting would 'stick' when the pivot table was recalculated.
This section shows Excel 1997 and Excel 2000 users how to apply shading to the 'CANON Total', 'EPSON Total' subtotal lines in the table. Excel 5 and Excel 7 users should skip to the next section.
Click on 'CANON Total' in cell A15. All the sub-total lines are highlighted
Move the mouse pointer to the grey 15 brick opposite cell A15, 'CANON Total'. Move it gradually to the right until it assumes the shape of a thick horizontal black arrow. Then left click the mouse. All the sub-total lines are highlighted
To add shading, click on the yellow 'Fill Color' icon in the bottom row of icons, second from right (the one that looks like it's pouring a can of paint).
Now click anywhere to remove the highlighting. The 'Total' lines are all shaded yellow.
In addition, we'll shade and centre the monthly column headings, as follows:
Click on the DATE field button in cell E3. The column headings 'Apr' to 'Sep' are highlighted.
Apply the yellow Fill Color icon again. Or if you prefer another colour, click on the down arrow to the right of the icon, and select a colour from the palette. Personally, I like light blue or light green in the bottom row.
Now centre the months by clicking the Centre icon. Click anywhere to remove the highlight.
14. PRINTING THE PIVOT TABLE
We'll print out this report and give it to management.
First, do a Print Preview (which should be in Landscape mode) .
Note that at the bottom LH of the print preview screen, it says Page 1 of 2. (or maybe 1 of 3)
Click on Next to see a print preview of page 2. Page 2 has lost the column headings
To retain column headings on all pages you have to use the File - Page Setup option from the main menu.
First, Close the print preview. Then from main menu select: File - Page Setup
Click on the Sheet tab
Click onto the Rows To Repeat at Top box.
Type in $1:$5 (or else move the mouse to the top LH of the spreadsheet, then drag it down over bricks 1 to 5.)
Secondly, check the Gridlines box
Now click on the Header/Footer tab
Select: Custom Header
Left Section: Type: Printed: then click on the 8/7 icon. &[Date] appears
Right section: Type: Page: click on the # icon Type: of click on the ++ icon.
Center section: Type in capitals ANALYSIS OF PRINTER SALES BY MODEL [Excel 5, remove &tab]
Highlight this text with the mouse.
Click on the A icon. The 'Font' box appears
Select Bold and 10
OK - OK
Now click the Print Preview icon and look at both page 1 and page 2. Page 2 now has column headings
Now Print to print the report.
15. A TEST FOR YOU: ANALYSING SALES BY SALES EXEC
So far we have analysed by Product Group, by Customer and by Manufacturer. The SALANAL database also records which sales representative made the sale in the SALES EXEC field.
Right click on the pivot table. Select: Wizard The ROW-COLUMN-DATA box appears.
Management want to know the value of PC sales each month, broken down by Sales Exec. They also want to know which Sales Exec achieved the highest total sales of PC's.
Can you work out which fields you need to place into the ROW - COLUMN ' DATA ' PAGE areas in order to give them this information?
The answer is given at the end of the tutorial.
If you can't work out how to do it, don't worry. Just go on to the next section.
16. ANALYSING GROSS MARGIN ON SALES
So far, we have only looked at sales in terms of net sales value. However, even more important is the total gross margin generated, particularly if your company offers discounts.
The sales invoice records in SALANAL contain both selling price (SP) and cost price (CP) of each item sold, and therefore it is easy to calculate margins.
We'll do this now. However, rather than amend the previous pivot table as we have been doing up till now, we will leave it intact, and build a second pivot table to analyse the margins.
Do this as follows. Click back onto the main SALANAL worksheet. Then select: Data - Pivot Table Report
The Pivot Table Wizard, Step 1 of 4 screen appears. Click on Next
Step 2 of 4 The Range box should say: $A$1:$P$4188. Click on Next.
A message appears saying 'Your new Pivot Table will use less memory if ''..etc etc'. Select NO
Step 3 of 4: The COLUMN-ROW-DATA box appears. Drop and drop the following fields:
ROW : ACCNO, then CUSTNAME
COLUMN : leave blank
DATA : NET, then below it COST , then below it MRGN
The DATA area should now show Sum of NET, Sum of COST and Sum of MRGN underneath each other
In the ROW area, double click on ACCTNO. The Pivot Table Field box appears
Change Subtotals from Automatic to None. OK Finish
The pivot table is recalculated, showing Net, Cost and Margin for each customer.
Widen column B if necessary.
The NET, COST and MRGN figures are underneath each other. They would look better side by side. To do this:
Left click on the grey DATA field button in cell C1 and hold down the mouse key.
[note: Excel 2000 users; see the paragraph a few lines below]
Keeping the LH mouse key depressed, drag upwards very gently until a vertical 'brick' appears.
Keeping the mouse key depressed, slowly drag the brick to the right until it changes to horizontal. Then,
Release the mouse key. The pivot table is reformatted, with Sum of NET, Sum of COST and Sum of MRGN side by side.
[if you are using Excel 2000. Depress the LH mouse on cell C3 ('Data') and drag it down a few millimetres until you see a horizontal 'brick' and below it, an icon with a slab of blue to the left.]
[Excel 2000, continued. Keeping your eye on the slab of blue, drag the brick over to the Total box in cell D3. When the slab of blue changes to horizontal, release the mouse key.
The pivot table is reformatted, with Sum of NET, Sum of COST and Sum of MRGN side by side.]
17. USING FORMULAS TO CALCULATE THE MARGIN PERCENTAGE (Excel 97, Excel 2000 only)
[Note that only Excel 97 and Excel 2000 have the 'Formulas' option. If you have Excel 5 or 7, skip this section and go straight to section 18].
You would like to see the gross margin percentage that you are making on each customer.
Right click within the pivot table, then from the menu select: Formulas ' Calculated Field
The 'Insert Calculated Field' box appears.
In the 'Name' box, replace Field1 with MGN% (just type over it)
In the 'Formula' box click to the left of the 0. Delete the 0 Then:
In the list of Fields, find MRGN and highlight it. Then click on Insert Field.
MRGN appears in the Formula box
To the right of MRGN, add a right slash ('/') , meaning 'divide by'.
Go back into the list of fields. Find NET and highlight it. Insert Field
The Formula box should now display the formula =MRGN/NET
Click on Add, then OK
A fourth column appears in the pivot table, entitled 'Sum of MGN%'
The new column needs to be reformatted as a percentage. Therefore right click anywhere within the new column
Select: Field - Number - Category = Percentage - Decimal places = 0 OK OK
Margins vary from 76% for Eurotunnel plc, down to 14% for Hanson Industries
The overall margin percentage looks very healthy at 47%. However, it is not quite as simple as that. To find out why, let's recalculate the gross margin, this time by product group:
Right click anywhere on the pivot table. From the menu select: Wizard.
In the ROW area, remove ACCNO and CUSTNAME, and replace them with PRODGROUP. Finish
The pivot table is recalculated, showing margin percentages by Product Group.
The percentages on INTERNET, SERVICES and TRAINING are 89%, 90% and 97%. These are obviously too high..
18. DRILLING DOWN ON A BALANCE TO SEE THE UNDERLYING TRANSACTIONS
A valuable feature of pivot tables is the ability, if you see a balance that is suspicious, to 'drill-down' on that balance and see the individual transactions that make it up.
So in this case, the 97% margin percentage for TRAINING in cell E15 needs to be checked.
Double click on the 97% in cell E15
[Excel 5 or Excel 7 users. At present your pivot table shows Sales, Costs and Margin by Customer. You need to change this to show them by Product Group. Go into the pivot table, remove ACCNO and CUSTNAME from the ROW area, and replace them with PRODGROUP. Finish to recalculate. Then double click on cell D15, the total margin for Training].
A new worksheet is generated, listing all the transactions that make up this figure.
Click anywhere in the data to remove the highlighting.
Scroll to the right to see the NET and COST values for each line in columns L and N.
In many cases the COST value is zero, resulting in a 100% gross margin and overstatement of profitability.
Finally, right click back onto the margins worksheet, Sheet2. Rename this worksheet MARGINS.
Note: This is your first hint of the fact that, when data is exported from an accounts package, it may frequently contain errors in the form of wrong values, miscodings, blank records, etc. Before anything, you will first have to tidy the data up.
Cost prices are a particular problem. Sales Admin departments who process sales orders and invoices tend not to bother about them.
The Selling Price is obviously important, since the customer will complain if it is wrong, but who cares about accurate Cost prices? In addition, when services are supplied as opposed to products, it is often difficult to work out what exactly is the true cost?
While Sales Admin or Marketing personnel can be trained to use pivot tables to report daily or monthly sales by value, gross margin reporting should only be done by a trained accountant, who is able to appreciate the potential pitfalls.
END OF TUTORIAL
Answer to section 15 ' Analysing Sales by Sales Exec:
The top salesman was Eddie Mars, who sold £1,055,231 of PC's. To display this you needed:
Row = SALES EXEC. Column = DATE Data = NET Page = PRODGROUP. Then in cell B1 select PC's
DAVID CARTER, Jan 6th 2000.