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 storeYour name with your titleTitle of your spreadsheet (Ex: Blue Devil Candy Sales Statistics)

Date to enter:

Candy (Data Title)

M & M'sSnickersMilky WayButterfingersDays of the week (Monday through Friday)Weekly Sales (This would be the sum ofeachcandy 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'sSnickersMilky WayButterfingerOur 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.30Price 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.50Profit/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 |

**Which candy bar made the most net profit**

Create graphs to show:

Most candy bars sold per day (You would have a separate graph for each day)

Most candy bars sold per week (weekly sales)

Have your teacher check the accuracy of your information. There is only one correct way to do this worksheet. This will determine a large portion of your 9 week grade, so make sure you put effort and thought into your creation. Once checked, you may go back and correct your spreadsheet to make a better grade.