Module 2-Optimization Techniques - Comprehensive Exercise #1

comprehensive exercise, use the Excel spreadsheet program.

linear programming and Solver in Excel to solve the problem described in the following scenario.

A political candidate is planning her media budget for an upcoming election. She has $90,500 in campaign funds to spend. Her political consultants have provided her with the following estimates of additional votes she could win as a result of using the following "potential" advertising effort: For every small sign placed by the roadside, she could possibly garner 10 additional votes. For every large sign placed by the roadside, she could possibly garner 30 additional votes. For every thousand bumper stickers placed on cars, she could possibly garner 10 additional votes. For every hundred personal mailings to registered voters, she could possibly garner 40 additional votes, and For every radio ad heard daily in the last month before the election, she could possibly garner 485 additional votes. The costs for each of these advertising media programs, along with the practical minimum and maximum that should be planned for each, are shown on the following table. Advertising Medium Cost Minimum Maximum Bumper Stickers (in thousands) $30 40 100 Personal Mailings (in hundreds) $81 500 800 Radio Ads (per day) $1,000 3 12 Small road side signs $25 100 500 Large road side signs $60 50 300 To complete the comprehensive exercise, formulate and solve the problem using Excel's Solver. Additionally, in your workbook respond to Parts A and Part B (listed below).

Provide the answers to the following questions

in Column A, five (5) rows below the last row in your LP model.

Part A: How should the candidate plan to spend her campaign money? How many additional votes should she expect to get using this media plan?

Part B: Will all the funds in her campaign fund be completely depleted? What constraints are binding constraints? What constraints have slack or surplus? How much slack or surplus?