logo for solving-math-problems.com
leftimage for solving-math-problems.com

Excel Linear Programming











































Universal Claims Processors processes insurance claims for large national insurance companies. , Most claim processing is done by a large pool of computer operators, some of whom are permanent and some of whom are temporary. A permanent operator can process 16 claims per day, whereas a temporary operator can process 12 per day, and on average the company processes at least 450 claims each day. The company has 40 computer workstations. A permanent operator generates about 0.5 claims with errors each day, whereas a temporary operator averages about 1.4 defective claims per day. The company wants to limit claims with errors to 25 per day. A permanent operator is paid $64 per day, and a temporary operator is paid $42 per day. The company wants to determine the number of permanent and temporary operators to hire in order to minimize costs.

A. Formulate a linear programming model for this
B. Solve this problem using graphical analysis

Comments for Excel Linear Programming

Click here to add your own comments

May 28, 2011
Excel Linear Programming - Graphical Analysis
by: Staff

----------------------------------------------

Part IV

Condition 1: A permanent operator can process 16 claims per day, whereas a temporary operator can process 12 per day, and on average the company processes at least 450 claims each day.

16*P + 12*T ≥ 450

16*20 + 12*10 ≥ 450

320 + 120 ≥ 450

440 is not ≥ 450, this doesn’t quite meet the criteria



Condition 2: The Company has 40 computer workstations.

P + T ≤ 40

20 + 10 ≤ 40

30 ≤ 40, OK



Condition 3: The Company wants to limit claims with errors to 25 per day.

0.5*P + 1.4*T ≤ 25

0.5*20 + 1.4*10 ≤ 25

10 + 14 ≤ 25

24 ≤ 25, OK


Conditions 4 & 5: The values of P & T are both positive.


-----------------------------------------------

The final answers to your question:

A. Formulate a linear programming model for this

P = number of permanent operators
T = number of temporary operators
C = cost

Minimize the objective function

C = 64*P + 42*T


Subject to

16*P + 12*T ≥ 450

P + T ≤ 40

0.5*P + 1.4*T ≤ 25

P ≥ 0

T ≥ 0

--------------------------


B. Solve this problem using graphical analysis


The lowest cost ($1700 per day) occurs when P = 20 and T = 10


However, these are rounded values. One of the boundary conditions is violated using these numbers.

The volume is only ≥ 440 per day. The volume should be ≥ 450 per day


If you hire one more temporary employee to correct this problem, the error rate also increases to slightly over 25 errors per day.


It’s a business decision. The correct answer depends on what is more important: an error rate of under 25, or a volume of over 450.






Thanks for writing.


Staff
www.solving-math-problems.com

May 28, 2011
Excel Linear Programming - Graphical Analysis
by: Staff

---------------------------------------------------

Part III

(1) Click the following link to VIEW the “BOUNDED FEASIBLE REGION” & COST FUNCTIONS; or (2A) highlight and copy the link, then (2B) paste the link into your browser Address bar & press enter:

Use the Backspace key to return to this page:

http://www.solving-math-problems.com/images/graph-bounded-feasible-region-2011-05-25-03.png


Of the two functions plotted, the only cost function which passes through the “bounded feasible region” is:

T = (1900/42) – (32/21)*P
(shown in brown on the graph)

This is the only cost function that satisfies all the boundary conditions.

Although this function satisfies all the boundary conditions, it is not the minimum cost function.

To find the minimum cost function using this method, we would need to plot cost after cost until we found the minimum cost.


There is, however, a better, more direct way of finding the minimum cost function.

-----------------------------------------------

Corner Values

The minimum cost function can be found by computing the corner values for the “bounded feasible region”.


(1) Click the following link to VIEW the CORNER VALUES; or (2A) highlight and copy the link, then (2B) paste the link into your browser Address bar & press enter:

Use the Backspace key to return to this page:

http://www.solving-math-problems.com/images/graph-bounded-feasible-region-2011-05-25-04-corner.png


Computation of corner values:

Upper left:

The intersection of

0.5*P + 1.4*T = 25

&

16*P + 12*T = 450

At the intersection, P = 20.1219512 and T = 10.6707317

Since you cannot hire fractional employees, P = 20 and T = either 10 or 11


Lower left:

The intersection of

T = 0

&

16*P + 12*T = 450

At the intersection, P = 28.125 and T = 0

Since you cannot hire fractional employees, P = 28 and T = 0
Upper right:

The intersection of

0.5*P + 1.4*T = 25

&

P + T = 40

At the intersection, P = 34.4444444 and T = 5.55555557

Since you cannot hire fractional employees, P = 34 and T = either 5 or 6


Lower right:

The intersection of

T = 0

&

P + T = 40

At the intersection, P = 40 and T = 0



Compute the cost function for each of the corner values:


Upper left: Since you cannot hire fractional employees, P = 20 and T = 10

Cost = 64*P + 42*T

Cost = 64*20 + 42*10

Cost = 1700



Lower left: Since you cannot hire fractional employees, P = 28 and T = 0

Cost = 64*P + 42*T

Cost = 64*28 + 42*0

Cost = 1792



Upper right: Since you cannot hire fractional employees, P = 34 and T = 5

Cost = 64*P + 42*T

Cost = 64*34 + 42*5

Cost = 2386



Lower right: P = 40 and T = 0


Cost = 64*P + 42*T

Cost = 64*40 + 42*0

Cost = 2560



The lowest cost ($1700) occurs when P = 20 and T = 10

However, since we rounded the number of employees to whole numbers, we need to check to see if any of the boundary conditions have been violated.

----------------------------------------------

May 28, 2011
Excel Linear Programming - Graphical Analysis
by: Staff

--------------------------


Part II

Graph boundary constraints

I’ll get back to the cost function in a moment. But first, we are going to plot all of the boundary conditions for the cost function. The boundary conditions are the 5 equations listed under “Subject to”.


(1) Click the following link to VIEW the GRAPH; or (2A) highlight and copy the link, then (2B) paste the link into your browser Address bar & press enter:

Use the Backspace key to return to this page:

http://www.solving-math-problems.com/images/graph-bounded-feasible-region-2011-05-25-01.png

Note that the only area of the graph which satisfies all five boundary conditions at once is highlighted in yellow. This area is called the “bounded feasible region”.


When the “bounded feasible region” is enlarged, it looks like this:

(1) Click the following link to VIEW the ENLARGED “BOUNDED FEASIBLE REGION”; or (2A) highlight and copy the link, then (2B) paste the link into your browser Address bar & press enter:

Use the Backspace key to return to this page:

http://www.solving-math-problems.com/images/graph-bounded-feasible-region-2011-05-25-02.png


All possible values of P & T (the number permanent and temporary operators) within the “bounded feasible region” satisfy all five boundary conditions.

As an example, I’m going to arbitrarily pick a point within the highlighted yellow region and test the values of P and T against the boundary conditions.

(35,3)

When P = 35, T = 3

These values fall within the highlighted yellow area (the “bounded feasible region”).

Do they satisfy the boundary conditions listed in the problem statement?


Condition 1: A permanent operator can process 16 claims per day, whereas a temporary operator can process 12 per day, and on average the company processes at least 450 claims each day.

16*P + 12*T ≥ 450

16*35 + 12*3 ≥ 450

560 + 36 ≥ 450

596 ≥ 450, OK



Condition 2: The Company has 40 computer workstations.

P + T ≤ 40

35 + 3 ≤ 40

38 ≤ 40, OK



Condition 3: The Company wants to limit claims with errors to 25 per day.

0.5*P + 1.4*T ≤ 25

0.5*35 + 1.4*3 ≤ 25

17.5 + 4.2 ≤ 25

21.7 ≤ 25, OK


Conditions 4 & 5: The values of P & T are both positive.



My choice of P & T satisfies all the boundary conditions in the statement of the problem.

------------------------------------


Now, back to the cost

Minimize the objective function

C = 64*P + 42*T

Solve for T

T = (C/42) – (32/21)*P

The cost function has a slope of -32/21

By choosing various values for C (cost), this function can be plotted on the same graph which shows the “bounded feasible region”.

If cost = 1500

T = (1500/42) – (32/21)*P
(shown in light blue on the graph)


If cost = 1900

T = (1900/42) – (32/21)*P
(shown in brown on the graph)


---------------------------------------------------

May 28, 2011
Excel Linear Programming - Graphical Analysis
by: Staff


Part I

The question:

Universal Claims Processors processes insurance claims for large national insurance companies. , Most claim processing is done by a large pool of computer operators, some of whom are permanent and some of whom are temporary. A permanent operator can process 16 claims per day, whereas a temporary operator can process 12 per day, and on average the company processes at least 450 claims each day. The company has 40 computer workstations. A permanent operator generates about 0.5 claims with errors each day, whereas a temporary operator averages about 1.4 defective claims per day. The company wants to limit claims with errors to 25 per day. A permanent operator is paid $64 per day, and a temporary operator is paid $42 per day. The company wants to determine the number of permanent and temporary operators to hire in order to minimize costs.

A. Formulate a linear programming model for this
B. Solve this problem using graphical analysis


The answer:


Universal Claims Processors processes insurance claims for large national insurance companies.
, Most claim processing is done by a large pool of computer operators, some of whom are permanent and some of whom are temporary.

A permanent operator can process 16 claims per day, whereas a temporary operator can process 12 per day, and on average the company processes at least 450 claims each day.

The company has 40 computer workstations.

A permanent operator generates about 0.5 claims with errors each day, whereas a temporary operator averages about 1.4 defective claims per day.

The company wants to limit claims with errors to 25 per day.

A permanent operator is paid $64 per day, and a temporary operator is paid $42 per day.

The company wants to determine the number of permanent and temporary operators to hire in order to minimize costs.

A. Formulate a linear programming model for this

B. Solve this problem using graphical analysis



Equations

P = number of permanent operators
T = number of temporary operators
C = cost


A permanent operator can process 16 claims per day, whereas a temporary operator can process 12 per day, and on average the company processes at least 450 claims each day.

16*P + 12*T ≥ 450


The company has 40 computer workstations.

P + T ≤ 40


A permanent operator generates about 0.5 claims with errors each day, whereas a temporary operator averages about 1.4 defective claims per day.

The company wants to limit claims with errors to 25 per day.

0.5*P + 1.4*T ≤ 25


A permanent operator is paid $64 per day, and a temporary operator is paid $42 per day.

C = 64*P + 42*T

---------------------------------


In symbolic terms, you problem has been reduced to the following equations:


Minimize the objective function

Cost = 64*P + 42*T


Subject to

16*P + 12*T ≥ 450

P + T ≤ 40

0.5*P + 1.4*T ≤ 25

P ≥ 0

T ≥ 0

--------------------------

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Math Questions & Comments - 01.



Copyright © 2008-2015. All rights reserved. Solving-Math-Problems.com