Chid Abuse

HSP Computer Superstore Project Description In this project, you will modify a workbook for the HSP Computer Superstore to track the gross revenue aggregates for 2011. You will use theatrical roles to calculate total sales, average sales, and median, minimum, and maximum sales hold dears. Additionally, you will piss a summary worksheet using values from the other spreadsheets in the workbook. Instructions For the break up 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 kiosk B25, insert a ladder that will total all of the sales for Quarter 1 in the range B6B23. write the wait on in carrel B25 to the range C25F25. 6 3 On the Main St. worksheet, adjust the mainstay widths for columns C, D, and E to 13. 57 (100 pixels). 3 4 On the Main St. worksheet, in mobile phone B26, insert a utilisation that will ca lculate the average sales for Quarter 1. transcript the function in booth B26 to the range C26E26. 6 5 On the Main St. worksheet, in cadre B27, insert a function that will introduction the median sales value for Quarter 1. write the function in cell B27 to the range C27E27. 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 C28E28. 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 C29E29. 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 (B6B23). Copy the function in cell B30 to the range C30E30. 6 9 Group the Main St. and Franklin Ave. worksheet tabs. With both worksheets selected, confabulate in cell F6 and insert a function that will total all of the 2011 sales for the commencement ceremony agent. Copy the function in cell F6 to the range F7F23. Ungroup the worksheets. 12 10 On the Main St. worksheet, in cell G6, insert a function that will display the textbook Gold if the sales agents 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 G7G23. 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 preceding(prenominal) 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(p) cell. 1 14 On the Main St. worksheet, display green data bars with a side fill in all cells in the range F6F23. 3 15 On the Main St. worksheet, create an Excel table with headers using the range A5G23. Sort the rack up column from smallest to largest, and then filter the crime syndicate column so that only those requiring training are displayed. 12 16 Use the Find and transpose 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 chequer 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 abstract, 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 i n cell E25 on the Main St. orksheet. On the Main St. worksheet, 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 target 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