Please explain how to do the steps below as in what ill put into the box to comp
ID: 2820058 • Letter: P
Question
Please explain how to do the steps below as in what ill put into the box to compute the correct output its requesting
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, rowwise 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.
Explanation / Answer
I Shall first guide you in filling out the excel.
Expenses = Expense per each car * number of cars
Expenses per each car = hybrid + non-hybrid expenses = N1+ N2. Since these values are constant and same values are needed to be used for the whole column 'G'; w re-write them as N$1 + N$2 (By putting a dollar symbol in front of the row numbers 1 & 2, even when the formula is dragged to all the vertical cells in column G, they take values from N1 & N2 only instead of skipping to its below cells N3, N4 etc. )
Thus Expenses formula in G2 = (N$1+N$2)*F2. Now hold this formula and drag it to the whole column G to apply the same formula to all its cells.
Adjust the column width by double-clicking on the right end margin of the column G (OR) select the column G and left click, then you get an option "Format Width". Provide a convenient number in this option.
Select the whole column G and again left click & look at the home tab on the top. It contains a section named Number. In this section, you can see a $ symbol. click on it to convert the whole column into a USD currency number. To the alternate right of this $ symbol in the Number section, you can see two shortcut buttons to adjust the decimal places in a number. Use the second one in them to reduce the decimal places to zero.
Ctrl+A the whole excel sheet and then change the font to Arial and size to 10 in the home section located at the top of the excel sheet.
19. Profit formula for column H would be Revenues - Expenses. But given profit should be a positive number, thus an 'IF' statement needs to be used in cell H2 as if(E2-G2 >=0, E2-G2,0). and drag it to the whole column. This way we only retain the positive profits in the column H and losses will be just recorded as 0.
Profit per car = Profit / NumCar. Therefore use formula H2 / F2 in I2 cell and drag it to the whole column. This way all the required calculations are completed.
Adjust the column width, decimal places to 2 digits, Font & size & currency symbol in a similar way I had explained above.
This completes your Sheet and question 19.
20. A pivot table is asked to be built between Location, car class & profit. Therefore, hide the columns E,F & G. Now select all the columns from C to H together and go to insert tab and click on Pivot table which is the first option in this tab. In that further, assign the K4 cell under the EXISTING WORKBOOK cell as the location for the pivot table and click okay. Now a list is opened at the right end. select & drag PROFIT into values column at the bottom. Drag LOCATION to row & drag carclass to the column. Thus the generated pivot table solves for everything asked in the question.
21. Similarly, now create a pivot table by selecting all the cells in the excel. Drag Quarter to Row, Drag Location& carclass to Column. drag profit to value and then click on the down arrow button at the "sum of profit" cell. click value field settings and select AVERAGE and click ok. Thus the required calculations are achieved. Now, adjust the currency, font, decimal places etcc as discussed in the above questions.
Year Quarter Location CarClass Revenue NumCars Expenses Profit ProfitPerCar Hybrid Expenses per Car 100 2015 Q3 Downtown Economy $ 805,325 5,577 $ 836,550 $ - $ - Non-Hybrid Expense per Car 50 2015 Q3 Airport Economy $ 734,028 4,922 $ 738,300 $ - $ - 2015 Q4 Airport Economy $ 640,474 4,278 $ 641,700 $ - $ - Sum of Profit Column Labels 2015 Q4 Downtown Economy $ 532,065 3,610 $ 541,500 $ - $ - Row Labels Economy Premium SUV (blank) Grand Total 2015 Q2 Airport Economy $ 502,546 3,489 $ 523,350 $ - $ - Airport 0 103475 18698 122173 2015 Q1 Airport Economy $ 470,125 3,408 $ 511,200 $ - $ - Downtown 0 33399 0 33399 2015 Q2 Downtown Economy $ 396,848 2,881 $ 432,150 $ - $ - (blank) 2015 Q1 Downtown Economy $ 375,118 2,849 $ 427,350 $ - $ - Grand Total 0 136874 18698 155572 2015 Q4 Airport Premium $ 663,901 4,055 $ 608,250 $ 55,651 $ 13.72 2015 Q4 Downtown Premium $ 624,053 4,016 $ 602,400 $ 21,653 $ 5.39 2015 Q3 Downtown Premium $ 540,217 3,533 $ 529,950 $ 10,267 $ 2.91 2015 Q2 Downtown Premium $ 487,629 3,241 $ 486,150 $ 1,479 $ 0.46 2015 Q3 Airport Premium $ 505,403 3,148 $ 472,200 $ 33,203 $ 10.55 2015 Q1 Downtown Premium $ 370,679 2,514 $ 377,100 $ - $ - 2015 Q1 Airport Premium $ 375,811 2,466 $ 369,900 $ 5,911 $ 2.40 2015 Q2 Airport Premium $ 317,110 2,056 $ 308,400 $ 8,710 $ 4.24 2015 Q4 Airport SUV $ 663,548 4,299 $ 644,850 $ 18,698 $ 4.35 2015 Q4 Downtown SUV $ 623,328 4,264 $ 639,600 $ - $ - 2015 Q3 Downtown SUV $ 540,623 3,758 $ 563,700 $ - $ - 2015 Q2 Downtown SUV $ 487,843 3,427 $ 514,050 $ - $ -