EXCEL COURSE PART ONE - FORMATTING A DATABASE



INTRODUCTION TO THE SALES ANALYSIS TUTORIAL

In this tutorial you will learn how to use pivot tables to analyse your company’s sales. You will also use Excel’s “Import Wizard” to load into Excel a data file that was exported from a third party accounts package.

The accompanying data file SALANAL.TXT contains 4,188 sales invoice line detail records taken directly from an accounts package such as Sage, Pegasus, SunAccount, Navision, etc.

The data has been exported from the accounts package as a “Text” file, which Excel can then import via the Import Wizard. Files in Text format are sometimes also called “CDF” (Comma Delimited Files), or “CSV” (Comma Separated Values) or “ASCII” files.

Package suppliers now recognise that it is essential to include facilities to export data into Excel. Hopefully, you will be able to get your own sales data into Excel and analyse it with pivot tables as we do here. 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, sales by sales rep, etc etc.

This tutorial should take you about 30 minutes to work through. If possible, it is a good idea for two people to do it together, one reading the instructions, the other operating the keyboard.

You will make extensive use of both left and right mouse buttons. When I say “right click” I mean click the right mouse button. When I say “click” on its own, I mean “click the left mouse button”.

The tutorial is designed to be used with Excel 2003 and Excel 2000. There is an earlier version for use with Excel 5, Excel 95 and Excel 97, which can be found on AccountingWeb -News & Info – Expert Guides – Free Excel Tutorials.

1. IMPORTING THE TEXT FILE INTO EXCEL

Start up Excel. Import the SALANAL.TXT file into Excel as follows:

File - Open. Open the folder that contains SALANAL.TXT. You can’t see SALANAL.TXT.

Files of Type: (at the bottom) change from Microsoft Excel Workbook to Text Files

SALANAL now appears. Open it.

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. The Text Import Wizard - Step 2 of 3 appears.

There should be vertical lines between the columns (Excel identifies the tab marks as delimiters).

In addition, you can see field names at the head of each column – TRANSN, INVNO, DATE, etc.

Click Next. Step 3 of 3 appears.

The TRANSN column is highlighted. At the head of the column is the format which Excel assumes it will be in when it is imported – the GENERAL format. Excel assumes GENERAL for all fields.

At top right 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 at top right 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.

Click on the “General” heading of the third column, DATE. The column is highlighted

At the top right click on: Date. The heading of the DATE column should now read DMY.

Click on Finish. Wait a moment. The file is imported into Excel.

[Note: If your default date format was MDY rather than DMY, 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 day/month/year 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) or English (British). Reboot the PC as instructed. Start the tutorial again. This time the Import wizard should offer DMY as default]

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.

Click on the top LH grey brick above the 1 and left of the A. The whole worksheet is highlighted.

Double click between column headings A and B. All the columns are widened to their best fit.

Highlight Row 2 by clicking on 2 at the left. Then select: Window - Freeze panes

Now 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. CONTENTS OF THE SALANAL DATABASE

The database is a set of records derived from the sales invoice “detail” file within the accounts package. Each record contains the following fields:

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

CUSTNAME

PARTNO

DESCRIPTION

MANUFR = Manufacturer (this is old data, and quite a few of these are now defunct)

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 2004 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: hold down the right hand Ctrl key with your right thumb, 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.

Click onto the L at the top of column L. At the bottom of the screen it says “Sum = 5442458

Make a note of this number. Click to remove the highlighting from column L.

Return to the top of the file. (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 and Chart Report The Pivot Table Wizard, Step 1 of 3 screen appears.

Click on Next. Step 2 of 3 appears 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 by hand].

Click on Next.

Step 3 of 3: Click on Layout at bottom left. The COLUMN-ROW-DATA box appears.

To the right are all the column headings of your spreadsheet. Drag and drop as follows:

PRODGROUP into the ROW area and 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 OK then Finish.

A list of Product Groups appears with a total beside each one. At the bottom of the sheet is the total 5442458. From the 4,188 records in SALANAL, Excel has generated a pivot table summarising total Net Sales 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. The original sales invoice database is still there.

Now right click back onto Sheet1 with the pivot table. Rename this worksheet PIVOT.

6. FORMATTING THE NUMBERS IN A PIVOT TABLE

The sales figures would be easier to read if the thousands were separated by commas. Therefore:

Click on any of the numbers in column B. Then right click the mouse.

A menu appears, with “Format Cells” at the top.

[If you are using Excel 2003, clear the screen with Hide Pivot Table Toolbar and Hide Field List].

Then select: Field Settings.

The “Pivot Table Field” dialogue box appears. From the options on the right, select: Number.

The “Format Cells” box appears. From the “Category” list, select: Number

Then: Decimal Places = 0 - 1000 separator?, tick for YES - -1234 in red OK OK

In the pivot table commas now separate the thousands.

7. SORTING THE PIVOT TABLE

You would like to rank the product groups to show the best sellers first. To do this:

Click anywhere in the Total column, eg on cell B10

Click on the ZA icon. The Product groups are sorted in Descending order, with PC’s at the top

[if you don’t have the ZA icon, use the AZ icon instead and sort ascending.]

Now move your mouse pointer around the top of cell A4 PRODGROUP until it turns into a thick black vertical down arrow. Then click the mouse. The column is highlighted.

[If you can’t get the black down arrow, it may be because selection is switched off. Right click for menu - Select - Enable Selection. Now try again.]

Click on the AZ icon. The product groups are sorted back into alphabetical order, ascending.

8. ANALYSING SALES BY CUSTOMER

Having seen sales by Product Group, you would now like to analyse them by customer.

Right click anywhere within the pivot table. The menu appears.

From the menu: Wizard [Excel 2003 = Pivot Table Wizard] - Layout

The COLUMN-ROW-DATA box re-appears.

Drag and drop PRODGROUP anywhere outside the ROW area.

Drop ACCNO into the ROW area.

Then drop CUSTNAME below ACCNO in the ROW area. Then: OK - 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:.

Right click on the grey ACCNO field button in cell A4. The menu appears

Select: Field Settings.

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. This time the sales are broken down by customer.

8. SUMMARISE SALES BY MONTH

The sales invoices were dated from April to September. We will summarise them by month:

Right click within the pivot table. The menu appears. Select:

P/T Wizard - Layout The COLUMN-ROW-DATA box re-appears.

Drag and drop DATE into the COLUMN area. OK. Finish. The pivot table is recalculated.

Across the top there is now a series of dates. They need to be grouped by month. To do this:

Right click on the grey DATE field button in cell C3. The menu appears.

Select: Group & Outline – Group [In Excel 2003 Group and Show Detail - 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.

[“Cannot Group This Selection”]

You should not get this error message during the tutorial. However, if you ever do, the following notes may be helpful.………The Group command is invaluable, but a bit 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 were expressed in UK day/month/year format. To check this, take a closer look at the way your dates are currently formatted in Excel. If you are a UK user and they are US with the month first, you need to reset your Regional Settings 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 still get the “Cannot Group This Selection” message. In this case delete the entire pivot table and build it again from scratch: this time it should work. Finally, sometimes a Date field will not Group when put in the ROW area, but will if put it in the COLUMN area In this case, Group them first in COLUM, then go into the wizard and move Years/Date out of COLUMN and into ROW. You will find that the Grouping stays OK.]

9. SHADE THE COLUMN HEADINGS

We will tidy up the column headings with some shading.

Move the mouse pointer over DATE in D3 until it turns into a thick down arrow, then click.

The column headings “Apr” to “Sep” 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).

Centre the months by clicking the Centre icon. Also click the B icon for Bold print.

Now click anywhere to remove the highlighting. The monthly headings are all shaded yellow.

10. 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 for menu. Select: P/T Wizard - Layout

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. OK. Finish

The pivot table is recalculated, showing sales by manufacturer. Again, the total is £5,442,458.

[The list of manufacturers should be in alphabetical order, starting with 3COM. But sometimes DEC appears first – I’ve no idea why. If it does, sort the manufacturers alphabetically as follows:

Move the mouse pointer over MANUFR in A5 until it turns into a thick down arrow, then click.

The column is highlighted.

Click on the AZ icon. The column is sorted and DEC goes to its proper position.

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

You see a list of the product groups - (All), COMMS, INTERNET, MEMORY, MONITORS etc.

Highlight PC’s OK. 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.

Click on the B1 down arrow again and now 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 for menu - P/T Wizard – Layout.

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. OK Finish

The pivot table is redisplayed, but this time shows sales of the individual models of printer.

12. HIDING THE DETAIL

The Hide Detail and Show Detail commands will expand and collapse the pivot table. So:

Right click on A4, MANUFR. From the menu select: Group and Outline - Hide Detail

The pivot table is summarised back to top level with one line per manufacturer.

To see more detail for EPSON: Double left click on EPSON in cell A6

Excel expands EPSON to show the individual products.

Double left click anywhere in the gap below EPSON, say cell A8. You go back to one summary line.

You can see the detail of any manufacturer, just by double clicking on them. Try IBM.

IBM is expanded. Now double click in the gap below IBM. You go back to summary level.

13. SHADING SUBTOTAL LINES

We want to make the sub-totals for Canon, Epson etc stand out by shading them.

Move the mouse pointer to the grey 15 brick left of cell A15, “CANON Total”. Move it gradually to the right until it turns into a thick horizontal black arrow. Then left click the mouse. All the sub-total lines are highlighted.

[Note: if the black arrow stays within the 15 box, only Row 15 is highlighted. But if the arrow is partly over cell A15 as well, ALL the sub-total rows are highlighted].

We’ll use the yellow Fill Color icon again (the one that looks like it’s pouring a can of paint). However, this time 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.

14. SALES AND GROSS MARGIN BY SALES EXECUTIVE

Having analysed the company’s sales by product group, by customer, by manufacturer and by product, we will now analyse sales by salesperson.

Right click for menu – P/T Wizard – Layout - COLUMN-ROW-DATA box re-appears.

Drop MANUFR, PARTNO and DESCRIPTION out of the ROW area. And drop PRODGROUP out of the PAGE area.

Now drop SALES EXEC into the ROW area. OK Finish

The pivot table is recalculated to show sales by sales executive. Total sales are still £5,442,458.

Top salesmen over the 6 months were Eddie Mars (£1,643,489) and Bernie Ohls (£1,116,160).

But suppose you pay your sales force on the basis of gross margin earned, not turnover:

Right click for menu – P/T Wizard - Layout - COLUMN-ROW-DATA box re-appears.

Drop MRGN below “Sum of NET” in the DATA area, so you get “Sum of MRGN” OK Finish

Now the pivot table shows the Margin each salesman has earned, underneath their Net sales.

We’ll tidy the MRGN lines up a bit. Firstly, put commas between the thousands as follows:

Move the pointer to the left of Sum of MRGN until you get a thick black right arrow, then click

Right click for menu. Field Settings. From the right hand list of options, select: Number

Then: Number - Decimal Places = 0 - 1000 separator, YES - -1234 in red OK OK

The MRGN lines now include commas separating the thousands.

The Sum of MRGN lines should still be highlighted. Now:

Click the down arrow on the Fill Color icon (the yellow paint can). A palette of colours appears.

From the palette, select Tan (bottom row, second from left).

Click anywhere to remove the highlighting. The Margin lines are all shaded light brown.

15. PRINTING THE SALES REPORT.

We will print out this pivot table showing monthly sales and margin by sales executive.

(You don’t have to print this report. If you wish, you can skip this and go on to section 16).

First, click the Print Preview icon to see the report on screen

This report would look better if printed in Landscape. Therefore select::

Setup - Page - Landscape - OK Then, select:

Setup - Margins - at bottom, tick the Horizontally and Vertically boxes - OK. Then, select:

Setup - Sheet - check the Gridlines box - OK. Then select:

Setup - Header/Footer then click on: Custom Header. Type in the following:

Left Section: Type: Printed: then click on the 8/7 icon. &[Date] appears

Right section: Type: Page: then click the # icon - type: of - click on the ++ icon.

Center section: Type in capitals SALES AND MARGINS BY SALES EXECUTIVE

Highlight this text with the mouse .

Click on the A icon. The “Font” box appears

Select Bold and 10

OK OK OK Then Print to print the report.

16. DISPLAYING “NET” AND “MRGN” IN COLUMNS.

The sales by Sales Exec screen displays the NET and MRGN values underneath each other.

You would prefer to see them side by side: Right click for menu – P/T Wizard - Layout.

Move Years and DATE into the PAGE area. OK Finish. The row of months disappears..

Wave the mouse over Sum of Margin for thick black right arrow. Click to highlight all rows.

We’ll remove the colouring. Click on the down arrow in the Fill Color icon.

The colour palette appears. Click the “No Fill” box. The brown colouring disappears.

Now click the LH mouse button on the grey DATA field button in cell B4 and hold it down. Now drag it down a few millimetres until an icon with a slab of blue to the left appears.

Keeping your eye on the slab of blue, slowly drag the brick over to the Total box in cell C4. When the slab of blue changes to horizontal, release the mouse key.

The pivot table is reformatted, with Sum of NET and Sum of MRGN side by side.

17. USING “FORMULAS” TO CALCULATE GROSS MARGIN PERCENTAGE

Margin is obviously important, but you suspect some of your salesmen have been buying business by

discounting the list price. So you want to see the gross margin percentage your salesmen are making on sales.

We will use the Formulas option to calculate gross margin percentage (i.e. Margin divided by Net Sales value).

Right click on the pivot table for menu. Select: Formulas – Calculated Field

[In Excel 2003 the Formulas option is rather hidden away. From the menu, select Pivot Table Toolbar – click on

Pivot Table at the left – click the down arrows to display the complete menu. Select: Formulas – Calculated field]

The “Insert Calculated Field” box appears.

In the “Name” box, replace Field1 with MGN%

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%”

It needs to be reformatted as a percentage. Also, the “Sum of MGN%” title is making it too wide.

Right click anywhere in the new column. From the menu, select: Field Settings

In the Name box at the top, click to the right of the “f” in “Sum of”. Then use the big Left Arrow key to delete

everything to the left . (leave one blank space before MGN% , otherwise Excel gets confused)

Also select: Number - Percentage - Decimal places = 0 OK OK

Interesting. While Eddie Mars and Bernie Ohls have generated the biggest margin in absolute terms, Vivian Regan and Jane Seymour are earning the highest percentage return on sales. And what on earth is Harry Jones doing at only 25%?

We need to “slice and dice” to find out what’s happening. Maybe the answer lies in the different mix of products they are selling. So: right click for menu – P/T Wizard – Layout.

Move SALESEXEC into the PAGE area. Put PRODGROUP in the ROW area. OK Finish

The pivot table is recalculated, showing the margin earned by Product Group.

Now, next to SALES EXEC, click on the down arrow in column B. Select: Harry Jones.

Most of Harry’s sales were PC’s. That’s hardware, with a margin percentage of only 33%.

Now from the list choose Vivian Regan. Over half of her sales (£136,559) were TRAINING, which is services. They generated 97% margin!

18. DRILLING DOWN ON A VALUE TO SEE THE UNDERLYING TRANSACTIONS

An invaluable feature of pivot tables is the ability, if you are doubtful about a value, to “drill-down” on that value and see the individual transactions that make it up.

So in this case, the margin of 136,559 on TRAINING needs to be checked.

Double click on the: 136,559. A new worksheet is generated, listing all the transactions..

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 is zero, resulting in 100% gross margin. This explains the 97% average.

Finally, right click back onto the PIVOT worksheet.

OK. It’s time to take break. Save your work via: File – Save . Then relax.

Final food for thought:

This is your first hint of the fact that, when data is exported from an accounts package, it will invariably 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 the sales orders and invoices, are usually not bothered about them. Selling Prices are obviously important, since customers will complain if they are 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 sales by quantity or value, they should NOT be allowed to report on margins and profitability. This should only be done by a trained accountant, who is able to appreciate the potential pitfalls

DAVID CARTER, October 2004

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download