Home /
Expert Answers /
Operations Management /
using-solver-in-excel-give-me-step-by-step-directions-to-solve-this-problem-data-from-the-customer-e-pa566

Using solver in excel give me step by step directions to solve this problem:

Data from the customer e-mail survey have begun to trickle in. Walker wants to develop a prototype model based on the current survey results. So far, 53 surveys have been returned. To keep the prototype model manageable, Walker wants to proceed with model development using the data from the 53 returned surveys and using only the first 10 news sites in the questionnaire. The costs of ads per week for the 10 web sites are given in the following table, and the budget is $10,000 per week. For each of the 53 responses received, the 10 web sites visited regularly are shown below. For a given customer–web site pair, a one indicates that the customer regularly visits that web site and a zero indicates that the customer does not regularly visit that site. Applecore can choose which websites to select whether a site is operated or not. A customer is "Counted" if one of the websites that a customer regularly visits is "Chosen". Therefore "Counted" is a binary decision variable which is equal to one if at least one of the webistes that customer engaged with is "Chosen". The cost per week to operate each website is shown.

a) Develop a model that will allow Applecore to maximize the number of customers reached for a budget of $10,000 for one week of promotion. Take a screenshot of your solver dialog box and paste it here.

(b) Solve the model. What is the maximum number of customers reached for the $10,000 budget?

(c) Perform a sensitivity analysis on the budget for values from $5,000 to $50,000 in increments of $5,000. In other words, create a table to capture the objective value for each of those budget values. Also, calculate the percentage reach for each row.

(d) Construct a graph of percentage reach versus budget, from your answer in part (c).

(e) Is the additional increase in percentage reach monotonically decreasing as the budget allocation increases? Why or why not?

(f) What is your recommended budget? Explain.