Part - A

a. How can a manager prepare for changing conditions using sensitivity analysis?

b. What is meant by sensitivity analysis?

c. What is meant by an allowable range for a coefficient in the objective function?

d. How would you use the values in the allowable increase and allowable decrease columns in the sensitivity report provided by Solver?

Part - B

Q1: New research findings indicate that short sightedness among the aged can be cured if their diet is supplemented with three minerals; which are rare to obtain from natural sources of food and, also difficult to absorb in the human body. A company desires to produce a new vitamin tablet for treating short sightedness among aged people and discovers the ideal composition of the tablet should consist of exactly 40% Magnesium, 35% Beta-Carotene and 25% Zinc. Various sources for obtaining these minerals and their costs are given in the table below.

The objective is to determine the proportions of these food sources that should be blended to produce the new vitamin tablet at a minimum cost.

e. Formulate and solve a LP model for the problem on a spreadsheet.

f. Identify and list the functional constraints.

g. What is the optimum solution?

h. Produce a sensitivity report and interpret the allowable increase and allowable decrease columns for your manager in a few sentences.

Q2: A new fitness club offers two types of exercise equipment for their members. The machines are the latest in the industry and produce reports of the calories burned in detail. The calories burned by using each machine are given below:

The gym charges guests by the number of calories burned using each machine and it costs $0.60 for using the Treadmill and $ 0.50 for using the Elliptical Cross Trainer. Each guest must burn at least 60, 30 and 126 calories using the three intensity settings on both machines to complete their workout regimen.

a. Implement your model in a spreadsheet and solve it.

b. Produce a sensitivity analysis report and interpret clearly how the resulting values under the Variable Cells and Constraints tables can be used.

Q3: A company produces fork lifts in three plants and needs to ship them to their five distribution centers located across Indiana. Specialized fork lift carriers are required to transport the fork lifts. The freight cost charged by the carrier is 50 cents per mile plus $100 for toll charges per trip/destination since all carriers use the Tri-State Toll Way. Plants 1, 2 and 3 manufacture 12, 17 and 11 fork lifts per month respectively. Each distribution center must receive 10 fork lifts per month.

a) Determine how many fork lifts need to be shipped from each plant to minimize costs by implementing the model in a spreadsheet.

b) Produce a suitable report to identify all binding constraints, list and interpret them for your manager in a few sentences.

Q4: A company installs stereo systems for three vehicles. The installation time varies depending on the type of vehicle. Since business volumes are low, the business is managed with two workers. The installation times for the three vehicles are given below:

Each worker is allowed to work a maximum of 40 hours per week and the profit per generated per vehicle is as follows:

a. Implement your model in a spreadsheet and solve it.

b. Perform a sensitivity analysis and compute the maximum and minimum hours Tom and John can work?

c. What does the shadow price calculated for Tom mean to you?