Problem 2 involves a different scenario, where the caf owner has decided to offer catering packages to

Problem 2 involves a different scenario, where the café owner has decided to offer catering packages to local groups. Three catering package types, breakfast, lunch, and coffee break, each consisting of both food and drink options, need to be developed, and their contents will depend on the size of the group. The question is, given the package type and group size, what array of food and drink items will be best for profit? A list of possible menu items (expanded upon from problem 1), along with their costs and retail prices, is found on the Problem 2 worksheet.

Expanded Menu	Cost	Retail Price
Cappuccino (6oz)	1.24	3.75
Café latte (10oz)	2.02	3.75
Espresso (1oz)	0.3	2.5
Americano (8oz)	1.28	4.75
Egg and cheese English muffin	2.25	5.71
Cookie	0.78	3
Cinnamon roll	1	3.75
Quiche	3.42	5.75

Table 1, outlines required total items (# of Items) for group sizes from 10 to 50, in intervals of 10. (For now, anyway, we will require group size to be a multiple of 10.) The second column multiplies the number of people by 2, creating the starting assumption that we will provide two items (one drink and one food item) per person. Lastly, the third column, # of Items, adds 5, 7, 9, 11, or 13 (depending on the group size) to the People * 2 value. This (and not merely People * 2) is what we will treat as our conservative estimate of items required, speaking to the fact that some in the group might want seconds, and that running out of items makes both the caterer and the event organizer look bad. So, for example, if the café will be catering for 10 people, then the bundle should include exactly 25 items (food and drinks combined).

# of People	People * 2	# of Items
10	20	25
20	40	47
30	60	69
40	80	91
50	100	113

Table 1: Number of Items Required

Now, drilling down into the question of what kind of drinks and what kind of food to provide, we need to consider the type of catering. It is more important to provide various coffee drinks at a coffee break than at a lunch event. The table below lists, for each menu item, per-person minimums. For example, at a breakfast event, for every 1 person, we will provide at least .2 cappuccinos, .2 café lattes, and so on. So if the café caters to a group of 10 for breakfast, the catering package must contain at least 2 cappuccinos, 2 café lattes, 2 espressos, and 2 Americanos (10 * .2 for each of those items).

Per-person minimums
	Breakfast	Lunch	Coffee break
Cappuccino (6oz)	0.2	0.1	0.25
Café latte (10oz)	0.2	0.1	0.25
Espresso (1oz)	0.2	0.1	0.25
Americano (8oz)	0.2	0.1	0.25
Egg and cheese English muffin	0.2	0.1	0
Cookie	0	0.15	0.5
Cinnamon roll	0.2	0.1	0.5
Quiche	0.2	0.6	0

Table 2: Minimums per Item

Constraints

The total items provided, both food and drinks, must equal the conservative estimates (# of Items) found in the first table for the relevant group size.

Separately, the number of food items must be greater than or equal to the number of people expected. The same goes for drinks. This ensures that each person would receive both one drink and one food item, at the very least.

Using the solver add-in in excel, use the required constraints to generate the profit maximizing value. Basically, given the package type and group size, what array of food and drink items will be best for profit?

Answer rating: 100% (QA)

Lets work through an example for the breakfast package and a group size of 30 Well use the Solver addin in Excel to find the profitmaximizing array of food and drink items 1 Create a new Excel workshe... View the full answer