Tutorial Assignment TA5: Data Visualization
Overview
This assignment is intended to show you a simple example of the use of Chart-based data visualization as a basis to answer questions.
Background: The owners of a retail supermarket chain (in Asia) need to get a better sense of what’s going on in their sales. As their analyst you will be given a rather raw set of retail transactions and will have to perform some analyses via Microsoft Charts. There will be 2 kinds of analyses you will perform:
A more specified and deterministic set of analyses (with corresponding questions, intended to build/review your skill set with excel Charts
A second set of analyses that is more open-ended and will challenge you to have to improvise more
Assignment Version # / Date/Time stamp: Version 2.3 11/28/2021 @ 9 PM
(this will be updated as edits are made – if they need to be – in response to students)
Instructions
There are 3 parts to this assignment – Data/file preparation, then Parts A & B
You will submit 2 spreadsheet files (each with several labeled worksheets) and one document file as your assignment submission. One spreadsheet will be for the operations in Part A and the second will be for the operation in Part B. Your answers to the questions (please use the question ID#s for your answers) for both parts will go into the document file.
Data/file preparation:
Before you begin, click here to download the sample retail data that you have been provided with.
Please save a copy of this file as “TA5_Part_A_ {your last name}”
Please open a new WORD document and mark out the Question ID #s [i.e. Q1, Q2, A1-Q3, A1-Q4, A2-Q5 A2-Q6, A3-Q7, B1-Q8, B2-Q9, B3-Q10, B4-Q11, B4-Q12] – then save your document file as “TA5_{your last name} answers”
In the Spreadsheet file TA5_Part_A_ {your last name}, create a 2nd worksheet and copy the data from the first worksheet (“RawData”) into it. Please label this 2nd worksheet Table_Data.
You will create several rows of new data by copying existing rows, as per the following specifications.
Q1: What is your student ID?
Q2: What are the 3 non-zero digits of your ID after the first digit. For example, if you student ID was 94007621, these 3 digits would be 4, 7 and 6. These 3 digits will be referred to as A, B & C.
You will count A rows down from the top row and copy that row and insert this paste this at the top of the rows. Change the Invoice-ID for this row to 9999-0A
You will count B+10 rows down from the top row and copy that row and insert this paste this at the top of the rows. Change the Invoice-ID for this row to 9999-1B
You will count C+20 rows down from the top row and copy that row and insert this paste this at the top of the rows. Change the Invoice-ID for this row to 9999-2C
Essentially you are inserting some duplicate data (hashed according to digits in your student ID) rows, then assigning these rows (records) unique invoice numbers.
When you are done you will have inserted 3 additional rows of data before the first row of data in the Table_Data worksheet.
In the worksheet Table_Data, click the mouse to highlight the first column, then drag the mouse across to extend the highlight across all the columns. Then use the Home – Format as Table command to convert all the data to a table format. In the “Format as Table” command make sure that the data for your table includes all the columns of data AND that the checkbox for “”My data has headers” is checked.
You now have an Excel table with column headers ready for sorting and filtering. Save your work in this spreadsheet file, then save a copy of this spreadsheet (“TA5_Part_A_ {your last name}”) as another spreadsheet file: “TA5_Part_B_ {your last name} ” – you will need that for Part B of this assignment.
Close “TA5_Part_B_ {your last name}” and then re-open “TA5_Part_A_ {your last name}” once again. You are now ready to begin work on Part A of this assignment.
As you go through the steps that have been outlined for you in the instructions below for Part A, try to remember what you are doing because you will be doing this on your own in Part B.
Part A (learning the skills)
A1: Creating a basic PivotChart as a Column Chart, then rescaling the data
Click on any cell in the data area of the Table_Data worksheet and use the Insert – PivotChart commend to create a PivotChart in a new worksheet. You will note that the default for the “Select a Table or Range” is the Excel table you just created and that the default is for the PivotChart to be created in a new worksheet.
You will now see a full PivotChart Field list in a new worksheet.
In the new worksheet for Pivot Chart, create a Column chart for Average of Unit price by Gender. Drag the Unit Price into the Values field, then drag Gender into Legend (Series).
A1-Q3: Does there appear to be a significant different in the average price of items purchased by gender?
Now please rescale this Chart. Double-click on the scale for the graph to the left in the chart. Double-click on it. Under Format Axis (to the right) change the minimum to 0 and the maximum to 75. (to see the “Format Axis”, you may have to first close the Field list to hide it)
A1-Q4: Now, does there appear to be a significant difference in the average price of item purchased by gender? what kind of interpretation bias could occur from alternate scaling?
Save this worksheet as “Avg Price by Gender”, then go back the Table_Data worksheet.
A2: Creating a Pie Chart and labeling the slices with data values
Going back to the Table data, insert a pivot chart again – This time label your new worksheet “Sales by Product line”,
Create a chart of the Sum of Sales revenue (i.e. Sum of “Total”) by product line.
Once you have a Column Chart for this, Click on Design-Change Chart Type to convert this into a Pie Chart. However, in order to get the Product categories to display in this Pie Chart, you may have to drag Product Line into Axis (Categories). You will then see a Pie Chart divided into slices of Sales Revenue by Product line.
Double-Click on the point in the very middle of the Pie, then right click to “Add Data Labels”.
Right-click again to Format Data Labels. In the Format Data Labels pop-up box scroll down to change the Label Position to Outside-end and (below that) the Number option for Category to Currency.
You will see a Pie Chart with the total revenue for each Product category adjacent to each slice (just outside the slice).
A2-Q5: Which Product line has the lowest Total revenue? Which Product line has the highest Total revenue?
A2-Q6: Move your mouse slowly over the Product line with the largest revenue and over the slice with the smallest to review to see the % of the total Sales revenue for each Product categories – what is the net difference between these 2 percentages? Is this large?
Click on the Chart title to change the Title to Revenue by Product Line. Please rename the Worksheet with the same title.
A3: Using a slicer
Create a copy of the Revenue by Product Line worksheet and put it at the end as the last worksheet. Right-click on the title of the Revenue by Product Line worksheet, then click on Move or Copy. Then change the name of this copy to “Revenue by ProductLine & Gender”
In the new copy of this Pie Chart, click on the Chart area, then create a slicer using the Insert-Filters-Slicer command. In the Insert Slicers Pop-up window, check the box for Gender.
Drag the new Slicer control for Gender off the Chart to the right of it. Then click on Male and then on Female in the slicer.
A3-Q7: Which Product Line makes up the largest slice of Sales Revenue for Males? for Females?
Save and Close your Part A Spreadsheet file.
Part B: (applying the skills)
Open the Part B Spreadsheet file (if you did your data preparation correctly, you should be starting with the same table you worked for Part A with the supplemental duplicate rows you have already added).
B1: Create a 3D Column Chart for the Sum of Qty sold by City and Product Line.
Please create a Column Chart for the Sum of Qty sold by City.
Change the Chart type to 3D Column chart. Use Quantity (SUM) for Value, City for Legend (Series) and Product Line for Axis (Categories).
Change the Chart title and Worksheet name to something descriptive of this chart.
B1-Q8: Which City shows the greatest variances between the different Product Lines in respect to Quantity of inventory sold?
B2: City and Profits
In a new worksheet, please create a Column chart that shows which City is yielding the most significant profits (i.e. Gross Income). Then change this to a Pie Chart.
B2-Q9: In which kind of chart are the distinctions in total profits more evident? Why?
B3: Payment type and Profits
In a new worksheet, please create a chart that shows, on average, how much profit is yielded per Sales transaction.
B3-Q10: Which payment method usually yields the smallest gross Income (profit) per transaction?
B4: Sales transactions involving Food & Beverage
Create a Column chart with a Slicer for ProductLine to answer the following question.
B4:-Q11 Which Customer Type purchases more Food & Beverage products?
B4:-Q12 Adding in Gender as a Legend, can you describe why this is so?
Assignment submission
Make sure each chart i& each chart’s worksheet is labeled appropriately.
You will upload to the assignment folder:
Your Spreadsheet file for Part A (“TA5_Part_A_ {your last name}” )
Your Spreadsheet file for Part B (“TA5_Part_A_ {your last name}” )
The document file containing your answers to the questions (TA5_{your last name} answers”)
