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, $75 per car sold (actual volume); 2nd quarter, $80 per car sold; 3rd quarter, $85 per car sold; and 4th quarter, $80 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: 400 2nd Quarter: 410 3rd Quarter: 415 4th Quarter: 390 An overall sales volume bonus based on exceeding expected annual volumes: Dealerships that exceeded the expected annual sales volume by more than 15% are awarded a $10,000 bonus. Dealerships that exceeded the expected annual sales volume by 15% or less are awarded a $5,000 bonus. Otherwise, no bonus is awarded ($0). A Best in Class bonus of $8,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 the file 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: Display Excellent if the estimate is within 10% (higher or lower) of the actual sales volume. (Hint: For example, if you wanted to determine if the estimated value 26 is within +/ 25% of the sales value 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 estimate is greater than 10% higher or lower, but within 20% higher or lower of the actual volume. Display Poor if the estimate is greater than 20% 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 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.

Expected volume:	400	410	415	390
	Quarterly Sales Volumes
Dealer#	1Q	2Q	3Q	4Q
3321	433	521	460	477
3322	315	414	398	243
3323	284	119	272	205
3324	501	429	395	459
3325	321	339	294	157
3326	536	249	234	211
3327	311	496	484	504
3328	383	210	102	365
3329	210	406	183	409
3330	185	355	495	510
3331	499	325	227	390
3332	126	208	219	189
3333	400	410	415	390
3334	270	339	297	291

Please provide formulas for each step