18

In the Profit Analysis worksheet: Expenses costs include maintenance, cleaning, and administrative expenses associated for a car rental. The Expenses for one rental transaction will be determined from the values specified in the worksheet. DO NOT enter the values in this column on a cell by cell basis. a. In the first cell directly under the Expenses label, calculate the total Expenses that corresponds to the number of cars rented IN THAT ROW (NumCars) using an "IF" statement to as part of the formula to determine the value of Expenses for one vehicle. b. First, use the "IF" statement to determine the single vehicle Expenses and second, edit the cell contents to multiply it by the NumCars so that the Expenses value represents the total Expenses costs for the Number of Cars (NumCars) rented in a given row. c. Use the absolute cell address for the numerical Expenses value for ONE vehicle which should then be multiplied by the number of cars in each row. d. Adjust column width as needed to eliminate truncation after format adjustments.. Note: the use of absolute addresses allows those Expenses values to be changed and then by recalculation, update the entire Excel table. Format: Currency (leading dollar sign) No decimal places Arial 10 point Normal font Step Requirement Comments 19 In the Profit Analysis Worksheet: a. In the first cell directly under the Profit label, row-wise calculate total profit by subtracting Expenses from revenue (should be a positive number). b. In the first cell directly under the ProfitperCar label, row-wise divide Profit by NumCars. c. Adjust column widths as needed to eliminate truncation after formatting. Format: Currency (leading dollar sign) No decimal places for Profit and two decimal places for ProfitperCar data Arial 10 point Normal font 20 In the Profit Analysis worksheet: a. Create a Pivot Table using numeric data from Profit column. b. Display the total profit for each of the four classes of cars. c. Display the total profit for Airport as well as the Downtown location. d. Row and column calculations (sum) are required so that the total profit for EACH location and the total profit for EACH carclass are visible in the pivot table. There must be a grand total (sum) that represents the sum of ALL profit. e. Position the upper left corner of the Pivot Table in the same column as the Hybrid Expense per Car label begins and two rows below it. f. Use format as defined in the Comments. Locate the top edge of the Pivot Table in Row 4 and left aligned with the Hybrid Expenses label. Format (for pivot table labels, text, data as appropriate): Currency (leading dollar sign) No decimal places Right-align pivot table column labels Arial 10 point Normal font 21 In the Profit Analysis worksheet: a. Create a Pivot Table using numeric data from Profit column. b. Display the average quarterly profit for each of the four classes of cars. c. Display the average quarterly profit for each location. d. Row and column calculations (average) are required so that the average quarterly profit for EACH location and the average quarterly profit for EACH carclass are visible in the pivot table. There must be a composite average (average) that represents the average of all quarterly profit values. e. Position the upper left corner to align with the pivot table above and two rows below the pivot table above. f. The positioning of the column and row categories should match those of the above pivot table. This pivot table displays average NOT total. Adjust column widths to avoid data and label truncation after both pivot tables are created. Format (for pivot table labels, text, data as appropriate): Currency (leading dollar sign) No decimal places Arial 10 point Right-align pivot table column labels Normal font Step Requirement Comments For the questions below, in the Profit Analysis worksheet, present your answers in a very readable format left-aligned with and one or two rows beneath the lower pivot table. You can type your answer in one cell, then highlight and select several rows and columns, selecting merge cells and selecting Wrap Text. Do NOT widen the columns as this will adversely impact the appearance of the worksheet items above. You will want to change the text from Center to Left justification. Play with this a bit. If you simply type your answer on a single line in that will also be ok. But be sure the entire answer can be read without the reader having to change any formatting. Reading the answer in the cell editor is NOT acceptable, the answer must be visible in the worksheet itself. 22 Question 1: Based on the upper pivot table, which of the two locations produces the greater profit (include the profit figure) and which vehicle class of the four produces the least profit (include the profit figure)? Label your response Question 1. Answer this question in 2 to 3 sentences one or two rows below and left-aligned with the lower pivot table. Format: Arial 10 point Normal/Black Left-align text in the cell 23 Question 2: Based on the lower pivot table, which combination of location and carclass is MOST profitable on a quarterly basis and which combination is least profitable on a quarterly basis car (include the average quarterly values with the answer). Label your response Question 2. Answer this question in 2 to 3 sentences in a new row left-aligned with the Question 1 response and one or two rows beneath it. Format: Arial 10 point Normal/Black Left-align text in the cell

Text file:

Year Quarter Location CarClass Revenue NumCars

2017 Q1 Downtown Economy "$977,700" " 6,290 "

2017 Q1 Airport Economy "$1,047,832" " 5,963 "

2015 Q3 Downtown Economy "$805,325" " 5,577 "

2016 Q4 Airport Economy "$959,953" " 5,413 "

2016 Q1 Downtown Economy "$751,466" " 5,080 "

2015 Q3 Airport Economy "$734,028" " 4,922 "

2016 Q4 Downtown Economy "$736,727" " 4,796 "

2016 Q3 Downtown Economy "$712,259" " 4,715 "

2016 Q2 Downtown Economy "$670,449" " 4,472 "

2015 Q4 Airport Economy "$640,474" " 4,278 "

2017 Q2 Downtown Economy "$601,257" " 3,758 "

2016 Q3 Airport Economy "$621,108" " 3,710 "

2015 Q4 Downtown Economy "$532,065" " 3,610 "

2015 Q2 Airport Economy "$502,546" " 3,489 "

2016 Q1 Airport Economy "$521,919" " 3,445 "

2015 Q1 Airport Economy "$470,125" " 3,408 "

2017 Q2 Airport Economy "$622,565" " 3,324 "

2015 Q2 Downtown Economy "$396,848" " 2,881 "

2015 Q1 Downtown Economy "$375,118" " 2,849 "

2016 Q2 Airport Economy "$385,122" " 2,280 "

2015 Q4 Airport Premium "$663,901" " 4,055 "

2016 Q3 Airport Premium "$688,440" " 4,045 "

2015 Q4 Downtown Premium "$624,053" " 4,016 "

2017 Q1 Airport Premium "$710,376" " 3,957 "

2017 Q2 Airport Premium "$722,782" " 3,961 "

2016 Q2 Airport Premium "$627,078" " 3,711 "

2016 Q1 Airport Premium "$591,816" " 3,570 "

2015 Q3 Downtown Premium "$540,217" " 3,533 "

2016 Q2 Downtown Premium "$523,205" " 3,247 "

2015 Q2 Downtown Premium "$487,629" " 3,241 "

2016 Q4 Airport Premium "$565,096" " 3,228 "

2015 Q3 Airport Premium "$505,403" " 3,148 "

2016 Q3 Downtown Premium "$517,183" " 3,150 "

2016 Q1 Downtown Premium "$444,247" " 2,798 "

2016 Q4 Downtown Premium "$451,490" " 2,698 "

2017 Q1 Downtown Premium "$452,565" " 2,685 "

2015 Q1 Downtown Premium "$370,679" " 2,514 "

2015 Q1 Airport Premium "$375,811" " 2,466 "

2015 Q2 Airport Premium "$317,110" " 2,056 "

2017 Q2 Downtown Premium "$345,020" " 1,996 "

2015 Q4 Airport SUV "$663,548" " 4,299 "

2016 Q3 Airport SUV "$688,301" " 4,263 "

2015 Q4 Downtown SUV "$623,328" " 4,264 "

2017 Q1 Airport SUV "$710,588" " 4,166 "

2017 Q2 Airport SUV "$722,606" " 4,160 "

2016 Q2 Airport SUV "$626,757" " 3,943 "

2016 Q1 Airport SUV "$591,590" " 3,779 "

2015 Q3 Downtown SUV "$540,623" " 3,758 "

2016 Q2 Downtown SUV "$523,359" " 3,400 "

2015 Q2 Downtown SUV "$487,843" " 3,427 "

2016 Q4 Airport SUV "$565,293" " 3,381 "

2015 Q3 Airport SUV "$504,888" " 3,349 "

2016 Q3 Downtown SUV "$518,004" " 3,345 "

2016 Q1 Downtown SUV "$444,127" " 2,939 "

2016 Q4 Downtown SUV "$450,725" " 2,846 "

2017 Q1 Downtown SUV "$451,944" " 2,833 "

2015 Q1 Downtown SUV "$371,061" " 2,646 "

2015 Q1 Airport SUV "$376,497" " 2,621 "

2015 Q2 Airport SUV "$317,066" " 2,151 "

2017 Q2 Downtown SUV "$344,938" " 2,078 "

2015 Q4 Airport Hybrid "$663,467" " 4,139 "

2016 Q3 Airport Hybrid "$688,782" " 4,089 "

2015 Q4 Downtown Hybrid "$623,719" " 4,109 "

2017 Q1 Airport Hybrid "$709,847" " 4,050 "

2017 Q2 Airport Hybrid "$722,481" " 4,018 "

2016 Q2 Airport Hybrid "$626,363" " 3,807 "

2016 Q1 Airport Hybrid "$591,561" " 3,669 "

2015 Q3 Downtown Hybrid "$540,387" " 3,597 "

2016 Q2 Downtown Hybrid "$523,471" " 3,320 "

2015 Q2 Downtown Hybrid "$488,036" " 3,281 "

2016 Q4 Airport Hybrid "$565,829" " 3,261 "

2015 Q3 Airport Hybrid "$505,782" " 3,236 "

2016 Q3 Downtown Hybrid "$517,267" " 3,210 "

2016 Q1 Downtown Hybrid "$444,554" " 2,867 "

2016 Q4 Downtown Hybrid "$450,842" " 2,757 "

2017 Q1 Downtown Hybrid "$452,493" " 2,702 "

2015 Q1 Downtown Hybrid "$370,963" " 2,554 "

2015 Q1 Airport Hybrid "$376,241" " 2,533 "

2015 Q2 Airport Hybrid "$317,492" " 2,106 "

2017 Q2 Downtown Hybrid "$344,374" " 2,033 "