Shelly Cashman Excel 365 | Module 8: SAM Critical Thinking Project C At Your Doorstep

GETTING STARTED

1. Save the file SC_EX365_CT8C_FirstLastName_1.xlsx as SC_EX365_CT8C_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file SC_EX365_CT8C_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Shelby Cooke is a financial analyst for At Your Doorstep, an online grocery delivery service in Cleveland, Ohio. She is using an Excel workbook to analyze the company's recent financial performance, and asks for your help in creating advanced types of charts and PivotTables to provide an overview of delivery services, customers, and stores. Go to the Deliveries worksheet, which contains a table named Services that contains data about grocery deliveries. Shelby asks you to create a chart comparing the total charges to the three service types to identify the median charge by service type.

a. Create a Box and Whisker chart based on the Service Type data (heading and values) and the Total Charge data (heading and values).

b. Resize and position the chart so that it covers the range B19:G33, and use Total Charge by Service Type as the chart title.

c. Format the data series using the default gradient fill to make the data easier to interpret.

2. The Deliveries worksheet also contains a table named Deliveries that compares the delivery data for each day of the week. Shelby asks you to create a chart that shows the relationship between the day and deliveries made.

a. Insert a Scatter chart that shows the relationship between the day of the week (heading and values) and the deliveries (heading and values).

b. Resize and position the Scatter chart so that it covers the range I12:M33, and use Deliveries and Days of the Week as the chart title.

3. Shelby wants to further analyze the relationship between deliveries and days of the week. Add a Logarithmic trendline to the scatter chart.

4. Go to the Customers worksheet, which lists customer details in a table named Customers. Shelby wants to display the number of customers that have deliveries made to their homes or offices or to pick up in the store. Insert a recommended PivotTable based on the Customers table.

a. Insert the Count of Customer by Location recommended PivotTable on a new worksheet that uses Delivery Locations as the worksheet name. [Mac Hint: Use the Location field in the Rows area, the Customer field in the Values area, and show grand totals for columns only. Update the Value Field settings for the Customer field to Count.]

b. Apply the Light Green, Pivot Style Medium 9 style to the PivotTable.

c. Add the Charge field to the Values area of the Field List, and then change its summary function to determine the average charge for delivery locations. Change its Number format to Currency with 2 decimal places and the $ symbol.

d. Use Total Customers as the column heading for the number of customers per location, and use Average Charge as the column heading for the average delivery charges.

5. Insert a PivotChart based on the new PivotTable as follows to help Shelby visualize the data:

a. Insert a Combo PivotChart based on the new PivotTable.

b. Display the Total Customers as a Clustered Column chart and the Average Charge as a Line chart, and include a secondary axis for the Average Charge data.

c. Change the minimum bounds for the value axis on the right to 6.

d. Change the PivotChart colors to Monochromatic Palette 1.

e. Hide the Field List, resize and position the chart so that it covers the range D3:K17, and then display the Field List again.

6. Shelby also wants you to insert a PivotTable that includes other customer data so that she can analyze customer and scheduled delivery information. Return to the Customers worksheet, and then create a PivotTable based on the Customers table.

a. Place the PivotTable on a new worksheet, and then use Customers Pivot as the name of the worksheet.

b. Display the Location field in columns, the Area field and then the Customer field as rows, and display the Years field as the values.

c. Display the Service Type field as a filter, and then filter the PivotTable to display customer information for Scheduled deliveries only.

d. Hide the field headers to reduce clutter in the PivotTable.

7. Go to the Stores worksheet. The Stores table shows the sales from three categories of store deliveries, with each category divided into types and then into store names. Shelby wants you to display these hierarchies of information in a chart. Insert a Sunburst chart to display the hierarchies for Shelby.

a. Insert a Sunburst chart based on the data in the Stores table.

b. Resize and position the chart so that it covers the range G3:L24, and use Participating Stores as the chart title.

8. Go to the Customers by Service worksheet, which contains a PivotTable showing the number of customers, stores, and charges for each service type, delivery location, and area. Shelby does not need to display the charges, but does want to compare the subtotals of customers and stores for each delivery location within the service types. Modify the PivotTable for Shelby as follows:

a. Remove the field showing charge totals from the PivotTable.

b. Change the report layout to Tabular Form.

9. Go to the Deliveries Pivot worksheet, which contains a PivotTable listing the order amounts and number of deliveries for each day of the week. Shelby wants you to include the amount per delivery. Reorder the fields and add a calculated field to the PivotTable as follows:

a. Change the order of the fields to display the delivery amount totals before the total number of deliveries in the PivotTable.

b. Create a calculated field using Per Delivery as its name.

c. Insert a formula that uses the Amount field and the Deliveries field to calculate the amount per delivery.

d. Use Amt Per Delivery as the column heading for the calculated field.

e. Change the Number format of the Amt Per Delivery value field to Currency with 2 decimal places and the $ symbol.

10. Go to the Location Pivot worksheet, which contains a PivotTable and PivotChart that should show total charges according to delivery location and area. Reorder the fields in the Rows area to show the areas within each location and make PivotTable easier to interpret.

11. Make the PivotChart easier to understand and use.

a. Change the PivotChart to a clustered column chart.

b. Apply Layout 3 to the PivotChart to display the legend below the chart, and then apply the Monochromatic Palette 1 colors if the colors changed when you changed the chart type.

c. Increase the height of the chart until the lower-right corner is in cell G40.

12. Add a slicer to the PivotTable and PivotChart as follows to make it easy for Shelby to filter the data:

a. Add a slicer based on the Stores field.

b. Without resizing the slicer, position it so that its upper-left corner is in cell G2.

c. Use the slicer to filter the PivotTable and PivotChart to display delivery locations with 10, 11, or 12 stores only.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

Final Figure 1: Deliveries Worksheet

Final Figure 2: Delivery Locations Worksheet

Final Figure 3: Customers Pivot Worksheet

Final Figure 4: Customers Worksheet

Final Figure 5: Stores Worksheet

Final Figure 6: Customers by Service Worksheet

Final Figure 7: Deliveries Pivot Worksheet

Final Figure 8: Location Pivot Worksheet