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

(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
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.
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?