For the pricing decision model in Example 14.8, suppose that the company wants to keep the price at a maximum of $500. Note that the solution in Figure 14.19 will no longer be feasible. Modify the spreadsheet model to include a constraint on the maximum price and solve the model.

Data from Example 14.8

A spreadsheet model for this problem is shown in Figure 14.19. To find the maximum revenue using Solver, we identify the objective function cell as B13 and the decision variable cell as B5. The model has no constraints. In Solver, you should select “GRG Nonlinear” as the solving method. The Solver model is shown in Figure 14.20. The optimal solution is to set the price at about $549 and achieve a total revenue of $890,574.

Figure 14.19

 

1 Pricing Decision Model 2 3 Data 4 S 6 7 Model 8 Price 9 Sales Function Slope 10 Sales Function Intercept 11 12 13 $549.58 -2.9485 3240.9 Sales 1620.45001 Total Revenue $890.574.26