EXCEL PIVOT TABLES

BACKGROUND
You are a marketing analyst working for a company called XYZ Wholesale Inc. You have been assigned to assist the strategic planning team with product and profitability analysis of the company. Your IT team has pulled transactional data from 2015 through 2019 for you to analyze. The data are stored in an Excel file called Wholesale Customers Data.xlsx.
Everyone on the team agrees that you are the best person to do the initial exploration of the data set. Although you are free to do your own analysis, at the very least, you need to answer the questions in the following section so that you can report back to the team.
EXCEL PIVOT TABLES
The most common tool used to slice and dice data is a spreadsheet. Typically slicing and dicing is done by creating a pivot table which creates what is called a cross-tabulated structure or crosstab. The crosstab displays aggregated and summarized data based on which way you sort the columns and rows. The pivot table can be easily transposed by swapping the axes; hence the name, pivot.
For this assignment, you will use Microsoft Excel pivot tables to answer some business questions related to your assigned profitability analysis. The step-by-step instructions will guide you through the use and manipulation of pivot tables.
1. Open the data file Wholesale Customers Data.xlsx in Microsoft Excel
2. Your data should appear as below.
3. The table below describes what each attribute represents
Attribute Definition
Region Customer’s region, 1 = North, 2 = South, 3 = East
Channel Customer’s channel, 1 = Horeca (Hotel/Restaurant/Cafe),
2 = Retail
Fresh Sales of Fresh produce ($)
Milk Sales of Milk ($)
Grocery Sales of grocery products ($)
Frozen Sales of frozen products ($)
Detergents Sales of detergents and paper products ($)
Delicatessen Sales of deli products ($)
Total Sales Total Sales of all products ($)
Year, Month, Day Year, month and day the sale was made
Customer ID ID of the customer to whom the sale was made
4. Select the entire dataset by using Shift + Control + Right arrow and then Down arrow keys
5. Click on Insert  Pivot Table. You should get the screen in figure below. Notice that Excel suggests the entire table as the range of cells for your pivot table.
6. Click OK.
7. This inserts a pivot table in a new spreadsheet.
8. Figure below shows the layout of the pivot table and associated menus. Explore the menus and tool bar.
8. Now, use the pivot table to answer the questions below
Question 1: Are the number of customers increasing over the years?
(Use year (Column) and count of customers (Values). The default operation is sum for customers. You will need to change it to count by right clicking on the field, selecting “Value Field Settings” and then changing to “Count”)
Question 2a: Which channel performs better (in terms of number of customers) across all years? (Use year (Column), Channel (Rows) and count of customers (Values))
Question 2b: Based on the above results, which channel is likely to be generating higher sales?
Question 3: Which channel performs better (in terms of number of customers) across all years? (Use year and channel (Rows) and sum of total sales (Values))
Question 4: Is there a difference in the results you found for question 2 and 3 above? Explain
Question 5: Can you think of any reasons for this anomaly? Make any assumptions necessary.
Question 6: Identify which products are responsible for lower sales in channel 1 in 2018? In order to do this, you will need to drill down further into the data (Use year and channel (Rows) and products (Values))
9. From this point forward, you will need to figure out which field is dragged to which box areas of the crosstab. I will provide you only selective hints where necessary.
Question 7: Identify the top 2 customers for the total sales in each region? (Hint: You will need to sort row label in descending order by total sales)
Question 8: How do the three regions compare each other in terms of their contribution towards total sales? Perform this analyses both by using “Sum” of total sales” and “Average of total sales”
Question 9: In your report back to the team, which operation would you choose to report the results of the above question with: “Sum of total sales” or “Average of total sales”?
Question 10: For all the years combined, which month generates the highest sales and which month generates the lowest sale? (Hint: Sort total sale by descending/ascending order)
10. Use line and column charts to answer the next set questions. To insert a chart, click on Insert in the ribbon and then click on Recommended Charts. Next select Line or column (depending on the question) from the options displayed.
Question 11: Is the month of July consistently highest over the years?
Question 12: Is the month of August consistently lowest over the years?
Question 13: In what ways can your findings for question 11 and 12 above be useful to the strategic planning team.
Question 14: What products consistently rank as the top 3 across all years?
Question 15: Which region is the highest consumer of Fresh products every year?
Question 16: Which product is sold the highest in each channel?
Question 17: Are your results from the above question consistent every year?
Question 18: Has the company ever lost a customer? (Hint: You are looking for all the customers who have purchased a product from the company in the past, but have no total sales in the year 2019 or years 2018 and 2019)
Question 19: In what way(s) do you think the above information can be useful to the strategic planning team?
Question 20: Run at least one additional analysis to gain further insight into the data. Clearly express your question and provide the answer below it. What information does the answer to your question provide to the strategic planning team?
Question 21: Based on your profitability analysis above, write an executive summary report to inform the strategic planning team about the company? You are free to run additional analyses to gain further insights. Your report should be no more than 1 page.

Calculate a fair price for your paper

Such a cheap price for your free time and healthy sleep

1650 words
-
-
Place an order within a couple of minutes.
Get guaranteed assistance and 100% confidentiality.
Total price: $78
WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!
👋 Hi, how can I help?