Blue Devil Candy Store/An Excel Challenge

Lesson Created by Patricia Janann Nicholson

Assignment 7

Now your the expert. You have been asked to run the Blue Devil Candy Store and it is your responsibility to develop a spreadsheet to automatically calculate daily and weekly statistics. The information you will need to include is listed below, however you will have to determine which mathematical formulas to include in order to get the appropriate statistical results. Hints will be provided on where to enter the data in your chart in order to get the best overall layout for your spreadsheet. Remember to use appropriate formatting tools in order to make your spreadsheet look attractive (merge and center, borders, bold, highlighting, etc).

Include at the top of your spreadsheet:

  • Name of the school store
  • Your name with your title
  • Title of your spreadsheet (Ex: Blue Devil Candy Sales Statistics)

Date to enter:

  • Candy (Data Title)
      • M & M's
      • Snickers
      • Milky Way
      • Butterfingers
  • Days of the week (Monday through Friday)
  • Weekly Sales (This would be the sum of each candy bar sold per week)
  • Daily Statistics (Data Title)
      • Total of all Candy Sold Per Day (There should be one for Monday, Tuesday, Wednesday, Thursday, Friday) (Hint-to get the total, use the function sum)
      • Most Candy Bars Sold Per Day (Monday-Friday) (Hint-use the function max)
      • Least Candy Bar Sold Per Day (Mondy-Friday) (Hint-use the function min)
  • Weekly Statistics (Data Title)
      • Most Sold Weekly (Hint-use the function max)
      • Least Sold Weekly (Hint-use the function min)
      • Total Sold Weekly (Hint-use the function sum)
  • Calculated Profit For Blue Devil Candy Sales (Data Title)
      • Candy (Data Title)
        • M & M's
        • Snickers
        • Milky Way
        • Butterfinger
      • Our Cost (The information in parenthesis have already been included above, you do not need to enter it again.)
        • (M & M's)-$0.30
        • (Snickers)-$0.28
        • (Milky Way)-$0.25
        • (Butterfinger)-$0.30
      • Price Sold at (The information in parenthesis have already been included above, you do not need to enter it again.)
        • (M & M's)-$0.50
        • (Snickers)-$0.50
        • (Milky Way)-$0.50
        • (Butterfinger)-$0.50
      • Profit/Candy
        • Enter formulas here to calculate the profit made for each candy item (Hint-this can be found by subtracting your cost from the price sold)
      • Weekly sales per candy item per week (which you have already entered earlier)
      • Gross profit per candy item (Hint-this can be found by multiplying the weekly sales of each candy item by your cost)
      • Net profit per candy item (Hint-this can be found by multiplying the weekly sales of each candy item by the profit made for each)
  • Totals for both Gross Profit and Net Profit (Hint-this can be found by using the sum function)

After completing the spreadsheet, enter the following information on the number of candy bars sold per day. The numbers are all you will enter into your chart. As you enter these numbers, the formulas you have already entered should calculate your results. Your answers will be checked for accuracy.

Candynday
Mondaysday
Tuesday
Wednesday
Thursday
Friday
M & M's0
30
27
34
25
45
Snickers
20
10
29
11
44
Milky Way
15
17
41
17
38
Butterfinger
12
15
22
29
55