• Replace "Your Name" with your first and last name
• 2. Add annual totals for the Sales and Quota columns.
• 3. Include a column which shows the Profit/Loss between Sales and Quota. The cities that have not met
• their quota will be in red using a condition formatting rule.
• 4. In the next column over inserting a Sparkline to show the trend line for Sales and Quota.
• 5. The company gives a bonus to locations that have had a successful sales year. Add the Sales bonus calculations in a separate column. If the Profit is greater than $50,000 calculate a 5% bonus otherwise no bonus is given, and the cell should remain blank. The bonus percent should be absolutely referenced.
• 6. You wish to classify your Monthly Sales as OUTSTANDING, NORMAL, POOR. Sales >$500,000 are
• OUTSTANDING, Sales between $250,000 and $500,000 are NORMAL; and Sales below $250,000 are
• POOR. Vlookup Table for this information. Then add a new column that uses the Vlookup function to give a rating to each city.
• 7. To the right of the data insert a Pivot Table showing the City's Sales within Region.
• 8. Copy the information from Columns A, B, C, D to a new sheet, and rename the sheet 2020. Change the text from A3 to 2020. Rename the sheets to reflect their year.
• 9. On the 2019 sheet, custom sort to have Region sorted first then City.
• 10. chart (not a pie chart) which compares the monthly sales for the cities. Be sure to label the
• axis and give the chart an appropriate title. Place the graph beside the data and snap it to the grid.
• 11. Use the SubTotal feature to total Sales and Quota by Region.

Boston East $343,677 $400,000 New York East $677,000 $400,000 Miami South East $432,050 $500,000 Los Angeles South West $980,003 $500,000 Las Vegas South West $324,560 $500,000 New Orleans South $210,000 $210,000 Buffalo North $193,475 $200,000 Detroit North $156,233 $200,000 Cincinatti Central $120,000 $200,000 Chicago Central $568,030 $500,000 Houston South $330,780 $500,000 San Jose South West $563,780 $500,000 San Francisco West $678,920 $500,000 Baltimore East $22,345 $200,000 Charlotte South East $157,880 $157,880 Cleveland Central $320,889 $200,000 Kansas City Central $212,000 $200,000 Fresno South West $450,600 $500,000 Atlanta Central $578,000 $500,000 Virginia Beach East $220,050 $200,000 Toledo North $320,000 $320,000 Arlington East $238,900 $200,000 Jersey City East $346,780 $200,000 Akron Central $37,890 $200,000 Fort Wayne Central $60,000 $60,000 Richmond South East $220,400 $200,000 Sacramento South West $875,000 $500,000 Durham South East $234,500 $200,000 Boise Central $128,900 $200,000