Skip to main content

Willy Wonka Instructions

  1. In cells B1, B2, and B3, respectively, type your name, Banner A - number, and date.
  2. Format the titles "Willy Wonka Candy Sales from 1983 - 2003" in cell B5 to 14pt Underline and "Northeast Region" in cell B6 to 12 pt. Italics.
  3. Merge and center the contents of cell B5 across the cells B5 through L5. Merge and center the contents of cell B6 across the cells B6 through L6.
  4. Increase the width of columns D through L to 13 points wide.
  5. In cell D21, enter a function (not a formula) to calculate the highest number of candy sales for Dec/Jan.
  6. Calculate the bi-monthly highest total sales for all months by copying the function in cell D21 to cells E21 through J21.
  7. Sort all candy sales data by the "State" data from A to Z, then by the "Zone" data from Z to A.
    • Note: Make sure the integrity of the data is not lost. Do not include column headers or the "Highest Sales" row in the sort.
  8. In cell K9 enter a formula (not a function) to find the ratio of candy sales to the national average. Divide the state/zone's "Total" by the "National Average" in cell B23. In your formula, use an absolute reference to cell B23.
  9. Calculate the ratio for each state/zone by copying the formula in cell K9 to cells K10 through K20.
  10. Format all the values in cells D9 through J21 to numbers with 4 decimal places.
  11. Set columns D through L to be center aligned.
  12. In cell L9 enter a function to compare the "Dec/Jan" sales and the "Oct/Nov" sales for the first state/zone. If the "Dec/Jan" sales is greater than "Oct/Nov" sales display the text "Dec/Jan", if not display the text "Oct/Nov".
  13. Copy the function in cell L9 to cells L10 through L20.
  14. Enclose the contents of cells J21, K20, and L20 in quotes so the formula or function texts are visible. Change the alignment on the three cells to "left".
  15. Create a column chart that shows the monthly sales for all state/zones during Dec/Jan. Do not include the Highest Sales values in the chart. Be sure the chart has all of the following attributes:
    • The horizontal axis labels correspond with the data in B9 through B20 and C9 through C20.
    • The title of the chart is "Dec/Jan Candy Sales - Your full name".
    • The horizontal axis label is "State/Zone".
    • The vertical axis label is "Sales in Millions".
    • The Legend corresponds with the data in D8.
    • Move the chart to a new worksheet.
  16. Examine the chart. Based on the information shown in the chart, which state/zone had the highest sales? Type the answer in cell B25 of the data sheet.
  17. Set up the data sheet and chart so each page prints landscape and on one page each. Check the file in Print Preview.