Boulder Tea Brewers Daily Production Report

Only add each part if solver runs. If solver does not run, do not add the inventory report, rather give reasons why the solver did not run and what could be done at the inventory level to resolve the issue.

Model 1a:

Example

Sandals = S, Boots = B

Max: 7S + 15B

Subject to:

3S + 6B < 120

7S + 8B < 72

2B < 10

S, B < 0

Maximum Profit 1a:

Inventory Report:

Example:

Recommendation for 1a:

Model 1b (only put in changes from 1a):

Example

Sandals = S, Boots = B

Subject to:

2B < 10

S, B < 0

Maximum Profit 1b:

Inventory Report:

Example:

Recommendation for 1b:

Model 2a:

Example

Sandals = S, Boots = B

Min: 7S + 15B

Subject to:

3S + 6B ≥ 120

7S + 8B ≥ 72

2B ≥ 10

S, B ≥ 0

Minimized Cost 2a:

Inventory Report:

Example:

Recommendations 2a:

Model 2b (only put in changes from 2a):

Example

Sandals = S, Boots = B

Subject to:

3S + 6B ≥ 120

7S + 8B ≥ 72

Minimized Cost 2b:

Inventory Report:

Example:

Recommendations 2b:

Overall Recommendations:

INFO 1010 Project 3

Boulder Tea Brewers

FRESH BREW TEA

Boulder Tea Brewersmakes 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 itplans to make and packages the teas in the Boulder, Coloradofacility.

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.

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

Table 3 Stock Report

Situation:

• You are the manager ofthe tea manufacturing facility for the signature teas.
• You are paid based on profitability and efficiency as indicated by profitability 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.

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.
   1. Identify the Objective function, write the equation, identify the Target
   2. Identify the Constraints, write the equations
   3. Identify the Changing Cells (quantity)
4. Run the model, and provide a copy of your answer report.
   1. Identify # boxes of each tea will be produced (does it make sense to make .73 of a box?)
   2. Identify the quantity of each ingredient will be left over in inventory.
   3. Identify the $ value of the inventory.
5. Run the Answer Reports
   1. For each type of tea, identify the limiting factor(s) which preventmaking more of that tea.

Manager’s Objective #1b: 

6. Your boss calls you AFTER you finish your analysis.
   1. You must use as much of the Mango Powder as possible(no more than 50 grams left) in today’s production due to expiration dates.
   2. Add this constraint to your spreadsheet (keeping all from 1a) and analysis to your memo.

Manager’s Objective 2a:

1. Using the Objective 1a scenario, you can produce no more 200 boxes each of all teas except Peppermint Patty and and State of Bliss, which you can produce no more of 400 boxes each.
2. Re-run your model using the model from Objective 1a 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 1000 boxes
8. Re-run the production model using the Objective 1a scenario with the 1000 limitation, AND NOT a minimum of 100 boxes per tea, Run 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 spreadsheetsand 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.