Problem 1: (80 points) J.B. Machine Co. has 4 warehouses in Detroit, Houston, Los Angeles and
New York. J.B. Machine Co. is considering opening one or more plants which will be serving the
warehouses. Three locations which are candidates to open plants are Cincinnati, Kansas city, and
Seattle.
Plant potential capacities, market demand, transportation cost per unit shipped, and fixed costs
per month at each plant are shown in Table 2.
Transportation Unit Cost to Demand City Potential Fixed
Supply City Detroit Houston NY LA Supply Cost
Cincinnati $25 $55 $40 $60 5800 $400,000
Kansas City $35 $30 $50 $40 7300 $550,000
Seattle $60 $38 $65 $27 5000 $350,000
Demand 1400 1500 3600 3500
Table 2
Formulate and solve a spreadsheet model which finds the location (of factories to open) and the
demand allocation (how much to ship from each plant to each warehouse) for J.B. Machine Co.,
which minimizes the total cost of fixed facilities and transportation.
Michael Wise operates a newsstand at a busy intersection downtown.
Demand for the Sunday Times averages 300 copies with a standard deviation of 50 copies (assume
a normal distribution). Michael purchases the paper for $0.75 and sells them for $1.25. Any left
paper at the end of the day is bought back at a price which is uniformly distributed between $0.10
and $0.20.
a) Suppose that Michael buys 350 copies for his newsstand each Sunday morning. Use Crystal
Ball to perform 1000 trials of a computer simulation on a spreadsheet. What will be
Michael’s mean profit from selling the Sunday Times? What is the probability that Michael
will make at least $0 profit? (80 points)
b) Generate a Decision table to consider five possible order quantities between 250 and 350.
Which order quantity maximizes Michael’s mean profit? (40 points)
Sample Solution