Your team has been hired to manage a product line for XYZ Company. The company would like you to maximize the profit generated by the product line, but they have noted to your team that there are several constraints that must be managed. You have been provided with the following production data about the five products that you need to manage. Each product requires set quantities of two different types of raw materials, and a specific time for two different types of labor to produce the product. You have also been provided with data regarding the selling price, variable cost (labor, materials, etc.) as well as the fixed cost for each product.

 XYZ Company has a limited amount of labor and raw materials available to them, as noted below:

 In addition to the labor and raw material constraints noted above, XYZ Company has been informed by the government that they must produce a minimum number of each product (as these are considered essential products). Finally, XYZ Company has completed demand forecasts for each product, and they do not want to produce more than the maximum demand or they will be left with inventory at the end of the year. The minimum and maximum amounts are noted below:

 

Task: Using Excel, build an appropriate model and use Solver to optimize a solution that maximizes the profits earned from the product line based on the constraints that have been placed on labor, raw materials as well as minimum and maximum quantities.

Transcribed Image Text:

Product 1 Machine Operator (hours) 6.0 Assembler (hours) 9.1 Raw Material 1 (kg) 3.3 Raw Material 2 (kg) 3.0 Unit Selling Price Unit Variable Cost Fixed Cost Product 2 Product 3 Product 4 Product 5 5.6 4.1 3.5 2.4 6.5 5.6 5.0 3.1 2.9 1.6 1.2 0.8 2.1 1.5 0.8 1.6 $59.99 $50.00 $200.00 $169.99 $133.99 $125.00 $100.00 $500.00 $400.00 $99.99 $79.99 $85.00 $60.00 $450.00 $250.00 Product 1 Machine Operator (hours) 6.0 Assembler (hours) 9.1 Raw Material 1 (kg) 3.3 Raw Material 2 (kg) 3.0 Unit Selling Price Unit Variable Cost Fixed Cost Product 2 Product 3 Product 4 Product 5 5.6 4.1 3.5 2.4 6.5 5.6 5.0 3.1 2.9 1.6 1.2 0.8 2.1 1.5 0.8 1.6 $59.99 $50.00 $200.00 $169.99 $133.99 $125.00 $100.00 $500.00 $400.00 $99.99 $79.99 $85.00 $60.00 $450.00 $250.00

Answer rating: 100% (QA)

Step 1 Set Up Your Excel Spreadsheet Create an Excel spreadsheet and structure it as follows Product Selling Price Variable Cost Fixed Cost Labor Type ... View the full answer