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 volumes 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 quarterly 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 volumes 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 column, 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 following categories: DisplayExcellent if the estimate is within 5%(higher or lower)of the actual sales 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<=40+.25*40.)

DisplayGoodiftheestimateisgreaterthan5%higherorlower,butwithin10% higher or lower of the actual volume.

Display Poor if the estimate is greater than 10% higher or lower.

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

13. Save and close the Promo Large Dealerships.xlsx workbook.

CKGPromo Workbook:

Expected volume:	325	425	440	350
	Quarterly Sales Volumes
Dealer#	1Q	2Q	3Q	4Q
3321	333	447	460	377
3322	315	414	398	243
3323	284	119	272	205
3324	321	339	294	157
3325	536	249	234	211
3326	311	496	484	504
3327	383	210	102	365
3328	210	406	183	409
3329	185	355	495	510
3330	499	325	227	390
3331	270	339	297	291