Note: To complete this assignment, you will be required to use the Data Files for Students. Visit www.cengage.com/ct/studentdownload for detailed instructions or contact your instructor for information about accessing the required files. Problem: You are part of a task force assessing the classroom capacities of the middle schools in your district. You have been charged with creating a master worksheet for the district and separate worksheets for each of the two middle schools. The middle school worksheets should be based on the district worksheet. Once the worksheets have been created, the middle school data can be entered into the appropriate worksheets, and the district worksheet will reflect districtwide information. The district worksheet appears as shown in Figure 1. Figure 1 The master worksheet used to create the capacity evaluation workbook is part of the Data Files for Students. Instructions: Perform the following tasks. 1. Run Excel. Open the workbook Lab 5-1 Overlea Schools from the Data Files for Students. Save the workbook as a workbook using the file name, Lab 5-1 Overlea Schools Complete. 2. Add two worksheets to the workbook after Sheet1 and then paste the contents of Sheet1 to the two empty worksheets. 3. From left to right, rename the sheet tabs District, Hillview Middle School, and McCarty Middle School. Color the tabs as shown in Figure 1. On each of the school worksheets, change the title in cell B2 to match the sheet tab name. On each worksheet, fill the range B2:G3 to match Figure 1. Enter the data in Table 1 into the school worksheets. Table 1 4. On the two school worksheets, calculate Average Students per Room in column E and totals in row 9. 5. On the District worksheet, use the SUM function, 3-D references, and copy-and-paste capabilities of Excel to populate cells C6:D8 and cells F6:G8. First, compute the sum in cells C6:D6 and F6:G6, and then copy the ranges C6:D6 and F6:G6 through ranges C7:D8 and F7:G8 respectively. Finally, calculate average students per room for the district for each grade level, and for the district as a whole. 6. Select the range F6:F9 on the District worksheet. Select all the worksheets. 7. Use the Format Cells dialog box to apply a custom format of [Red]#,###; -. 8. Select the range G6:G9 on the District worksheet. Select all the worksheets. 9. Use the Format Cells dialog box to apply a custom format that will format all nonzero numbers similar to the format applied in Step 7 but with green for nonzero entries. 10. Use the Cell Styles button (HOME tab | Styles group) to create a new cell style named My Title. Use the Format button (Styles dialog box) to create a format. Use the Font sheet (Format Cells dialog box) to select the Britannic Bold font. Check only the Alignment and Font check boxes in the Style dialog box. 11. Select cells B2:B3 on the District worksheet. Select all the worksheets. Apply the My Title style to the cell. 12. Using Figure 5 72 as a guide, add borders to the worksheets. The borders should be the same on all worksheets. 13. If requested by your instructor, enter the text Prepared by followed by your name in the header, on the left side. 14. Save the workbook. Submit the revised workbook as specified by your instructor. 15. Did you calculate an average in cell E9 using the data in column E or the data in row 9? Explain your reasoning for your choice.