## (solution) available for each resource: Resource Table Chair Bookcase

available for each resource:

 Resource Table Chair Bookcase Available Labor 2 1 3 60 hours Wood 15 5 10 400 board-feet Machine Time 1 2 3 35 machine hours

Part 1 ? Formulate a Linear Programming Model for this problem using the Bowls and Mugs model as a guide.  Include all three of the components that make up a good LP model, i.e. decision variables, objective function, and constraints.

Part 2 ? Input the model you developed into the spreadsheet template posted to Scholar (or make one of your own).  Identify the decision variables cells using the color YELLOW and identify the objective function cell using the color GREEN.  Once your model is set up correctly in Excel, run Solver to obtain your best solution and include both an Answer Report and Sensitivity Report.  Your spreadsheet should have ONLY ONE ANSWER REPORT and ONLY ONE SENSITIVITY REPORT.

Part 3 ? Using ONLY the Answer Report and Sensitivity Report you developed in your spreadsheet, answer the following questions in the Quiz assignment posted to Scholar.  Note:  Do NOT re-solve your model.  All answers are to be derived from your ORIGINAL Answer Report and Sensitivity Report.

Quiz Questions ? All questions are to be answered via the Quiz 2 assignment posted to the course website on Canvas.

The questions are repeated here for your convenience.  Do NOT turn in this paper copy.

What is the optimal solution?

Which constraints are binding?

Which constraints are non-binding?  How much is left of each non-binding resource?

What is the profit range for a Table such that the original optimal solution you found does not change?

Between \$__________ and \$__________

What is the profit range for a Chair such that the original optimal solution you found does not change (use 2 decimal places)?

Between \$__________ and \$__________

Currently we are not making any Bookcases.  Profit per Bookcase would need to increase to what value before we would begin to make Bookcases?

Greater than \$__________

How much additional profit do we make if the profit per Table increases by \$20?

How much profit do we lose if the profit per Bookcase drops by \$10?

The profit per Chair has decreased by \$10.  How much profit do we lose?

How much is one additional board-foot of Wood worth?

Quiz 2 ? LP Model Formulation, Solution, and Sensitivity Analysis

BIT 2406 ? QUANTITATIVE METHODS II

Fall 2016

Complete this problem in Excel with Solver and then answer the Quiz 2 questions (located under the

Assignments tab) posted to the course website on Canvas (will be available late Wednesday, 21 September

2016).

Product Mix Problem. The objective of this assignment is for you to develop an appropriate LP Model using the

information below and for you to use Solver in Excel to create an Answer Report and Sensitivity Report. From your

Answer Report and Sensitivity Report, please answer the 10 questions provided below in Part 3 of the assignment.

You have a new product mix problem with the following information.

Timberway Furniture Company makes three different products:

Tables Chairs Bookcases These products are made from three resources:

Labor Wood Machine Time The profit per Table sold is \$40, the profit per Chair sold is \$30, and the profit per Bookcase sold is \$45.

Per unit requirements for each of the resources is given in the following table, along with the total amount that is

available for each resource:

Resource

Labor

Wood

Machine Time Table

2

15

1 Chair

1

5

2 Bookcase

3

10

3 Available

60 hours

400 board-feet

35 machine hours Part 1 ? Formulate a Linear Programming Model for this problem using the Bowls and Mugs model as a guide.

Include all three of the components that make up a good LP model, i.e. decision variables, objective function, and

constraints.

Part 2 ? Input the model you developed into the spreadsheet template posted to Scholar (or make one of your own).

Identify the decision variables cells using the color YELLOW and identify the objective function cell using the color

GREEN. Once your model is set up correctly in Excel, run Solver to obtain your best solution and include both an

ONE SENSITIVITY REPORT.

following questions in the Quiz assignment posted to Scholar. Note: Do NOT re-solve your model. All answers are

to be derived from your ORIGINAL Answer Report and Sensitivity Report. Quiz Questions ? All questions are to be answered via the Quiz 2 assignment posted to the course website on

Canvas.

The questions are repeated here for your convenience. Do NOT turn in this paper copy.

1. What is the optimal solution? 2. Which constraints are binding? 3. Which constraints are non-binding? How much is left of each non-binding resource? 4. What is the profit range for a Table such that the original optimal solution you found does not change?

Between \$__________ and \$__________ 5. What is the profit range for a Chair such that the original optimal solution you found does not change (use

2 decimal places)?

Between \$__________ and \$__________ 6. Currently we are not making any Bookcases. Profit per Bookcase would need to increase to what value

before we would begin to make Bookcases?

Greater than \$__________ 7. How much additional profit do we make if the profit per Table increases by \$20? 8. How much profit do we lose if the profit per Bookcase drops by \$10? 9. The profit per Chair has decreased by \$10. How much profit do we lose? 10. How much is one additional board-foot of Wood worth?

Solution details:

STATUS

QUALITY

Approved

Sep 13, 2020

EXPERT

Tutor