Question

The Spurling Group is considering using magazine outlets to advertise their online Web site. The company has identified seven publishers. Each publisher breaks down its subscriber base into a number of groups based on demographics and location. These data are shown in the following table:

Publisher	Group	Subscribers/Group	Cost/Group
A	5	460,000	$               1,560.00
B	10	50,000	$                   290.00
C	4	225,000	$               1,200.00
D	20	24,000	$                   130.00
E	5	1,120,000	$               2,500.00
F	1	1,700,000	$               7,000.00
G	2	406,000	$               1,700.00

      

       The company has set a budget of $25,000 for advertising and wants to maximize the number of subscribers exposed to their ads. However, publishers B and D are competitors and only one of these may be chosen. A similar situation exists with publishers C and G.

              Formulate and solve an integer optimization model to determine which publishers to select and how many groups to purchase for each publisher.

I DO NOT HAVE PREMIUM SOLVER 2. Pls solve it with normal Solver function!

Answer 1

To set up the publisher groups selection problem, let denote the number of groups from the selection of the ith publisher, and the selection of the ith publisher. Since the selection of types will be whole numbers and negative amounts of types cannot be selected, and a publisher can either be selected or not selected, this leads to the restriction

x 20 and integer, i = 1,..., 7 Y, binary, i = 1,...,7

Additionally, the company wants to maximize subscriber exposure, yielding the objective function

Sub,x;

Since a $25000 budget is available, the total cost of groups cannot exceed $25000, which yields the constraint

ΣCor, 1 < 25000

Since publishers B and D are competitors, as well as publishers C and G, at most one of the two competitors can be selected, if at all, yielding the constraints

YA+Y, 51 Y+Y, SI

Additionally, if a publisher is selected, the number of groups purchased for access cannot exceed the total amount of groups available from each publisher, which yields the constraint

x SGroups ,

Putting the objective function together with constraints, the integer LP model is

Maximize

Sub,x;

Subject to

X Cost,x, 5 25000 YA+Y, S1 x 20 and integer, i = 1,...,7 , SGroups, Y, Y, +Y, 51 Y, binary, i = 1,..., 7

Now set up an Excel spreadsheet as shown

H 1 Spurling Group SOLUTION Publisher Groups Selection Publisher Groups Subscribers/Group 460,000 50,000 225,000 24,000 1,120,000 1,700,000 2 406,000 Cost/group $1,560.00 $290.00 $1,200.00 $130.00 $2,500.00 $7,000.00 $1,700.00 SUB EXPO 0 25000 14 COST 15 AT MOST 1 B &D 16 AT MOST 1 C&G 17 A 18 B 19 C O O O O O O O OOO

Next, give the following cell assignments to set up the objective function and constraints

G12=SUMPRODUCT(C4:C10,G4:G10)

B14=SUMPRODUCT(D4:D10,G4:G10)

B15=H5+H7 B16=H6+H10

B17=G4 B18=G5

B19=G6 B20=G7

B21=G8 B22=G9

B23=G10

Now give the variables, objective, and constraints of the LP model to Solver as follows

Solver Parameters Set Objective: $G$12 To: Max Min Value of: By Changing Variable Cells: $G$4:$H$10 Subject to the Constraints: $B$14:5B523 <= $C$14:5C523 $G$4:$G$10 = integer SH$4:SHS10 = binary Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Solver Results Solver found a solution. All Constraints and optimality conditions are satisfied. Reports Answer Keep Solver Solution O Restore Original Values Return to Solver Parameters Dialog Outline Reports OK OK Cancel Save Scenario... Solver found a solution. All Constraints and optimality conditions are satisfied. When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution.

After running solver, the selection solution is given in the Excel file

PubEsther Groups Selection SUB EXPC 8952000

Note: Please like the answer, if it is helpful to you. Please mention the reason in the comment section, before disliking. Thank You.......!