Complete the following:

1) Use the data in the CKG_Auto.csv file to create your HW spreadsheet, placing the data in a worksheet named Auto. (This past years Quarterly Sales Volumes are in the data file provided...assumptions such as the Expected Volume & Shipping Rebate can be found in the steps below.) Note: The CKG_Auto.csv file is a text file...NOT an Excel file! Please be sure to save your file in Excel format (with a file extension of ".xlsx"). Add appropriate formatting to make the worksheet easy to readmake the layout and design of your worksheet look like the screenshot provided. (In your worksheet, cells with #'s will show numbers & cells with @'s will show text.) All data, formulas, etc. should reside in the same cells as my screenshot. No merged cells were used. (For labels such as "Manufacturer's Expected Per Vehicle Quarterly Sales Volume & "Per-Vehicle Shipping Rebates for Exceeding Expected Sales Volume Estimates", center those across the range shown. Expected volume and Shipping Rebate per vehicle labels are right aligned.) Do not forget to include a Documentation sheet as specified in my Notes.)

2) The top of your worksheet contains inputs (assumptions) such as bonus amounts, shipping rebates, and so on. List the inputs as shown in the screenshot. Wrap text and format the data as needed. (The accounting format is used for the dollar formats, with dashes for zeros...only the first row of values in the long list of data shows the dollar sign, which is aligned at the left edge of the cell.) Make it so that you can always see the column headings and dealer numbers, no matter how much you scroll the worksheet. (Note that the Annual Expected Volume is the sum of the individual quarters.) Make it so that you can quickly and easily collapse (hide) or expand (show) the rows containing the assumptions. Ensure all assumptions are formatted as shown in the screenshot. (The actual number values for these cells can be found in subsequent steps.) Do not just type in the "within" and the ">=".thevalues in these cells must still be numbers so that they can be used in formulas. Typing words or symbols into the cell will turn the numbers into text labelsyou must use an Excel "feature in order to be able to type a number, and have it display with certain text before the number.

3) For each dealer, determine the total value of the Annual Sales Volume for all 4 quarters. (Ensure that a comma is displayed as the thousands separator is displayed for any values >= 1,000. The number one thousand should display as 1,000 NOT 1000.) Remove any "error-alert" indicators (specified by little green triangles) from your spreadsheet.

4) Calculate the value of the Shipping Rebate for each quarter for each dealership. A rebate on shipping expenses is 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, $109 per car sold; 3rd quarter, $88 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. In quarters when a dealer is eligible for a shipping rebate, award the per-vehicle rebate for all carsshipped that quarter. HINT: You should only have to enter 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. Expected sales volumes for large dealerships for each quarter are as follows: 1st Quarter, 300; 2nd Quarter, 425; 3rd Quarter, 410; 4th Quarter, 320 Remember--dealers will only receive rebates in quarters where their actual quarterly sales volumes exceeded expected sales volumes. To make this clear to users of your spreadsheet, insert the following comment in the cell for the Shipping Rebate per Vehicle: "When Expected Volume is exceeded, rebate is applied to ALL vehicles shipped that quarter." Calculate the Annual Shipping Rebate (for all four quarters) for each dealership.

5) Sales Estimate Quality: Analyze the quality of the Manufacturer's Expected Quarterly Sales Volume estimates by comparing the estimated Annual Expected Sales Volume versus the actualAnnual Sales Volumes for each dealership. The task specified above can be accomplished in different ways, but one of the easiest ways is to first calculate the % Difference between the actual sales volume & the expected sales volume estimate for each dealer. (Put these formulas in a column to the right of the Total Shipping Rebate.) Hint: Use the format for the standard % difference formula (as specified in the text): (new_value - old_value) / old_value Or, in this case: (Actual - Expected) / Expected (You can use the results of this formula when calculating the "Overall Sales Volume Bonus" later in this problem.) Leave the answer as a negative value if it comes out that way. In addition, the answer should be formatted as a percentage with 1 decimal place (i.e. 9.2% or -9.2%). Insert a comment in the cell containing the column heading for % Difference that makes it clear what formula is being used to calculate the % Difference. Simply insert the following text in your comment: (Actual_sales - Expected_sales) / Expected_sales Then, in the next adjacent column calculate the Sales Estimate Quality according to the guidelines below. (The closer the dealer's estimate is to actual sales, the better. If the dealer overestimates sales, he will have a lot of extra cars sitting on the lot. But if he underestimates the sales, he will not have enough inventory on hand to meet demand.) Display "Excellent" if the actual sales volume is within 5% (higher or lower, inclusive) of the expected sales volume estimate. Display "Good" if the actual sales volume is not within 5% (higher or lower) of the expected sales volume, but is within 10% (higher or lower, inclusive) of the expected sales volume estimate Display "Poor" if the actual sales volume is not within 10% (higher or lower) of the expected sales volume estimate. BIG HINT: Although it is possible to complete this problem by making use of the AND function, it is MUCH easier it you make use of the ABS (absolute value) function within this formula. Make it easier to distinguish the different Dealer Estimate Qualities by applying colors to each of the labels. Note: Do NOT manually apply these font colors. The formatting should be flexible so that if the actual sales volume is changed, then both the Estimate Quality text and font color both change accordingly. This formatting should work regardless of what labels are used. For example, If the Sales Estimate Quality labels are changed to Great, Mediocre and Bad, the appropriate formatting should still be applied. Also apply this same formatting to the assumptions at the top of the column. If the Dealers Estimate Quality is Excellent, make the font for Excellent automatically turn green and bold. If the Dealers Estimate Quality is Good, make the font for Good automatically turn blue and bold. If the Dealers Estimate Quality is Poor, make the font for Poor automatically turn red and bold.

6) In the next adjacent column, calculate the value of the annual Overall Sales Volume Bonus for each dealership. Dealerships that exceeded the expected annual sales volume by at least 1% are awarded a $5,000 bonus. Dealerships that exceeded the expected annual sales volume by at least 10% are awarded a $15,000 bonus.

7) In the next adjacent column, calculate the value of the "Best in Class" bonus for each dealership. A "Best in Class" bonus of $20,000 is awarded to the dealership with the highest Annual Sales Volume in its class. (Only the dealership with the highest annual sales volume will receive this; all others will receive $0.)

8) In the next adjacent column, determine whether (TRUE or FALSE) this company Received none of the (three) promotions.(columns K, N, O) (No Rebate or Bonus) Use a single BOOLEAN logical function to perform this calculation.

9) In the next adjacent column, determine whether (TRUE or FALSE) this company Received only theshipping rebate. Use a single BOOLEAN logical function to perform this calculation (and do not refer to the result in the cell to the left).

10) Below the data, calculate Totals for all numeric values (except the Dealer # and % Difference). For each rebate/bonus, calculate the Average Value of Actual Bonuses Received.and the Number of Dealerships Receiving Bonus. Do NOT include dealerships that did not earn a rebate/bonus in the average calculation. Do not average in the 0 valuesaverage only the values where a rebate/bonus was actually received. If there are two bonuses: $15,000 & $5,000, then the average bonus should be $10,000. (The formula to calculate the average bonuses should be flexible and should not refer to or rely your Total or # of Dealership formulas.)

11) If a Dealers Total Shipping Rebate, Sales Volume Bonus & Best in Class Bonus add up to at least $200,000, the dealer is considered a "Big-wig Dealer." In such case, make the background fill color for cell containing the Dealers # turn red. (To make it easier to read, also make the font bold and white.). Make sure the conditional formatting is flexible...the Rebate/Bonus threshold, dealer sales volume values, or any other values could change at any time.

12) Use Boolean functions to complete the table for the items shown at the bottom right corner of the screenshot. Each formula should simply return either a TRUE or FALSE. (Your formulas should be flexible. They should only reference the TRUE/FALSE values in the table of data abovedo not refer to the Number of TRUE values or any other cells.) Extra Practice

13) Make a copy of the Auto worksheet and name it Auto_2. For any formulas where you used nested IF statements, instead use a single logical function. (HINT: Use IFS.)