$(3$ pts$)$ For $\%$ of Total Volume in column D$,$ first calculate the total volume of all cars sold for this quarter in cell C$25.$ Then in cell D$3,$ for that model, find the $\%$ of its volume as compared to the Total Volume now located in C$25.$ For full credit, you should write your formula in D$3$ using proper cell referencing $($hint: Mixed referencing puts the $ sign in front of either the column OR the row and absolute referencing puts $ signs in front of both column AND row. One of these two techniques is the better$/$more efficient choice so that is the one that will get you the most points! Hint: use absolute referencing when copy down and across. Use mixed referencing to protect the row number if you are dragging down or protect the column letter if you are dragging the formula to the right. Format this column as percentage with one decimal place $($i$.$e$.5\mathrm{.}4\%)$

$(3$ pts$)$ In cell G$3,$ create a formula that calculates the Gross Profit Margin $\%$ and copy that formula down to G$23.$ Format the values to show the percent value with zero decimal places $($i$.$e$\mathrm{.}24\%).$ Please note the warning about about adding unnecessary parentheses. Remove the hint from cell G$2$ after you get it working.

$(2$ pts$)$ In cell H$3,$ create a formula that calculates the Total Cost and copy that formula down to H$23.$ Apply the Currency format to all of the lines but only show the $ sign on the top row! Please note that when you format a cell or range of cells as currency and then disable the $ sign that this alters the formatting type to "number". This is an expected behavior and is therefore fine. In addition to the Currency formatting, format all of the numbers in this column to have zero decimal places. If you ever see the formatting type of "Custom" or "Accounting" in any cell in this assignment, that is wrong and you will lose points. Remove the hint from cell H$2$ after you get it working.

Formatting: Format the volumes of cars in column C as discussed in step $21$: All numbers that are not money$-$related should be formatted as Number with $0$ decimal places and with commas. Format the costs and selling prices in columns E & F as also discussed in step $21$: All columns on all sheets containing values that are currency should:

For the first row and any Totals$/$Summarization rows at the bottom of the table be formatted as Currency with $0$ decimal places and have the $ sign included

All succeeding rows in the table should be Currency with $0$ decimal places WITHOUT the $ sign included. As discussed earlier, this causes the formatting type to change to "Number" and that is OK$/$expected

No cells in this spreadsheet should ever have the formatting type of "Custom"!

$(2$ pts$)$ In cell I$3,$ create a formula that calculates the Total Sales to Dealer using the logic above and copy that formula down to I$23.$ Refer to step $6$ above for formatting. Consistency is key! Remove the hint from cell I$2$ after you get it working.

$(0$ pts$)$ For the following steps: summarize the columns as needed for only the shaded cells in rows $25-30.$ Be sure to write all formulas so that they can be copied across as necessary without having to re$-$enter in the formula manually into each cell.

$(2$ pts$)$ In row $25,$ calculate the totals for each of the $3$ shaded columns $($C$,$ H & I$).$ Formatting for these columns was covered in step $7$ above and summarized in step $21.$

$(3$pts$)$ For row $26,$ Calculate the averages for all shaded columns. Mentally make a note of what the AVERAGE formula calculates for the columns with no number in them for sales. Now put zeroes into the Sales Volume cells that are empty $($NULL$)$ and note the difference. Zero counts differently than empty$/$NULL$!$ Leave in the zeroes for the rest of this Assignment because it is important to note that they did not sell any of those models! Formatting is covered above in step $7$ and summarized in step $21.$

$(2$ pts$)$ In cells C$27$ and C$28$ write formulas that find the highest and lowest volumes of all of the car models. Format these as numbers with commas and no decimal places so that all of the numbers found in column C have the exact same formatting discussed in step $7.$

$(2$ pts$)$ In cell G$30$ write a formula that calculates the number of different models that are available regardless of whether there were sales for that model or not $($hint: you will need to use the "Model" column in order to do this because that is the only column that will always contain data for each model$).$ You will need to use the formula that counts cells containing text $($i$.$e$.$ Panda$)$