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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: