Tools for Accountants and Finance Executives: Using Solver for Linear Programming

DOIhttp://doi.org/10.1002/jcaf.22169
Date01 May 2016
Published date01 May 2016
95
© 2016 Wiley Periodicals, Inc.
Published online in Wiley Online Library (wileyonlinelibrary.com).
DOI 10.1002/jcaf.22169
D
e
p
a
r
t
m
e
n
t
s
SEC
Tools for Accountants and Finance
Executives: Using Solver for Linear
Programming
Harlan J. Fuller
Editor’s note: This is the first installment
of a series in which several authors will
provide “tools” useful to accounting and
finance executives.
Excel Add-in Solver can be
used to quickly solve linear
programming problems, includ-
ing optimization of the inputs
and shadow prices.
Linear programming is a
method of determining the
product mix that maximizes
profits using multiple scarce
resources. Shadow prices, or the
LaGrange Multipliers, are the
economic benefit of an increase
one of the inputs. Calculated
by hand, linear programming is
a time-consuming calculation
of constraints and optimization
functions. While there are many
open source and proprietary
programs available, Excel users
can add in the Solver function
and solve almost all linear
programming problems.
LINEAR PROGRAMMING
EXAMPLES
A farmer has 1,000 acres
to plant in corn and beans.
He has to plant at least 200
acres of beans to satisfy a spe-
cial contract with a soy sauce
manufacturer. Profit on corn
is $50 per acre and beans is
$20 per acre. Corn planting
takes 0.4 hours per acre and
bean planting takes 0.2 hours
per acre. The farmer has only
300 hours for planting. Corn
needs 10 pounds of fertilizer per
acre and beans need 20 pounds
of fertilizer per acre. The
far mer has only 12,000 po unds
of fertilizer.
The optimization function
would be profit. Profit would
be cell F5 in the file below.
Total planting time would
be G5, and total fertilizer
is H5.

To continue reading

Request your trial

VLEX uses login cookies to provide you with a better browsing experience. If you click on 'Accept' or continue browsing this site we consider that you accept our cookie policy. ACCEPT