3. (0pts) For each of the highlighted cells in rows 424, create formulas to perform the necessary calculations as outlined above in the Column Descriptions. For your convenience on this assignment I have applied a yellow shading to all of the cells that will need you to generate formulas in. Formatting instructions are summarized in the last step at the end of this assignment. 4. (3 pts) In cell F4, create a formula that calculates the Gross Profit Margin % and copy that formula down to F24. Format the values to show the percent value with zero decimal places (i.e.24\%). Please note the warning about about adding unnecessary parentheses. 5. (2 pts) In cell G4, create a formula that calculates the Total Cost and copy that formula down to G24. 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 your ever see the formatting type of "Custom" or "Accounting" in this assignment, that is wrong and you will lose points. 6. (2 pts) In cell H4, create a formula that calculates the Total Sales to Dealer using the logic above and copy that formula down to H24. Refer to step5 above for formatting. Consistency is key! 7. (3 pts) For % of Total Volume in column I, first calculate the total volume of all cars sold in cell C26. Then in cell 14 , for that model, find the % of its volume as compared to the Total Volume now located in C26. For full credit, you should write your formula in 14 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 the cells in column I to display percentage values to the nearest tenth of a percent (e.g. 5.6% ). 8. (0 pts) For the following steps: summarize the columns as needed for the shaded cells in rows 26-31. 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. 1. (0pt) Open the workbook named A1 Sales.xIsx downloadable from links at the top of this page and then save it as LastName FirstName Auto Sales. xlsx (where LastName is YOUR last name and FirstName is YOUR first name). 2. (1 pt) Rename the Sheet1 worksheet tab as "Q1 Actual" and change the color of the tab to Red. 3. (0 pts) For each of the highlighted cells in rows 4-24, create formulas to perform the necessary calculations as outlined above in the Column Descriptions. For your convenience on this assignment I have applied a yellow shading to all of the cells that will need you to generate formulas in. Formatting instructions are summarized in the last step at the end of this assignment. 4. (3 pts) In cell F4, create a formula that calculates the Gross Profit Margin % and copy that formula down to F24. Format the values to show the percent value with zero decimal places (i.e.24\%). Please note the warning about about adding unnecessary parentheses. 5. (2 pts) In cell G4. create a formula that calculates the Total Cost and copy that formula down to G24. 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 this assignment, that is wrong and you will lose points. 6. (2 pts) In cell H4, create a formula that calculates the Total Sales to Dealer using the logic above and copy that formula down to H24. Refer to step 5 above for formatting. Consistency is key! 7. (3 pts) For \% of Total Volume in column I, first calculate the total volume of all cars sold in cell C26. Then in cell 14, for that model, find the \% of its volume as compared to the Total Volume now located in C26. For full credit, you should write your formula in 14 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 the cells in column I to display percentage values to the nearest tenth of a percent (e.g. 5.6%). 8. (0 pts) For the following steps: summarize the columns as needed for the shaded cells in rows 26-31. 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. 3. (0pts) For each of the highlighted cells in rows 424, create formulas to perform the necessary calculations as outlined above in the Column Descriptions. For your convenience on this assignment I have applied a yellow shading to all of the cells that will need you to generate formulas in. Formatting instructions are summarized in the last step at the end of this assignment. 4. (3 pts) In cell F4, create a formula that calculates the Gross Profit Margin % and copy that formula down to F24. Format the values to show the percent value with zero decimal places (i.e.24\%). Please note the warning about about adding unnecessary parentheses. 5. (2 pts) In cell G4, create a formula that calculates the Total Cost and copy that formula down to G24. 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 your ever see the formatting type of "Custom" or "Accounting" in this assignment, that is wrong and you will lose points. 6. (2 pts) In cell H4, create a formula that calculates the Total Sales to Dealer using the logic above and copy that formula down to H24. Refer to step5 above for formatting. Consistency is key! 7. (3 pts) For % of Total Volume in column I, first calculate the total volume of all cars sold in cell C26. Then in cell 14 , for that model, find the % of its volume as compared to the Total Volume now located in C26. For full credit, you should write your formula in 14 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 the cells in column I to display percentage values to the nearest tenth of a percent (e.g. 5.6% ). 8. (0 pts) For the following steps: summarize the columns as needed for the shaded cells in rows 26-31. 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. 1. (0pt) Open the workbook named A1 Sales.xIsx downloadable from links at the top of this page and then save it as LastName FirstName Auto Sales. xlsx (where LastName is YOUR last name and FirstName is YOUR first name). 2. (1 pt) Rename the Sheet1 worksheet tab as "Q1 Actual" and change the color of the tab to Red. 3. (0 pts) For each of the highlighted cells in rows 4-24, create formulas to perform the necessary calculations as outlined above in the Column Descriptions. For your convenience on this assignment I have applied a yellow shading to all of the cells that will need you to generate formulas in. Formatting instructions are summarized in the last step at the end of this assignment. 4. (3 pts) In cell F4, create a formula that calculates the Gross Profit Margin % and copy that formula down to F24. Format the values to show the percent value with zero decimal places (i.e.24\%). Please note the warning about about adding unnecessary parentheses. 5. (2 pts) In cell G4. create a formula that calculates the Total Cost and copy that formula down to G24. 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 this assignment, that is wrong and you will lose points. 6. (2 pts) In cell H4, create a formula that calculates the Total Sales to Dealer using the logic above and copy that formula down to H24. Refer to step 5 above for formatting. Consistency is key! 7. (3 pts) For \% of Total Volume in column I, first calculate the total volume of all cars sold in cell C26. Then in cell 14, for that model, find the \% of its volume as compared to the Total Volume now located in C26. For full credit, you should write your formula in 14 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 the cells in column I to display percentage values to the nearest tenth of a percent (e.g. 5.6%). 8. (0 pts) For the following steps: summarize the columns as needed for the shaded cells in rows 26-31. 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
