Chid Abuse

HSP Computer Superstore Project Description: In this project, you will modify a workbook for the HSP Computer Superstore to track the sales totals for 2011. You will use functions to calculate total sales, average sales, and median, minimum, and maximum sales values. Additionally, you will create a summary worksheet using values from the other spreadsheets in the workbook. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step| Instructions| Points Possible| 1| Start Excel.Open the downloaded Excel workbook named GO_e02_Grader_IR. xlsx. | 0| 2| On the Main St. worksheet, in cell B25, insert a function that will total all of the sales for Quarter 1 in the range B6:B23. Copy the function in cell B25 to the range C25:F25. | 6| 3| On the Main St. worksheet, adjust the column widths for columns C, D, and E to 13. 57 (100 pixels). | 3| 4| On the Main St. worksheet, in cell B26, insert a function that will calculate the average sales for Q uarter 1. Copy the function in cell B26 to the range C26:E26. 6| 5| On the Main St. worksheet, in cell B27, insert a function that will display the median sales value for Quarter 1. Copy the function in cell B27 to the range C27:E27. | 6| 6| On the Main St. worksheet, in cell B28, insert a function that will display the minimum sales value for Quarter 1. Copy the function in cell B28 to the range C28:E28. | 6| 7| On the Main St. worksheet, in cell B29, insert a function that will display the maximum sales value for Quarter 1. Copy the function in cell B29 to the range C29:E29. | 6| 8| On the Main St. orksheet, in cell B30, insert a function that will count the number of sales agents with sales greater than $75,000 in Quarter 1 (B6:B23). Copy the function in cell B30 to the range C30:E30. | 6| 9| Group the Main St. and Franklin Ave. worksheet tabs. With both worksheets selected, click in cell F6 and insert a function that will total all of the 2011 sales for the first agent. Copy the function in cell F6 to the range F7:F23. Ungroup the worksheets. | 12| 10| On the Main St. worksheet, in cell G6, insert a function that will display the text Gold if the sales agent's total sales alue for 2011 is greater than or equal to 225000. Otherwise, the function will display the text Training Required. Copy the function in cell G6 to the range G7:G23. | 6| 11| Copy the contents of cell A4 on the Franklin Ave. worksheet, and paste them into cell A4 on the Main St. worksheet. | 1| 12| On the Main St. worksheet, freeze above row 6 so that row 5 continues to display as you scroll down on the worksheet. | 2| 13| On the Main St. worksheet, in cell B32, enter the value 1/15/12. Clear the contents and formatting in cell B32, and then enter the date January 15, 2012 in the same cell. 1| 14| On the Main St. worksheet, display green data bars with a gradient fill in all cells in the range F6:F23. | 3| 15| On the Main St. worksheet, create an Excel table with headers using the range A5 :G23. Sort the Total column from smallest to largest, and then filter the Category column so that only those requiring training are displayed. | 12| 16| Use the Find and Replace dialog box to find the text Sales Agent ID on the Main St. worksheet and replace it with the text Sales Rep ID. | 2| 17| Change the scaling of the Main St. worksheet so the contents fit on one page. 4| 18| Reposition the Main St. worksheet tab so that it appears to the right of the Franklin Ave. worksheet tab. Rename the Sheet1 worksheet tab to Summary, and then change the tab color to blue (under Standard Colors). | 6| 19| On the Summary worksheet, rotate the text in cells B4 and C4 so that it is angled counterclockwise (45 degrees). | 2| 20| On the Summary worksheet, in cell B8, construct a linking formula that will refer to the value in cell E25 on the Franklin Ave. worksheet. In cell C8, construct a linking formula that will refer to the value in cell E25 on the Main St. orksheet. On the Main St. workshe et, change the value in cell E6 to 45581. 09. Note that the Quarter 4 sales total for the Main St. store updates automatically in the Summary sheet. | 7| 21| On the Summary worksheet, in cell B11, use the NOW function to display the current date. | 3| 22| Ensure that the worksheets are correctly named and placed in the following order in the workbook: Summary, Franklin Ave. , Main St. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. | 0| | Total Points| 100|