Spreadsheet Mastery: Exercises

Your responsibilities as a Data Analyst at Amazon encompass both reporting and analysis across various departments. Recently, your team received a request for a comprehensive analysis of shipping speed and status for orders shipped to India over the past three months. Upon reviewing the request, your department director concluded that more information is necessary before deciding to proceed with a full analysis.

As a result, your director has asked you to compile a concise report summarizing sales orders and associated shipping information from March 31, 2022 to June 29, 2022.

Additionally, your director would like specific information for a random sample of orders, for which the order IDs have been provided.

The initial reporting request specifies the following metrics, KPIs, and sample order information:

1. Reporting Period Begin Date.
2. Reporting Period End Date.
3. Count of days in Reporting Period.
4. Date of report preparation.
5. Total Order Count.
6. Total Unit Count.
7. Total Sales Dollars.
8. Average Order Value (AOV).
9. Average Unit Retail (AUR).
10. Units Per Transaction/Order (UPT).
11. Sales Dollars and % of Total Sales Dollars by Shipping Status Category.
12. Unit Counts and % of Total Units by Cancellation Status.
13. Date, Status, Ship Service Level, Units, Dollars, City, State, and Zip Code for these 20 Order IDs:

Ship Service Level and Zip Code for these 20 Order IDs:

Upon reviewing the report requirements, you realize that you do not have an existing report template for these specifications, so you will need to create one. A cleaned dataset has already been generated by another member of your team and is stored in a CSV file on the department network drive.

You know that your director likes to receive one workbook per project rather than multiple files, so you decide to use several worksheets within the same workbook.

You’re ready to get started and have a plan for how you’ll proceed, which you outlined as follows:

1. Download the dataset from Canvas. You will find it under Files > Spreadsheet Mastery Resources > Order Data.csv.
2. Open the file, save it as an Excel Workbook (*.xlsx), and name it “Order Summary Report_Your Name”.
3. Rename the worksheet to Data.
4. Review the dataset and make note of anything you think will be helpful as you proceed.
5. Note to self: EDA and cleaning already performed by a colleague.
6. Add a new worksheet and name it Summary Report.
7. Add another new worksheet and name it Sample Order Data.
8. Reorder the worksheets so that the Summary Report is first, and the Data worksheet is last.
9. Using simple formatting options, create a Summary Report template that looks like this mock-up.

9. Create a template for the Sample Order Data that looks like this

Begin with populating the Summary Report

1. Populate the Reporting Period Begin Date using the MIN function.
2. Populate the Reporting Period End Date using the MAX function.
3. Populate the Count of Days in Period by calculating the range between the begin and end dates.
4. Populate the Report Preparation Date using the TODAY function.
5. Use the COUNT function to populate the Total Orders cell.

6. Use the SUM function to populate the Total Units cell.
7. Use the SUM function to populate the Total Sales $ cell.
8. Populate the Average Order Value (AOV) KPI using the AGGREGATE function.

9. Populate the Average Unit Retail (AUR) KPI using cell references and a division formula.

10. Use the AVERAGE function to populate the Units Per Transaction (UPT) KPI.

11. Populate the Sales Dollar amount for each Shipping Status Category by using the SUMIF formula.

12. Populate the % of Total for each Shipping Status Category using cell references and a division formula.

13. Populate the Quantity of Units for each Cancellation Status Category by using the SUMIF formula.

14. Populate the % of Total for each Cancellation Status category using the same method as above.
15. Validate that your basic functions and IF functions result in accurate data. Make corrections where needed. Validate that the percentage of total values for Shipping Status and Cancellation Status sum to 100%. If they do not, add more categories and formulas as appropriate and sort section to retain original sort order. Double check all date and number formats.

16. Next, populate the Sample Order Data Report.
17. Find the Top Destination Postal Code using the MODE.SINGL function.
18. For the first set of twenty provided Order IDs, populate the Date, Status, Ship Service Level, Units, Dollars, City, State, and Zip Code using the VLOOKUP function.
19. For the second set of twenty provided Order IDs, populate the Ship Service Level and Zip Code by combining the INDEX and MATCH functions.
20. Validate that your VLOOKUP and INDEX/MATCH functions are returning the correct data. Make any necessary corrections to your formulas. Double-check all formats.

Submitting Your Work

Your director has asked that you submit the report for review as soon as you have it prepared and gave you a heads-up that added requirements will be provided as soon as the initial review is completed. You’re ready to submit this initial report, so you review the submission requirements just to be sure that you follow the specifications.

Upload the Microsoft Excel Workbook under Class 4 Exercises: Order Summary Report and click Submit.