You are Here: Home >< Maths

Excel Solver HELP Linear Programming watch

1. 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.
2. (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.
3. (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
4. (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 or

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?
5. 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
Attached Files
6. TrainingProgramme.xlsx (10.2 KB, 57 views)
7. (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.
8. ok ive updated it but i dont think its the correct answer
Attached Files
9. TrainingProgramme.xlsx (10.3 KB, 58 views)
10. (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.
11. (Original post by ghostwalker)
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.
brilliant i think thats right now thanks for you help
12. (Original post by JoeyA95)
brilliant i think thats right now thanks for you help
You're welcome.

Related university courses

TSR Support Team

We have a brilliant team of more than 60 Support Team members looking after discussions on The Student Room, helping to make it a fun, safe and useful place to hang out.

This forum is supported by:
Updated: November 1, 2016
The home of Results and Clearing

3,642

people online now

1,567,000

students helped last year
Today on TSR

Hang on, have Edexcel's come out already?

University open days

1. SAE Institute
Animation, Audio, Film, Games, Music, Business, Web Further education
Thu, 16 Aug '18
2. Bournemouth University
Fri, 17 Aug '18
3. University of Bolton
Fri, 17 Aug '18
Poll
Useful resources

Maths Forum posting guidelines

Not sure where to post? Read the updated guidelines here

How to use LaTex

Writing equations the easy way

Study habits of A* students

Top tips from students who have already aced their exams

Chat with other maths applicants