Production & Operations

Express Car Rental

Jason Easton is a member of the support/decision team for the San Diego branch of Express Car Rental. He created a worksheet to keep track of weekly rentals in an attempt to identify trends in choices of rental vehicles, length of rental, and payment method. This spreadsheet is designed only for Jason and his supervisor to try and find weekly trends and possibly use this information when marketing and forecasting the type of cars needed onsite. The data for the dates of rental, daily rates, payment method, and gas option has already been entered.

a. Open the Excel file, e02ch03CarRental. Save your file as e02ch03CarRental_LastName_FirstName, using your last and first name.

b. On the RentalData worksheet, in cell E6, enter a date formula to determine the length of the rental in days. Copy this formula to the range E7:E32.

c. Name the range A37:B40 RentalRates.

d. In cell G6, use the appropriate lookup and reference function to retrieve the rental rate from the named range rental rates. The function should look for an exact matching value from column A in the data. Copy the function down the column to cell G32.

e. Name cell B42 Discount and cell B44 GasCost.

f. In cell H6 enter a formula to determine any discount that should be applied. If the payment method was Express Miles or Rewards, the customer should receive the discount shown in B42. If no discount should be applied, the formula should return a zero. Use the named range for cell B42, not the cell address, in this formula. Copy the formula to the range H7:H32.

g. In cell J6, enter a formula to determine the cost of gas if the customer chose that option as indicated in column I. Use the named range for cell B44 in this formula. If the customer did not take the gas option, return a value of 0. Copy the formula to the range J7:J32.

h. In cell K6, enter a formula to determine the total cost of the rental based on the daily rate, the number of days rented, the cost of gas, and any discount given to the customer. Copy the formula to the range K7:K32. Note the discount applies only to the car rental, not the cost of gas.

i. In cell B46, use the appropriate function to calculate the median length of the rental in days.

j. In cell B47, use the appropriate function to count the number of rentals in the data using column A.

k. On the ClientData worksheet, use Flash Fill to separate the clients names in column A to the FirstName and LastName columns. Adjust column width of columns B and C if necessary.

k. Complete the Documentation worksheet according to your instructor's direction. Insert the file name in the left-footer section of all worksheets in the workbook.

l. Save the workbook, close Excel, and then submit your file on the given link in the Week 4 Case Study folder Case Study submission link