This assignment is designed to expose you to problem solving and using Excel to assist you in making decisions. It is a blank slate and you are to build a spread sheet that will help you answer the project questions.

All of the information you need to complete the spread sheet and answer the questions is provided.

Part one: You want to increase your coffee shop revenue without making any changes other than increasing the price of your coffee. For this exercise we are not worried about why you want more revenue. In a real business it could be for capital improvements, better benefits, salary increases and many more reasons. Also I am only providing customer preferences for one price change. Using too many options would complicate the assignment and is not necessary.

The local College of Business offered to help you out by offering their students to help with gathering data and analyzing surveys. For a six week period the students monitored your sales; who purchased coffee and what size. They also solicited customer reaction to raising the price of coffee 25 cents a cup on all three sizes.

With the provided data you are to create a spreadsheet that determines your average weekly gross profit/ gross margin. Gross profit/margin is your profit after you subtract the cost of your goods from your revenue. As an example, if a cup cost 10 cents and the coffee costs 30 cents and you sell coffee for $1; the gross profit is 60 cents. It does not take into account marketing, labor, equipment, rent and taxes. If these were subtracted you would have your net profit.

In Question 4, you are to apply the price increases and determine your new average weekly gross profit / gross margin. Your new predicted weekly sales on all coffee sizes is affected by the price increase. You have to figure out how each coffee cup size is affected.

Part Two: You have applied the price increases and then find out that your coffee supplier is raising their price for coffee that they sell to you. You are to determine how that will affect your gross profit. You want to make up for the lost gross profit by increasing the price of a large cup of coffee. As an example, the coffee cost increase lowers your weekly gross profit from $1000 to $950. What price to you need to charge for a large cup of coffee to get back to $1000.

Data for the Assignment:

Customer Purchase Preference (The percentage of each size cup purchased by customer type

College Faculty and Staff: 10% Small, 30% Medium and 60% Large

Local Traffic: 30% Small, 50% Medium and 20% Large

Other: 60% Small, 20% Medium and 20% Large

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

Used in Question 4

College Faculty and Staff: +3% Small, +2% Medium and -5% large

Local Traffic: -4% Small, +1% Medium and +3% large

Other: +10% Small, -5% Medium and -5% large

Coffee Sales by Cup for Six Weeks

Week 1	Small Coffee			2035
	Medium Coffee			1766
	Large Coffee			869
Week 2	Small Coffee			2060
	Medium Coffee			1788
	Large Coffee			889
Week 3	Small Coffee			2061
	Medium Coffee			1764
	Large Coffee			870
Week 4	Small Coffee			2008
	Medium Coffee			1741
	Large Coffee			837
Week 5	Small Coffee			2048
	Medium Coffee			1758
	Large Coffee			868
Week 6	Small Coffee			2042
	Medium Coffee			1758
	Large Coffee			878

Coffee Cup Pricing

Questions 1-3

Small - $2.50

Medium - $3.50

Large - $4.50

Questions 4 and 5

Small - $2.75

Medium - $3.75

Large - $4.75

Cost of Goods Sold (GOGS)

Questions 1-4

Small Cup - $ .08

Medium Cup- $ .09

Large Cup - $.11

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

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

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

Question 5

Coffee price increase to apply for the question.

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

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

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

Instructions: Using the data for the assignment complete your spreadsheet and answer the five questions. When you make the changes for question 4 your spreadsheet will change and your spreadsheet answer from question 3 may change depending on how you build your spreadsheet. That is OK, we can look at your spreadsheet and do some quick checks.

1. What is your average weekly revenue over the six weeks the students gathered their data? Insert the cell with the answer here ____________. It will make it easier to find if you tell us the cell the answer.

2. What is the total Cost of Goods Sold for a large cup of coffee? Insert the cell with the answer here _______________.

3. What is your weekly gross profit / gross margin based on your average weekly sales? Insert the cell with the answer here _______________. We will check the cell in your spread sheet.

4. What is your weekly gross / gross margin after raising the price of each cup size by 25 cents? Insert the cell with the answer here _______________. We will check the cell in your spread sheet. This question is worth 4 points

5. What is the price you have to charge for a large cup of coffee to regain the reduction in your gross profit after apply the coffee price increase? Insert the cell with the answer here _______________.

used Excel to determine the answer.

Final step save excel spreadsheet once completing questions