Shelly Cashman Excel 365 | Module 8: SAM Project A At Your Doorstep

GETTING STARTED

1. Save the file SC_EX365_8A_FirstLastName_1.xlsx as SC_EX365_8A_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_8A_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 in the range B3:G17 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. Insert a chart as follows:

a. Create a Box and Whisker chart based on the Service Type data (range C3:C17) and the Total Charge data (range G3:G17).

b. Resize and position the chart so that it covers the range B19:G33.

c. Use Total Charge by Service Type as the chart title.

d. 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 in the range I3:K10 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 (range I3:I10) and the number of deliveries made (range K3:K10).

b. Resize and position the Scatter chart so that it covers the range I12:M33.

c. 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 as follows:

a. Insert the Count of Customer by Location recommended PivotTable. [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. Use Delivery Locations as the name of the new worksheet.

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

d. Add the Charge field to the Values area of the Field List, and then change its summary function to Average so that Shelby can determine the average charge for delivery locations.

e. Change the Number format of the Average of Charge value field to Currency with 2 decimal places and the $ symbol.

f. Use Total Customers as the column heading in cell B3, and use Average Charge as the column heading in cell C3.

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.

c. Include a secondary axis for the Average Charge data.

d. Hide the Field List so that you have room to format the chart.

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

f. Change the PivotChart colors to Monochromatic Palette 1.

g. Resize and position the chart so that it covers the range D3:K17.

h. 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 as follows:

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

b. Display the Location field as column headings.

c. Display the Area field and then the Customer field as row headings.

d. Display the Years field as the values.

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

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

7. Go to the Stores worksheet. The Stores table in the range B3:E14 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 as follows:

a. Insert a Sunburst chart based on the store data in the range B3:E14.

b. Resize and position the chart so that it covers the range G3:L24.

c. 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 Sum of Charge field from the Values area.

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 in the Values area to display the Sum of Amount field before the Sum of Deliveries field.

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

c. The formula should divide the Amount field value by the Deliveries field value to calculate the amount per delivery.

d. Use Amt Per Delivery in cell D1 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. Move the Area field below the Location field in the Rows area so that the PivotTable is easier to interpret.

11. Make the PivotChart easier to understand and use as follows:

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