Oper Mgmt Case Study (Excel - Solver & Report)

Must formulate a linear programming problem and solve using solver. Then generate a report that answers questions as well. Refer to the attached pdf for all instructions. Must include a complete excel workbook and a written summary/answers to questions Dauphin Island Adventures Dauphin Island Adventures is an amusement park located in South Carolina. The marketing department is looking to create an advertising campaign for next spring and summer. The park has hired the consulting firm, Braun Marketing Group (BMG), to assist in the planning process. Dauphin Island Adventures has set their total advertising budget at $279,000 for print, radio and television mediums. They are relying on BMG to recommend how to divide the budgeted dollars among the three advertising outlets. Below is the BMG estimate of market effectiveness, for each medium. BMG gives estimates for the exposure rating, potential to bring new customers and the cost per ad. Exposure ratings are used to measure the value of the advertisement to both new and existing customers. It is a function of image, message recall, and visual and audio appeal. Advertising Media Exposure Rating per Ad New Customers per Ad Cost per Ad Print 10 1,000 $ 1,000 Radio 25 2,000 $ 3,000 Television 90 4,000 $ 10,000 BMG informs Dauphin Island’s marketing team that the reach and exposure data only applies to the first few ads in each type of advertising. Print media’s maximum exposure and new customer reach is only effective on the first 20 ads. After 20 the ratings for exposure and new customer reach falls to 5 and 800, respectively. Radio advertisements’ maximum effectiveness is only for the first 15 airings. Their exposure rating falls to 20 and new customer reach rating falls to 1,200 per ad. Television’s ratings also drops to 55 for the exposure rating and 1,500 for the new customer reach rating after 10 ads. The objective of the campaign, for Dauphin Island’s management, is to maximize total exposure across all three forms of media. Due to the nature of the amusement park industry, a goal of reaching 100,000 new customers was set during a meeting with BMG. To achieve the goal for reaching new customers and maximize exposure, the following guidelines were adopted: 1. Budget in dollars per medium a. Print - at least $30,000 b. Television - at least $140,000 c. Radio - maximum of $99,000 2. Purchase no more than 20 television ads, and use at least twice as many radio ads as television Assignment As the operations manager of BMG, you have been tasked with writing the report that will be presented to Dauphin Island Adventures’ marketing department. Your report should include discussions and recommendations with respect to the following items: 1. Provide an advertising campaign recommendation to Dauphin Island Adventures. Recommend the number of advertisements planned for each of the three mediums and specify the budget required for each. Specify the expected number of potential new customers and total exposure under this advertising plan. 2. If the advertising budget could be increased by an additional $10,000, what effect would it have on total exposures, if any?