Question

please show how to do in excel file if possible

Please complete the following problems: 1. The Sea Wharf Restaurant would like to determine the best way to allocate a monthly advertising budget of $1000 between newspaper advertising and radio advertising Management decided that at least 25 percent of the budget must be spent on each type of media and that the amount of money spent on local newspaper advertising must be at least twice the amount spent on radio advertising. A marketing consultant developed an index that measures audience exposure per dollar of advertising on a scale from 0 to 100, with higher values implying greater audience exposure. If the value of the index for local newspaper advertising is 50 and the value of the index for spot radio advertising is 80, how should the restaurant allocate its advertising budget to maximize the value of total audience exposure? a. Formulate a linear programming model that can be used to determine how the restaurant should allocate its advertising budget in order to maximize the value of total audience exposure. b. Develop a spreadsheet model and solve the problem using Excel Solver 2. The following questions refer to a capital budgeting problem with six projects represented by binary variables a, b,c, d, e, and f. a. Write a constraint modeling a situation in which two of the projects 1, 3, 5, and 6 must be undertaken. b. Write a constraint modeling a situation in which, if project 3 or 5 is undertaken, they must both be undertaken. c. Write a constraint modeling a situation in which project 1 or 4 must be undertaken, but not both. d. Write constraints modeling a situation where project 4 cannot be undertaken unless projects 1 and 3 are also undertaken. e. Revise the requirement in part (d) to accommodate the case in which, when projects 1 and 3 are undertaken project 4 must also be undertaken.

Answer 1

Question 1:

(a) The constraints are:

Maximize Z = 50N + 80R

N + R <= 1000

N >= 250

R >= 250

N >= 2R

N >= 0

R >= 0

(b) The Excel setup is:

The Excel formulas are:

The solver parameters are:

The solution is:

N = 667

R = 333

Z = 60000