Consider 4 types of spa tub: Aqua-Spa (or FirstSpa, or P1), Hydro-Lux (or SecondSpa, or P2), ThirdSpa (or P3) and FourthSpa (or P4), with the...

300 390 500

We wish to produce the number to maximise total profit.

The questions follow below:

a) Formulate a Linear Programming (an LP) formulation for this problem. Save your formulation in the text-based .pdf file [FamilyName-StudentId-2ndSem2018FIT5097.pdf]. (6 marks)

b) Create a spreadsheet model for this problem. Store the model in your Excel workbook [FamilyName-StudentId-2ndSem2018FIT5097.xlsx] and name your spreadsheet something like (e.g.) 'ManySpas' (6 marks)

c) Solve the problem - using Microsoft Excel Solver. Generate the Sensitivity report for the problem and name your spreadsheet (e.g.) 'Qu 1 ManySpas Sensitivity Rep'.

(2 marks)

Using the Microsoft Excel Solver sensitivity report, provide answers (in the .pdf file) to the following questions: (You must include explanations with your answers.)

d) What is the optimal production plan and the associated profit? Refer to your answers to any of a), b) and/or c) above as appropriate. (2 marks)

For the remaining parts of this question, explain your answer(s), possibly referring to relevant spreadsheet entry/ies and/or specific relevant parts of spreadsheet reports.

e) Is the solution degenerate - and why or why not? (1 mark)

f) Is the solution unique - and why or why not? (1 mark)

g) Which constraints - if any - are binding? (2 marks)

h) How much does the solution change by if we require X1, X2, X3 and X4 to be integers? What are the new values of the Xi and the objective function? (2 marks)

i) If we no longer require X1, X2, X3 and X4 to be non-negative, what is the new solution? What are the Xi and the objective function? (2 marks)

Unless stated otherwise, X1, X2, X3 and X4 are not required to be integers.

Regarding non-negativity, make sure to clearly state what you deem appropriate.

j) The company is offered the possibility of buying an extra pump (Component1) or

possibly as many as 100 extra pumps at a substantial discount (or bargain basement price).

Try to solve as many of the two sub-questions immediately below as possible without re-running the Solver (and only re-run the Solver if necessary).

j.i) How much should the company be willing to pay for 1 pump?

j.ii) How much should the company be willing to pay for 100 pumps?

Try to solve as many of the two sub-questions immediately above as possible without re-running the Solver (and only re-run the Solver if necessary). (1 + 1 = 2 marks)

k) Changes are to be made to the number(s) and/or amount(s) of pumps, labour and thing5: 18 less pumps, 150 more hours of labour and 250 more of thing5.

Try to solve as many of the two sub-questions immediately below as possible without re-running the Solver (and only re-run the Solver if necessary).

k.i) What is the (new) optimal number of each of X1, X2, X3 and X4?

k.ii) What is the (new) optimal value of the objective function?

Try to solve as many of the two sub-questions immediately above as possible without

re-running the Solver (and only re-run the Solver if necessary). (4 + 1 = 5 marks)

l) Due to discounting, the profit on the FourthSpa is being reduced by 6%.

Try to solve as many of the two sub-questions immediately below as possible without re-running the Solver (and only re-run the Solver if necessary).

l.i) What is the (new) optimal number of each of X1, X2, X3 and X4?

l.ii) What is the (new) optimal value of the objective function?

Try to solve as many of the two sub-questions immediately above as possible without re-running the Solver (and only re-run the Solver if necessary). (4 + 1 = 5 marks)

m) A new spa, called FifthSpa, is being considered.

It requires 2 pumps (Component1), 10 hours of labour (Component2), 20 ft of tubing (Component3), 10 units of thing4 (or Component4), and 10 units of thing5 (or Component5), and the plan is for it to sell it at a profit of $310.

Try to solve as many of the two sub-questions immediately below as possible without re-running the Solver (and only re-run the Solver if necessary).

m.i) Is it profitable to make any FifthSpas? (Explain why or why not.)

m.ii) If so, then by how much could we decrease the profit of a FifthSpa while still having it worthwhile to make? And, if not, then by how much would we have to increase the profit of a FifthSpa for it to be profitable to make?

Try to solve as many of the two sub-questions immediately above as possible without re-running the Solver (and only re-run the Solver if necessary). (2 + 3 = 5 marks)

n) If only three of the four types of spa are to be made, which three should they be, and in what amounts, to maximise profits? What is the profit? (3 + 1 = 4 marks)

o) If only two of the four types of spa are to be made, which two should they be, and in what amounts, to maximise profits? What is the profit? (2 + 1 = 3 marks)

p) If all four types of spa may be made but we have start-up costs of 1800, 2300, 2200 and 2600 respectively (not assuming integer values),

p.i) what is the (new) optimal number of each of X1, X2, X3 and X4?

p.ii) what is the (new) optimal value of the objective function? (3 + 1 = 4 marks)

q) If all four types of spa may be made but we have start-up costs of 1800, 2300, 2200 and 2600 respectively (assuming integer values),

q.i) what is the (new) optimal number of each of X1, X2, X3 and X4?

q.ii) what is the (new) optimal value of the objective function? (1 + 1 = 2 marks)

r) If all four types of spa may be made and we have no start-up costs and we can make a maximum of 220, 180, 140 and 100 respectively (not assuming integer values), and (for those where more than 0 is produced) we must make at least a minimum of 20, 30, 40 and 50 respectively,

r.i) what is the (new) optimal number of each of X1, X2, X3 and X4?

r.ii) what is the (new) optimal value of the objective function? (3 + 1 = 4 marks)

s) If at most three types of spa may be made and we have no start-up costs and we can make a maximum of 220, 180, 140 and 100 respectively (not assuming integer values), and (for those where more than 0 is produced) we must make at least a minimum of 20, 30, 40 and 50 respectively,

s.i) what is the (new) optimal number of each of X1, X2, X3 and X4?

s.ii) what is the (new) optimal value of the objective function? (2 + 1 = 3 marks)

t) If at most two types of spa may be made and we have no start-up costs and we can make a maximum of 220, 180, 140 and 100 respectively (not assuming integer values), and (for those where more than 0 is produced) we must make at least a minimum of 20, 30, 40 and 50 respectively,

t.i) what is the (new) optimal number of each of X1, X2, X3 and X4?

t.ii) what is the (new) optimal value of the objective function? (2 + 1 = 3 marks)

u) Suppose at most three types of spa may be made and we have no start-up costs and we can make a maximum of 220, 180, 140 and 100 respectively (not assuming integer values), and (for those where more than 0 is produced) we must make at least a minimum of 20, 30, 40 and 50 respectively. And let us suppose further that we have the additional specific requirement that if we make a SecondSpa (Hydro-Lux) then (in that case) we would be required to also make an Aqua-Spa (FirstSpa).

u.i) What is the (new) optimal number of each of X1, X2, X3 and X4?

u.ii) and what is the (new) optimal value of the objective function? (3 + 1 = 4 marks)