SmartExpert

(118)

$30/per page/Negotiable

About SmartExpert

Levels Tought:
Elementary,Middle School,High School,College,University,PHD

Expertise:
Accounting,Business & Finance See all
Accounting,Business & Finance,Economics,English,HR Management,Math Hide all
Teaching Since: Apr 2017
Last Sign in: 56 Weeks Ago, 6 Days Ago
Questions Answered: 7570
Tutorials Posted: 7352

Education

  • BS,MBA, PHD
    Adelphi University/Devry
    Apr-2000 - Mar-2005

Experience

  • HOD ,Professor
    Adelphi University
    Sep-2007 - Apr-2017

Category > Business & Finance Posted 06 Jun 2017 My Price 11.00

Express Car Rental Grader as CarRental

YO16_XL_CH03_GRADER_PS2_AS - Car Rental 1.1

 

Project Description:

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 on site. The data for the dates of rental, daily rates, payment method, and gas option have already been entered.

 

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 file named e02ch03_grader_as_CarRental.xlsx. Save the file with the name e02ch03_grader_as_CarRental_LastFirst, replacing LastFirst with your last and first name.

0.000

2

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

12.000

3

Assigned a named range of RentalRates to cells A37:B40.

5.000

4

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

12.000

5

Assign a named range of Discount to cell B42 and a named range of GasCost to cell B44.

5.000

6

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.

15.000

7

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

12.000

8

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. Note that the discount applies only to the car rental, not the cost of gas. Copy the formula to the range K7:K32.

10.000

9

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

10.000

10

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

10.000

11

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

5.000

12

Insert the FileNamein the left footer section of all worksheets in the workbook.

4.000

13

Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

0.000

 

Total Points

100.000

 

 

 

Express Car Rental
Rentals Initiated in Week Starting 4/5/2017
Created by Jason Easton
                     
Auto Type Auto Id Date Rented Date Returned/or Expected Return #Days Rented Payment Method Daily Rate Amount of Discount Gas Option Cost of Gas Total Cost
Green Collection 988 4/5/2017 4/8/2017   Credit Card     n    
Compact/Midsize 275 4/5/2017 4/10/2017   COD     n    
Compact/Midsize 277 4/9/2017 4/10/2017   Express Miles     n    
Green Collection 990 4/5/2017 4/6/2017   Credit Card     n    
Fullsize/Standard 350 4/5/2017 4/8/2017   Credit Card     y    
SUV/Minivan 550 4/5/2017 4/9/2017   Rewards     y    
SUV/Minivan 551 4/7/2017 4/10/2017   Credit Card     n    
Fullsize/Standard 352 4/7/2017 4/9/2017   Credit Card     y    
Green Collection 989 4/6/2017 4/9/2017   Credit Card     n    
Compact/Midsize 275 4/11/2017 4/18/2017   COD     n    
Fullsize/Standard 355 4/9/2017 4/16/2017   Credit Card     y    
Fullsize/Standard 356 4/6/2017 4/8/2017   Credit Card     y    
Fullsize/Standard 350 4/9/2017 4/13/2017   Rewards     y    
SUV/Minivan 553 4/8/2017 4/14/2017   Express Miles     y    
SUV/Minivan 550 4/11/2017 4/17/2017   COD     y    
Green Collection 988 4/9/2017 4/15/2017   Credit Card     n    
Green Collection 989 4/10/2017 4/16/2017   Express Miles     n    
Green Collection 990 4/7/2017 4/10/2017   Credit Card     n    
Fullsize/Standard 356 4/9/2017 4/10/2017   Rewards     n    
SUV/Minivan 551 4/11/2017 4/15/2017   Express Miles     y    
SUV/Minivan 554 4/7/2017 4/9/2017   Express Miles     y    
Fullsize/Standard 352 4/10/2017 4/11/2017   Credit Card     y    
Fullsize/Standard 356 4/11/2017 4/17/2017   Express Miles     y    
Compact/Midsize 276 4/11/2017 4/14/2017   Credit Card     n    
Compact/Midsize 277 4/11/2017 4/15/2017   Rewards     n    
SUV/Minivan 554 4/10/2017 4/12/2017   COD     y    
Green Collection 990 4/11/2017 4/18/2017   Express Miles     n    
                     
                     
                     
Rental Rates                    
Green Collection $49.99                  
Compact/Midsize $76.49                  
Fullsize/Standard $84.99                  
SUV/Minivan $104.99                  
                     
Discount for Express Miles or Rewards 20%                  
                     
Cost to Fill Tank $65.00                  
                     
Median Rental Length                    
Number of Rentals                    
                     

Answers

SmartExpert
(118)
Status NEW Posted 06 Jun 2017 04:06 PM My Price 11.00

-----------

Attachments

file 1496777181-Express Car Rental Grader as CarRental.xlsx preview (97 words )
Ex-----------pre-----------ss -----------Car----------- Re-----------nta-----------lRe-----------nta-----------ls -----------Ini-----------tia-----------ted----------- in----------- We-----------ek -----------Sta-----------rti-----------ng -----------4/5-----------/20-----------17C-----------rea-----------ted----------- by----------- Ja-----------son----------- Ea-----------sto-----------nAu-----------to -----------Typ-----------eAu-----------to -----------IdD-----------ate----------- Re-----------nte-----------dDa-----------te -----------Ret-----------urn-----------ed/-----------or -----------Exp-----------ect-----------ed -----------Ret-----------urn-----------#Da-----------ys -----------Ren-----------ted-----------Pay-----------men-----------t M-----------eth-----------odD-----------ail-----------y R-----------ate-----------Amo-----------unt----------- of----------- Di-----------sco-----------unt-----------Gas----------- Op-----------tio-----------nCo-----------st -----------of -----------Gas-----------Tot-----------al -----------Cos-----------tGr-----------een----------- Co-----------lle-----------cti-----------onC-----------red-----------it -----------Car-----------dnC-----------omp-----------act-----------/Mi-----------dsi-----------zeC-----------ODE-----------xpr-----------ess----------- Mi-----------les-----------Ful-----------lsi-----------ze/-----------Sta-----------nda-----------rdy-----------SUV-----------/Mi-----------niv-----------anR-----------ewa-----------rds-----------Ren-----------tal----------- Ra-----------tes-----------Dis-----------cou-----------nt -----------for----------- Ex-----------pre-----------ss -----------Mil-----------es -----------or -----------Rew-----------ard-----------sCo-----------st -----------to -----------Fil-----------l T-----------ank-----------Med-----------ian----------- Re-----------nta-----------l L-----------eng-----------thN-----------umb-----------er -----------of -----------Ren-----------tal-----------sCl-----------ien-----------t
Not Rated(0)