Emma sells and rents musical instruments. Her rental business involves renting instruments to K-12 students who participate in school bands and orchestras. Emma maintains an Excel worksheet to track instrument rentals. The information kept is as follows:
• Renter – name of renter
• Instrument – type of instrument rented
• Rental Date- the date rented
• Rental Period – option of 3 or 9 months only
• Insur Cov – whether renters elected instrument insurance
• Shipping Code – option of delivery to school (Ground or Rush) or pick up at store (Pickup)
Emma wants you to expand the information that is tracked. You will use VLOOKUP & HLOOKUP as well as IF (and Nested IF’s), COUNTIF, and SUMIF functions.
Open the ‘Music’ spreadsheet. Notice the different tabs in the spreadsheet- complete the following requirements carefully:
1. In the Documentation worksheet, enter your name and the date.
2. In the Rental Data worksheet, in column G, create a formula that uses a HLOOKUP function to
assign a group code from the Instrument Groups range in the Rental information worksheet to
the instrument listed in column B.
3. In column H, create a formula to provide the return date of the rental period. Hint: Use the
function EDATE.
4. In column I, create a formula using the IF and VLOOKUP functions to calculate the rental charges
for each instrument based on the instrument’s group code, the rental period, and the
Instrumental Rental Charges table. (For the IF function arguments, use one VLOOKUP function
for 3 months and another 9 months. The defined name RentalCharges has been assigned to the
Instrumental Rental Charges table. Formula is basically saying IF the rental period = 3, VLOOKUP
the Group Code, in the rental charges supplemental table, provide me the data in column 2, and
give me exact match (FALSE), if not (which means it is the other option of 9 months) then
VLOOKUP the group code, in the rental charges supplemental table, provide me the data in
column 3, and give me an exact match (FALSE).
5. In column J, enter a formula to calculate the insurance cost if the rental has elected insurance
coverage. Use the instrument’s group code and the Monthly Insurance column in the
RentalCharges table to look up the insurance cost. Remember to multiply the monthly insurance
charge by the rental period. If the renter has not elected insurance, the cost is 0. Hint: If the
InsCov = “Yes”, VLOOKUP the Group Code, in the rental charges supplemental table, provide info
from column 4, and make it exact match (FALSE), multiply that by the rental period column
(which is the 3 or 9 months), if not put 0.
6. In column K, create a Nested IF function to determine the shipping cost for each instrument. Use
the shipping code (column F) and the shipping charge option Pickup (0), Ground ($25), and Rush
($50) to assign shipping costs to each rental instrument. The calculation can be done in different
ways by nesting two of the items for the true and the leftover is the false item. Here is one
option: IF the Shipping Code = Rush, put in 50, IF Shipping Code = Ground, put in 25, if not put in
0.
7. In column L, calculate the total cost, which is the sum of the rental charges, the insurance cost,
and the shipping cost.
8. Format columns I through L with the Accounting format with no decimal places. If you have
done it correct, below is what your first line should look like.

9. In the Rental Data worksheet, enter the new record:a. Renter: Clack
b. Instrument: Trombone
c. Rental Date: 9/15/18
d. Rental Period: 9
e. Insur Cov: Yes
f. Shipping Code: Rush
10. In the Rental Report worksheet, complete the Rental Summary report by creating formulas in
the range C4:D5 using the COUNTIF and SUMIF functions. For the SUMIF: SUMIF the rentals
spreadsheet in the Rental Period Column, contains a 3, sum the Rental $ column. Be sure to
select the Rental $ not the Total Cost.

  
 
 
 
  

 

Renter Calmos Instrument Flute Rental Date 8/15/18 Insur Rental Period Coverage Yes Shipping Group Code Code Ground A Return Date Rental S 11/15/18 $ Insurance Shipping Cost 9 $ 25 Cost 47 S Total Cost $