Excel99

Office 2016 – myitlab:grader – Instructions Excel Project

YO16_XL_CH07_GRADER_PS1_HW - Music 1.6

Project Description: You have been hired as an intern to help Ingrid Theobald, the owner of Ingrid’s Instrument Rentals, evaluate how her business is going by using an Excel workbook. She has created a simple workbook with four 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.

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 file named e04ch07_grader_hw_Music.xlsx. Save it as e04ch07_grader_hw_Music_LastFirst using your last and first name. Enable content if necessary. 0 2 Group the Valley through Mills worksheets. Create a formula in cells D5:D15 to calculate the total charges for each instrument. 10 3 With the worksheets still grouped, format cells C5:D16 with the Accounting Number Format with no decimals. 10 4 In cells B16 and D16, with the worksheets still grouped, calculate the total number of students and total charges for the school. Ungroup the worksheets, and then save the workbook. 10 5 On the Summary worksheet, enter a 3-D SUM function in cells B5:B16 to calculate the total students for all four schools. 10 6 Use Fill Across Worksheets to copy the contents and formatting of cells C5:D16 from the Mills worksheet to the Summary worksheet. 10 7 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. 10 8 On the LinkedSummary worksheet, change the column width of column A to 13, hide column B, and change the column width of Columns C:E to 12. 6 9 Delete the RentalRates worksheet. Open e04ch07_grader_hw_MusicRates.xlsx and arrange the workbooks side by side. On e04ch07_grader_hw_Music_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 e04ch07_grader_hw_MusicRates workbook 16 10 Copy the formula in cell C5 to cells C6:C15. Ungroup the sheets. Save the workbooks. Close the MusicRates worksheet. 8 11 On e04ch07_grader_hw_Music_LastFirst, if necessary, add the Compare & Merge Workbooks button to the Quick Access Toolbar. Share e04ch07_grader_hw_Music_LastFirst and allow changes by more than one user at the same time. Save e04ch07_grader_hw_Music_LastFirst as e04ch07_grader_hw_Music2_LastFirst, using your last and first name. Change the Valley Day Schools #Students for violin to 22 and for tuba to 2. Save the changes and close the workbook. Open e04ch07_grader_hw_Music_LastFirst, click Enable Content (if necessary), and compare and merge the workbook with e04ch07_grader_hw_Music2_LastFirst. Save all the changes to a new sheet but do not stop sharing. Save and close any open workbooks. Note, Mac users, on the Review tab, click Share Workbook. Ensure that the Allow changes by more than one user at the same time option is selected, and then click OK. From the Tools menu, click Merge Workbooks. 10 12 Submit e04ch07_grader_hw_Music_LastFirst with the following worksheets in this order: Valley, Mission, Jupiter, Mills, Summary, and LinkedSummary. 0 Total Points 100

Updated: 11/10/2017 1 Current_Instruction.docx