You have been hired as an intern to help Ingrid, the owner of Ingrids Instrument Rentals, evaluate how her business is going by using an Excel workbook. She has created a simple workbook with 4 worksheets, one for each school to which she rents instruments. She wants to know how much she is making from each school, as well as summary data for all four schools. Each worksheet lists the type of instruments she is renting to the school, the number of students, and the rate per student. She would like to see on each worksheet total charges for each instrument and a total number of students and total charges for the school. The Rate per Student comes from the RentalRates worksheet, but she would like it to come from a separate workbook. She would also like to be able to share the workbook with the music directors at each school so they can update their number of students each semester.
a.Open e04ws07Music and save it as e04ws07Music_LastFirst, replacing LastFirst with your actual name.
b.Group the Valley through Mills worksheets. Create a formula in cells D5:D15 to calculate the total charges for each instrument. Format cells C5:D16 with the Accounting Number Format. In cells B16 and D16, calculate the total number of students and total charges for the school. Ungroup the worksheets and save the workbook.
c.On the Summary worksheet, enter a 3-D SUM function in cells B5:B15 to calculate the total students for all four schools. Use Fill Across Worksheets to copy the contents and formatting of cells D5:D15 and cell B16 from the Mills worksheet to the Summary worksheet. Use Fill Across Worksheets to copy the formatting for cells B5:C15 from the Mills worksheet to the Summary worksheet. Save the workbook.
d.On the LinkedSummary worksheet, in cell A4, create a linked consolidation using cells A4:D15 from each of the school worksheets. Be sure and select Top Row, Left Column, and Create links to source data in the Consolidate dialog box. Change the column width of column A to 13, hide column B, and AutoFit columns C:E. Save the workbook.
e.Save e04ws07Music_LastFirst as e04ws07MusicLink_LastFirst, replacing LastFirst with your actual name, and delete the RentalRates worksheet. Open e04ws07MusicRates and arrange the workbooks side by side.
f.On e04ws07MusicLink_LastFirst, group the Valley through Summary worksheets. Click cell C5 and in the formula bar replace RentalRates in the VLOOKUP (which is no longer a valid range name) with a link to the range InstrumentRates (cells A4:B14) on the e04ws07MusicRates workbook. Copy the formula to cells C6:C15. Ungroup the sheets. Save the workbooks. Close e04ws07MusicRates.