Scribd
Upload
116 views

Sensitivity Analysis - Three Problems: Important Instructions For The Following 3 Problems

This document provides instructions for three sensitivity analysis problems. It explains that all models, answers, and reports should be combined in one Excel file labeled "Sensitivity Analysis" with separate tabs for each question. It also states that answers to the sensitivity questions should be provided in a Word document labeled "Sensitivity Analysis Answers" with brief explanations rather than just numbers. The document then provides details of the three questions, which involve optimizing costs for purchasing chemical bags, blending paint colors, and solving a linear programming problem graphically on graph paper.

Uploaded by

Fareedabbasi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
116 views

Sensitivity Analysis - Three Problems: Important Instructions For The Following 3 Problems

This document provides instructions for three sensitivity analysis problems. It explains that all models, answers, and reports should be combined in one Excel file labeled "Sensitivity Analysis" with separate tabs for each question. It also states that answers to the sensitivity questions should be provided in a Word document labeled "Sensitivity Analysis Answers" with brief explanations rather than just numbers. The document then provides details of the three questions, which involve optimizing costs for purchasing chemical bags, blending paint colors, and solving a linear programming problem graphically on graph paper.

Uploaded by

Fareedabbasi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

Sensitivity Analysis – Three Problems

Important Instructions for the Following 3 Problems


• All Excel models, Answer Reports, and Sensitivity Reports are to be combined in one
Excel file. Save the Excel file as “Sensitivity Analysis.” Tabs should be labelled as per
the question number (Question 1, Question 2).

• Give the answers to the sensitivity questions in one combined Word Document. Save
the file as “Sensitivity Analysis Answers.” Do not just give a numerical answer. Please
explain the answers in a couple of brief and clear sentences with supporting data.

• Question 3 requires hand-drawn graphs to derive the answer, not computer generated
(50 x 50 graph paper attached).

• I will provide you with samples, so you know my Excel style.

Question 1
Every operating day an oil refinery requires at least 2100 kilograms (kg) of chemical A, 2780 kg
of chemical B, and 1500 kg of chemical C. They externally purchase two types of bags which
contain these chemicals. Each type 1 bag contains 17.5 kg of chemical A, 10 kg of B, and 2.5 kg
of C. Each type 2 bag contains 8.75 kg of chemical A, 14 kg of B, and 15 kg of C. A type 1 bag
costs $140, while a type 2 bag costs $200. Storage limitations mean that no more than 290 bags
(of both types combined) can be purchased each day. They wish to minimize the daily cost of
buying these bags. (Because of the repetitive nature of the operations, the number of bags need
not be integer.)

We define X1 and X2 to be respectively the number of type 1 and 2 bags purchased each day.

A. Use Excel Solver to create all necessary models, Answer Reports, and Sensitivity Reports.
B. What happens to the OFV in the following scenarios? (each is independent of the others)
(i) The storage capacity falls by 30 units.
(ii) The price per bag of Type 1 falls by $90.
(iii) The price per bag of Type 2 falls by $10.
(iv) The price per bag of Type 1 falls by $40 while the price per bag of Type2 increases
by $380.
(v) The company could decrease what they have to buy of Chemical B by making up to
500 units of Chemical B in its own operations at a cost of $5/kg.
(vi) The company could decrease what they have to buy of Chemical C by making up to
300 units of Chemical C in its own operations at a cost of $0.40/kg.
* For part B, all answers for (i) to (vi) should be clearly and briefly explained in written
sentence form in a Word document (Don’t just give a numerical answer). *

Question 2
3) A paint blending operation buys paint in five colours. These are white, black, and the three
primary colours of red, blue, and yellow. There are five vats in which to receive these colours
from the supplier; the white vat and the black vat each has a capacity of 5000 litres. Each
primary colour vat has a capacity of 25,000 litres. All paint regardless of colour is purchased at a
price of $9 per litre. Each product is made as a specified percentage of the five basic paints. Here
is the list of their products:

There is a dedicated 15,000 litre capacity vat in which to store each of these six products. All
these products are sold at a price of $12 per litre. Assume that no paint is lost in the blending
process.

A. Give the algebraic model for this situation. (Hint: only 11 variables are needed.)

B. Solve this model in Excel Solver, and obtain the Answer Report and Sensitivity
Report.
C. The plant management could seek some funds which could be used to expand the
capacity of one or more of the 11 vats. Spreading out such costs over time would work
out to a price of about $2.00 per litre to expand any of the 11 vats. Based on the
information which could be gleaned from the sensitivity report(s) alone, and
considering several possibilities, what would you recommend to management? Explain
this answer clearly in a couple of sentences; do not just give a numerical answer.

* For part C, explain the answer clearly and briefly in written sentence form (not just
numerically) in the same Word document as previous questions. *

Question 3
Question 3 is to be drawn by hand, showing all the steps needed to derive the answers. (You
can use Excel to verify your answers if you wish, but you do not need to provide me with the
Excel file). Please write/explain the solutions in the WORD document.

A. Solve the following model graphically (drawn by hand) using a 50 by 50 grid.

B. Perform a sensitivity analysis for each of the objective function coefficients.

C. Perform a sensitivity analysis for the right-hand-side values for each of the nonbinding
constraints.

D. Perform a sensitivity analysis for the right-hand-side values for each of the two binding
constraints.

E. For each of the two binding constraints, find the change to each variable as function of
the right-hand side value, and from these relationships calculate the two shadow prices.

You might also like