excel assignment

Step

Instructions

Points Possible

1

Start Excel. Download and open the workbook named: Chapter_13_Ad_Selection_Problem_Start.

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

Total Points

41