A sports manufacturer produces two products: footballs and baseballs. These products can be produced either during the morning shift or the evening shift. The cost of manufacturing the football and the baseball in the morning shift is $20 each, and the cost of manufacturing the football and the baseball in the evening shift is $25 each. The amounts of labor, leather, inner plastic lining, and demand requirements are given as follows:
Resource Football Baseball
Labor (hours/unit) 0.75 2
Leather (pounds/unit) 7 15
Inner plastic lining (pounds/unit) 0.5 2
Total demand (units) 1500 1200
Based on the information about the company, we know that the maximum labor hours available in the morning shift and evening shift are 5,000 hours and 2,000 hours, respectively, per month. The maximum amount of leather available for the morning shift is 15,000 pounds per month and 14,000 pounds per month for the evening shift. The maximum amount of inner plastic lining available for the morning shift is 2,000 pounds per month and 1,500 pounds per month for the evening shift.
Solve the given problem scenario so as to minimize the production cost and determine the number of footballs and baseballs made by this company by formulating and setting up the problem in Excel Solver.
Directions:
Provide the written formulation in a word document. (Provide a complete description of the decision variables used along with their units and also label the constraints mentioned in the problem as completely as possible.)
The Excel set-up should provide clearly labeled values used for the decision variables, constraints, and objective function.