Question

Instructions: Using the data for the assignment complete your spreadsheet and answer the five questions. When you make the changes for question 4 and 5 your spreadsheet will change and your spreadsheet answer from the previous question may change depending on how you build your spreadsheet. That is OK, we can look at your spreadsheet and do some quick checks. Again, we are expecting to see formulas/functions in the spreadsheet. This assignment is similar to the Biz Café Individual Assignment pricing worksheet.

What is your average weekly revenue over the six weeks the students gathered their data? Insert the answer and the cell for answer here

1. . We will look for it in your spreadsheet. We need the cell so that we don’t have to navigate your spreadsheet to find your calculations.
   1. Hint: You need to average sales for each cup size separately before determining your revenue.

Coffee Sales by Cup for Six Weeks

Week 1	Small Coffee	2045
	Medium Coffee	1689
	Large Coffee	922
Week 2	Small Coffee	2065
	Medium Coffee	1751
	Large Coffee	901
Week 3	Small Coffee	2057
	Medium Coffee	1008
	Large Coffee	868
Week 4	Small Coffee	2032
	Medium Coffee	1698
	Large Coffee	901
Week 5	Small Coffee	2035
	Medium Coffee	1723
	Large Coffee	865
Week 6	Small Coffee	2076
	Medium Coffee	1802
	Large Coffee	903

               

               

               

                Price by Cup Size

                Small -                   $2.50

                Medium -            $3.50

                Large -                  $4.50

2. What is the Cost of Goods Sold for a large cup of coffee? Insert the answer and the cell with the answer here ............ We will check the cell in your spread sheet.
   1. Hint: We are looking for the Cost of Goods Sold for a single large cup of coffee not the total of all large cups sold.

Cost of Goods Sold (GOGS)

Cost of each Cup

Small Cup - $ .08

Medium Cup- $ .09

Large Cup - $.11

Coffee cost for each Cup Size

1/64 lb. of Coffee (Small) - $.11

2/64 lb. of Coffee (Medium) - $.22

3/64 lb. of Coffee (Large) - $.33

3. What is your weekly gross profit based on your average weekly cup sales? Insert the answer and the cell with the answer here _______________.   We will check the cell in your spread sheet.
   1. Hint:   This is where you have to use your average cup sales to determine your average cost of goods sold.

No new data required.

4. What is your weekly gross profit after raising the price of each cup size by 25 cents? Insert the answer and the cell with the answer here _______________.   We will check the cell in your spread sheet.
   1. Hint: You have to determine the forecasted change in average weekly sales based on each cup size.

New Cup Prices by Size

Small - $2.75

Medium - $3.75

Large - $4.75

Customer Response to Coffee Price Change of +25 cents. (How the customer reacts to the price increase)

Small-              +4% (increase in demand)

Medium -        -10% (decrease in demand)

Large-              +6% (increase in demand)

5. Your coffee supplier has increased their coffee price to $7.68 per pound. What is your new weekly gross profit? Insert the answer and the cell with the answer here _______________.   We will check the cell in your spread sheet.
   1. Hint: You have to determine your new coffee cost for each cup size.

Answer 1

Week 1			Week 2			Week 3			Week 4			Week 5			Week 6
Small Coffee	Medium Coffee	Large Coffee	Small Coffee	Medium Coffee	Large Coffee	Small Coffee	Medium Coffee	Large Coffee	Small Coffee	Medium Coffee	Large Coffee	Small Coffee	Medium Coffee	Large Coffee	Small Coffee	Medium Coffee	Large Coffee
2045	1689	922	2065	1751	901	2057	1008	868	2032	1698	901	2035	1723	865	2076	1802	903
SALES		after change in price
Avg small coffee	2052	2134
Avg medium coffee	1612	1451
Avg large coffee	893	947
Cup Size	Price	COGS(cup)	COGS(coffee)	total cogs
Small -	2.50	0.08	0.11	0.19
Medium -	3.50	0.09	0.22	0.31
Large -	4.50	0.11	0.33	0.44
Increase in price	0.25
Weekly gross profit	before increase in price	after increase of price	after change in demand
Small -	USD 4,739.35	USD 5,252.27	USD 5,462.36
Medium -	USD 5,141.75	USD 5,544.71	USD 4,990.24
Large -	USD 3,626.93	USD 3,850.27	USD 4,081.28