Level 3 Analyzing Dealership Promotions for CKG Auto CKG Auto runs several promotions each year to reward dealerships for their sales efforts, sometimes on specific car models and other times for overall sales. CKG Auto 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 exceeding expected quarterly volumes: These are savings CKG Auto 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, $65 per car sold (actual volume); 2nd quarter, $80 per car sold; 3rd quarter, $65 per car sold; and 4th quarter, $122 per car sold. Dealerships are awarded the rebate on a quarter-by-quarter basis, only for quarters where their actual sales exceeded expected volumes for that quarter. Expected sales vol- umes for large dealerships for each quarter are as follows:

1st Quarter: 325 2nd Quarter: 425 3rd Quarter: 440 4th Quarter: 350 Anoverallsalesvolumebonusbasedonexceedingexpectedannualvolumes:Dealerships that exceeded the expected annual sales volume by more than 7% are awarded a $10,000 bonus. Dealerships that exceeded the expected annual sales volume by 7% or less are awarded a $5,000 bonus. Otherwise, no bonus is awarded ($0). A Best in Class bonus of $6,000 awarded to the one dealership with the highest overall sales volume in its class 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 quar- terly sales volumes have already been entered in a worksheet. Now, you will finalize the analysis. Complete the following:

1. Open the workbook named CKGPromo.xlsx located in the Chapter 4 folder, and then save it as Promo Large Dealerships.xlsx. This past years quarterly sales vol- umes and expected sales volumes for large dealerships have already been entered into this workbook. Complete the analysis using any additional columns and/or rows as you deem necessary. All formulas should work when copied either across or down, as needed. Include titles in each column and/or row to identify the corresponding data. Add any appropriate formatting to make the worksheet easy to read. 2. Insert rows at the top of the worksheet to create an input area where you can list the inputs such as bonus amounts, shipping rebates, and so on. List the inputs explicitly and use only one worksheet for this task, so that any inputs can be easily displayed for management and then later copied and modified to calculate the promotions for both the medium and small dealership classes. Insert rows as needed, and be sure to clearly label each input so that the data can be interpreted and modified easily next year. Wrap text and format the data as needed. 3. In a column adjacent to the quarterly sales data, calculate the corresponding annual sales volume for each dealership. 4. Calculate the value of the shipping rebate for each dealer for each quarter (use four new columns). This should require only one formula that can be copied down the column and across the row. Be sure your inputs are set up so that this can be easily accomplished. Remember, dealers will only receive rebates in quarters where their actual quarterly sales volumes exceeded expected sales volumes. In an adjacent col- umn, determine the total value of the shipping rebate for all four quarters by dealership.

5. Analyze the quality of these volume estimates by categorizing the quality of the annual volume estimate versus the actual annual volumes for each dealership into the fol- lowing categories: DisplayExcellentiftheestimateiswithin5%(higherorlower)oftheactualsales volume. (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

6. In an adjacent column or columns, calculate the value of the annual sales volume bonus for each dealership. 7. In an adjacent column, calculate the value of the Best in Class bonus for each dealership. (Only the dealership with the highest annual sales volume will receive this; all others will receive $0.) 8. In a row below the data, calculate the total values for all dealers for sales volume, shipping rebates, sales, and best in class bonuses. 9. In an adjacent column, determine if (TRUE or FALSE) this dealership received money during this year for both a shipping rebate and a sales volume bonus. Copy the formula down the column to obtain the corresponding value for each dealership. 10. Skipping one row below the totals, in the column just used in Step 9, determine (TRUE or FALSE) if none of the dealerships received both shipping rebates and a volume bonus. Label the row accordingly. 11. Just below the result of Step 10, determine if only dealerships with Excellent estimate qualities (determined in Step 5) received both shipping rebates and a sales volume bonus. This formula need not work if any of the input data or formulas are later updated. Label the row accordingly. 12. Again, skip a row below the data. Then, in the following rows, determine for each rebate/bonus the number of dealerships receiving this rebate/bonus and the average value of the bonus (include dealerships that did not earn a bonus in the average cal- culation). 13. Save and close the Promo Large Dealerships.xlsx workbook

This problem is out of the book Succeeding in Business with Microsoft Excel 2010 page 286-288

1 Expected volume 2 325 425 440 350 Quarterly Sales Volumes 4 Dealer 6 8 10 1Q 2Q 3Q 4Q 3321 3322 3323 3324 3325 3326 3327 3328 3329 3330 3331 315 284 321 536 311 383 210 185 499 270 47460 414398 119 272 339 294 249 234 496 484 210 102 406 183 355 495 325 227 339 297 377 243 205 157 211 504 365 409 510 390 291 12 13 15 16 17