**Tags**

constraints, cost and profit analysis, linear programming, maximizing function, minimizing function, simplex method

Simplex method is a standard method of maximizing or minimizing a linear function of several variables under several constraints on other linear functions.

Simplex method can be solved easily using MS Excel for both maximizing and minimizing constraints of the objective function in question.

Let us take an example and understand how we can solve simplex method using MS Excel.

An advertising company manages their portfolio in three different media – Television, Radio and Newspaper. The cost and estimates of audience coverage is given below.

Television | Newspaper | Radio | |

Cost per advertisement | 2000 | 600 | 300 |

Audience per advertisement | 100000 | 40000 | 18000 |

The local newspaper restricts the number of weekly advertisements from a single advertising company to 10. In order to balance the advertising among three types of media, no more than half the total number of advertisements should occur in radio and at least 10% should occur in television. The weekly advertising budget is 18200. Find out how many advertisements should be run in each of the three types of media to maximize the total audience.

Let

X1: Number of Advertisements on Television

X2: Number of Advertisements on Newspaper

X3: Number of Advertisements on Radio

The Objective Function that has to be maximized is given by

Z = 100000X1 + 40000 X2 + 18000 X3

It is to be noted that X1, X2 and X3 should be greater than or equal to zero.

The constraints for the problem are as below.

2000X1 + 600X2 + 300X3 ≤ 18200 ≡ 20X1 + 6X2 + 3X3 ≤ 182

X2 ≤ 10

X3 ≤ 0.5 (X1 + X2 + X3) ≡ – X1 – X2 + X3 ≤ 0 (after dividing by 0.5)

X1 ≥ 0.1 (X1 + X2 + X3) ≡ – 9X1 + X2 + X3 ≤ 0 (after dividing by 0.1)

Now let us formulate the equations in MS Excel and solve it using SOLVER as in the case of Transportation Problem or Assignment Problem.

The solution is shown below.

This shows that the advertising agency has to run 4 advertisements on television, 10 advertisements on newspapers and 14 advertisements on radio in order to maximize their total audience. The optimum cost of running these advertisements are 1052000.