Sell 100 Cups Per Excel Assignment 1

This assignment reinforces the decisions you make running your coffee shop. In Exercise 1 we focus on Staffing. You are graded on your ability to use to Excel to make the Biz Café decisions and complete each question.

Let’s assume you sell a cup of coffee for $3.00. If you sell 100 cups per day from M-F it is easy to mentally calculate your weekly revenue. At $300 per day for five days our revenue will be $1500. It is when you have multiple size cups, varying hours and other factors that the math is not as simple. What Excel does for you is those calculations and by using formulas you can make a price change and instantly revenue data is updated. Make changes to your operating hours your revenue is updated. This happens because you are referencing cells in your formulas. Any change in a cell that is referenced will automatically be applied to associated cells/formulas the cell is used for a calculation.

In our exercise the servers are equal in their capacity to server coffee pre hour. In real life we know that servers have varied capacities. The determining factors in your café’s capacity are hours open and number of servers available per hour.

If you have one server who can make 18 cups of coffee per hour then your capacity is simple. In Biz Café the manager can serve coffee but at a slower rate. It is 5 cups per hour. The manager should only server coffee when necessary; when the servers are over worked or if the manager is the only person working.

If you are open for 10 hours with one server your capacity is 180 cups a day. We will talk about customers in another exercise, but you are limited to selling 180 cups a day. If you add another server, you can sell up to 360 cups a day.

You could also increase your hours to 15 hours but would need 1.5 servers to cover the day. At 15 hours your capacity is 270 cups a day.

Servers are paid by the hour so having too many servers will cut into your profit but not having enough servers will negatively affect your customer satisfaction score. You must know how much your employees will cost you each week.

In this exercise you will determine how many hours your managers work each week, how many servers you need to have at least one server available during operating hours, your daily server capacity and finally the total compensation required for the week.

You will use your operating data for hours open, servers and pay an add them to the blank white cells. You will use formulas/functions to calculate the cells filled with blue.

The grading is based on a rubric (see bottom of this page). There is a range for the rubric. We are looking at your formulas and answers. We understand that a small error in one cell will cause a calculation using that cell to be incorrect. This means depending on your formulas we will assign you points on where you fall within the rubric. However, you need to check your answer for reasonableness.