(Solved): WPC 300 Assignment 9 (FoodEmporiumLabFile)...

WPC 300

Problem Solving and Actionable Analytics

Data Visualization Assignment

Instruction:
•    Finish Data Visualization lab before you work on this assignment.  You will use the FoodEmporiumLabFile.xlsx file for this assignment.
•    Rename the file yourasurite_DataVisualizationAssignment.docx by replacing yourasruite with your asurite.
•    Answer all questions in yourasurite_DataVisualizationAssignment.docx. 
•    Save your answers, close the files, then submit both the FoodEmporiumLabFile.xlsx and yourasurite_DataVisualizationAssignment.docx files to Canvas
Background Intro:  
You’ve been asked by Emma, the CEO of Foods Emporium, to better understand customers with Gold customer Status. Emma thinks the company should be able to generate more sales from customers with Gold status.  Foods Emporium sells a variety of fine products, such as Gustaf's Knäckebröd, Pâté Chinois, and Maxilaku. The company’s motto is “Food so good even we don’t know what it is!”
Emma needs you to create pivot charts for the questions listed below and provide her some recommendations in how to increase sales. All questions can be answered by constructing insert ? PivotChart (or Chart) in Excel. The Excel spreadsheet “FoodEmporiumLabFile.xlsx” has already been created for you. You have also created a new Dashboard, called Dashboard 2, in that file.  
In the spreadsheet, “Full Price”, or the sale price, is the base price of the line item without a discount. The “Extended Price”, is the discounted sales price offered to the customer (i.e., what they actually pay). The “Total Discount” is the difference between the Full Price and the Extended Price. 
Questions:
1.    Insert the Dashboard, located in “Dashboard 2” worksheet, created from the Data Visualization and Interpretation lab below.  (30 points total; 5 points each chart)

2.    For this question you will create 2 pivot charts to answer “Do Gold status customers generate more sales monthly based on the data provided”? 

2.1    The first pivot chart you will create is a pie chart which will show the total sales by customer status.  Make sure both the value and % data labels are added. (10 points total; the chart is 5 points, readable data labels 5 points, missing % data labels -3 points)
(Insert the pivot chart screenshot here)

2.2    The second pivot chart you will create is a line chart which will show the monthly total sales by customer status (5 points).  Is there a recognizable seasonal pattern in customers’ sales based on their status? (5 points) 
(Insert the pivot chart screenshot here)

 
Yes, there is a pattern in the way customer statuses, the sales are high at the middle of the year and start lowering towards the end. Also the sales rises slightly at the end of the year.

3.    Create a horizontal bar pivot chart to answer “Which states did the Gold status customers make purchase”? (10 points total; the chart is 5 points, readable data labels 3 points, all States are displayed in Y-Axis 2 points)
(Insert the pivot chart screenshot here)

4.    Create a 3-color map chart to find out “Where are the Gold status customers located (in what states)”? (15 points total; 10 points for the map chart, make sure the data labels are readable with the colors picked 5 points)
(Insert the Map chart screenshot here)
 

5.    Create a pivot chart (you can select a type of chart you like) to find out the top 10 products the Gold status customers purchased most, based on the extended price data (10 points; 5 points for the chart, 5 points for the data labels) 
(Insert the pivot chart screenshot here)

6.    Create a “Q6 Dashboard” worksheet and include all the pivot charts created from questions 2 - 5 to that worksheet for Emma to review it. (10 points; 2 points each pivot chart).  In the same worksheet, provide at least 2 recommendations to Emma in how to increase the sales from the Gold status customers.  (5 points; 2.5 points for each recommendation)
(Insert the “Q6 Dashboard” screenshot here)