A world-class company located in Singapore, produces cellphones for Motorola. The products that come out of the company have to be of extremely high quality and must operate at a high level of performance to keep up with the products from other competing companies. The company makes 3 specific kinds of cellphones including the X250, Y350, and Z1000 models.
The company has its own assembly building, so all the items are made on the premises. There are four basic machines or steps that must be followed in the process. These consist of gathering, cleaning, assembly, and inspection.
The company already has orders for 500 Z1000's, 1100 Y350's, and 875 X250's that it must meet in its production plan. It was determined that the company would like to produce more of each item and has more potential demand than can be met.
The data in the table below summarizes the time taken on each of the four processes per unit and the limitations on the time available for each of the four processes.
Production Time (minutes)
X250 Y350 Z1000
Total Minutes
Gathering
Cleaning
Assembly
Inspection
27
49
30
24
40
30
26
18
28
38
29
18
100,000
100,000
75,000
60,000

The selling price (revenue) and variable costs per unit are given below

Z1000 Y350 X250

Revenue
Variable Cost
$299.00
$120.00
$199.00
$105.00
$99.00
$75.00
Management wants to know what product mix would be most profitable (i.e., contribute the most towards fixed costs).


Write out the complete algebraic LP formulation (decision variables, objective function, constraints)

Build a spreadsheet model that incorporates the entire algebraic model you just developed in question 1. Find the optimal solution using the Solver. Make sure you save the sensitivity report.
a.How much of each type of cellphone should be produced (fractions are allowed)?
b.What is the total profit contribution?
c. Which constraint(s) are binding?
Using your sensitivity report:
a. What would happen to the profit contribution if one more X500 model had to be made?
b. How much could the price drop on the Z999 model and still keep the same production plan?
c. How much more Cleaning time could be added at the same shadow price?
How much would the profit contribution go up if we could get another hour in Assembly?