Let’s consider a problem that you can use SOLVER to sort out for us: set out in formal, linear programming style:
Maximise profits 4a + 3b
Subject to:
21a + 16b <= 336
13a + 25b <= 325
15a + 18b = 0
What this means is, in summary, there are two products, a and b, for us to consider. Product a earns us £4 per unit contribution (or profit) and product b earns us £3 per unit contribution.
There are three constraints, such as raw materials, direct labour and cash; or constraints such as skilled labour, unskilled labour and machine time
In any case, for example, raw materials might be available only up to a total of 336 kilogrammes and each unit of a uses 21 kg of the raw material and each unit if b uses 16 kg of that raw material.
That’s the problem: SOLVER will tell us how many units of a to make and how many units of b to make in order to maximise profits.
There are four tasks for this problem
a) solve this problem using SOLVER and make a note of the profit earned; AND when Excel offers to save your model as a scenario, say yes and type a name like scenario 1!
b) change the contribution per unit of b to £4 and ask SOLVER to rework the problem and make a note of the profit earned; AND save this attempt as a new scenario … scenario 2, is a good name!
c) with your solution to part b) you will find an answer such as x.xxxx units of a and y.yyyy units of b: let’s assume that we cannot make fractions of a product and make a note of the profit earned … use the integer setting in SOLVER to force it to give us our answers for a and b as integers or whole numbers. Save this as scenario 3
d) this is a profit maximisation problem: change it to a problem in which the profit MUST be, say £70 … rework the problem and see what happens …
The problem is not complex but it is rich in the ideas and feedback it provides. I hope you like the scenario manager addition, by the way. By selecting Data, Data Tools, What-If Analysis, Scenario Manager, you can compare the three scenarios you will be able to save during this exercise.
Try it!
If you need any help, drop me a line at info@excelmaster.co.uk and I will send you my completed worksheet!
Duncan Williamson
Excel with Excel Master 