Case Problem THE GRAPHIC PALETTE The Graphic Palette is a firm in Charleston, South Carolina, that does graphic artwork and produces color and black-...

Case Problem

THE GRAPHIC PALETTE

The Graphic Palette is a firm in Charleston, South Carolina, that does graphic artwork and produces color and black- and-white posters, lithographs, and banners. The firm's owners, Kathleen and Lindsey Taylor, have been approached by a client to produce a spectacularly colored poster for an upcoming arts festival. The poster is more complex than anything Kathleen and Lindsey have previously worked on. It requires color screening in three stages, and the processing must proceed rapidly to produce the desired color effect.

By suspending all their other jobs, they can devote three machines to the first stage, four to the second stage, and two to the last stage of the process. Posters that come off the machines at each stage can be processed on any of the machines at the next stage. However, all the machines are different models and of varying ages, so they cannot process the same number of posters in the specified time frame necessary to complete the job. The different machine capacities at each stage are as follows:

Stage 1Stage 2Stage 3

Machine 1 = 750Machine 4 = 530Machine 8 = 620

Machine 2 = 900Machine 5 = 320Machine 9 = 750

Machine 3 = 670Machine 6 = 450 

Machine 7 = 250 

Because the machines are of different ages and types, the costs of producing posters on them differ. For example, a poster that starts on machine 1 and then proceeds to machine 4 costs $18. If this poster at machine 4 is then processed on machine 8, it costs an additional $36. The processing costs for each combination of machines for stages 1, 2, and 3 are as follows:

Machine (cost)

Machine4567

1$18$23$25$21

220262419

324242223

Machine (cost)

Machine89

4$36$41

54052

64246

73349

Kathleen and Lindsey are unsure how to route the posters from one stage to the next to make as many posters as they possibly can at the lowest cost. Determine how to route the posters through the various stages for Graphic Palette to minimize costs.

I would like an excel solver problem! Thank you!