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)
