info
INFO 1010 Project 3
Boulder Tea Brewers KEY
FRESH BREW TEA
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 10/2/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) 23 gr Mango (MG) 15 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.
Table 2: Herbal Ingredients
|
|
Inventory Report 10/2/2018 |
|
Ingredient |
Grams in Inventory |
|
HB |
23025 |
|
CH |
33000 |
|
SP |
30454 |
|
20500 |
|
|
TF |
18500 |
|
BL |
27350 |
|
RH |
25000 |
|
PP |
22700 |
|
VB |
10000 |
|
OP |
11600 |
|
EL |
5210 |
|
CI |
50000 |
|
GI |
13500 |
|
TG |
7500 |
|
AB |
10000 |
|
MG |
4500 |
Table 3 Stock Report
Situation:
· You are the manager of the 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 100 boxes of each variety of herbal tea to meet customer demand.
· Assume the data are already cleaned.
Maximize Profit Optimization
Manager’s Objective 1a:
1. Set up a Linear Programming model to maximize profit.
2. Write out (type) the model into the memo provided.
3. Build the model in Excel.
a) Identify the Objective function, write the equation, identify the Target
b) Identify the Constraints, write the equations
c) Identify the Changing Cells (quantity)
4. Run the model, and provide a copy of your answer report.
a) Identify # boxes of each tea will be produced (does it make sense to make .73 of a box?)
b) Identify the quantity of each ingredient will be left over in inventory.
c) Identify the $ value of the inventory.
5. Run the Answer Reports
a) For each type of tea, identify the limiting factor(s) which prevent making more of that tea.
Manager’s Objective #1b:
6. Your boss calls you AFTER you finish your analysis.
a) You must use all of the Eleuthero (EL) Powder in today’s production due to expiration dates.
b) Add this constraint to your spreadsheet and analysis to your memo.
Minimize Costs Optimization
Manager’s Objective 2a:
1. The accounting department wants you to minimize costs today, as your budget it tight.
2. Re-run your model and provide a copy of your answer report.
3. Type out your model and constraints, fill in the memo.
4. Identify the quantity of boxes of each tea to produce and how much of each ingredient will be left over in inventory.
5. Identify the inventory $ value.
Manager’s Objective 2b:
6. After your analysis is finished, the Maintenance Manager calls.
7. Maintenance has to shut down production. Limit today’s production to exactly 1200 boxes total.
8. Re-run the production model with the 1200 limitation. Both minimize total costs given this, and maximize your total profit, given this new constraint.
Minimum Assignment Deliverables to earn an average grade, 75%, or a C.
1. Create spreadsheets and use solver to answer these questions.
2. Create a tab for each Objective
3. Produce an Answer Report within each tab for each.
4. Clearly answer each question that is part of the project.
5. Fill in the memo answers
6. No grammatical mistakes allowed.
7. Hand in both products:
1) The spreadsheets in Excel.
2) The Memo in Word. Upload both products into your drop box.
3