Economics Network CHEER Virtual Edition

Volume 11, Issue 1, 1997

Excel logo

Economic Optimisation using Excel's Solver

- a macroeconomic application and some comments regarding its limitations.


John Houston
Glasgow Caledonian University

Introduction

Recent issues of this review have included papers regarding economic optimisation. Two of these (MacDonald 1995, 1996) have extolled the virtues of Excel's Solver as a user-friendly and flexible tool for economic optimisation. Both these papers have described the application of the Solver to simple Linear (and Integer) Programs as used by that author in his teaching, and stress the ease with which students can set up and solve models of this nature. The purpose of this paper is twofold : firstly to describe another economic application of the Solver as used in the author's teaching and research and secondly, to make some general comments on the limitations of the Solver as an modelling tool.

Intertemporal Optimisation

Another advantage from using Excel's Solver (as opposed to LINDO, for example), is that it makes it possible to construct and solve optimisation models that have an intertemporal dimension to them. Many problems in Economics require an optimal set of decisions, with one to be made in each of several time periods. Optimal in this sense implies that the modeller wishes to maximise (or minimise) some total amount over the entire period covered by the model. The important distinction to be made between this and 'one-off' optimisation is that any single decision may not be optimal in itself, but that it is one part of a grander plan to optimise over the longer run. So, for example, we might expect a very short-lived agent to consume all of his wealth in the single period of his life - this is optimal. However, a longer lived agent must eke out his wealth over his lifetime, implying that he cannot consider consuming it all in one period. Any decision to do so will most certainly not be inter-temporally optimal.

Macroeconomic theory has been preoccupied with trying to understand the relationship between aggregate consumption and aggregate income. Researchers working in this area assume the existence of a rational, representative economic agent who wishes to maximise his total lifetime utility from consumption. Each period, the agent has to make a decision regarding how much of his wealth he should spend on goods and services and how much of current income should be saved or how much he should borrow. The author has taught elements of this theory on second- and third-level courses in economic modelling and macroeconomics, and has found the Solver to be an invaluable tool in bringing life to the concepts explained in the lectures. Students construct models similar to the one that will be described in this paper, then use the Solver to determine the agent's optimal period-by-period consumption. They can then alter the models' parameters and observe the impact of these changes on the pattern of consumption. Thus, for example, they can better appreciate the influence of the initial endowment and the rate of interest on the accumulation of wealth and the consumption pattern; the rate of discount applied to utility in increasing the agent's 'impatience' to consume earlier and; the importance of the assumptions made about the form of the agent's utility function.

The basic model

The model described here will be the simplest of the family used by the author. A considerably more complex model is described in Houston and Gasteen (1997), which, inter alia, generates aggregate consumption functions from ind ividual intertemporal functions and demographic profiles for the UK economy. The model described here starts by making the following assumptions :

The basic spreadsheet model is illustrated in Figure 1, with the logic for the first period displayed in full. This can then be copied across the columns for as many periods as the agent is assumed to live (with the proviso that from the second period on, the wealth brought forward is last period's wealth carried forward). This particular model assumes a 50-period lived agent, with no non-negative lower bound on consumption, who inherits 1000, with wealth attracting 10% interest per period. The logic is very simple, and can be quickly constructed and interpreted by the average undergraduate. In addition, it is very simple to alter any of the parameters of the model and observe the effect on consumption. Notice that row 15 (apart from A15) is left blank at this stage. It is into these cells that the Solver will trial various consumption levels in its effort to maximise this agent's utility given the constraints imposed on the model.

Download the screen shot
Figure 1 Structure and Logic of the Spreadsheet Model

Setting up the Solver

Instruction in how to set up the Solver is given in both of MacDonald's papers and need not be repeated here. Figure 2 illustrates how the Solver Dialogue Box should look like for this model. Obviously, we wish to maximise total utility (E1) by allowing consumption to change (B15 outwards). The first constraint ensures that the agent leaves at least a minimum amount of wealth upon his death. The second (actually several constraints modelled jointly) ensures that the agent does not consume more than his wealth (plus current income) in any period. This constraint is written in such a way that each cell on both sides will be paired together correctly (i.e. unprintable
characters, etc.) and saves a lot of typing!

The third and final constraint ensures that the agent consumes at least some minimum amount in each period (a 'starvation' level), as specified in B4.

Download the screen shot
Figure 2 The Solver Dialogue Box

Solving the Model

Once this has been set up, the student then clicks on the Solve button and awaits the result. When Solver has found an optimal solution (after about 100 iterations taking 5 minutes), the consumption row (# 15) will have a series of amounts which have been placed there by the Solver. It is from these that period utilities are calculated and summed in E1. This is the maximum utility this agent can obtain in his lifetime given the parameters. From this, the student can produce graphs of consumption, income and wealth over time (Figure 3) as well as consumption versus income (Figure 4).

Download the screen shot
Figure 3 Consumption, Income & wealth over the agent's lifetime

Download the screen shot
Figure 4 Intertemporal Consumption Function for the representative agent

Altering the assumptions

Having solved the model, the student can then make changes to any of its parameters. Whilst various combinations of changes can be tried, it is probably better from a pedagogic viewpoint to examine the effects of altering just one parameter at a time. For example, the student may be interested in learning what effect time-discounting utility has on the lifetime pattern of consumption. This can be done by first using Copy - Paste Special - Values to store the consumption figures from the original, undiscounted model to another part of the sheet, increasing the discount rate (in cell B9), then resolving the model. The new consumption levels can be pasted to a row or column adjacent to the originals, and both plotted against time (Figure 5).

Download the screen shot
Figure 5 Consumption profiles for 0% and 5% discounting

In this case, student can see that reducing the real value of future utility (in relation to the rate of interest) makes the agent more impatient to consume in the early stages of his life. As a result, he retains less of his wealth in the early stages, which lowers the amount of Capital on which later Income can be earned. As a result, the agent cannot consume at the same level in later life.

Problems will occur where the student unwittingly generates a scenario for which there is no feasible solution. For example, it is obviously impossible to insist on a minimum level of consumption (per period) that would result in all wealth being consumed long before the end of the agent's life. In this example, the agent has an endowment of 1000 and a 10% return on wealth to last him say, 50 years. Roughly speaking therefore, average annual income will be 100. If the starvation level of consumption is set above 100, then no feasible solution can exist, given the assumptions of the model. This would be complicated further by insisting that some wealth be bequeathed upon death - the greater the bequest, the tighter will be the rein on consumption, and the greater the chance of there being no solution. There is no way of prechecking the feasibility of the problem as set before attempting to solve it (except where it is obvious), thus there is always the risk of waiting for a solution that never comes. The student will be informed if the Solver can't find a solution, in which case the student needs to go back to the model and look to relax some of the constraints.

The ease with which models can be set up and solved clearly makes the Solver a useful tool in teaching and research. That said, this author has experienced some of its shortcomings which presently limit its application to relatively small, simple models, such as the one described above. Two limitations in particular are worth mentioning, viz.,

Time taken to solve larger models

Given the flexibility and ease of use of the Solver, the temptation always exists to develop larger and more sophisticated optimisation models. As well as the second- and third level classes in economic modelling, the author teaches part of a fourth level class in business modelling. The basis of the practical computing sessions is a spreadsheet-based cash flow model of a single-product company. Period demands are estimated from a set of variables, including Own Price, Competitors Price and Interest Rate. The students then decide on the numbers of workers and machines to employ in each period (a Cobb-Douglas technology is assumed), which can be more or less than the demand in that period (thus permitting Stocks or Lost Sales to occur). Factor productivities can be selected with unit costs being drawn from lookup tables. Once the students had had time to 'manually' optimise the model by trial and error, the intention was then to let the Solver optimise the total net cash flow over 10 periods. In doing this, it was to be permitted to select the numbers of full- and part-time workers, the number of machines, their rate of output and unit prices for the finished product. The students would then compare their best attempts with a truly optimal production plan. Unfortunately, the time taken by the Solver to perform this task was so long, as to preclude using it 'as live' in the practicals. Even when using a 100Mhz Pentium PC, obtaining a solution took the best part of five hours. As most of the teaching machines at Glasgow Caledonian are still 486 standard, the only option was to provide students with a ready-solved model, depriving them of the experience of setting it up and solving it. As a consequence of this, it was not possible, for them to gauge the effect on total net cash flow of parameter changes, such as raising or lowering unit prices. Obviously, as the power of the teaching-lab PCs increases, then this will become less of a problem, though it currently has to be seen as a severe limitation on work of this kind.

Getting trapped at a local optimum

An ever-present problem of any 'hill-climbing' optimisation technique, is that it can arrive at what appears to be the optimum solution, when in fact it is only a local one. Solver trials solutions and moves in the direction that appears to yield the fastest rate of change in the value of the objective function. Once it gets to a point where there appears to be no further gains to be had, it stops and reports that it has found an optimum solution. The temptation is always to take this as the optimum solution. However, the author has experienced several cases where running the Solver from different 'starting' locations has resulted in different optimum solutions being obtained. This happens, for example, when the objective function's surface is comprised of 'spikes' (see Figure 6), as a result of the complexity of the underlying relationship between the objective and the causal variables.

Download the screen shot
Figure 6 Objective Function Surface with local and global optima

As can be seen from this rather exaggerated example, should the search start with a low value for Input 1, then there is a good chance that it will be drawn to the local peak. On the other hand, if the model was seeded to start from a higher Input 1 value, then there would be a much better chance of finding the true global optimum. Of course, it is only possible to plot the objective function surface for a model with one or two inputs, thus the only practical solution is to run the model several times using different seed values. In the event that most, if not all, of the runs yield the same solution, you can be reasonably sure that you have the true optimum.


References

Houston, J. and Gasteen, A., 1997
"From the general to the specific : estimating aggregate intertemporal consumption functions from survey data", Department of Economics Discussion Paper, Glasgow Caledonian University.
MacDonald, Z., 1995
"Teaching Linear Programming using Microsoft Excel Solver", Computers in Higher Education Economics Review, 9 (3), 7-10
MacDonald, Z., 1996
"Economic optimisation : an Excel alternative to Estelle et.al's. GAMS approach", Computers in Higher Education Economics Review, 10 (3), 2-5

Address for correspondence:

John Houston, Department of Economics, Glasgow Caledonian University, Cowcaddens Road, Glasgow, Scotland, G4 0BA

E-mail J.Houston@gcal.ac.uk

What's Related:
  · Other articles on the use of spreadsheets in teaching economics

Top | CHEER Home

Copyright 1989-2007