Excel Assignment In this problem, you will use the Solver Add-in to determine the profit maximizing prices for different levels of advertising. For each advertising level you will calculate the quantity demanded, total revenue, total cost, and profit. Then you will determine how much advertising and promotion a firm should do. Problem

Ad Selection Problem

The Blue Line Lounge and Grill is one of the most popular martini bars in the Chicago area. While they serve food and other beverages, they are best known for their selection of 30 different martinis.

Suppose a firm similar to the Blue Line Lounge and Grill is trying to decide how much advertising and promotion they should do and the corresponding price to charge to maximize profit from the sale of martinis. For simplicity’s sake, we will assume demand for the firm’s martinis is only affected by price and advertising. The firm needs to decide how many thousands of dollars to spend on advertising and promotion. Advertising and promotion for this firm includes ads on local radio, social media advertising, food and drink specials during slower times, etc.

A template for the firm’s demand and cost for martinis is completed below. Use this template, solver, and the instructions to complete the below table.

a) Start at an advertising level of 0 indicating no money spent on advertising. Using solver, find the profit maximizing price and enter the information in the corresponding cells in the table.

b) Increase the advertising level to 1 indicating $1,000 spent on advertising. Using solver, find the profit maximizing price and enter the information in the corresponding cells in the table.

a) b)

DEMAND DEMAND

Coefficients Values Coefficients Values

Intercept 500 Intercept 500

Advertising 200 0 Advertising 200 1

Advertising Squared -15 Advertising Squared -15

Price -35 Price -35

Demand Demand

Total Revenue Total Revenue

COST COST

Coefficients Values Coefficients Values

Marginal Cost $2 Marginal Cost $2

Advertising Cost $1,000 Advertising Cost $1,000

Total Cost Total Cost

Profit Profit

c) Repeat this process for advertising level of 2, 3, 4, 5, and 6 and complete the table.

DEMAND DEMAND

Coefficients Values Coefficients Values

Intercept 500 Intercept 500

Advertising 200 2 Advertising 200 3

Advertising Squared -15 Advertising Squared -15

Price -35 Price -35

Demand Demand

Total Revenue Total Revenue

COST COST

Coefficients Values Coefficients Values

Marginal Cost $2 Marginal Cost $2

Advertising Cost $1,000 Advertising Cost $1,000

Total Cost Total Cost

Profit Profit

DEMAND DEMAND

Coefficients Values Coefficients Values

Intercept 500 Intercept 500

Advertising 200 4 Advertising 200 5

Advertising Squared -15 Advertising Squared -15

Price -35 Price -35

Demand Demand

Total Revenue Total Revenue

COST COST

Coefficients Values Coefficients Values

Marginal Cost $2 Marginal Cost $2

Advertising Cost $1,000 Advertising Cost $1,000

Total Cost Total Cost

Profit Profit

DEMAND

Coefficients Values

Intercept 500

Advertising 200 6

Advertising Squared -15

Price -35

Demand

Total Revenue

COST

Coefficients Values

Marginal Cost $2

Advertising Cost $1,000

Total Cost

Profit

Advertising Price Demand Profit

0

1

2

3

4

5

6

d) How many thousands of dollars should the firm spend on advertising?

Advertising = thousands of dollars

How many martinis does the firm expect to sell and what is the expected profit from this sale?

Demand =

Profit =

Instructions

Project Description:

In this problem, you will use the Solver Add-in to determine the profit maximizing prices for different levels of advertising. For each advertising level you will calculate the quantity demanded, total revenue, total cost, and profit. Then you will determine how much advertising and promotion a firm should do.

For the purpose of grading the project you are required to perform the following tasks:

Step Instructions Points Possible

1 Start Excel. 0

2 Prepare cells E12, I12, E27, I27, E41, I41, and E55 to use the Solver Add-in.

In cell E12, by using a cell reference, calculate the squared value of advertising specified in cell E11. Use the Excel POWER function. Copy the formula from cell E12 to cells I12, E27, I27, E41, I41, and E55. 1

3 Prepare cells E14, I14, E29, I29, E43, I43, and E57 to use the Solver Add-in.

In cell E14, by using cell references, calculate the quantity demanded for the advertising level specified in cell E11. Use the Excel SUMPRODUCT function. Use cells D10, and D11-E13. Copy the formula from cell E14 to cells I14, E29, I29, E43, I43, and E57. 1

4 Prepare cells E15, I15, E30, I30, E44, I44, and E58 to use the Solver Add-in.

In cell E15, by using cell references, calculate the total revenue for the advertising level specified in cell E11. Use cells E13 and E14. Copy the formula from cell E15 to cells I15, E30, I30, E44, I44, and E58. 1

5 Prepare cells E18, I18, E33, I33, E47, I47, and E61 to use the Solver Add-in.

In cell E18, by using a cell reference, enter the quantity of martinis demanded for the advertising level specified in cell E11. Refer to an appropriate cell among E11-E15. Copy the formula from cell E18 to cells I18, E33, I33, E47, I47, and E61. 1

6 Prepare cells E19, I19, E34, I34, E48, I48, and E62 to use the Solver Add-in.

In cell E19, by using a cell reference, enter the advertising level. Refer to an appropriate cell among E11-E15. Copy the formula from cell E19 to cells I19, E34, I34, E48, I48, and E62. 1

7 Prepare cells E20, I20, E35, I35, E49, I49, and E63 to use the Solver Add-in.

In cell E20, by using cell references, calculate the total cost for the advertising level specified in cell E11. Use the Excel SUMPRODUCT function and cells D18-E19. Copy the formula from cell E20 to cells I20, E35, I35, E49, I49, and E63. 1

8 Prepare cells E21, I21, E36, I36, E50, I50, and E64 to use the Solver Add-in.

In cell E21, by using cell references, calculate the profit for the advertising level specified in cell E11. Use cells E15 and E20. Copy the formula from cell E21 to cells I21, E36, I36, E50, I50, and E64. 1

9 Use the Solver Add-in to find the price that maximizes the profit for the advertising level specified in cell E11.

Using Solver Add-in

Select the Solver in the Analyze group of the Data tab of the Ribbon (note you should add this Add-in in case you do not have it in the Data tab already). Choose cell E21 in the Set Objective field. Select the Max option below. Choose cell E13 in the By Changing Variable Cells field. There are no additional constraints. Make sure that the “Make Unconstrained Variables Non-Negative” box is checked. Leave the GRG Nonlinear solving method. Click Solve. In the popup window, select the Keep Solver Solution option. Do not check any additional boxes and click OK.

As the result, you will see the price in cell E13 that gives the maximum profit for the advertising level specified in cell E11. The values for the quantity demanded, total revenue, total cost, and profit will update automatically. 4

10 Use the Solver Add-in to find the price that maximizes the profit for each level of advertising specified in cells I11, E26, I26, E40, I40, and E54. Use templates corresponding to each level of advertising for your calculations.

As the result, you will see the prices in cells I13, E28, I28, E42, I42, and E56 that give the maximum profits for the corresponding advertising levels. The values for the quantities demanded, total revenues, total costs, and profits will update automatically. 24

11 In cells D66-D72, by using cell references, enter the prices corresponding to advertising levels specified in cells C66-C72. Refer to appropriate cells among E11-E15, I11-I15, E26-E30, I26-I30, E40-E44, I40-I44, and E54-E58. 1

12 In cells E66-E72, by using cell references, enter the quantities of martinis demanded corresponding to advertising levels specified in cells C66-C72. Refer to appropriate cells among E11-E15, I11-I15, E26-E30, I26-I30, E40-E44, I40-I44, and E54-E58. 1

13 In cells F66-F72, by using cell references, enter the profits corresponding to advertising levels specified in cells C66-C72. Refer to appropriate cells among E18-E21, I18-I21, E33-E36, I33-I36, E47-E50, I47-I50, and E61-E64. 1

14 In cell D74, by using a cell reference, determine how many thousands of dollars the firm should spend on advertising. Refer to an appropriate cell among C66-C72. 1

15 In cell D76, by using a cell reference, determine the expected quantity demanded for the advertising level specified in cell D74. Refer to an appropriate cell among E66-E72. 1

16 In cell D77, by using a cell reference, determine the expected profit for the advertising level specified in cell D74. Refer to an appropriate cell among F66-F72. 1

17 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0