Level 3 – Analyzing Dealership Promotions for CKG Auto

CKG Auto runs several promotions each year to reward dealershipsfor their sales efforts, sometimes on specific car models and othertimes for overall sales. CKG Auto is running three differentpromotions for large dealerships, based on performance over thispast calendar year. Small and medium-sized dealerships have similarpromotions but based on different expected volumes and rebatepercentages. The promotions are as follows:

• A rebate on shipping expenses based on exceeding expectedquarterly volumes: These are savings CKG Auto realizes from itstrucking carriers and has decided to pass along as a reward todealerships that have exceeded expectations. Rebates for eachquarter were set by management as follows: 1st quarter, $65 per carsold (actual volume); 2nd quarter, $80 per car sold; 3rd quarter,$65 per car sold; and 4th quarter, $122 per car sold. Dealershipsare awarded the rebate on a quarter-by-quarter basis, only forquarters where their actual sales exceeded expected volumes forthat quarter. Expected sales vol- umes for large dealerships foreach quarter are as follows:

– – – –

1st Quarter: 325 2nd Quarter: 425 3rd Quarter: 440 4th Quarter:350

•Anoverallsalesvolumebonusbasedonexceedingexpectedannualvolumes:Dealershipsthat exceeded the expected annual sales volume by more than 7% areawarded a $10,000 bonus. Dealerships that exceeded the expectedannual 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 onedealership with the highest overall sales volume in its class

You have been asked to set up a worksheet to record the dealerinformation for the past year and apply the appropriate promotionsto each dealership. The actual dealership quar- terly sales volumeshave already been entered in a worksheet. Now, you will finalizethe analysis.

Complete the following:

1. Open the workbook named CKGPromo.xlsx located in the Chapter4 folder, and then save it as Promo Large Dealerships.xlsx. Thispast year’s quarterly sales vol- umes and expected sales volumesfor large dealerships have already been entered into this workbook.Complete the analysis using any additional columns and/or rows asyou deem necessary. All formulas should work when copied eitheracross or down, as needed. Include titles in each column and/or rowto identify the corresponding data. Add any appropriate formattingto make the worksheet easy to read.

2. Insert rows at the top of the worksheet to create aninput area where you can list the inputs such as bonus amounts,shipping rebates, and so on. List the inputs explicitly and useonly one worksheet for this task, so that any inputs can be easilydisplayed for management and then later copied and modified tocalculate the promotions for both the medium and small dealershipclasses. Insert rows as needed, and be sure to clearly label eachinput so that the data can be interpreted and modified easily nextyear. Wrap text and format the data as needed.

3. In a column adjacent to the quarterly sales data, calculate thecorresponding annual sales volume for each dealership.

4. Calculate the value of the shipping rebate for each dealer foreach quarter (use four new columns). This should require only oneformula that can be copied down the column and across the row. Besure your inputs are set up so that this can be easilyaccomplished. Remember, dealers will only receive rebates inquarters where their actual quarterly sales volumes exceededexpected sales volumes. In an adjacent col- umn, determine thetotal value of the shipping rebate for all four quarters bydealership.

5. Analyze the quality of these volume estimates by categorizingthe quality of the annual volume estimate versus the actual annualvolumes for each dealership into the fol- lowing categories:

•Display“Excellent”iftheestimateiswithin5%(higherorlower)oftheactualsalesvolume. (Hint: For example, if you wanted to determine if the value26 is within +/– 25% of 40, you would need to test this value tomake sure that both 26>=40–.25*40 and 26<=40+.25*40.)

•Display“Good”iftheestimateisgreaterthan5%higherorlower,butwithin10%higher or lower of the actual volume.

• Display “Poor” if the estimate is greater than 10% higher orlower.

6. In an adjacent column or columns, calculate the value of theannual sales volume

bonus for each dealership.

7. In an adjacent column, calculate the value of the “Best inClass” bonus for each dealership. (Only the dealership with thehighest annual sales volume will receive this; all others willreceive $0.)

8. In a row below the data, calculate the total values for alldealers for sales volume, shipping rebates, sales, and best inclass bonuses.

9. In an adjacent column, determine if (TRUE or FALSE) thisdealership received money during this year for both a shippingrebate and a sales volume bonus. Copy the formula down the columnto obtain the corresponding value for each dealership.

10. Skipping one row below the totals, in the column just used inStep 9, determine (TRUE or FALSE) if none of the dealershipsreceived both shipping rebates and a volume bonus. Label the rowaccordingly.

11. Just below the result of Step 10, determine if only dealershipswith Excellent estimate qualities (determined in Step 5) receivedboth shipping rebates and a sales volume bonus. This formula neednot 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 receivingthis rebate/bonus and the average value of the bonus (includedealerships 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 withMicrosoft Excel 2010 page 286-288

## Expert Answer

. . .