Question

As part of a quality improvement initiative, Consolidated Electronics employees complete a three-day training program on teaming and a two-day training program on problem solving. The manager of quality improvement requested that at least 8 training programs on teaming and at least 10 training programs on problem solving be offered during the next six months. In addition, senior-level management specified that at least a total of 25 training programs must be offered during this period. Consolidated Electronics uses a consultant to teach the training programs. During the next six months, the consultant has 84 days of training time available. Each training program on teaming costs $10,000 and each training program on problem solving costs $8,000

1) Build a spreadsheet representation of the model, and then solve it using Solver. Keep the solution as part of your spreadsheet. How many training programs of each kind would be offered, and what would be the total cost at the optimal point?

Answer 1

Decision Variables:

Let,

Number of three-day training program on teaming = x1

Number of two day training program on problem solving = x2

Objective Function:

The objective here is to minimize the cost of training.

Each training program on teaming costs $10,000 and each training program on problem solving costs $8,000

So the total cost is 10000*x1 + 8000*x2

This total cost has to be minimized. Hence the objective function is:

MINIMIZE 10000*x1 + 8000*x2

Constraints:

At least 8 training programs on teaming and at least 10 training programs on problem solving be offered during the next six months.

x1 >= 8

x2 >= 10

A total of 25 trainings must be provided during this six months.

x1 + x2 >= 25

During the next six months, 84 days are available for training.

Each teaming training takes 3 days and each problem solving training takes 2 days.

The total training time should not exceed the available time.

3*x1 + 2*x2 <= 84

The number of trainings cant be in fraction.

x1 = integer

x2 = integer

Putting the information in Excel:

Solver Parameters:

Optimal Solution:

Teaming training	Problem Solving training
x1	x2
8	17

Hence,

Teaming trainings offered = 8

Problem Solving trainings offered = 17

Total Optimal (minimized) cost = $216,000

.

IF YOU HAVE ANY DOUBT, KINDLY COMMENT. I WOULD LOVE TO HELP YOU!!!

IF YOU LIKE THE ANSWER, PLEASE GIVE AN UP-VOTE OR THUMB UP. THIS WILL ENCOURAGE ME TO ANSWER MORE!!