Overview

Data is not useful until it becomes information. Turning raw numbers into a visual representation can help us understand meaning and connections much more quickly. The ability to use and analyse data supports the move towards evidence based decision making.

The problem a lot of businesses (large and small) face is having too much data because we know that’s a good thing, yet very little of it enables effective decisions. Often, the data isn’t going to help you. Either it’s not the right kind of data or it isn’t compiled and analyzed properly. In other words, it isn’t Information

— it’s just data. You need information for decision making. The data itself is usually not very helpful as is.

The purpose of this lab is to apply excel skills learned in the first four week to convert raw data into information.

Task Details

Task 1.

View the following randomised data set. While the information on its own has some value, the collective whole of the information would have much more value.

(Each row below is a unique data entry for 1 vehicle, i.e. a sales record.)

Every record contains individual data points that make up the sum of that record.

The columns contains a range of data, but the data show above is not specific. It is randomised.

1. Open Excel and identify clear column names (Fields) that best describe the characteristic of each kind of data visible in each row. (See figure 1) 

Figure 1. Example Field Name.

2. Once the column field names have been identified (Checked by your tutor), type in the data into their appropriate cells and save the excel file. Make sure that row integrity remains (i.e. Don’t mix up the data from row 2 with row 3).

3. Based on the completed spreadsheet with appropriate column names, provide an analysis of the data. Use custom sort function to sort the cell data. Use the Excel SUM(), SUMIF() and COUNTIF() functions to achieve these results.

4. Answer the following questions:

1) Which salesperson sold the most amount of cars? 

2) Total Sales for the week. $

3) Most sold vehicle type. 

4) Most popular vehicle fuel type sold. 

5) What other trends or patterns can be extrapolated from this data?

Additional Resources for using SUMIF() and COUNTIF() functions.

• SUMIF(): https://support.office.com/en-us/article/sumifs-function-c9e748f5-7ea7-455d-9406- 611cebce642b

• COUNTIF(): https://support.office.com/en-us/article/countif-function-e0de10c6-f885-4e71- abb4-1f464816df34?ui=en-US&rs=en-US&ad=US

• Custom Sort overview: https://support.office.com/en-us/article/Sort-data-using-a-custom-list- def8ff2b-681a-4fc3-9bd2-a06455c379e1

Task 2.

Download the “FedUni_Coffeesales.xlsx” available on Moodle and complete the following tasks:

1. Un-randomise the data into appropriate columns with clear field names.

HINT: Data cells in same colour should be un-randomised into column with clear field name with same colour, e.g, data in cell D4 should be put into column “Order time” as they have the same colour – yellow.

2. Using appropriate functions as used “Task 1”, answer the following questions:

1) Who are Fed Uni Cafés best best barrista/sales rep by total sales?

2) Who is Fed Uni Cafés most popular coffee by quantities sold?

3) What is the overall profit from each sale. (may require additional columns to be added)?

4) Based on the sales data, identify a pattern or trend?