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.

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 *Min*imized by Changing the Variable Cells
containing the coefficients (yellow):

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)e^{0.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.