Business Analytics Help !
DeMario34
McDonald_YO19_Excel_Ch03_Assessment_Car_Rental1.zipYO19_Excel_Ch03_Assessment_Car_Rental_Instructions.docx
Grader - Instructions Excel 2019 Project
YO19_Excel_Ch03_Assessment_Car_Rental
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.
Steps to Perform:
|
Step |
Instructions |
Points Possible |
|
1 |
Start Excel. Download and open the file named Excel_Ch03_Assessment_CarRental.xlsx. Grader has automatically added your last name to the beginning of the file name. Save the file to a location where you are storing your files. |
0 |
|
2 |
On the RentalData worksheet, in cell E6, enter a DATEDIF formula to determine the length of rental in days based on the date rented and the date returned or expected return. Copy this formula through cell range E7:E32. |
2 |
|
3 |
Assigned a named range of RentalRates to cell range A37:B40. |
0.8 |
|
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 formula through cell G32. |
2 |
|
5 |
In cell B42, assign a named range of Discount In cell B44, assign a named range of GasCost |
1.6 |
|
6 |
In cell H6, enter a formula to determine any discount that should be applied. If the payment method in column F was Rewards, the customer should receive the discount shown in B42, otherwise the formula should return a zero. Use the named range for cell B42, not the cell address, in this formula. Copy the formula through cell range H7:H32. |
2 |
|
7 |
In cell J6, enter a formula to determine the cost of gas. If the value in column I is "Y", then assign the cost of gas in B44, or else the formula should return zero. Use the named range for cell B44 in this formula, not the cell reference. Copy the formula through cell range J7:J32. |
2 |
|
8 |
In cell N11, use the appropriate function to count the number of rentals in the data, using the data in column A. |
2 |
|
9 |
In cell N6, determine the total number of times the criteria "Rewards" in cell M6 is used. Use absolute referencing so you can copy the formula through cell N9. |
2 |
|
10 |
In cell Q6, enter a formula to determine the average total cost based on the payment method type in cell P6. Use absolute references where necessary to copy the formula through cell Q9. |
2 |
|
11 |
In cell R6, enter a formula to calculate the total cost per payment method based on the payment method in cell P6. Use absolute references so that you can copy the formula through cell R9. |
2 |
|
12 |
On the ClientData worksheet, in cell B2, enter a formula to change the client's name to proper case. Copy the formula through cell B26. |
1.2 |
|
13 |
Insert the File Name code in the left footer section of all worksheets in the workbook. |
0.4 |
|
14 |
Save and close Excel_Ch03_Assessment_CarRental.xlsx. Exit Excel. Submit the file as directed. |
0 |
|
Total Points |
20 |
Created On: 12/14/2019 1 YO19_Excel_CH03_Assessment - Car Rental 1.0
McDonald_Excel_Ch03_Assessment_CarRental.xlsx
RentalData
| Express Car Rental | Car Rental Analysis | ||||||||||||||
| Rentals Initiated in Week Starting 4/5/2022 | |||||||||||||||
| 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 | Analysis Criteria | Payment Method | Analysis Critieria | Average Total Cost | Total Cost |
| Green Collection | 988 | 4/5/22 | 4/8/22 | Credit Card | N | $0.00 | Rewards | Rewards | |||||||
| Compact/Midsize | 275 | 4/5/22 | 4/10/22 | Cash | N | $0.00 | Express Miles | Express Miles | |||||||
| Compact/Midsize | 277 | 4/9/22 | 4/10/22 | Express Miles | N | $0.00 | Credit Card | Credit Card | |||||||
| Green Collection | 990 | 4/5/22 | 4/6/22 | Credit Card | N | $0.00 | Cash | Cash | |||||||
| Fullsize/Standard | 350 | 4/5/22 | 4/8/22 | Credit Card | Y | $0.00 | |||||||||
| SUV/Minivan | 550 | 4/5/22 | 4/9/22 | Rewards | Y | $0.00 | Number of Rentals | ||||||||
| SUV/Minivan | 551 | 4/7/22 | 4/10/22 | Credit Card | N | $0.00 | |||||||||
| Fullsize/Standard | 352 | 4/7/22 | 4/9/22 | Credit Card | Y | $0.00 | |||||||||
| Green Collection | 989 | 4/6/22 | 4/9/22 | Credit Card | N | $0.00 | |||||||||
| Compact/Midsize | 275 | 4/11/22 | 4/18/22 | Cash | N | $0.00 | |||||||||
| Fullsize/Standard | 355 | 4/9/22 | 4/16/22 | Credit Card | Y | $0.00 | |||||||||
| Fullsize/Standard | 356 | 4/6/22 | 4/8/22 | Credit Card | Y | $0.00 | |||||||||
| Fullsize/Standard | 350 | 4/9/22 | 4/13/22 | Rewards | Y | $0.00 | |||||||||
| SUV/Minivan | 553 | 4/8/22 | 4/14/22 | Express Miles | Y | $0.00 | |||||||||
| SUV/Minivan | 550 | 4/11/22 | 4/17/22 | Cash | Y | $0.00 | |||||||||
| Green Collection | 988 | 4/9/22 | 4/15/22 | Credit Card | N | $0.00 | |||||||||
| Green Collection | 989 | 4/10/22 | 4/16/22 | Express Miles | N | $0.00 | |||||||||
| Green Collection | 990 | 4/7/22 | 4/10/22 | Credit Card | N | $0.00 | |||||||||
| Fullsize/Standard | 356 | 4/9/22 | 4/10/22 | Rewards | N | $0.00 | |||||||||
| SUV/Minivan | 551 | 4/11/22 | 4/15/22 | Express Miles | Y | $0.00 | |||||||||
| SUV/Minivan | 554 | 4/7/22 | 4/9/22 | Express Miles | Y | $0.00 | |||||||||
| Fullsize/Standard | 352 | 4/10/22 | 4/11/22 | Credit Card | Y | $0.00 | |||||||||
| Fullsize/Standard | 356 | 4/11/22 | 4/17/22 | Express Miles | Y | $0.00 | |||||||||
| Compact/Midsize | 276 | 4/11/22 | 4/14/22 | Credit Card | N | $0.00 | |||||||||
| Compact/Midsize | 277 | 4/11/22 | 4/15/22 | Rewards | N | $0.00 | |||||||||
| SUV/Minivan | 554 | 4/10/22 | 4/12/22 | Cash | Y | $0.00 | |||||||||
| Green Collection | 990 | 4/11/22 | 4/18/22 | Express Miles | N | $0.00 | |||||||||
| 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 | $75.00 | ||||||||||||||
ClientData
| Client Data | Proper Name |
| DAWN SCHALOW | |
| LAUREL KALLIO | |
| STEVEN THAO | |
| IAN FALU | |
| SUZETTE KARREN | |
| JERON JACOBSON | |
| ANDREA RAMIREZ | |
| KEITH WREATH | |
| GEORGE LINSER | |
| CONNOR CHING | |
| KELSEA VANBUREN | |
| ELI ZIMMERMAN | |
| JAMIE MICKELSON | |
| DIANE KOPISKI | |
| RYAN KELLEY | |
| JOE KRUGER | |
| YANG VANG | |
| AARON ATKINSON | |
| SUZETTE KERR | |
| MICHAEL STANOWICZ | |
| RAMONA UNGER | |
| TY NY | |
| ELIJAH REYNOLDS | |
| SALLY JOHNSON | |
| BRAD HALSTAD |
