A formula to calculate the total cost per payment method – Pro Academia Help

Pro Academia Help

Pro Academia Help

A formula to calculate the total cost per payment method

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 FileName codein 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

Calculate Price

Price (USD)

$

Don't Miss Our Special Offers

Total price (USD) $12.99

Online Essay Writing

Admission Essay

Analytical Essay

Argumentative Essay

Cause and Effect Essay

College Essay

Compare and Contrast Essay

Critical Essay

Definition Essay

Descriptive Essay

Explicatory Essay

Expository Essay

Five-Paragraph Essay

Informative Essay

Lab Report Essay

Narrative Essay

Persuasive Essay

Process Essay

Reflective Essay

Research Essay

Responsive Essay

Review Essay

Simple Essay

Nursing Essay Writing

Business Essay

Cheap Essay Writing

Custom Essay

Law Essay Writing

Marketing Essay

Medical Essay

Disclaimer: Our online Essay Writing Service provides custom assignment writing services to facilitate college students. We offer references
of reliable writing resources as well, to aid your learning process.