Data Project

Excel Solution

The Overview page gives an outline of requirements for Widge-It Ltd. We can separate the data that the company needs to record into three data sets, which are customers, transactions and Widge-It Ltd sales staff. This will cut down on the amount of data that will need to be input into the system. We will need to ensure that the system is as dynamic as possible to reduce the need for developer updates, so it is advisable to place this data into Excel tables.

A suitable structure for this project would be to create three data tables:

Customers
Staff (Widge-It Ltd Sales Staff)
Transactions

This will be a one to many design, where Customers and Staff contain one record per contact but Transactions can hold as many references to Customers and Staff as required.

The solution is available to download at the foot of this page.

Three Tables

The three tables needed are split across three Excel worksheets and are as follows:

Customers table

The Customers table will hold records of all customers. To simplify matters, the data is to be structured so that there is one customer contact name included in each customer record. In a full CRM system, there would likely be a different structure to allow for more contact names. The table will need a primary key to identify each unique customer, so that they could be linked to transactions. The primary key is named 'cust_id' in the table.

Staff table

The Staff table will hold records for all staff members of Widge-It Ltd. The primary key for this table will be a unique staff ID number. This is named 'staff_id' in the table.

Transactions table

Transactions will hold the sales data, which will link between the customers and staff tables.

The primary key for this table is 'trans_id'. This will be a unique ID.

The foreign keys within this data are 'trans_cust_id' and 'trans_staff_id'. The 'trans_cust_id' is linked to the customer table, while the 'trans_staff_id' is linked to the staff table.

The Excel workbook will initially contain three worksheets, with each worksheet representing each of the above three tables. The primary and foreign keys will be linked as per the screenshot below:

Excel design considerations

Initial fields/columns

As the Customers table is designed to hold customer information, in addition to the customer ID, it is necessary to include fields for postal details and contact details. It is common within businesses for this type of data to be used for marketing and customer communication. To enable the data to be used properly within an external mail merge, salutation, first name and surname fields should be separated out. A valid email address should also be stored for electronic communication.

The business is likely to also need full contact details of sales staff, including postal and email details. These will be added to the Staff table.

Fields/columns should be added to include the number of green widgets and blue widgets sold. Also for the income from the sale of green widgets, blue widgets and insurance. The obvious place for this is the Transactions table.

Privacy and data protection legal requirements

It is quite common in businesses for contact data to be used for electronic communications and in some circumstances to aid business partners. It is with this in mind that two additional fields should be added to the Customers table/worksheet. These are:

Email opt out - for registering that the customer doesn't want email contact
Third party opt out - for customers that don't want their data passing to third parties

At this point, the structure should look like this:

Additional fields/columns

If the user of an Excel workbook wants the system to be self-contained, they will generally want to use the worksheets as a user interface. Excel allows for worksheets to be used for filtering data.The user often wants the flexibility to be able to filter as much information as possible, so there is a tendency for the Excel developer to create extra columns based on data that already exists in the table.

For example, a column may exist that contains a sales date. The Excel developer might add a column to show a week number, based on that original sales date. This will allow the user to easily filter the table based on a week number.

For this reason, additional columns have been added to the Transactions table (in the Transactions worksheet). These columns do not require data to be typed in as they are calculated from the date that the transaction was made. These columns are:

Week Commencing
Day
Week
Month

Database developers would approach this a different way. In essence, a week number based on the date of sales would not be stored in a database table as a separate field, as the source data itself already exists in its own field. Instead, a database developer would just format the existing data differently when displaying it on screen. A developer would also build sales data based queries on the week number queries using this same sales date field.

To aid the data input clerk in validating typed in data, some extra fields/columns are required that perform mathematical calculations. These calculations can be manually checked against any sales documents submitted. This should include:

Total Widget Sales
Total Widgets Sold
Total Sales

These additional fields would not be required in a traditional database, as the source data is stored elsewhere.

Input of primary/foreign key fields

An Excel developer would need to take into account that a user requires a system that cuts out as many data entry mistakes as possible. Input mistakes could lead to loss of data, so it is important to design the input interface to be as simple to use as possible.

In the test case given, the data entry clerk will need to enter names of both sales staff members and customers. Within reason, it could be assumed that a company will not have a large turnover of sales staff, yet will have an ever increasing number of customers. When designing an Excel workbook with this in mind, a dropdown list for selecting staff members by actual name would be prudent, rather than manually entering staff member ID's.

Entering customer names however, would be best suited to entering Customer ID's rather than selecting from a dropdown list of customer names. A dropdown list of customers names would soon become unusable when the list of customers becomes large.

To ensure data input quality, a lookup should be performed when entering keys on the Transactions table. These fields/columns will be:

fullname - concatenated staff name fields (lookup using staff_id)
customer name - (lookup using cust_id)

It is expected that the majority of data will be entered via the Transactions table.

Structure

With the extra required fields/columns, the structure should look like this:

Yellow and green indicates a link via a primary or foreign key.
Blue highlighting indicates a returned lookup value.
Orange highlighting indicates a summing calculation.

Usability

Using the freeze panes functionality improves the usability of a worksheet when the worksheet is wider than the screen width. This locks the columns on the left hand side and top of the screen when the user scrolls to the right. This functionality keeps data on the screen that is important to refer to when populating cells on the far right of the screen. This alleviates a lot of scrolling on the part of the user.

Locking cells also prevents the accidental overwriting of formulae.

It can also help to colour cells that don't require user input. A light grey colour has been used in the example.

Hiding columns which do not need to be referred to on a daily basis should also be considered, although this has not been carried out in the example for clarity.

Accounting format has been employed for showing figures. This will report the '-' symbol instead of showing zero values. This makes numeric data easier to read where there are multiple cells containing a zero figure. This has been utilised in the example in the 'Sales by week' and 'Sales by month' worksheets.

A separate colour scheme for tabs/worksheet names that are used daily can also aid usability. Green tab/worksheet names have been used in this example.

Formula design

Considerations for easy update

The rule of separating data from formulae has been followed to simplify updating. As it can be very time consuming to modify worksheets where there is a mix of both in the same cells.

Named ranges make a worksheet easier to edit as it is generally simpler for people to read a name rather than a set Excel range of numbers. For example, the two formulae listed below perform the same action:

=COUNTIFS(Staff,$A$8,Week,AW$3) =COUNTIFS(A2:A9,$A$8,I2:I18,AW$3)

The first example is easier to read as the user knows that the name 'Staff' relates to data containing staff, whilst 'Week' contains data referring to weeks.

Named ranges have another important benefit in that if the data referred to changes, the user only needs to change the range of data in one place in the Name Manager and not change every cell that contains every instance of the range.

Using $ appropriately in formulae for copying and pasting of absolute cell references. Careful use of the $ sign and also non-use for relative references in ranges allows for easier copying and pasting along rows and columns.

Concatenation has used to join values to strings of text. An example used in many worksheets is where the current year has been referenced from the 'Lookups' worksheet. This approach makes changing the year headings a quick process, as only one value needs amending. In the example, I have given the cell a named value of 'current_year' and have appended it to text strings as follows:

="Staff Sales by Week("&current_year&")"

Suppression of unwanted output

Excel formulae can result in unwanted output. A common example is that you may want to perform a calculation within a cell based on data that will exist in another cell. For example, you may have an empty cell that will be populated with a date. Another cell may be set up to calculate a week number based on the contents of that former cell. Until a date is typed into the first cell, the second cell will not give a meaningful result. This rogue data can look messy and clutters up the worksheet. There are functions that can help these types of problems.

The ISNA function tells Excel how to handle the display of a cell when a suitable value is not available for the calculation. In the downloadable example in the Transactions worksheet, a VLOOKUP is performed. This VLOOKUP examines the contents of cell B2 and compares it against data in the Staff_ID_lookup range. If a match is found, then it returns the value in the cell in the corresponding second column from the value. This would be normally written as:

=VLOOKUP(B2,Staff_ID_lookup,2,FALSE)

Although this is a correct use of a VLOOKUP, the downside is that Excel will show a value of '#N/A' if there is no value in cell B2. This can look messy where cells are surrounded by blank values.

A workaround is to use the ISNA function to specify what is displayed in the cell where normally it would show '#N/A'.

The following formula tells Excel to show an empty value of "" should the formula normally return '#N/A', but perform the VLOOKUP formula if it doesn't :

=IF(ISNA(VLOOKUP(B2,Staff_ID_lookup,2,FALSE)),"",VLOOKUP(B2,Staff_ID_lookup,2,FALSE))

This does make the formula longer, but makes the worksheet easier to read for the data input clerk, as shown below.

A further example is with the calculation of a week number within a table in the Transactions worksheet. A full date will be entered in the table in the F column. An example of how to perform this calculation is below:

=WEEKNUM(F2)

Encapsulating the function within an IF statement, that checks if the cell F2 is empty before calculating will resolve this problem:

=IF(F2<>"",WEEKNUM(F2),"")

Considerations should also be made where division is carried out. Take for example where percentage calculations are performed and it is assumed that a figure will be present to divide by.

The following formula assumes that cell F29 will hold a value to divide by:

=SUMIFS(Total_Widget_Sales,Staff,"="&$B28,Month_Num,F$6)/F$29

This may work in most cases, but will return a '#DIV/0!' error if there is a zero in cell F29.

It can be corrected by encapsulating the SUMIFS function within an IF statement. Here the formula checks if the value will be greater than zero. If it isn't it will return a value of 0:

=IF(SUMIFS(Total_Widget_Sales,Staff,"="&$B28,Month_Num,F$6)>0, SUMIFS(Total_Widget_Sales,Staff,"="&$B28,Month_Num,F$6)/F$29,0)

Adding extra whitespace can make it easier to read:

=IF(    SUMIFS(Total_Widget_Sales, Staff,"="&$B28, Month_Num,F$6) > 0,    SUMIFS(Total_Widget_Sales, Staff,"="&$B28, Month_Num,F$6) / F$29    ,0)

Implementation of fixed reporting

Colours

Custom colours were used in these reporting worksheets so that the reports have some defined contrast, which should make them easier to read.

Sales by week

The worksheet calculates the sales by week for each Wedge-It Ltd staff member.

There are three different types of formula used in the worksheet. These are:

SUMIFS
COUNTIFS
SUM

Cells in the A column contain staff names. These are referenced, along with the week number in row 3 to calculate against the relevant named ranges.

SUMIFS is used to calculate totals based on staff name in the A column and week range.

COUNTIFS is used to count instances of sales (one per row) based on staff name in the A column and week range.

SUM is used for calculations of rows and columns.

Both SUMIFS and COUNTIFS are array functions introduced in Excel 2007.

Correct use of the $ symbol in formulae will simplify making changes to the worksheet, should new sales staff be added. Existing staff data can be copied to the new section, where find and replace can be used to replace the absolute cell reference for the staff name being added.

There has been extensive use of named ranges in this worksheet to ensure that the formulae is easy to read and understand and is dynamic.

Sales by month

This report is very similar to 'Sales by Week', but the formulae references the month numbers in the Transactions table rather than week numbers.

Performance

This worksheet is similar to 'Sales by Month' in regards to formulae used to calculate widget sales by month. However there is an extra section which calculates percentages of sales for each sales staff member against the sales team as a whole.

Bearing in mind that staff will have time off throughout the year, it is assumed that a weekly version of this worksheet would be less useful for statistical purposes. Therefore a weekly version has not been implemented.

Implementation of dynamic reporting

Incentives

The user will need to calculate staff bonuses, based on the sales made by sales staff. For demonstration purposes, a bonus scheme has been implemented as follows:

If a staff member brings in £12,000 or more a month in widget sales, they are awarded a bonus of £300.00.
Hitting £11,000 a month earns a bonus of £200.00
While £8,000 a month brings in £100.00.

To make the system as dynamic as possible, the bonus figures are stored in the 'Lookups' worksheet along with the required sales rates.

The Incentives worksheet itself utilises INDEX MATCH referencing these rates to calculate the figures.

All the user needs to do to perform the required calculation, is to change the name of the month using the drop down list.

Customer sales by date range

This data calculates widget and insurance sales by a user supplied date range, so is not fixed by a set week or month. The information returned includes the cost price of the widgets and profit percentages made on sales in this date range.

Again, there is data stored in the 'Lookups' worksheet which is used in the calculations. This is the cost price of the green and blue widgets.

Pivot table: Sales by staff

The report allows the user to set two sets of criteria in the filter cell, staff name(s) and date range. The information is returned in the order of total sales by staff, highest first.

Pivot table: Sales by customer

The report allows the user to set two sets of criteria, customer name(s) and date range. The data is listed in the order of total sales by customer, highest first.

Charts

Sales Progression bar chart

A bar chart is displayed for each sales staff member, showing sales progression over the year. Data is pulled from the progression worksheet.

Sales Percentage pie chart

Percentages of staff sales are shown in this chart. Source data for these charts is also taken from the progression worksheet.

The workbook is available for download by clicking the download link below.

Download Excel Workbook

Please note that I have recognised that site visitors may be wary of opening downloaded Excel workbooks that contain macros, therefore I have not used macros in the example.