Question

Boulder Tea Brewers makes a variety of herbal teas (herbal infusions with a variety of ingredients). The company maintains a supply of various ingredients, some of which are costlier than others. Each day, Boulder Tea Brewers mixes the appropriate ingredients for the varieties of tea it plans to make and packages the teas in the Boulder, Colorado facility.

Some of their best-selling herbal teas are as shown in Table 1, along with the chief ingredients for each tea and the amount of each ingredient.

Boulder Tea Brewers Product List 2/1/2018
Herbal Tea Name	Ingredients (in grams) per box of 25 tea bags
Never Awake	25 gr Chamomile (CH) 23 gr Spearmint (SP) 19 gr Lemon Grass (LG) 15 gr Tilia Flower (TF) 14 gr Blackberry Leaves (BL)
Peppermint Patty	30 gr Peppermint (PP) 14 gr Blackberry Leaves (BL) 16 gr Lemon Grass (LG)
Head Rush	23 gr Hibiscus (HB) 24 gr Rose Hips (RH) 28 gr Peppermint (PP) 24 gr Orange Peel (OP)
Hibernation	26 gr Eleuthero (EL) 25 gr Peppermint (PP) 16 gr Ginger (GI) 13 gr Chamomile (CH) 10 gr Lemon Grass (LG)
State of Bliss	16 gr Hibiscus (HB) 15 gr Rose Hips (RH) 21 gr Blackberry Leaves (BL) 16 gr Orange Peel (OP) 13 gr Tangerine (TG)
Terrific Amazing Morning	20 gr Hibiscus (HB) 20 gr Rose Hips (RH) 17 gr Orange Peel (OP) 15 gr Acai Berry (AB) 13 gr Mango (MG) 13 gr Blackberry Leaves (BL)

Table 1: Herbal Teas

Each herbal tea is packaged as a box of 25 tea bags for a retail cost of $5.50 per box. While price is the same no matter the variety, the cost of ingredients varies for each herbal tea based on the type and amounts of ingredients in each.

Ingredient Cost Report
Ingredient Name	Cost per gram
Hibiscus (HB)	$       0.05
Chamomile (CH)	$       0.05
Spearmint (SP)	$       0.06
Lemon Grass (LG)	$       0.06
Tilia Flower (TF)	$       0.06
Blackberry Leaves (BL)	$       0.04
Rose Hips (RH)	$       0.04
Peppermint (PP)	$       0.03
Vanilla Bean (VB)	$       0.06
Orange Peel (OP)	$       0.04
Eleuthero (EL)	$       0.11
Cinnamon (CI)	$       0.03
Ginger (GI)	$       0.04
Tangerine (TG)	$       0.04
Acai Berry (AB)	$       0.06
Mango (MG)	$       0.06

Table 2: Herbal Ingredients

Inventory Report 2/1/2018
Ingredient	Grams in Inventory
HB	23025
CH	33000
SP	30454
LG	30500
TF	18500
BL	27350
RH	25000
PP	22700
VB	10000
OP	13580
EL	5210
CI	50000
GI	13500
TG	7500
AB	10000
MG	4500

Table 3 Stock Report

Situation:

You are the manager of a tea manufacturing facility for the signature teas.

You are paid based on profitability and efficiency at your facility.

You may assume any additional information you need to run your facility.

Constraints:

Using, dumping, or warehousing expired ingredients is against the law.

You use ingredients by a suggested use date (prior to expiration, just to be safe).

You must produce at least 200 boxes of each variety of herbal tea to meet customer demand.

Assume the data are already cleaned.

Maximize Profit Optimization

Manager’s Objective 1a:

Set up a Linear Programming model to maximize profit.

Write out (type) the model into the memo provided.

Build the model in Excel.

Identify the Objective function, write the equation, identify the Target

Identify the Constraints, write the equations

Identify the Changing Cells (quantity)

Run the model, and provide a copy of your answer report.

Identify # boxes of each tea will be produced (does it make sense to make .73 of a box?)

Identify the quantity of each ingredient will be left over in inventory.

Identify the $ value of the inventory.

Run the Answer Reports

For each type of tea, identify the limiting factor(s) which prevent making more of that tea.

Manager’s Objective #1b:

Your boss calls you AFTER you finish your analysis.

You must use all of the Eleuthero (EL) Powder in today’s production due to expiration dates.

Add this constraint to your spreadsheet and analysis to your memo.