Navigate to the Transactions worksheet and insert formulas/functions in columns H to N to calculate Days...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 <3 3 to 5 5 to 10 10 to 15 15 to 20 20 to 30 >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 <5, between 5 and 10, and >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 <5 5 to 10 >10 Total Revenue # of Transactions Average Revenue | Average Days Rented # of Transactions 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 <3 3 to 5 5 to 10 10 to 15 15 to 20 20 to 30 >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 <5, between 5 and 10, and >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 <5 5 to 10 >10 Total Revenue # of Transactions Average Revenue | Average Days Rented # of Transactions
Expert Answer:
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these algorithms questions
-
What are the Key Success factors in the Daycare industry? explain
-
Under what conditions may a firm assume a defender strategy?
-
Following on from Exercise 15.3, suppose that my colleagues were tired of having children tell them what they think we want to hear and gave them a heart-to-heart talk on the necessity of accurate...
-
The common field cricket makes its characteristic loud chirping sound using a specialized vibrating structure in its wings. The motion of this structureand the sound intensity that it producescan be...
-
At the beginning of 2017, Tanham Company discovered the following errors made in the preceding 2 years: Reported net income was $27,000 in 2015 and $35,000 in 2016. The allowance for doubtful...
-
Mickley Company's plantwide predetermined overhead rate is $21.00 per direct labor-hour and its direct labor wage rate is $11.00 per hour. The following information pertains to Job A-500: Direct...
-
Presented below are excerpts from Note 1 to Starbucks' September 30, 2012, consolidated financial statements in which Starbucks describes accounting policy for long-lived assets. a. Leasehold...
-
Rapid Enterprises allocates manufacturing overhead to Work in Process on the basis of 4.79 times direct material cost. If Product 17X had $32,000 of manufacturing overhead allocated to it during May,...
-
Encik Aziz is the Head of Credit Division of CIMB Bank Berhad. Suggest any FOUR (4) strategies which Encik Aziz could adopt to manage credit risk in his bank. (16marks) Bank Negara Malaysia reduced...
-
Part A - Scenario You are working on a project which is doing a refit of a restaurant. The restaurant has closed for 4 weeks to allow full access to the premises for all equipment and fittings to be...
-
3. Consider the following NFA: E &C 1 E B 8 0 D F G H 1.0 J E a) Use the subset construction method to transfer NFA to DFA. b) Minimize the resulting DFA.
-
16.- Rob Danvers currently holds the following position: 5 TSLA Jan 19, $305 Call The position was opened when TSLA was trading at $310. When is the position in the money?
-
Question 7 5 pts In Lab 8, consider the implementation of method swap(). First, consider the corner case where the nodes to be swapped are adjacent as shown below. Assume that both nodes I and J to...
-
3dx In using trigonometric substitution in evaluating 6x+2' which of the following substitutions is the right to use? A. x = 7 tan 0 C. x = 3+ V7 tan 0 B. x = 3 + V7 sec 0 D. x = 3+ V3 sec 0
-
Baxter, Inc., owns 90 percent of Wisconsin, Inc., and 20 percent of Cleveland Company. Wisconsin, in turn, holds 60 percent of Clevelands outstanding stock. No excess amortization resulted from these...
-
Russell (age 50) and Linda (age 45) Long have brought you the following information regarding their income and expenses for the current year. Russell owns and operates a landscaping business called...
-
Tom has a successful business with $100,000 of income in 2012. He purchases one new asset in 2012, a new machine which is 7-year MACRS property and costs $25,000. If you are Tom's tax advisor, how...
-
Jim is fired from his job as a waiter and decides to take an extended trip to Europe. After touring Europe for 3 months, Jim returns to look for a new job as a waiter. Are his job-hunting expenses...
-
Let \(X, Y, X_{n}, Y_{n}: \Omega ightarrow \mathbb{R}, n \geqslant 1\), be random variables. a) If, for all n > 1, Xn Yn and if (Xn, Yn) (X, Y), then XIL Y. b) Let X Y such that X, Y ~ B1/2 = (80...
-
Let \(X_{n}, Y_{n}: \Omega ightarrow \mathbb{R}^{d}, n \geqslant 1\), be two sequences of random variables such that \(X_{n} \xrightarrow{d} X\) and \(X_{n}-Y_{n} \xrightarrow{\mathbb{P}} 0\). Then...
-
Let \(X_{n}, X, Y: \Omega ightarrow \mathbb{R}, n \geqslant 1\), be random variables. If \[\lim _{n ightarrow \infty} \mathbb{E}\left(f\left(X_{n}ight) g(Y)ight)=\mathbb{E}(f(X) g(Y)) \quad \text {...
Study smarter with the SolutionInn App