Assignment 4 2020

1.  Download the Start file named EX-Assignment-4.xlsx onto your desktop. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor and save it.
2.  If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify the workbook.
3.  In cells F17:F28 adjacent to the quarterly sales data, calculate the corresponding annual sales volume for each dealership by adding together the 4 quarterly sales volumes in columns B, C, D and E. Format accordingly for the type of number in this cell range.
4.  In columns G through J, use an IF formula to calculate the value of the shipping rebate for each dealer for each quarter. If you use proper cell referencing, you should be able to write a formula in cell G17 that can be copied down the column and across the row. Remember, dealers will only receive rebates in quarters where their actual quarterly sales volumes met or exceeded expected sales volumes located in cells C4:F4.
5.  In cells K17:K28 determine the total value of the shipping rebates for all four quarters for each dealership (found in columns G:J).
6.  In column L, utilizing your newly found expertise in either nested IF functions or the new IFS function calculate the value of the Overall Sales Volume Bonus for each dealership per the descriptions in the opening paragraphs of this assignment.
7.  In column M, calculate the value of the “Best in Class” bonus for each dealership (The dealership with the highest annual sales volume will receive the amount in cell C9 and the dealership with the 2nd highest annual sales volume will receive the amount in Cell C10. All others will receive $0). Hint: There are a few different ways that you can accomplish the Logical Argument of your IF statements for this one. Think through what your eyes/mind does when you look at the Total annual sales volume cells. You look at a number (F17 for example) to see if it is the largest in range, right? Another way would be to think something like... "does this F17 number's rank equal to 1?"
8. Ken wants to compare the Total Annual Sales Volume for each dealer (in cells F17:F28) to Expected Annual Sales Volume in G4 to see how accurately they set their dealership quotas:
   1.  In cell G4 sum the 4 quarterly expected volumes in cells C4:F4 together to obtain the Annual Expected Volume.
   2.  In N17:N28 create a nested IF or IFS formula for each dealer analyze the quality of these volume expectations by comparing each dealer’s Total Annual Sales Volume to the Annual Expected Volume. Categorize the quality of these comparisons into the following categories:

• Display “Excellent” if the actual volume is within 3.15% (<=103.15% and >=96.85%) of the expected sales volume in G4. (Hint: For example, if you wanted to determine if the value 26 is within +/– 25% of 40, you would need to test this value to make sure that both 26>=40–.25*40 AND 26<=40+.25*40).
• Display “Good”: If the "Excellent" test in the previous bullet point fails (the actual volume was not within 3.15% of expected from the prior paragraph) then we want to check to see if the actual is within 8.45% of the expected value. If it is within this range, then we want our awesome nested IF statement to output the word "Good".
• Display “Poor” if the actual volume is greater than 8.45% higher than expected or less than 8.45% lower than the expected volume.

This step is kind of tough so I created a video to help you through this one: https://www.youtube.com/watch?v=ptwRmiLJn0Q

9. In row 29, columns B through M calculate the sum of values for these columns (cars sold and $ awarded) to all dealers for sales volume, shipping rebates, sales, and best in class bonuses.
   1.  Format each cell in this Totals row correctly and make these cells bold.
   2.  If the cell includes a dollar value, format that cell with Currency format and include the ($) symbol.

Hint: Since it is a total row you should include $ signs if that cell is money and not include it if it is a sales volume. You can use the Currency format for all of the cells but notice that if you take the $ sign out of a cells format it just converts itself to Number. This is expected and fine.

10.  In cell O17, create a formula to determine (by displaying the Boolean value of either TRUE or FALSE) if this dealership received money during this year for both a shipping rebate (from any quarter) AND a sales volume bonus. Copy the formula down the column to row 28 to obtain the corresponding value for each dealership.

Hint: Remember here that double-quoting the letters T-R-U-E like this... "TRUE" as that is a BAD thing because it creates a chunk of text out of the perfectly good Boolean value of TRUE. Watch the video called TRUE vs "TRUE" for a full description. On your upcoming exam I see lots of students putting TRUE's and FALSE's in double-quotes and you will lose points for doing this!!!

In this class if you ever put your TRUEs or FALSEs in double quotes anywhere in any formula you will be doing it wrong!!!

11.  In column P, Ken wants to see an overall effectiveness rating of all of their dealers. All of the dealers should be classified as either Excellent, Good or Poor based upon the following criteria:

Excellent: Received a Quarterly Shipping Rebate (completed in step 4) in 3 or more of the 4 quarters AND also reached their Overall Sales Volume Bonus.

Good: Received a Quarterly Shipping Rebate (completed in step 4) in at least 1 of the 4 quarters OR also reached their Overall Sales Volume Bonus.

Poor: They were neither Excellent nor Good.

12. Create Conditional Formatting Rules to change the formatting on cells in column P.
    1.  Create a rule to change the formatting on cells equal to "Excellent" to Green Fill with Dark Green Text formatting.
    2.  Create a rule to change the formatting on cells equal to "Good" to display Yellow Fill with Dark Yellow Text formatting.
    3.  Create a rule to change the formatting on cells equal to "Poor" to display Light Red Fill with Dark Red Text formatting. Hint: These 3 different formats are 3 the top 3 choices of conditional formatting so use the values from this dropdown as opposed to making up your own coloring scheme. It should look something like the accompanying image when complete (but with different values in the different cells).
13. In rows 30 & 31 for each column that contains a rebate/bonus (G through M), create formulas that determine the number of dealerships receiving the bonus and the average value of the bonus:
    1.  In cells G30:M30 determine the number (count) of dealerships receiving this rebate/bonus.
    2.  In cells G31:M31 determine the average value of the bonus (include dealerships that did not earn a bonus in the average calculation). Apply Currency number formatting to the values.
14. In cells B17:E28, Ken wants the dealerships quarterly cells that are Greater Than or Equal to (>=) the quarterly expected volume cells (C4:F4) to be formatted with White, Background 1 font color and Green fill (6th color from the left). You must create a Conditional Formatting rule using a formula to accomplish this.
    1.  In cell B17 create a conditional formatting rule that uses a formula to determine whether or not to format the cell in with White, Background 1 font color and Green fill (6th color from the left on the Fill tab). Use a mixed reference making the row absolute to the quarterly expected volume cell for that quarter.
    2.  Use the format painter to copy the conditional formatting across and down throughout cells B17:E28

Hint: Since cell C17 (dealer # 3258's 2nd quarter sales) has a value of 479 and since that volume is greater than the 2nd quarter Expected Volume of 464 in cell D4 it should turn green. That dealer's 1st quarter sales in cell B17 should NOT automatically turn green since 367 is less than the 1st quarter's expected volume of 369 located in cell C4.
Get the conditional formatting working for cell B17. The formula used for this conditional formatting should compare B17 to C4. If you get the referencing correct in this formula you will be able to use the format painter to copy it across and down to the entire B17 - E28 range of cells.

15.  Save and close the workbook.
16.  Upload and save your project file.
17.  Submit project for grading.