linear programming problem

Question

The advertising director for Karisma, a chain of four retail stores on
Alexandria's north side, is considering three media possibilities: 1- Ads
in the Ahram newspaper, 2- ads in a local UGO trade magazine that is
distributed free to all houses in the city and suburbs, and 3- ads on a
local Alex TV station. The stores are expanding their lines of Do -It-
Yourself tools and the advertising director is interested in a total new
customer exposure level of at least 50% within the city and 60% in the
suburbs. Each TV ad has a new-customer exposure level of 5% in the
city and 3% in the northwest suburbs. The newspaper ads have
corresponding exposure levels per ad of 3.5% and 3%, while the trade
magazine has corresponding exposure levels per ad of 0.5% and 1%.
The relevant costs are $1000 per newspaper ad, $300 per trade
magazine ad, and $2000 per TV ad. So that all three types of media
are used, Karizma would like to ensure that no single medium
consumes more than 45% of the total amount spent. Karizma would
like to select the least costly advertising strategy that would meet
desired exposure levels. Formulate the model using LP and solve it
using Excel. [newspapers ads = 9, magazine ads = 30, TV = 1, Cost
$20,000

Answer 1

Let N be the number of ads in the newspaper, M the number in the trade magazine and T the number on TV.

Cost of advertising, $C=1000N+300M+2000T to be minimised;

City exposure: 3.5N+0.5M+5T≥50;

Suburb exposure: 3N+M+3T≥60;

N,M,T>0, because all media are to be used.

To ensure none of the media use more than 45% of C:

45% of C is 450N+135M+900T.

1000N≤450N+135M+900T, 300M≤450N+135M+900T, 2000T≤450N+135M+900T, so:

-550N+135M+900T≥0, 450N-135N+900T≥0, 450N+135M-1100T≥0 are more constraints.

The 5 constraints are shown in red, while the objective function is shown in green.

In Excel, I made columns for N, M, T and C where C values use the objective function C(N,M,T) shown above in green.

I added columns for the 5 constraints and values are calculated from the functions:

3.5N+0.5M+5T-50, 3N+M+3T-60, -550N+135M+900T, 450N-135N+900T, 450N+135M-1100T.

The starting values for N, M, T are all 1 so these values produced many negative values in the constraints columns, as expected.

The next step is take ascending values of T and find minimum values of C when the constraints for N and M are satisfied.

T=1:

3.5N+0.5M≥45, 3N+M≥57

7N+M≥90. The lines 3N+M=57 and 7N=90 intersect at 4N=33, N=8.25. If we set N=8 (has to be an integer), then M≥34. When N=7, M≥41, but according to Excel this makes M cost exceed 45% of the total. When N=9, however, M≥30, and Excel confirms that all constraints are met and C=$20000. In particular, 3 constraints (suburb exposure, 45% limit for N and M) are just met (M and N use exactly 45% each of the total).