Shelly Cashman Excel 365 | Module 8: SAM Project A

Описание: Please contact us if you need this project or any other project in any subject to be completed.
Whatsapp: +1-343-321-3276
Email: [email protected]
#shellycashmanexcel365 #AtYourDesktop

Shelly Cashman Excel 365 | Module 8: SAM Project A
At Your Doorstep
ANALYZE DATA WITH CHARTS AND PIVOTTABLES
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:
5. Insert a PivotChart based on the new PivotTable as follows to help Shelby visualize the data:
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:
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:
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:
12. Add a slicer to the PivotTable and PivotChart as follows to make it easy for Shelby to filter the data: