Please post as soon as possible
Case Problem 3 CHALLENGE Data File needed for this Case Problem: Wait.xIsx YuriTech Kayla Schwartz is the customer service manager at YuriTech, an electronics and computer firm located in Scottsdale, Arizona. Kayla is analyzing the calling records for technical support calls to YuriTech to determine which times are understaffed, resulting in unacceptable wait times. She has compiled several months of data and calculated the average wait times in one-hour intervals for each day of the week. You will format Kayla's workbook to make it easier to determine when YuriTech should hire more staff to assist with customer support requests. Complete the following: 1. Open the Wait workbook located in the Excel2 > Case3 folder, and then save the workbook as Wait Times in the location specified by your instructor. 2. In the Documentation sheet, enter your name in cell B3 and the date in cell B4. 3. Apply the lon theme to the workbook. 4. Format the Documentation sheet as follows: a. Format the title in cell Al using a 36-point Impact font with the Purple, Accent 6 font color. h. Format the range A3:A5 with the Accent6 cell style. c. Add a border around the cells in the range A3:B5. Wrap the text within each cell, and top-align the cell text. 5. Copy the format you used in cell Al of the Documentation sheet, and paste it to cell A1 of the Wait Times worksheet. Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203 EX 128 Excel | Module 2 Formatting Workbook Text and Data 6. Format the text in cell A2 with 14-point bold font and the Purple, Accent6 font color. 7. In the range A14:H39, format the average customer wait times for each hour and day of the week data as follows: a. Merge and center the range A14:H14, and apply the Title cell style to the merged contents. b. Change the number format of the data in the range B16:H39 to show one decimal place. c. Format the column and row labels in the range A15:H15,A16:A39 with the Accent6 cell style. Center the column headings in the range B15:H15. 8. In cell B5, enter the value 22 as an excellent wait time. In cell B6, enter 34 as a good wait time. In cell B7, enter 45 as an acceptable wait time. In cell B8, enter 60 as a poor wait time. In cell B9, enter 78 as a very poor wait time. In cell B10, enter 90 as an unacceptable wait time. 9. In the range A4:C10, apply the following formats to the wait time goals: a. Merge and center the range A4:C4, and apply the Accent6 cell style to the merged cells. b. Add borders around the cells in the range A4:C10. 10. In cell E4, enter the label Average Wait Time (All Days). In cell E7, enter the label Average Wait Time (Weekdays). In cell E10, enter the label Average Wait Time (Weekends). 11. Merge and center the range E4:F6, wrap the text in the merged cell, center the cell content both horizontally and vertically, and then apply the Accento cell style to the merged cell. 12. Copy the format from the merged cell E4:F6 to cells E7 and E10. 13. In cell G4, enter a formula to calculate the average of the wait times in the range B16:H39. In cell G7, enter a formula to calculate the average weekday wait times in the range C16:G39. In cell G10, calculate the average weekend rate times in the range B16:B39, H16:H39. 14. Merge and center the ranges G4:G6, G7:G9, and G10:G12, and then center the calculated averages vertically within each merged cell. 15. Add borders around the cells in the range E4:G12. 16. Change the fill color of the range A5:C5 to a medium green, the fill color of the range A6:C6 to a light green, the fill color of the range A7:C7 to a light gold, the fill color of the range A8:C8 to a light red, and the fill color of the range A9:C9 to a medium red. Format the range A10:C10 with white text on a black background. + Explore 17. Use conditional formatting to highlight cells with custom formats as follows: a. Select the range G4:G12, B16:H39. Use conditional formatting to highlight cells with values less than 22 with a custom format that matches the fill color used in the range A5:C5. b. Use conditional formatting to highlight cells with values greater than 90 in the range G4:G12, B16:H39 with a custom format of a white font on a black fill. c. Use conditional formatting to highlight cells with values between 22 and 34 in the range G4:G12,B16:H39 with a custom format that matches the fill color used in the range A6:C6. d. Use conditional formatting to highlight cells with values between 34 and 60 in the range G4:G12, B16:H39 with a light gold fill color that matches the cells in the range A7:C7. e. Use conditional formatting to highlight cells with values between 60 and 78 in the range G4:G12, B16:H39 with light red, matching the fill color of the cells in the range A8:C8. f. Use conditional formatting to highlight cells with values between 78 and 90 in the range G4:G12, B16:H39 with medium red, matching the fill color of the cells in the range A9:09. 18. In cell A41, enter the label Notes and then format it with the Title cell style. 19. Merge the range A42:H50. Top- and left-align the contents of the cell. Turn on text wrapping within the merged cell. Add a thick outside border to the merged cell. 20. Within the merged cell in the range A42:H50, summarize your conclusions about the wait times. Answer whether the wait times are within acceptable limits on average for the entire week, on weekdays, and on weekends. Also indicate whether there are times during the week that customers are experience very poor to unacceptable delays. 21. Format the printed version of the Wait Times worksheet as follows: a. Scale the sheet so that it fits on a single page in portrait orientation. b. Center the sheet on the page horizontally and vertically. Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WON 02-200-203
