Question

Clifford T. Ragsdale"s Spreadsheet Modeling and Decision Analysis, 8th edition. Chapter 3 question 3 asks to refer to question 13 at the end of Chapter 2. implement a spreadsheet model for this problem and solve it using solver.

Question 13 says Bibbons Manufacturing produces softballs and baseballs for youth recreation leagues. Each softball costs $11 to produce and sells for $17 while each baseball costs $10.50 and sells for $15. The material and labor required to produce each item is listed here along with the availability of each resource.

Amount Required Per Amount

Resource Softball Baseball Available

Leather 5 oz 4 0z 6000 oz

Nylon 6 yds 3 yds 5400 yds

Core 4 oz 2 oz 4000 oz

Labor 2.5 min 2 min 3500 min

Stitching 1 min 1 min 1500 min

Implement a spreadsheet model for this problem and solve it using solver.

Answer 1

Let S: Number of softball produced
      B: Number of Baseball produced

The objective is to maximise profit.
Hence Z(max) = S(17-11) + B(15-10.50)
i.e. Z(max) = 6S + 4.5B

We have to solve to maximize Z subject to the following constraints

1) 5S + 4B 6000 (Contraint for Leather resource)
2) 6S + 3B 5400 (Contraint for Nylon resource)
3) 4S + 2B 4000 (Contraint for Core resource)
4) 2.5S +2 B 3500 (Contraint for Labor resource)
5) S + B 1500 (Contraint for Stitching resource)

We formulate the above conditions in a spreadsheet as follows

We can solve using solver by Maximizing the value of Z, subject to the contraints given above,

we get,

S = 400,
B = 1000