Excel Help !!!

profileDeMario34
McDonald_YO19_Excel_Ch07_Assessment_Music.zip

YO19_Excel_Ch07_Assessment_Music_Instructions.docx

Grader - Instructions Excel 2019 Project

YO19_Excel_Ch07_Assessment_Music

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.

Steps to Perform:

Step

Instructions

Points Possible

1

Start Excel. Download and open the Excel file named Excel_CH07_Assessment_Music.xlsx. Your name will automatically be added to the start of the file name. Save the file to a location where you are storing your data files.

0

2

When worksheets are grouped, what you do to one worksheet happens to the other worksheets in the group. Group the Valley through Mills worksheets. Create a formula in cells D5:D15 to calculate the total charges for each instrument.

2

3

With the worksheets still grouped, format cells C5:D16 with the Accounting Number Format with no decimals.

2

4

Entering formulas into grouped worksheets is a very efficient way to simultaneously create new data in multiple worksheets and is the same as entering data in grouped worksheets. 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.

2

5

3-D references allow formulas and functions to use data from cells and cell ranges across worksheets. On the Summary worksheet, enter a 3-D SUM function in cells B5:B16 to calculate the total students for all four schools.

2.1

6

To save yourself time in formatting the Summary worksheet, use Fill Across Worksheets to copy the contents and formatting of cells C5:D16 from the Mills worksheet to the Summary worksheet.

2

7

On the LinkedSummary worksheet, you want to consolidate data and include the cell references from other worksheets that contribute to the consolidated data result. 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.

2.8

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.

1.2

9

Next, you want to create a link between two workbooks to ensure the rates in the Music workbook are always up to date by linking to the rates in the Rates workbook. Open Excel_CH07_Rates.xlsx and arrange the workbooks side by side. On the Excel_CH07_Assessment_Music workbook, group the Valley through Summary worksheets. Click cell C5, and in the formula bar, replace RentalRates in the VLOOKUP function (which is no longer a valid range name) with a link to the range InstrumentRates (cells A4:B14) on the Excel_CH07_Rates workbook.

3.9

10

Copy the formula in cell C5 to cells C6:C15. Ungroup the sheets. Save the workbooks. Close the Excel_CH07_Rates workbook.

2

11

Save and exit Excel. Submit the Excel_CH07_Assessment_Music.xlsx file as directed.

0

Total Points

20

Created On: 12/14/2019 1 YO19_Excel_Ch07_Assessment - Music 1.0

McDonald_Excel_CH07_Assessment_Music.xlsx

Valley

Instrument Rentals
Valley Day School
First Semester for School Year 2021-2022
Instrument #Students Rate per Student Total Charges
Violin 20 350
Viola 12 350
Cello 3 450
Bass 2 450
Clarinet 10 275
Oboe 5 295
Flute 8 195
Saxophone 8 335
French Horn 3 420
Tuba 2 425
Trumpet 7 210
Total

&F

Mission

Instrument Rentals
Mission Junior High
First Semester for School Year 2021-2022
Instrument #Students Rate per Student Total Charges
Violin 12 350
Viola 10 350
Cello 4 450
Bass 3 450
Clarinet 9 275
Oboe 4 295
Flute 10 195
Saxophone 12 335
French Horn 2 420
Tuba 2 425
Trumpet 8 210
Total

&F

Jupiter

Instrument Rentals
Jupiter High School
First Semester for School Year 2021-2022
Instrument #Students Rate per Student Total Charges
Violin 17 350
Viola 10 350
Cello 5 450
Bass 2 450
Clarinet 12 275
Oboe 4 295
Flute 11 195
Saxophone 8 335
French Horn 2 420
Tuba 2 425
Trumpet 8 210
Total

&F

Mills

Instrument Rentals
Mills Elementary School
First Semester for School Year 2021-2022
Instrument #Students Rate per Student Total Charges
Violin 15 350
Viola 8 350
Cello 7 450
Bass 3 450
Clarinet 8 275
Oboe 6 295
Flute 12 195
Saxophone 10 335
French Horn 4 420
Tuba 2 425
Trumpet 10 210
Total

&F

Summary

Instrument Rentals
Mills Elementary School
First Semester for School Year 2021-2022
Instrument #Students Rate per Student Total Charges
Violin
Viola
Cello
Bass
Clarinet
Oboe
Flute
Saxophone
French Horn
Tuba
Trumpet
Total

&F

LinkedSummary

Instrument Rentals
Mills Elementary School
First Semester for School Year 2021-2022

&F

RentalRates

Rental Rates
Instrument Rate per Student
Bass $ 450.00
Cello $ 450.00
Clarinet $ 275.00
Flute $ 195.00
French Horn $ 420.00
Oboe $ 295.00
Saxophone $ 335.00
Trumpet $ 210.00
Tuba $ 425.00
Viola $ 350.00
Violin $ 350.00

&F

Excel_CH07_Rates.xlsx

Rates

Rental Rates
Instrument Rate per Student
Bass $ 450
Cello $ 450
Clarinet $ 275
Flute $ 195
French Horn $ 420
Oboe $ 295
Saxophone $ 335
Trumpet $ 210
Tuba $ 425
Viola $ 350
Violin $ 350

&F