MS-Excel Solver

Example 1 | Example 2 | Example 3 | XLS spreadsheet

Excel has a Solver tool that can search out optimal solutions to fairly complex problems that may not be solvable with conventional algebra and/or calculus. We will use Solver in this course to model optimal management strategies for various natural resources.

Solver is found under Excel's Data menu (Windows) or Tools menu (Mac).  If it's not already installed, you can load it via File--Options--Add-Ins (Windows) or Tools (Mac).

Example 1: Linear regressions

Download the same data that you used in the introductory regression lab; copy and paste it (as text) into a blank Excel spreadsheet.  Your objective here is to replicate the regression analysis with Solver, obtaining the set of intercept and slope coefficients Β0, Β1 and Β2 for the linear model

Quantity = Β0 + Β1Price + Β2Income + e

that minimizes the sum of the squared e's.

First, enter zeroes as initial coefficient estimates (yellow cells).  Next, in an adjacent column, calculate the predicted Quantities from the coefficents and respective Price and Income values; the initial predicted quantities will all be zeroes.  In the next adjacent column, calculate the squares of the differences between the predicted and actual Quantities.  Finally, calculate the sum of these squared errors (pink cell).

Now open the Solver window, specify the sum of the squared errors (pink) as the cell to be Minimized by Changing the Variable Cells containing the coefficients (yellow):

Make sure the "Make Unconstrained Variables Non-Negative" box is not checked, and the "GRG Nonlinear" solving method is selected, and click "Solve."  Click "OK" to save the Solver solution. 

Now in Data--Data Analysis, use the Regression utility to re-run the regression, specifying the Quantity column as the Y-range and the Price and Income columns as the X-range.  You should obtain the same coefficient estimates:

You can estimate other types of regression models with Solver as well.  For example, you might want the coefficients that minimize the sum of the absolute values of the errors (using Excel's ABS function).  Simply change the formulas in the right column and re-solve:

Example 2: Linear programming and resource shadow prices

Most resource management problems deal with resource constraints.  Solver is really good at solving constrained optimization problems.

Suppose a simple economy has fixed quantities of four production inputs.

    110 units of LABOR are available for hire at $1.50/unit.
    160 units of CAPITAL are available for rent at $1/unit.
    150 units of LAND and 90 units of OIL are available for the taking; there is no market price for these.

This economy produces three goods: SHELTER worth $13/unit, FOOD worth $10/unit and CLOTHES worth $9/unit.

    Producing each unit of SHELTER requires 1 LABOR + 3 CAPITAL + 3 LAND + 1 OIL.
    Producing each unit of FOOD       requires 2 LABOR + 1 CAPITAL + 2 LAND + 1 OIL.
    Producing each unit of CLOTHES requires 1 LABOR + 3 CAPITAL + 1 LAND + 2 OIL.

The minimum production requirement is 3 SHELTER + 2 FOOD + 3 CLOTHES.

What feasible combination of outputs will maximize this economy's net production value?

In other words, we need to solve for the values of the decision variables SHELTER, FOOD and CLOTHES that will optimize the objective function (net value) subject to constraints regarding resource availability and minimum production requirements.

Mathematically, the problem is to
        maximize NV = $13 SHELTER + $10 FOOD + $9 CLOTHES - $1.50 LABOR - $1 CAPITAL
subject to the input requirements (translated from the production recipes) and input availabilities:
    LABOR     = 1 per SHELTER + 2 per FOOD + 1 per CLOTHES ≤ 110
    CAPITAL = 3 per SHELTER + 1 per FOOD + 3 per CLOTHES ≤ 160
    LAND       = 3 per SHELTER + 2 per FOOD + 1 per CLOTHES ≤ 150
    OIL           = 1 per SHELTER + 1 per FOOD + 2 per CLOTHES ≤ 90

and the minimum production requirements
    SHELTER ≥ 3
    FOOD ≥ 2
    CLOTHES ≥ 3.

Here are the formulas for setting up the problem in Excel.

The yellow cell is the target cell (total value) to be maximized. Solver will adjust the output values in the green cells to maximize the total value in target cell. The blue cells are constants. The production requirements are translated as input demands in the inputs "used" column.

The net value function is straightforward. Start with zeroes (or any initial guesses) as initial values for product quantities. The total input use formula is the sum of each product's production level times its per-unit requirement. The "left over" column is simply the amount of input available minus the amount used.

Now open the Solver tool:

In the Solver window specify the target Net Value cell to be optimized, the output quantity cells to be adjusted to maximize Net Value, and then add the constraints by cell ranges.

Here the input quantities can't exceed the totals available, and the production quantities must equal or exceed the required minimums.

Clicking the "Solve" button yields the solution shown below. Solver outputs optional worksheets containing additional information about the solution. The "Sensitivity Report" worksheet contains "Lagrange Multipliers" for the inputs, which are "shadow prices" or implicit marginal rents for the inputs that ran out. I copied these values into the source worksheet.

Notice that the land and oil are all used up and have positive shadow prices. These are "strictly binding" constraints. An additional unit of land would generate an extra $2.50 of profit, and an additional unit of oil would generate $1 of profit if we had them. These are the marginal resource rents of land and oil. If you multiply the input quantities used times the input shadow prices and sum these, the sum equals the maximized profit.

Notice that the capital stock got used up too, but has a zero shadow price. The capital constraint is "just binding" and any additional would go unused. Finally, at a wage rate of $1.50, there is left-over labor; the labor constraint is "slack" or "non-binding." A lower wage would increase the demand for labor and (assuming people would work for the lower wage) eliminate the unemployment.

Example 3: Determining an optimal resource extraction schedule

Suppose you have 500 units of a non-renewable resource to sell off in a competive market. Your maximum extraction capacity is 100 units per year, so it will take at least five years to deplete this stock. Your cost of extraction is currently $4 per unit, but continuing improvements in extraction technologies will reduce this cost by 8% a year. The market price of this resource is $6 per unit, but you expect the market price to decline by 2% a year. Using a discount rate of 5%, determine the extraction schedule that will maximize the present value of your total resource rent stream from this stock.

You could set the problem up in an Excel spreadsheet like this.

Q(t) is the quantity to be extracted in time t;
X(t) is the stock available at the start of time t, where X(t) = X(t-1) - Q(t-1).
P(t)is the market Price, where P(t) = P(t-1)e-0.02t.
C(t) is the unit Cost of extraction, where C(t) = C(t-1)e-0.08t.
MRR(t) is the Marginal Resource Rent P(t)-C(t).
TRR(t) is the Total Resource Rent in time t.
PV_TRR(t) is the present value of TRR(t), where PV_TRR(t) = TRR(t)e0.05t.
MRR growth is the percent annual increase in MRR(t), where MRR growth = [MRR(t)/MRR(t-1)] - 1.

Your objective is to maximize the sum of the discounted TRR (yellow cell) by adjusting the extraction quantities Q(t) (green cells) over the time horizon t = 0...15, subject to four constraints:

  • extraction cannot exceed the remaining stock:   Q(t) ≤ X(t)   for t = 0...15
  • extraction cannot exceed 100 units per year:   Q(t) ≤ 100   for t = 0...15
  • extraction cannot be negative:   Q(t) ≥ 0   for t = 0...15
  • the ending stock cannot be negative:   X(15) ≥ 0
The blue cells are constants; you can specify whatever growth rates, discount rate and starting values X(0), P(0) and C(0) you want. I put a starting guess of 50 units per year in each of the green cells.

To solve this problem with Excel's Solver utility, specify the target cell to be optimized, the cells to be adjusted in order to yield the optimum, and the constraints.

Clicking the "Solve" button yields the optimal solution:

In this example, the optimal extraction period is years 2-6. The graph below illustrates the time trajectories of P(t), C(t) and MRR(t) (dollar scale on the left) and the MRR growth rate (percent scale on the right). The optimal extraction interval is more or less centered on the point where the declining MRR growth rate equals the discount rate.

MRR and the MRR growth rate are determined by the price and cost trends: falling costs and rising prices increase the effective MRR growth rate. Re-solving the problem with a lower discount rate such as 4% would push the optimal extraction period back a few years.

Note that you don't actually need Solver to solve this problem. Knowing the MRR schedule, you could simply identify the optimal time to extract when the MRR growth rate equals the discount rate, and center the extraction schedule on that time period.