Excel Solver HELP Linear Programming

Original post by JoeyA95

Having trouble working out this question as I don't know how to set it out in excel to find the minimum cost and optimum programmes could some please help me thanks

As a part of a quality improvement initiative, government employees complete a three- day training programme on teamwork and two-day training programme on problem solving. The manager of quality improvement requested that at least eight training programme on teamwork and at least ten training programmes on problem solving be offered during the next six months. In addition, senior-level management specified that at least 25 training programmes must be offered during this period. A consultant is used to teach the training programmes. During the next six months, the consultant has 84 days of training time available. Each training programme on teamwork costs £10,000 and each training programme on problem solving costs £8,000.

Use Excel Solver to find the optimum number of programmes while minimising total costs.

You want to start by writing it as an l.p. problem.

Reply 2

OP

Original post by ghostwalker

You want to start by writing it as an l.p. problem.

Yeah I've done that

Decision variables
X1 number of teamwork programme
X2 number of problem solving programme

Objective Func

Min 8000X1+10000X2

Constraints

X1+X2>25
X1+X2<84
X1>8
X2>10

Reply 3

Original post by JoeyA95

Yeah I've done that

Decision variables
X1 number of teamwork programme
X2 number of problem solving programme

Objective Func

Min 8000X1+10000X2

Constraints

X1+X2>25
X1+X2<84
X1>8
X2>10

Your strict inequalities shouldn't be. They're all

\geq

or

\leq

And X1+X2<84 should be 3X1 + 2X2 <= 84 taking the number of days each course runs into account .

Personally, I find it easier to use P and T; it's easier to keep track.

What's the problem you're having with the Solver then?

(edited 7 years ago)

Reply 4

OP

TrainingProgramme.xlsx10.5KB

i don't know how to design the model in excel ive set it out like a product mix example but it doesn't work ive attached my file

Reply 5

Original post by JoeyA95

i don't know how to design the model in excel ive set it out like a product mix example but it doesn't work ive attached my file

You don't seem to have any formulae in the G16:G19 cells, so usage is not tied in to the X1,X2 values.

Also, the E18:F19 square should have one "1" and one "0" in each row. Whichever way around.

Reply 6

OP

TrainingProgramme.xlsx10.6KB

ok ive updated it but i dont think its the correct answer

Reply 7

Original post by JoeyA95

ok ive updated it but i dont think its the correct answer

The E18:F19 square wants to be:

10
01

Not

10
10

Also I think you have the "Cost per programme" values the wrong way round.

I think that's it.

It's just a question of checking what you have matches the lp.

Reply 8

OP