Assignment Problem 1*
Print Media Advertising (PMA) has been given a contract to market Buzz Cola via newspaper ads in a major southern newspaper. Full-page ads in the weekday editions (Monday through Saturday) cost $2000, whereas on Sunday a full-page ad costs $8000. Daily circulation of newspaper is 30,000 on weekdays and 80,000 on Sundays.
PMA has been given a $40,000 advertising budget for the month of August. The experienced advertising executives at PMA feel that both weekday and Sunday newspaper ads are important; hence they wish to run the equivalent of at least eight weekday and at least two Sunday ads during August. (Assume that a fractional ad would simply mean that a smaller ad is placed on one of the days; that is, 3.5 ads would mean three full-page ads and one half-page ad. Also, assume that smaller ads reduce exposure and costs proportionately.) This August has 26 weekdays and 5 Sundays.
The objective is to determine the optimal placement of ads by PMA in the newspaper during August so as to maximize the cumulative total exposure (as measured by circulation) for the month of August.
a) Formulate the linear programming model for the problem.
b) Use the Graphical method to find the optimal solution. Show all steps.
c) Use Excel Solver to find the optimal solution. Copy and paste your spreadsheet and the Answer report in its entirety from Excel. Remember to not delete/modify any part of the Answer Report.
w = # of workday ads
s = # of sunday ads
cost constraint
2,000 w + 8,000 s </= 40,000
or simplified
w + 4 s </= 20
days needed constraints
8 </= w </= 26
2 </= s </= 5
graph these, box for the two days needed with line sloping down from left through the box for cost
three intersections
(2,8) , (2,12) , (3,8)
maximize E = 30,000 w + 80,000 s
check those three corners and pick the one with the biggest E
a) Formulating the linear programming model for the problem:
Let X be the number of weekday ads placed, and Y be the number of Sunday ads placed.
Objective function: Maximize the total exposure (circulation) for the month of August.
Maximize Z = 30,000 * X + 80,000 * Y
Subject to:
1. Budget constraint: The total cost of ads should not exceed the budget of $40,000.
2000 * X + 8000 * Y <= 40,000
2. Placement constraint: At least eight weekday ads and at least two Sunday ads should be placed during August.
X >= 8
Y >= 2
3. Non-negativity constraint: The number of ads cannot be negative.
X >= 0
Y >= 0
b) Using the graphical method to find the optimal solution:
To solve the linear programming problem graphically, we can plot the constraints on a graph and find the feasible region. Then, we can evaluate the objective function at the corner points of the feasible region to determine the optimal solution.
1. Plot the constraints:
Plot the lines:
2000 * X + 8000 * Y = 40,000 (budget constraint)
X = 8 (placement constraint)
Y = 2 (placement constraint)
2. Identify the feasible region:
The feasible region is the intersection of the shaded regions. It is the area where all the constraints are satisfied.
3. Evaluate the objective function at the corner points:
Evaluate Z = 30,000 * X + 80,000 * Y at the corner points of the feasible region to find the maximum value.
c) Using Excel Solver to find the optimal solution:
To use Excel Solver, set up a spreadsheet with the decision variables X and Y, the objective function Z, and the constraints. Then, use Solver to find the optimal values for X and Y.
Copy and paste the spreadsheet and Answer report from Excel:
Unfortunately, as a text-based AI, I cannot directly copy and paste the spreadsheet and Answer report from Excel. However, I can guide you through the process of using Excel Solver to find the optimal solution.
1. Enter the decision variables: In cells B2 and B3, enter the labels "X" and "Y" respectively. In cells C2 and C3, enter the initial values for X and Y (e.g., 0).
2. Enter the objective function: In cell B5, enter the label "Z". In cell C5, enter the formula "=30000*B2+80000*B3" to calculate the objective function value.
3. Enter the constraints: In cells B7 and B8, enter the labels "Budget Constraint" and "Placement Constraint" respectively. In cell C7, enter the formula "=2000*B2+8000*B3" to calculate the cost of ads. In cell C8, enter the initial constraint values (e.g., "=B2>=8" for the placement constraint on X, and "=B3>=2" for the placement constraint on Y).
4. Set up Solver: Go to the "Data" tab in Excel and click on "Solver" (this might vary depending on your Excel version). Set the objective cell as C5 (Z) and choose "Max" as the objective. Set the variable cells as C2:C3 (X, Y) and add the constraints for C7 and C8.
5. Run Solver: Click on the "Solve" button and let Solver find the optimal solution.
6. View the Answer report: Once Solver is finished, you can view the Answer report to see the optimal values for X and Y, as well as the maximum value of Z.
Note: Make sure to double-check the steps and formulas depending on your Excel version and setup.