Ice Cream Sales Challenge (page one)
The average number of ice cream product sold at Concord’s Best Ice Cream is 170 per day in the
month of July. An ice cream sale item includes: an ice cream cone, an ice cream dish, a frappe
or a sundae.
Concord’s Best makes all of its own ice cream from scratch. The shop uses the most “bulk” milk
and cream in the area. The process to make ice cream takes about 2-3 days.
The shop is located on a major road and has plenty of parking. The store has four walk-up
windows outside and the store has at least one window open at all times. There isn’t any indoor
seating at all. The store is open from Memorial Day to Halloween.
One of the challenges of running a business is planning. Good planning may make the
difference between making money (profit) and losing money, as well as the difference between
happy customers and unhappy customers.
There are a number of factors that influence sales. These factors are critical to understand so
you can prepare to serve people. The class should discuss the following questions to prepare:
1. What does the word “average” mean? Does it mean that the store sells 160 cones
2. What does the statement “factors that influence sales” mean in the above paragraph?
What is a factor? Brainstorm a list of factors that affect overall sales and the customer
3. How do these factors affect the business? (hint: ice cream takes a 2-3 days to make and
all supplies must be on hand, employees need a shift schedule at least a week in advance).
4. Pick a factor and explain how predicting it could help make a better business decision.
5. How do you predict the future and plan accordingly?
1. The following page has a series of graphs for you to produce. Save each graph on one
worksheet within one workbook on Excel. Call the workbook: “Concord’s Best Ice
2. Once complete, copy your graphs on to our special “Concord’s Best Prediction” template
3. For extra credit, produce a schedule for the business using the “schedule” template
Concord’s Best Ice Cream Sales Data:
SALES PER DAY GRAPH: Sales vs. Day of the Week: Number of ice cream products sold
(including cones, dishes, frappes and sundaes) compared to days of the week:
(average is 170)
WEATHER GRAPH: Sales vs. Cloud Cover: Number of ice cream products sold (including
cones, dishes, frappes and sundaes) compared cloud cover
Partly cloudy: 170
SPECIAL DAY GRAPH: Sales vs. Special Days/Events: Number of ice cream sold (including
cones, dishes, frappes and sundaes) compared to: special days:
Race Weekend: 450
Antique Car Night: 375
Average Day: 170
ITEMS SOLD GRAPH: Breakdown of Items Sold: Product items sold out of an average of
Bonus Exercise: Average Sales vs. Time of Day Out of an average of 160 products:
11 AM to 2 PM: 20
2 PM to 5 PM: 40
5 to 8 PM: 75
8 PM to 11 PM: 35
WEEKLY ANALYSIS REPORT ON JULY 7, 2006 to PREPARE FOR WEEK OF JULY
The weather forecast for the third week of August is sunny for the first half of the week (Monday
to Wednesday), partly cloudy for the next two days (Thursday and Friday) and rainy all
weekend. Given the data in the following graph of past weather performance:
(paste weather graph here)
(paste day of the week graph here)
Given the patterns outlined in the graphs above, the following table (you need to complete)
summarizes the history and gives a best guess prediction (column 3) of what will happen:
Day Regular Expected Predicted Sales Reason
Sales Weather Units
WEEKLY ANALYSIS REPORT ON JULY 14, 2006 to PREPARE FOR WEEK OF JULY
There is a big race at the track on Wednesday and construction on the road on Friday. The
weather is supposed to be sunny. We are trying to predict how much of each item we are going
(paste day of the week graph here)
(paste special day bar graph here)
(paste items sold pie graph here):
How much more business do you expect on these days versus normal days? __________
Estimate the number of items sold:
Average Day Wed (race) Friday
EXTRA BONUS QUESTION:
Create a Concord’s Best Ice Cream work schedule for employees.
All staff work one of two shift per day, 11 AM to 5 PM (DAY shift) or 5 PM to 11 PM
All staff work ONLY four shifts per week
You only have 56 shifts budgeted per week, you can use them however you deem fit
given the factors of the week.
Do a schedule for each week.
The 14 staff members are: Mary, Bob, Bill, Steve, Sally, Ashley, Megan, Dan, Collene,
Tootie, Sue, Celeste, Paula and Kris
The schedule prints in color so you can use colors in Excel.
The schedule goes from Monday to Sunday.
The schedule must be easy to read.