Problem

Data Files needed for this Case Problem: NP_EX_10-3.xlsx, Support_EX_10_Yogurt.accdb

Umai Frozen Yogurt Joan Amari is a sales executive for Umai Frozen Yogurt, a chain of frozen yogurt stores. Joan is responsible for overseeing 20 franchises in California. Joan wants to compare average customers per day at the franchises over the past three years and determine whether factors such as location, date, and weather play a significant role in the volume of customer traffic. She has data that contains over 21,000 records from the daily sales in the 20 stores from the past three years. Complete the following:

1. Open the NP_EX_10-3.xlsx workbook located in the Excel10 > Case1 folder included with your Data Files. Save the workbook as NP_EX_10_Umai in the location specified by your instructor.

2. In the Documentation sheet, enter your name and the date.

3. Use Power Query to access the Support_EX_10_Yogurt Access database, creating a connection to the Sales and Stores tables into the workbook's Data Model. You do not have to edit the query for these tables.

4. Open Power Pivot to view the Data Model in Diagram View. Create a relationship between the Sales and Stores table through the Store ID field.

5. In the Sales table, create a hierarchy named Calendar containing the Year, Month, and Weekday fields.

6. Joan wants to analyze the number of customers served by the Umai Frozen Yogurt. Return to the workbook, and then insert a PivotTable in cell B4 of the Customer Calendar worksheet using the Data Model. Name the PivotTable Customer Calendar. Place the Calendar hierarchy from the Sales table in the Rows area of the PivotTable. Place the Customers field from the Sales table in the Values area. Change the field settings of the Sum of Customers value field to display the average of the Customers field with no decimal places.

7. Create a Line PivotChart from the PivotTable data. Move the PivotChart into the Sales Report worksheet, covering the range B14:I29. Remove the field buttons and the legend from the chart. Change the chart title to Average Customers per Day. Add data labels to the chart showing the value of each data marker.

8. Joan wants to know whether days of intense rainfall cause a decline in customer traffic. In the Rain Table worksheet, in cell B4, insert a PivotTable using the Data Model. Rename the PivotTable as Rain Table. Place the Rainy field from the Sales table in the Rows area. Display the average of the Customers field from the Sales table in the Values area. Display the average in the Numbers format with no decimal places.

9. Create a Clustered Column Chart PivotChart of the Rain Table PivotTable. Move the PivotChart to cover the range K4:Q15 of the Sales Report worksheet. Remove the legend and the field buttons from the PivotChart. Change the chart title to Average Customers per Day during Rainfall. Add data labels to the chart.

10. Joan wants to examine the relationship between temperature and customer sales. In the Temperature Table worksheet, in cell B4, insert a PivotTable using the Data Model. Rename the PivotTable as Temperature Table. Place the Month field from the Sales table in the Rows area of the table. Display the average High Temp and Customers fields in the Values area of the table, in that order, to display the average high temperature and customers per month.

11. Joan wants to create a scatter chart of the relationship between average high temperature and average number of customers. Because PivotCharts cannot be created as scatter charts, you need to prepare the data to create a scatter chart of the relationship between average high temperature and average number of customers. Copy the range C4:D16 and use Paste Link to paste a link to the copied cells in the range F4:G16. Change the text in cell F4 to Temperature and change the text in cell G4 to Customers. Format the values in the range F5:G16 in the Number format with no decimal places.

12. Create a scatter chart of the data in the range F4:G16. Move the chart to the range K17:Q29 of the Sales Report worksheet. Change the chart title to Customers vs. Temperature. Add the axis titles to the chart, using Average Customers per Day for the Primary Vertical axis and Average Temperature for the Primary Horizontal axis. Change the scale of the vertical axis to go from 50 to 350. Change the scale of the Temperature axis to go from 50 to 110. Add a linear trendline to the chart.

13. Joan wants to view the charts on the Sales Report worksheet filtered by city. In the range B4:I12 of the Sales Report worksheet, insert a slicer using the City field from the Stores table. Lay out the slicer buttons in 4 columns and 5 rows. Connect the slicer to the three PiovtTables in the workbook.

14. Joan wants to show monthly results for the Malibu store from 2021. In the Sales Report worksheet, use the City slicer to show results for the Malibu store. In the Average Customers per Day chart, drill down into the 2021 Year category to the level of month.

15. As part of a presentation, Joan wants a map showing the locations of the stores with the highest average customers per day. Click cell A1 in the Sales Report worksheet, and then open a 3D Maps tour. Do the following to create a map showing the locations of the stores with the highest average customers per day:

a. Place the City field from the Stores table in the Location box.

b. Place the Customers field from the Sales table in the Height box. In the Height box, point to Customers (Sum), click the arrow, and then click Average on the menu to display the Customers (Average) instead of Customers (Sum).

c. Change the map style to a heat map.

d. Zoom into the state of California map to better view the average intensity of customer traffic in the 20 cites.

e. Display the map labels.

16. Close the 3D Map tour, save the workbook, and then close it.