Please note that you will be allowed to submit this assignment 3 times. MyITLab will keep your highest score of the 3. Some of these formulas are pretty complicated and there are almost always several ways to perform them. For each of the formulas I have included 4-8 different versions that MyLab will mark as correct. If you have a version that I didn't envision I apologize but since you get 3 tries at this assignment you should be able to get the version based upon the feedback provided by the auto-grader.

Step |
Instructions |
Points Possible |

1 |
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: |
0 |

2 |
NO POINTS but words of
wisdom/advice:At the top of the sheet are cells containing data needed by the
various formulas in this assignment. In your various formulas, each formula
should refer to these cells containing the data rather than sticking a number
into a formula. |
0 |

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

4 |
In columns G through J,
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. In
cells K17:K28 determine the total value of the shipping rebates for all four
quarters for each dealership (found in columns G:J). |
8 |

5 |
In column L, utilizing your newly-found expertise in
nested IF functions calculate the value of the Overall Sales Volume Bonus for
each dealership per the descriptions in the opening paragraphs of this
assignment. |
6 |

6 |
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). |
6 |

7 |
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: |
8 |

8 |
In row 29 for each column 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. Format each cell in this Totals row correctly and make
these cells bold. |
2 |

9 |
In column O, 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 obtain the corresponding value for each
dealership. 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!!! |
4 |

10 |
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: |
8 |

11 |
In rows 30 & 31 for each column that contains a
rebate/bonus (G through M), create formulas that |
4 |

12 |
In cells B17 - E28, Ken wants
the dealerships quarterly cells that are >= the quarterly expected volume
cells to be formatted as with the font color as White and the fill color as
Green. You must use conditional formatting to accomplish this as demonstrated
on pages 304-311 of the book... specifically the part on "Use Formulas
in Conditional Formatting" on 310. |
2 |

Total Points |
50 |

Get Higher Grades Now

Tutors Online