Product	Manufacturing cost	Labor hours	Selling price	Currently on order
Jim McMahon bobblehead (individually packaged)	$ 4.20	2	$ 13.00	200
Willie Gault water bottle (individually packaged)	$ 2.20	1	$ 11.00	1500
Walter Payton beach towel (individually packaged)	$ 3.70	1	$ 13.00	2500
Dan Hampton giant magic markers (individually packaged)	$ 1.20	0.25	$ 2.00	9000
Mike Singletary mouthguard (individually packaged)	$ 3.80	0.5	$ 13.00	1000
Steve McMichael compact tissue packs (individually packaged)	$ 0.90	0.25	$ 2.00	7000
Sun Fun Package			$ 20.00	100
Sport Fun Package			$ 20.00	250
Superfan Package			$ 66.00	100

The solution is intended to be run by Excel Solver, however how can it be set up the individual and package production limits are considered?
 

Transcribed Image Text:

A company that produces sports memorabilia has multiple production lines. In this problem we are concerned with the production of Jim McMahon bobbleheads, Willie Gault water bottles, Walter Payton beach towels, Dan Hampton giant magic markers, Mike Singletary mouthguards, and Steve McMichael compact tissue packs. All of these items are sold separately. In addition, some are sold as part of a package. The Sun Fun Package includes one Willie Gault water bottle and one Walter Payton beach towel. The Sport Fun Package includes one Willie Gault water bottle and Mike Singletary mouthguard. The Superfan Package includes one bobblehead, one water bottle, one beach towel, three magic markers, one mouthguard, and ten tissue packs. Manufacturing costs, labor hours required, and selling prices are given in the file attached below. Note that manufacturing costs and labor hours are not given for the three packages; these can be found by referring to the per-item cost and labor hours of the products in the package. The company can manufacture at most 1000 bobbleheads, 2500 water bottles, 5000 beach towels, 12,000 magic markers, 3000 mouthguards, and 12,000 compact tissue packs in each production run. (This means that, for example, if the plan is to produce 750 individually packaged bobbleheads, only 250 Superfan packages can be produced, because each Superfan package contains one bobblehead.) In addition, only 15,000 labor hours are available for the entire production plan. We currently have orders that must be met. Totals for these orders are given in the file attached below. Judging from past data, we also know that we cannot sell more than 800 individually-packaged Jim McMahon bobbleheads and 4000 individually-packaged Walter Payton beach towels. Demand for the other five products is assumed to be unlimited (note, however, the production limits mentioned above). What plan will allow the company to maximize its profit? A company that produces sports memorabilia has multiple production lines. In this problem we are concerned with the production of Jim McMahon bobbleheads, Willie Gault water bottles, Walter Payton beach towels, Dan Hampton giant magic markers, Mike Singletary mouthguards, and Steve McMichael compact tissue packs. All of these items are sold separately. In addition, some are sold as part of a package. The Sun Fun Package includes one Willie Gault water bottle and one Walter Payton beach towel. The Sport Fun Package includes one Willie Gault water bottle and Mike Singletary mouthguard. The Superfan Package includes one bobblehead, one water bottle, one beach towel, three magic markers, one mouthguard, and ten tissue packs. Manufacturing costs, labor hours required, and selling prices are given in the file attached below. Note that manufacturing costs and labor hours are not given for the three packages; these can be found by referring to the per-item cost and labor hours of the products in the package. The company can manufacture at most 1000 bobbleheads, 2500 water bottles, 5000 beach towels, 12,000 magic markers, 3000 mouthguards, and 12,000 compact tissue packs in each production run. (This means that, for example, if the plan is to produce 750 individually packaged bobbleheads, only 250 Superfan packages can be produced, because each Superfan package contains one bobblehead.) In addition, only 15,000 labor hours are available for the entire production plan. We currently have orders that must be met. Totals for these orders are given in the file attached below. Judging from past data, we also know that we cannot sell more than 800 individually-packaged Jim McMahon bobbleheads and 4000 individually-packaged Walter Payton beach towels. Demand for the other five products is assumed to be unlimited (note, however, the production limits mentioned above). What plan will allow the company to maximize its profit?

Answer rating: 100% (QA)

The following strategy and maximum profit are based on the aforementioned ideal solution Product Num... View the full answer