Capstone Project 3 Instructions
Benji’s Department Store
Capstone Project 3 Instructions 1
Benji’s Department Stores, with corporate headquarters in Colorado Springs, Colorado, operates department stores in midsize towns in selected areas of the West. The organization maintains a large database for its accounting operations; other departments often download data to complete additional analysis. Eve Darden, analyst for the corporate sales department, regularly creates reports based on her analysis of sales by product areas and regions. Right now, Eve is compiling and summarizing data about the best-and worst- performing product group and time period for certain regions and product groups. You are to complete the following:
1. Download the starter Excel data file named Capstone_3_Starter_Data_File and save it as a new Excel workbook with the name Benjis_Stores_nn.xlsx where nn are your first and last initials.
2. In the Documentation sheet, enter your name and the date (use the date you start this capstone project with the format of mm/dd/yyyy).
3. In the Sales Data worksheet, create an Excel table with the name of ProductSales. Format the Sales column in the Accounting format with no decimal places. Apply a table style of your choice.
4. Make a copy of the Sales Data worksheet with the new name of the copied worksheet as Sort. Sort the table in ascending order by Product group, then in ascending order by Region, then in ascending order by Year, and then in chronological order by Month (Jan, Feb, Mar, …).
5. Make a copy of the Sales Data worksheet with the new name of the copied worksheet as Filter with Total Row. Insert a slicer for the Product Group field. Move the slicer to row 1. Match the slicer style with the style you selected for the ProductSales table. Resize the slicer height and width to eliminate any excess space.
6. Display the records for Gardening and Sporting products in 2016, excluding sales in Utah. Sort this data in descending order of sales.
7. Add a Total row and calculate the average sales for the filtered data. Change the label in the Total row to Average.
8. Split the worksheet into two panes. In the top pane, display the table data. In the bottom pane, display the Total row. In the top pane, select cell A1.
9. Make a copy of the Sales Data worksheet with the new name of the copied worksheet as Subtotals 2016. Filter the list to display only data for 2016, and then include sutotals that calculate the total Sales by Region and Month.
Capstone Project 3 Instructions
Benji’s Department Store
Capstone Project 3 Instructions 2
10. Make a copy of the Sales Data worksheet with the new name of the copied worksheet as Bottom 15. Display the 15 lowest periods based on sales (each row represents a period). Sort the sales so that the lowest sales appear first.
11. Based on the data in the sales Data worksheet, create the PivotTable and PivotChart in a new worksheet with the name of the worksheet as PivotChart. shown as follows:
12. Based on the data in the sales Data worksheet, create a PivotTable to calculate the sum, average, minimum, and maximum sales categorized by region and product group. Insert a Year slicer (for years 2015 and 2016) and use it to only show sales in 2016. The name of this worksheet is Region Statistics. When the PivotTable is complete, the results should look like as follows (ignore the instruction headings):
Capstone Project 3 Instructions
Benji’s Department Store
Capstone Project 3 Instructions 3
13. Based on the data in the Sales Data worksheet, create a PivotTable and slicers displaying total sales by Year, Product Group, and Region. Include a second value calculation that displays each Product Group and Region as a percentage of the total company sales (i.e. percentage of a subtotal in PivotTable). The name of this worksheet is Percent of Company Sales. The results of this PivotTable should look as follows when completed:
Capstone Project 3 Instructions
Benji’s Department Store
Capstone Project 3 Instructions 4
14. Format the PivotTable and slicers with matching styles, and adjust the height and width of the slicers as needed to improve their appearance. Step 13 already has these changes applied and can be used as an example.
15. In the Percent of Company Sales worksheet, Filter the Product Group to display sales in 2016 for the Automotive and Electronics product groups in all Regions except Utah. When complete, the results of these changes will appear as follows:
16. Save the workbook as Benjis_Stores_nn.xlsx where nn are the initials of our first and last name and submit to Blackboard per the instructions of your instructor