Question 1: The TEA Company produces 5 different flavors of tea: apple, cherry, lemon, orange, and pineapple. TEA statisticians report to management with monthly projections of the minimum and maximum sales for each type of juice (based on previous retail orders). In the past these projections have been accurate, even though demand is very seasonal. Next months projections are given below (in 1000s of gallons).

Each month Management is faced with the decision of how much of each product is to be produces in the next month. Since raw material are readily available they do not constrain production, thus no finished product inventory is carried. What limits production are constraints on the amount of machine time for the three processes necessary to produce any of the 5 flavors. Total Blending time is limited to 2,500 hours per month. Straining time is limited to 2,000 hours. Bottling time limited to 3,000 hour a month and inspection to 300 hours. The time required for production of 1,000 gallons of each of the 5 teas is given below:

Demand

Maximum

Apple

50

Cherry

35

Lemon

50

Orange

Pineapple

50

20

Minimum

10

5

20

30

10

Apple

23

21

Cherry

22

40

10

Lemon

18

20

10

Orange

32

Pineapple

Blend

19

19

Strain

21

Bottle

10

10

10

Inspection

0

1

2.5

3

2

As a result of price and cost differences of the Tea each produces different revenue and contribution (per 1,000 gallons of production). This information is summarized below:

Set up this problem in Excel and using SOLVER, solve for optimal product mix Submit the excel sheet with Base Sheet, Answer Report and Sensitivity Report in D2L in Assignment Folder Test 1 Excel no later than 3.30 pm of Thursday October 1.

	Apple	Cherry	Lemon	Orange	Pineapple
Revenue	1,350	920	1,720	2,240	1,500
Variable Cost	500	600	600	800	700
Contribution	850	320	1,120	1,440	800