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