Spreadsheet Modeling and Decision Analysis (7th Edition) Chapter 7 question 14 : A new iTalian restaurant........

Could you explain for part a , could you explain in detail the logic behind the objective found (highlighted below in the screenshot) and how did you get the value 17250 as the target value for exposures ? Also, for part c and d , please provide the correct solution. In part d , how come under cost column , the goal value is showing up as 100000, when the actual value is 17600, under =0 and value =0. I am attaching screenshots including the question, and have highlighted the things mentioned above, please check them.

A new Italian restaurant called the Olive Grove is opening in a number of locations in the Memphis area. The marketing manager for these stores has a budget of $150,000 to use in advertising and promotions for the new stores. The manager can run magazine ads at a cost of $2,000 each that result in 250,000 exposures each. TV ads result in approximately 1,200,000 exposures each but cost $12,000 each. The manager wants to run at least 5 TV ads and 10 magazine ads, while maximizing the number of exposures generated by the advertising campaign. But the manager also wants to spend no more than $120,000 on magazine and TV advertising so that the remaining $30,000 could be used for other promotional purposes. However, the manager would spend more than $120,000 on advertising if it resulted in a substantial increase in advertising coverage. a. Formulate a GP model for this problem assuming the marketing manager has the following goals: Goal 1: Exposures should be maximized. Goal 2: No more than $120,000 should be spent on advertising. (Note that you will have to determine an appropriate target value for the first goal.) Assume the marketing manager wants to minimize the maximum percentage deviation from either goal. b. Implement your model in a spreadsheet and solve it. c. What is the solution you obtain? d. What changes do you make to your model if the manager wants to spend less on advertising than your solution suggests? Step-by-step solution Step 1 of 10 A a. Consider the following goals, a. Consider the following goals, Goal 1: Exposures should be maximized. Goal 2: No more than $120000 should be spent on advertising. The marketing manager can use the following advertising and promotion techniques. (1) The manager can run magazine ads at a cost of $2000 each that result in 250000 exposures each. (2) In TV ads which result in approximately 1200000 exposures but cost $12000 each. (3) Other promotional purposes which cost approximately #30000. The manager would spend more than $120000on advertising if it resulted in a substantial increase in advertising coverage. In this problem, the fundamental decision facing the manager is how to spend on TV commercials and the magazines. These quantities are represented by X and X, respectively. To summarize, the LP model for GP problem is: w MIN: di 17250 Subject to, w 120000 -di X, +d; -di = 17250 X2 +d; -d;= 120000 2000X, +12000X, +d; -d= 150000 dd 0 for alli d. If the manager wants to spend less on advertising than the solution suggests. Consider that the manager wants to spend only $100000 on the advertising. Consider the following excel model: A B D 1 2 The Olive Grove 3 4 Problem Data: Magazine TV 5 Number 10 13 6 Exposures (in 1000s) 250 1,200 7 Cost $2,000 $12,000 8 Min 10 5 9 10 Goal Constraints: Exposures Cost 11 Actual 18,100 $1,76,000 12 + Under 0 13 - Over 850 $0 14 = Goal 17,250 $1,00,000 15 Target 17,250 $1,00,000 16 17 Percentage Deviation: 18 Under 0.00% 0.00% 19 Over 4.93% 0.00% 20 21 Objective: 4.93% 22 If the manager wants to spend $100000 on the advertising then it must produce 13 TV commercials and 10 magazine ads. $0 Comment