# Assignment 4 2020

These instructions are compatible with both Microsoft Windows and Mac operating systems.

Armstrong Motor Works runs several promotions each year to reward dealerships for their sales efforts, sometimes on specific car models and other times for overall sales. Armstrong Motor Works is running three different promotions for large dealerships, based on performance over this past calendar year. Small and medium-sized dealerships have similar promotions but based on different expected volumes and rebate percentages. The promotions are as follows:

## Shipping Rebate:

A rebate on shipping expenses based on a dealer meeting or exceeding their expected quarterly volumes: These are savings Armstrong Motor Works realizes from its trucking carriers and has decided to pass along as a reward to dealerships that have exceeded expectations. Rebates for each quarter were set by management as follows: 1st quarter, \$84 per car sold (actual volume); 2nd quarter, \$97 per car sold; 3rd quarter, \$88 per car sold; and 4th quarter, \$141 per car sold. Dealerships are awarded the rebate on a quarter-by-quarter basis, but only for quarters where their actual sales met or exceeded expected volumes for that quarter. Expected sales volumes for large dealerships for each quarter are as follows and are located in cells C4:F4:

• 1st Quarter Quota: 369
• 2nd Quarter Quota: 464
• 3rd Quarter Quota: 477
• 4th Quarter Quota: 385

So… if Dealer 1 sells 370 cars in Q1, this is greater than 1st Qtr. Quota right? That then means that for Qtr 1 they get a rebate = \$84 (the value for the 1st quarter rebate) for each of the 369 cars sold.

## Overall Sales Volume Bonus:

An overall sales volume bonus based on meeting or exceeding expected annual volumes: All dealerships that met or exceeded the expected annual sales volume by 7.4% or more are awarded an \$8,450 bonus. Dealerships that met or exceeded the expected annual sales volume by 2.5% but by less than 7.4% are awarded a \$4,625 bonus. Otherwise, no Overall Sales Volume bonus is awarded (\$0).

### Best in Class Bonus

A “Best in Class” bonus of \$7,600 awarded to the one dealership with the highest overall (annual) sales volume in its class. A "2nd Best in Class" bonus of \$3,400 is awarded to the dealership with the 2nd highest overall (annual) sales volume.
You have been asked to set up a worksheet to record the dealer information for the past year and apply the appropriate promotions to each dealership. The actual dealership quarterly sales volumes have already been entered in a worksheet. Now, you will finalize the analysis.

This past year’s quarterly sales volumes and expected sales volumes for large dealerships have already been entered into this workbook. All formulas should work when copied either across or down, as needed. Add any appropriate formatting to make the worksheet easy to read. Remember all of the formatting from Module 1? Numbers larger than 1,000 should have commas... columns that are money-related should be formatted as currency with \$ signs in the top row as well as any summary rows at the bottom of the table. All numbers (general and money-related should be formatted to show 0 zero decimal places.

1.
2.
3.
4.
5.
6.
7.
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.
2.
• 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

1. 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.
2.

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.

1.

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!!!

1.

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.

1. Create Conditional Formatting Rules to change the formatting on cells in column P.
1.
2.
3.
2. 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.
2.
3. 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.
2.

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.

1.
2.
3.