Navigate to the Transactions worksheet and insert formulas/functions in columns H to N to calculate Days Rented, Daily Rate, Rental Cost, Daily Insurance, Insurance Cost, Discount, and Total Cost. Days Rented: This value is based on the Date Rented and Date Returned columns. Daily Rate: This value is based on the Car Size and the corresponding Rate per Day table shown below. Vehicle Compact Full Size Luxury Mid Size Subcompact Truck Rate per Day $18.90 $29.95 $45.00 $25.50 $18.00 $35.50 Rental Cost: This value is the product of the Days Rented and Daily Rate columns. Daily Insurance: This value is based on Days Rented and the corresponding Daily Insurance rate table shown below. Days Rented 30 Daily Insurance $30.00 $25.00 $20.00 $18.00 $16.50 $15.00 $13.00 Insurance Cost: This value is the product of Days Rented and Daily Insurance. Discount: If the Date Rented is Monday or Friday, then the discount amount is 20% of the Rental Cost. Otherwise, $0. Hint: Use the WEEKDAY function to determine the day of the week corresponding to the rental date. The WEEKDAY function will give you an integer value, ranging from 1 (Sunday) to 7 (Saturday), by default. Total Cost: This value is the sum of Rental Cost and Insurance Cost minus Discount. Save your workbook. Summarize your Data (a) In cells R6:R7, enter a function to calculate the total revenue from business and personal renters. Save your workbook. (b) In cells R11:R12, enter a function to calculate the total number of transactions by each payment type. Save your workbook. (c) In cells R16:S21, enter functions to calculate the average revenue and average days rented for each car size. Save your workbook. (d) In cells R25:R27, enter a function to calculate the number of transactions where the number of days rented is 10. (e) Save your workbook. Charts and Finalize your Workbook (a) Create two pie charts for data located R6:R7 and R11:R12. Add appropriate chart elements (title, label... etc.). Save your workbook. (b) Create a bar chart based on car size and average revenue (Q16:R21). Add appropriate chart elements (title, label... etc.). Move the chart to a new worksheet. Name the worksheet Average Revenue. Save your workbook. (c) Format your workbook as desired (Headings, Font, Fill color, Number format...) (d) Save and close your workbook. Transaction # 2012-001 2012-003 2012-005 2012-007 2012-008 2012-009 2012-010 2012-013 2012-014 2012-017 2012-021 2012-023 2012-025 2012-026 2012-027 2012-028 2012-029 2012-033 2012-034 2012-035 2012-036 Auto ID 1 7 8 18 6 22 10 13 7 20 30 1 8 16 20 21 18 27 9 30 13 Payment Type Credit Card Type Use Personal Business Account Business Account Business Account Business Personal Credit Card Credit Card Credit Card Credit Card Credit Card Personal Business Personal Business Credit Card Business Credit Card Personal Account Business Account Business Credit Card Business Credit Card Business Credit Card Personal Account Personal Account Personal Account Business Account Business Account Car Size Truck Mid Size Mid Size Luxury Luxury Subcompact Full Size Full Size Mid Size Compact Full Size Truck Mid Size Full Size Compact Full Size Luxury Luxury Truck Full Size Full Size Date Date Rented Returned 2022-06-01 2022-06-05 2022-06-01 2022-06-05 2022-06-02 2022-06-09 2022-06-04 2022-06-13 2022-06-05 2022-06-08 2022-06-08 2022-06-18 2022-06-08 2022-06-14 2022-06-09 2022-06-12 2022-06-09 2022-06-11 2022-06-09 2022-06-12 2022-06-09 2022-06-12 2022-06-10 2022-06-17 2022-06-12 2022-06-13 2022-06-14 2022-06-18 2022-06-14 2022-06-30 2022-06-14 2022-06-18 2022-06-15 2022-06-30 2022-06-15 2022-06-30 2022-06-15 2022-06-18 2022-06-15 2022-06-22 2022-06-16 2022-06-22 Days Rented Daily Rate Rental Cost Daily Insurance Insurance Cost Discount Total Cost Type of Use Business Personal Payment Type Account Credit Card Car Size Compact Full Size Luxury Mid Size Subcompact Truck Days Rented 10 Total Revenue # of Transactions Average Revenue | Average Days Rented # of Transactions