10. (3pts) For row 27, Calculate the averages for all columns except for the Gross Profit Margin (since that would require a weighted average and that is for a later module). 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 in step 21. 11. (2 pts) In cells C28 and C29 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. 12. (2 pts) In cell F31 write a formula that calculates the number of different models that are avallable regardless of whether there were sales for that model or not (higit: 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). I am sure that you have already watched the video "Module1 Level2 Part1" so I don't need to remind you to watch that one for the right formula here, Wow Ken, Assignment 1 and I'm already passive-aggressive! 13. (2pts) Now that you have your Q1 Actual sheet completed, your next task is to create 3 additional functionally identical sheets that instead of containing ACTUAL first quarter sales. Instead of actual sales data these 3 new sheets will use the estimated (forecasted) sales volumes for the upcoming Q2, Q3 and Q4 sales quarters. These new sheets that you must create will use sales volumes based on marketing forecast data sales values in the additional file/Asset provided by the name of: "Market Forecasts for Q2,Q3.Q4. x lsx". The marketing group has provided a list of all car models in identical order to the original data you received, with the expected sales volumes for each car model for April - June (Q2), July-September (Q3) and October-December Q4. Make sure that you update cell A1 on each of these new worksheets to accurately reflect the date range contained on each page. Manufacturing costs and Gross Profit Margins are assumed to be the same for all 4 quarters 50 there is no need to change them on your three new worksheet tabs. With the data and assumptions in mind, create a new worksheet named Q2 in your Auto Sales.xlsx workbook by making a copy of the 1st Quarter worksheet and renaming it Q2. Copy and paste the sales volumesyirom the Market Forecasts for Q2,Q3.Q4.1x workbook into your new worksheet into the appropriate cells (replacing the Q1 data with the new Q2 data in this new additional file. If you copy the sheet as recommended above, all of the formatting will be identical (which it should be since consistency is usually a good thing) but you should make sure the header at the top of each worksheet is edited to reflect the correct months in each quarter's worksheet. If there are models with empty sales volumes, replace these with 0 as you did in the first sheet. Verify that all the calculations in the new worksheet reflect the new data. Repeat for new additional Q3&Q4 worksheets. If there are any cells that do not have a number in them replace that empty cell with a 0 so that it does not throw off the value of your average function at the bottom of column C. Hint: Just make 3 copies of your "Q1 Actual" sheet and rename them Q2, Q3 \& Q4. Then download and open up the additional file from the top of this assignment "Market Forecasts for Q2, Q3.Q4.x1sX" and overwrite the sales volumes from this file into the sales volume cells in the A1 Sales.xlsx worksheet one quarter at a time by performing the awesome copy/paste special/values only! If you do this as designed then it should not take you any time at all :) Alter the colors of the 3 new tabs to something other than red. Each tab should be a different color. 14. 2 pts) Your starter file came with a sheet called "Sheet 2". Rename it to "Summary". Move this Summary tab to be the 5 th tab from the left (the far right one). Merge and Center the text in cell A1 ("Yearly Sales Summary Estimated") across columns A - L