Hammer Wines Pty Ltd

Workshop Learning Activities 1

Hammer Wines Pty Ltd wants all spreadsheet models to be reusable and adaptable in design. These activities are aimed at developing a flexible model that will cater for sales representatives moving to different states.

In this material you will learn how to:

• Create a validation list box
• Use dynamic linking
• Create simple formula such as divide & subtract
• Distinguish between an absolute cell reference and a relative cell reference
• Use absolute cell referencing
• Create range names and use them in a formula
• Sort data

Data Validation

This feature is used when you want to limit/restrict the entries in a cell.  In this case, we want to create a list box so the user can simply choose the state or geographic area. The first step is to create the list.  This should be in an area out of the way of your potential data.

• Open the file WLA1-1.xls
• In the worksheet Raw Sales cells S3:S6, enter the following options into this VIC & TAS, NSW, QLD & NT, WA & SA
• To create the list box first select the cell that you want to restrict – cell F2
• Select the Data tab, Data Tools section, Data Validation – the following dialog box should appear:

[pic 1]

Figure 1 - Data validation dialog box

• In the Allow box, use the drop list to select List.
• Click in the Source box. Highlight your list of valid data S3:S6, OK.  It is always more accurate to highlight rather than typing in a cell reference. The list must always be on the same sheet. Accept all other defaults as per Figure 1.
• We want to copy this to the rest of the Sales Representatives.  Select cell F2 and copy (Ctrl + C). Then select the adjacent cells G2:M2.  Finally, paste the list box into cells G2:M2 using Paste Special (in the Home tab) with the Validation option selected. (See Figure 2 below).  Paste Special allows the user to attach the same characteristics of one cell to another. Click OK.

[pic 2]

Figure 2 - Paste Special dialog box

Check it out!  Make sure you select the geographic areas for each Sales Representative as shown in Table 1 below:

Table 1  - Sales Representatives for each region

Your worksheet should resemble Figure 3 below. 

[pic 3]

Figure 3 – Excerpt of Raw Sales worksheet

Renaming Worksheets

Worksheets need to be appropriately labelled and should reflect the contents.

• Right click Sheet 1 and rename it Sales Analysis

Dynamic Linking

Data can be arranged either across the sheet or down the sheet.  It often depends on the size of the data.  

We want to create a summary sheet with the sales representative in the A column and we want it to be dynamically linked. This means that a change to the Raw Sales should be reflected in the Sales Analysis worksheet.

• Select the Sales Analysis worksheet, In A2 type =
• Click on the Raw Sales worksheet, select cell F3
• Complete the step above until you have all the sales representatives in Column A
• Look at the formula bar for A9, it should read Jedd, D
• Go to the Raw Sales worksheet, change the name in M3 to your name

Check out the Sales Analysis worksheet

Simple formula

To calculate the variance between Actual Sales and Target Sales, use a simple formulae = Total Sales – Target.

• Open WLA1-2.xls (Note: New data has been added)
• In D2 enter the following formula = B2-C2.  Do not type the formula. Enter =, then click on the cell to select B2.  Enter the subtract symbol and again click on the cell to select C2
• Now drag to copy this formula down to D9.
• Format as currency, 2 decimal places

% Variance

The percentage variance indicates the extent to which a salesperson has under or over achieved his/her sales target for a defined period (Variance/Target).  To calculate this, the formula in E2 should read = D2/C2

• Go to the Sales Analysis worksheet, highlight column E
• Select the Home tab, Number section to format for % to 2 decimal places, Alternatively right click, select Format Cells to bring up the Format Cells dialogue box. See Figure 4 below
• Enter the formula =D2/C2
• Drag to copy this formula down to E9

[pic 4]

Figure 4 - Format as Percentage

Note: Before formatting for % the data should be a decimal as Excel will multiply by 100

Absolute and Relative Cell Addressing

Percentage of Sales

This measure provides the opportunity to analyse the peer group performance across the sales force without their sales targets being a specific reference point.  A relatively simple calculation is required.

In G2, we will need to use the Individual Sales Figure for each Sales Representative divided by the Hammer Wines Total Sales.

• Open WLA1-3.xlsx, and go to the Sales Analysis worksheet
• Click in G2, enter the following formula – remember it is best to enter a formula by clicking on the relevant cell rather than typing the reference and all formulas start with =
• =B2/B10
• Copy this formula down

Excel uses relative cell addressing when copying formulae.  Relative to where it was in G2, it will do the same in G3.  It is easier to explain if we look at both formulae to see the difference (see Table 2).

Table 2 - Cell formula - can you pick the difference?

As we have gone down one row from G2 to G3, the range argument goes down one row from B2 to B3. But let’s look at the second reference. As it is copied across it goes from B10 to B11. B11 contains no data.  We want it always to refer to the Total Sales figure.