Challenge It

In this challenge activity, you will complete a project that incorporates many of the key skills learned in the Excel Unit. For this project, you are an IT manager responsible for managing the technology inventory for your college. You have been tasked with creating a workbook that manages, analyzes and summarizes technology inventory.

1. Open the Excel spreadsheet Starter_Excel_Challenge. Save the file to your flash drive or other safe location as designated by your instructor and name the file Lastname_Firstname_Excel_Challenge.
2. Merge and center the text in cell A1 across the range A1:G1 and apply the cell style Heading 2.
3. Merge and center the text in cell A2 across the range A2:G2 and apply the cell style Heading 4.
4. Apply the Title cell style to the range A11:F11.
5. Autofit all cell contents.
6. In cell B4, construct a function to SUM the total quantity in stock.
7. In cell B5, construct a function that will find the AVERAGE price (or value) of all of the items. Format as currency with two decimal places.
8. In cell B6, construct a function that will find the MEDIAN price (or value) of all items. Format as currency with two decimal places.
9. In cell B7, construct a function that will find the lowest or MIN price (or value) for all items. Format as currency with two decimal places.
10. In cell B8, construct a function that will find the highest or MAX price (or value) for all items. Format as currency with two decimal places.
11. Format the Values in column C as currency with two decimal places.
12. Select the range A4:B9, and move it to G4:H9. Autofit the cell contents if necessary so that all contents are visible.
    
13. Insert two new empty columns after Item# in Column B. 
14. Label the two new columns. Your new Column C should be Item # and new Column D should be Item Type.
15. In cell C12, use Flash fill to list the Item # in column C.
16. In cell D12, use Flash Fill to list the Item Type in column D.
17. Select column B and delete it. Ensure the entire column and its contents are deleted.
    
18. In cell H9, construct a function that will count the number of Laptop Categories.
    
19. Select the range G4:H9 and move to the range A4:B9 and apply the 20% Accent 1 cell style.
20. In cell F12, construct a function that will determine if the stock level is ok, or needs to be checked. The threshold for the stock level is 7. If the quantity is less than 7, then it needs to be checked. Use “Check” and “OK” as the true and false values. Use the fill handle to fill the function to cell F47.
21. With the range F12:F47 selected, use conditional formatting, highlight cells rule, text that contains “Check”. Format the text with Light Red Fill with Dark Red Text.
22. Run Spelling and Grammar check. There should be at least 4 spelling errors that need to be corrected.
    
23. Rename Sheet1 to Inventory.
24. Add a new sheet and name it Summary.
25. On the Summary Sheet, in cell A1 type PVCC Summary.
26. In cell A2, construct a formula that will display the current day and time.
27. Merge and center the text in cell A1 across the range A1:B1 and apply the cell style Heading 4.
28. Merge and center the text in cell A2 across the range A2:B2 and apply the cell style Heading 4.
29. In cell A4, type Total Quantity.
30. In cell B4 on the Summary Sheet, enter a formula that will reference the Total Quantity in cell B4 on the Inventory Sheet.
31. In cell A5 on the Summary Sheet, type Lowest Price.
32. In cell B5 on the Summary Sheet, enter a formula that will reference the Lowest Priced item in cell B7 on the Inventory Sheet.
33. In cell A6 on the Summary Sheet, type Highest Price.
34. In cell B6 on the Summary Sheet, enter a formula that will reference the Highest Priced item in cell B8 on the Inventory Sheet.
35. In cell A7 on the Summary Sheet, type Total Value. Then, in cell B7 on the Summary Sheet, construct a formula that will calculate the Total value by summing the Value on the Inventory sheet.
36. Select the range A4:B7 on the Summary Sheet, and left align. If necessary, autofit the cell contents so that all of the values are visible. Apply the 20% Accent 1 cell style to the range.
37. On the Inventory Tab, select the range A11:F47 and format as a table with headers.
38. Apply a filter to the category field so that only Laptops display. Add a total row.
39. On the Summary Sheet in cell A9 type Laptops. In cell B9, type the total number of laptops that displays on the Inventory Sheet.
40. On the Inventory Sheet, apply a filter to the category field so that only Networking displays.
41. On the Summary Sheet in cell A10 type Networking. In cell B10, type the total number of networking items that display on the Inventory Sheet.
42. On the Inventory Sheet, apply a filter to the category field so that only Tablets display.
43. On the Summary Sheet in cell A11, type Tablets. In cell B11 type the total number of Tablets that displays on the Inventory Sheet.
44. On the Summary Sheet, place a thick outside border around the range A9:B11.
45. On the Summary Sheet, select the range A9:B11 and insert the Clustered Column recommended chart.
46. Move the Chart to its own sheet named Chart.
47. Change the Chart Title to Current Quantity.
48. Apply Chart Style 9 and change colors to the first one under Monochromatic.
49. Apply Data Labels in the Center position to the Chart.
50. Add a new sheet to the workbook, and name it Trend.
51. In cell A1 type Inventory Trend. Merge and Center it across the range A1:G1 and apply the Heading 1 cell style.
52. In cell A3, type Item Type.
53. In cell B3, type January and press tab. In cell C3 type February and press tab.
54. Select the range B3:C3, and use the fill handle to fill the range to cell F3. The months of March, April and May will auto fill.
    
55. In cell A4 type Networking, in cell A5 type Laptop, and in cell A6 type Tablet.
56. On the Trend sheet, continue to enter the following data:

57. Select the range A3:F6 and apply the slice theme and autofit all cell contents.
58. In cell G3, type Trend.
59. In cell G4, add a sparkline that shows the trend for January-May. Apply markers, and use the fill handle to copy the sparkline to the cells G5 and G6.
60. Reorder the sheets so that they are in this order: Summary, Trend, Chart, Inventory.
61. Group all 4 sheets and launch the Page Setup Dialog Box.
62. Apply the following on the Page Setup Dialog Box:
    • Fit to 1 page
    • Center horizontally on page
    • Insert the File Name in the Footer on the left side
63. Ungroup the sheets and apply the following advanced properties:
    • Title: Excel Challenge
    • Subject: BPC110 and section #
    • Author: Your first and last name
    • Keywords: Charts, Functions, Sparklines
    • Comments: Right now I am feeling ___________. (complete the sentence and in one word tell me how you are feeling)
64. View the print preview, and carefully make any modifications necessary.  Take care not to un-do any of the tasks in this Challenge.
    
65. Run  spelling and grammar check, and submit your file based on your instructor’s instructions.