Reference no: EM132661964
Case problem 1
Data File needed for this Case Problem: Shirls.xlsx
Go Sports: Anton Aliyev is the store manager for Go Sports, a sports clothing store in Middletown, Ohio. In addition to its clothing inventory, the store willp rint logos provided by local sports teams on T-shirts, jerseys, or sweatshirts purchased at the store. Anton uses Excel for a variety of tasks, including pricing and inventory. He wants you to create an Excel table from information about current products and then analyze this data. Complete the following:
1. Open the Shirts workbook located in the Excel5 > Casel folder included with your Data Files, and then save the workbook as Shirts Inventory in the location specified by your instructor
2. In the Documentation worksheet, enter your name and the date.
3. In the Shirts worksheet, create an Excel table using all of the data in the worksheet. Rename the table as ShirtsTbl. Format the table with Table Style Medium 9. Change the Price data to the Currency format showing no decimal places. Change the In Stock data to the Number format with no decimals.
4. Make a copy of the Shirts worksheet, and then rename the copied worksheet as Sort by Style.
(Hint: Press the Ctrl key as you drag and drop the Shirts sheet tab to the right of the Shirts sheet tab to make a copy of the worksheet.)
5. In the Sort by Style worksheet, sort the data in ascending order by Style, and then in descending order by in Stock.
6. Filter the ShirtsTbl table by Size to remove the youth extra small (yxsm) and ladies extra small(lxsm) sizes.
7. Insert a Total row that shows the total shirts In Stock. Change the 'Total row label to Total Shirts.
8. Split the worksheet window into two horizontal panes. Place the split bar two rows above the bottom row of the worksheet. In the top pane, display the shirt data. In the bottom pane, display only the Total row.
9. Make a copy of the Shirts worksheet, and then rename the copied worksheet as Filter by Color. In the filter by color worksheet, filter the shirtsTbl table to display only T-shirt style.
10. Insert a slicer for Color, position the slicer so its upper-left corner is in cell G1, resize the slicer's height to 1.8" and its width to 1 2", and then form at the slicer with Slicer Style Dark 1.
11. Use the Color slicer to further filter the ShirtsTbl table to display only blue T-shirts and white T--shirts.
12. Filter the ShirtsTbl table so that it displays only blue and white T-shirts with a price greater than $10. Sort the filtered data in ascending order by Price and then in descending order by
In Stock.
13. Make a copy of the Shirts worksheet, and then rename the copied worksheet as Subtotals Convert the table to a range because the Subtotal command cannot be used with an Excel table.
Sort the table in ascending order by Style. Use the Subtotal command to display the minimum in Stock for each Style.
14. Based on the ShirtsTbl table in the Shirts worksheet, insert a PivotTable in a new worksheet that calculates the total In Stock for each Style and Color. Display both Style and Color in
rows. Use the Value Field Settings dialog box to rename Sum of In Stock as Total Inventory. Apply the Pivot Style Medium 9 style to the PivotTable. Rename the worksheet as PivotTable by Style and Color.
15. in the PivotTable by Style and Color worksheet, insert a PivotChart with the Clustered Column chart subtype. Place the PivotChart to the right of the PivotTable. Remove the legend. Filter the
PivotChart to exclude any white shirts. Change the chart title to Inventory by Style and Color.
16. Based on the ShirtsTbl table in the Shirts worksheet, insert a PivotTable in a new worksheet that displays the total In Stock and count of Item IDs by Style and Color. Place Style in the FILTERS
area R ename the worksheet as PivotTable by Style.
17 In the PivotTable by Style worksheet, format the PivotTable with Pivot Style Medium 2 style. In the Value Field Settings dialog box, rename the Count of Item ID as Number of Shirts and change the Number format to Number with no decimal places. Change the Number format of the Sum of In Stock to the Number format with no decimal places.
18. In the Pivot Table, change the Style filter to show only jersey
19. Save the workbook, and then close it.
Attachment:- Shirts.rar