Case Study #1 is intended to test your knowledge of how to summarize data using
Case Study #1 is intended to test your knowledge of how to summarize data using pivot tables in Excel using the data file, including using filters and interpreting the results Week 1 Case Study #1 Pivot Table Data.xlsm Download Week 1 Case Study #1 Pivot Table Data.xlsm.
The data file contains US sales and profits from orders for 2021 and 2022 for an office supply store. The data fields included are as follows:
Year – year of sale
Segment – corporate, consumer or home office
State/Province – state name
Region –distinct geographic region recorded
Category – product family of items sold
Sub-category – product line of items sold
Sales – in thousands of dollars of product shipped and invoiced per order
Qty – number of items shipped on the order
Profit – in thousands of dollars profit of order
After watching the video How to Create Pivot Tables in ExcelLinks to an external site., and How to Change Value Field SettingsLinks to an external site. create the following 5 pivot tables:
One pivot table – Total sales and count of orders by Year, by Region
One pivot table – Total sales and count of orders by Category within each Region, filtered by year. Table should allow you to filter data based on a select Year.
One pivot table – Total Sales by Category within each Region for 2021 and 2022 (year in columns) as a percent of Grand Total sales.
One pivot table – Total Profits by Category within Region for 2021 and 2022 (year in columns)
One pivot table – Total Profits by Sub-category by Region (columns), sorted by highest to lowest Sub-category Grand Total profits. (To sort values in pivot tables, select any number in the Grand Total row or column, and then click Sort > Largest to smallest)
Provide a brief summary noting the largest and smallest region/category/subcategory of sales and profits per year from the above. Summaries can be included in Excel in same worksheet(s) as each pivot table or in a separate worksheet.
Submit a single workbook showing the 5 fully functional pivot tables and the requested summary information.
Copy of instructions and USCB information about the data file: