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.
|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|
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))
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)
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.
Such a cheap price for your free time and healthy sleep
All online transactions are done using all major Credit Cards or Electronic Check through PayPal. These are safe, secure, and efficient online payment methods.