|
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:
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 366 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.
Download the starter file named A4Starter.xlsx (or whatever MyITLab names it)
associated with this assignment onto your desktop.
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.
|
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.
For example, if you are using a formula that needs the value of 8000 in it,
you “could” use the formula:
=IF(B3>8000,"Do
This","Do That")
but a better way would be to put the number 8000 in a cell such as A1 and
then your “Better” formula would be:
=IF(B3>A1,"Do This","Do That")
This way the number 8000 is readily visible/editable and all cells that
“need” it will then get any changes as opposed to you having to hunt through
all cells for the “hard-coded” number, 8000, and change it in each.
What this step boils down to is this: If you hard-code numbers (like the 8000
in the example above or percentages) into your formulas you will have points
deducted on the following steps.
Also, MyITLab is programmed to look for the correct formula as I have written
it. While I have put in several possible formulas that come to mind I may not
have come up with your exact formula so after you submit it, MyITLab will
give you some pretty strong hints as to how to fix your formula so that it
lines up with mine. If you take it up on its suggestions all will be fine on
your next resubmission.
|
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).
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?"
So... if it IS the largest they get the amount in C9. If it's not then you
look to see if it is the SECOND largest. If it IS the 2nd largest number than
they get the value in C10. If it is none of the above they get squat.
|
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:
In cell G4 sum the 4 quarterly expected volumes in cells C4:F4 together to
obtain the Annual Expected Volume. In N17:N28 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 the
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
|
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.
Hint: As we learned back in Assignment 1, 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.
|
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!!!
In this class if you ever put your TRUE 's or FALSE 's in double quotes
anywhere in any formula you will be doing it wrong!!!
|
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:
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
Apply conditional formatting to change the formatting on cells displaying
"Excellent" to be Green Fill with Dark Green Text, "Good"
to display Yellow Fill with Dark Yellow Text and "Poor" to display
"Light Red Fill with Dark Red Text". 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).
|
8
|
|
11
|
In rows 30 & 31 for each column that contains a
rebate/bonus (G through M), create formulas that
a) determines the number (count) of dealerships receiving this rebate/bonus
and
b) the average value of the bonus (include dealerships that did not earn a
bonus in the average calculation). Make sure that your formatting conforms to
the best practices that we learned back in Module 1!
|
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.
So for this assignment, since cell C17 (dealer # 3254's 2nd quarter sales)
has a value of 468 and since that volume is greater than the 2nd quarter
Expected Volume in cell D4 it should turn green. That dealer's 1st quarter
sales in cell B17 should NOT automatically turn green since 356 is less than
the 1st quarter's expected volume of 359 located in cell C4.
How I did it: Click on B17, create the new rule making sure that I use a
formula that utilizes referencing in such a way that I can use the format
painting tool to copy the conditional formatting across and down throughout
cells B17:E28 and still have the formulas compare these cells in B17:E28 to
cells C4:F4.
Hint:
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.
Green is the 6th color from the left when you get to the Fill tab. Don't
forget to also set the Font color to White!
|
2
|
