Q9 Coffee Stand
Attribution: Link to sweetclipart.com
Dig the Data – Coffee Shop
In this Quest, your data collection will come from your work at the Coffee Shop. Depending on the weather and customer preferences, the company will either gain or lose money. The owner of the company has contacted your teacher and requested a student to investigate the operations. You will have 14 days at the company to try to make as much profit as possible. Be careful because you can lose everything. You must record all of your data and conditions in a spreadsheet and analyze the data over the 14 days of work. Your teacher will be watching for your report at the end of this activity.
This Quest is provided as an Adobe Acrobat file to download - CoffeeShop.pdf
1. Get a data collection sheet from your teacher and something to write with or you may work directly on the Excel spreadsheet provided. The columns that are white should be filled-in each round before you start selling coffee. Then the green columns will be filled--in after you sell the day’s coffee.
You will be doing a 14 day simulation. (*Your computer will need the java plug-in installed. You may have to click on a link to allow it to run. If it doesn’t work please inform your teacher.)
Steps 3-6: Goal and coffee data tracking
3. Notice you start with $30. Your goal is to make more money than you started with. However, in order to make money, you’ll have to spend some money. In this game, you’ll decide how much coffee, how many teaspoons of sugar, and how many cups of milk to use per cup of coffee. Also, you’ll have to buy the right amounts of each, plus cups. Don’t forget, though, your milk can spoil by the end of the day, ants can invade your sugar causing you to lose some or all of your supply, and your coffee can go stale. If you are not careful, you will go bankrupt.
Buy all of your supplies in what you predict to be the “right” amount for the day.
4. Next, on your information sheet, write down the temperature and the weather forecast. This will be important when deciding how to prepare the coffee for the day.
5. Now it’s time to set up your recipe. How many teaspoons of coffee for each cup and how much milk and sugar to satisfy your customers? Set your preferences, and then write them down on your information sheet.
6. On the next screen, choose how much to charge for each cup of coffee. You need to make enough money per cup to cover your expenses, but don’t make it so expensive that people won’t buy it. (Optional…you can add up what you spent on your spreadsheet for the day to help decide how much to charge.) Also, do not change your price during the day as it will not show in your data.
Steps 7-9: Running the Coffee Stand
7. Now, run the simulator. Customers will come up to your table and buy your coffee and give you feedback. A dollar sign above their heads means they think it’s too expensive. You can roll over their other comments to see what they have to say.
8. At the end of each day, write down on your information sheet how many cups you sold, how many potential customers you had, and how much money you finished the day with.
9. Repeat these steps for days 2-14, buying supplies, making coffee, and recording your results. Between each day’s sales is where you can make changes to your recipe or price but be sure to record each day on your spreadsheet. When you have completed all 14 days then it is time to analyze the data. If you have been working on a paper collection sheet be sure to input everything into Excel.
Steps 10-11: Create your business report
10. Highlight the daily balance column and click the insert tab to begin making your line graph that shows the change over 14 days.
11. Customize your graph with a chart title and axis labels.
(Optional) You may choose to enhance your chart as seen in the Excel tutorial. Remember that your starting amount was $30.00 and your goal was to make a profit.
12. Answer the following questions:
How often did you profit? Would you recommend this company to continue sales or shut down its business according to your profit?
Did you go bankrupt and have to start over? What happened? What would the line graph look like for a bankruptcy?
How often did you change your recipe? Describe how and why you changed it.
What was the average price you charged for your coffee? (Do not forget about functions in your Excel program.)
Describe your overall sales trend over the 14 days. Was it mostly increasing, decreasing or a constant change daily?
Completing this Quest
To complete this Quest, you must save a screenshot of your spreadsheet and graph to the File Space. Then save your word processing file in your File Space and present your information to your teacher.
Check off this Quest on the 21t4s roadmap
Go to the next Quest Sort it out.