Question

In: Statistics and Probability

Eli Orchid has designed a new pharmaceutical product, Orchid Relief, which improves the night sleep. Before...

Eli Orchid has designed a new pharmaceutical product, Orchid Relief, which improves the night sleep. Before initiating mass production of the product, Eli Orchid has been market-testing Orchid Relief in Orange County over the past 9 weeks.

Now that the daily demand for Orange County can be predicted with reasonable accuracy using the M3 model, the COO of the company decided to use it to optimize the production of the new drug.

The daily demand values and production process data are recorded in the Excel file provided.

The new pharmaceutical product that the company wishes to introduce, Orchid Relief, uses two new ingredients. At this stage, Eli Orchid can procure limited amounts of each ingredient. The company has 4500 pounds of ingredient 1 and 3600 pounds of ingredient 2 available for this week.

Eli Orchid can manufacture the new product using any of its three existing processes that have different capabilities. The production with each of the processes is done in batches (a batch typically represents one full run of a machine from when it starts a task until it finishes it). Each batch of production by each of the processes uses different amounts of ingredients 1 and 2, and results in different number of units of Orchid Relief produced (note the difference between a batch and units of Orchid Relief produced). The table below outlines the cost per batch, amounts of the two ingredients required, and the number of units of Orchid Relief yielded per batch.

Process 1

Process 2

Process 3

Cost of production per batch

$14,000

$30,000

$11,000

Ingredient 1 required per batch (pounds)

180

120

540

Ingredient 2 required per batch (pounds)

60

420

120

Orchid Relief yielded per batch (units)

120

300

60

Eli Orchid needs to determine how many batches to produce with each process in the least costly way given the limited availability of the two ingredients. Also, the total production of Orchid Relief in units must be greater than or equal to the total forecasted demand (in units) for the following week.

The COO of the company asked the analyst[1]:

1. To use the new M3 model updated with week 9 data (d = 0.6568*Day -151.1703*Mon -136.2715*Tue -110.595*Wed -118.3629*Thu -74.7975*Fri + 1.7679*Sat+ 434.5675) to predict the total demand (in units) for Week 10 (days 64-70).

M3

Mon.

Tue.

Wed.

Thu.

Fri.

Sat.

Sun.

TOTAL:

2. To state if this is a maximization or a minimization optimization problem?

3. To provide the mathematical formulation of the objective function assuming that X1, X2, and X3 are the decision variables representing the number of batches of each process to be used.

4. To provide the mathematical formulation of the model constraints.

Supply of ingr. 1

Supply of ingr. 2

Units produced

Non-negativity

X1, X2, X3 >= 0

Integer

X1, X2, X3 : Integer

5. To use the “Production” tab of the Excel file and complete the setup by:

  • entering the forecasted total demand in the pink cell
  • entering formulas in the five grey cells based on the mathematical formulation

Excel Formulas:

Cost of Production

Supply of Ingr. 1

Unit Cost

6. To set up Excel Solver (Assume Constraint Precision of 0.000001 and Integer Optimality (%) of 0) and provide the solution to the optimization problem.

Number of batches

Process 1

Process 2

Process 3

Cost of production (obj.)

Unit cost ($###.##)

7. To label each constraint in the solution as binding or not-binding.

Supply of ingr. 1

Supply of ingr. 2

Units produced

8. To consider a possible shortage of ingredients in the following week. What would the optimized production process look like if Eli Orchard could only procure 4320 pounds of Ingredient 1 and 1440 pounds of Ingredient 2?

Number of batches

Process 1

Process 2

Process 3

Cost of production (obj.)

Unit cost ($###.##)

9. To label each constraint in the new solution (for the shortage of ingredients) as binding or not-binding.

Supply of ingr. 1

Supply of ingr. 2

Units produced

10. To make recommendations about the production processes and pricing of Orchid Relief.

Note: this paragraph must fit on page 3. The entire project report (with the original description) must fit on 3 pages.

[write your paragraph here]

[1] Round numbers to four decimal points (e.g. 0.1234), unless explicitly requested otherwise.

Solutions

Expert Solution

The Excel model and Solver solution for the problem is attached below

The Cost of production is the objective function highlighted in green color. This is obtained through solving the model by solver Addin.

Unit cost = Total Cost pf production/Number of units produced

Total cost of production =$282,000.00

Number of units produced is given by the constraint 120x1 + 300x2 +60x3 >= 2,757.0462.

After solving the problem, the LHS of this constraint is 2760 units.

Thus, total units produced is 2760.

Unit cost = $282,000/2760 = $102.17


Related Solutions

Eli Orchid has designed a new pharmaceutical product, Orchid Relief, which improves the night sleep. Before...
Eli Orchid has designed a new pharmaceutical product, Orchid Relief, which improves the night sleep. Before initiating mass production of the product, Eli Orchid has been market-testing Orchid Relief in Orange County over the past 8 weeks. The daily demand values are recorded in the Excel file provided. Eli Orchid plans on using the sales data to predict sales for the upcoming week. An accurate forecast would be helpful in making arrangements for the company’s production processes and designing promotions....
Eli Orchid has designed a new pharmaceutical product, Orchid Relief, which improves the night sleep. Before...
Eli Orchid has designed a new pharmaceutical product, Orchid Relief, which improves the night sleep. Before initiating mass production of the product, Eli Orchid has been market-testing Orchid Relief in Orange County over the past 8 weeks. The daily demand values are recorded in the Excel file provided. Eli Orchid plans on using the sales data to predict sales for the upcoming week. An accurate forecast would be helpful in making arrangements for the company’s production processes and designing promotions....
Eli Orchid has designed a new pharmaceutical product, Orchid Relief, which improves the night sleep.
  Eli Orchid has designed a new pharmaceutical product, Orchid Relief, which improves the night sleep. Before initiating mass production of the product, Eli Orchid has been market-testing Orchid Relief in Orange County over the past 9 weeks. Now that the daily demand for Orange County can be predicted with reasonable accuracy using the M3 model, the COO of the company decided to use it to optimize the production of the new drug. The daily demand values and production process...
Eli Orchidhas designed a new pharmaceutical product, Orchid Relief, which improves the night sleep. Before initiating...
Eli Orchidhas designed a new pharmaceutical product, Orchid Relief, which improves the night sleep. Before initiating mass production of the product, Eli Orchid has been market-testing Orchid Relief in Orange County over the past 8 weeks. The daily demand values are recorded in the Excel file provided. Eli Orchid plans on using the sales data to predict sales for the upcoming week. An accurate forecast would be helpful in making arrangements for the company’s production processes and designing promotions. Before...
Orchard Relief is a product that is designed to improve sleep at night. The company, Eli...
Orchard Relief is a product that is designed to improve sleep at night. The company, Eli Orchard, is guessing that sales of the product is somewhat related to sleeping patterns of customers over the days of the week. Before mass production of the product, Eli Orchid has market-tested Orchid Relief in only Orange County over the past 8 weeks. The weekly demand is recorded. Eli Orchid is now trying to use the sales pattern over the past 8 weeks to...
Orchard Relief is a product that is designed to improve sleep at night. The company, Eli...
Orchard Relief is a product that is designed to improve sleep at night. The company, Eli Orchard, is guessing that sales of the product is somewhat related to sleeping patterns of customers over the days of the week. Before mass production of the product, Eli Orchid has market-tested Orchid Relief in only Orange County over the past 8 weeks. The weekly demand is recorded. Eli Orchid is now trying to use the sales pattern over the past 8 weeks to...
A pharmaceutical manufacturer has been researching new medications formulas to provide quicker relief of arthritis pain....
A pharmaceutical manufacturer has been researching new medications formulas to provide quicker relief of arthritis pain. Their laboratories have produced three different medications and they want to determine if the different medications produce different responses. Fifteen people who complained of arthritis pains were recruited for an experiment; five were randomly assigned to each medication. Each person was asked to take the medicine and report the length of time until some relief was felt (minutes). The results are shown below. (Assume...
REM (rapid eye movement) sleep is sleep during which most dreams occur. Each night a person has both REM and non-REM sleep.
  REM (rapid eye movement) sleep is sleep during which most dreams occur. Each night a person has both REM and non-REM sleep. However, it is thought that children have more REM sleep than adults. Assume that REM sleep time is normally distributed for both children and adults. A random sample of ?1 = 12 children (9 years old) showed that they have an average REM sleep time of ?1 = 2.5 hours per night with ?1 = 0.6 hour....
REM (rapid eye movement) sleep is sleep during which most dreams occur. Each night a person...
REM (rapid eye movement) sleep is sleep during which most dreams occur. Each night a person has both REM and non-REM sleep. However, it is thought that children have more REM sleep than adults†. Assume that REM sleep time is normally distributed for both children and adults. A random sample of n1 = 10 children (9 years old) showed that they had an average REM sleep time of x1 = 2.6 hours per night. From previous studies, it is known...
REM (rapid eye movement) sleep is sleep during which most dreams occur. Each night a person...
REM (rapid eye movement) sleep is sleep during which most dreams occur. Each night a person has both REM and non-REM sleep. However, it is thought that children have more REM sleep than adults†. Assume that REM sleep time is normally distributed for both children and adults. A random sample of n1 = 8 children (9 years old) showed that they had an average REM sleep time of x1 = 2.9 hours per night. From previous studies, it is known...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT