Exploring Economic Models Using Excel.

• Author: Cahill, Miles

Miles Cahill [*]

George Kosicki [+]

This paper applies spreadsheet software to intermediate-level consumer theory concepts. Spreadsheets help make the concepts more accessible while allowing students to explore the ideas in more depth. Areas of application are utility functions, income and substitution effects, price indices, measures of welfare change, and the optimal saving rate. We chose the examples to stimulate awareness and discussion of the many classroom uses for four important Excel spreadsheet tools: three-dimensional (3-D) graphs, iteration, Goal Seek, and Solver.

1. Introduction

   This paper uses spreadsheet software to explore a series of consumer theory examples appropriate to intermediate-level economics classes. The paper also illustrates how spreadsheets can make some advanced topics more accessible to students, thereby helping to bridge the gap between undergraduate and graduate education in economics. [1] The examples pertain to important topic areas in consumer theory: utility functions, substitution and income effects, price indices, measures of welfare change, and the optimal aggregate saving rate.

   A web-based supplement accompanies this paper and includes additional examples pertaining to cost minimization, oligopoly models, IS-LM and aggregate supply-aggregate demand models, and the Solow growth model. This supplement is available on the Internet at http://sterling.holycross.edu/departments/economics/mcahill/sejpaper. html. The examples in the supplement draw on the same Excel tools that the paper presents. [2] The web supplement also contains student instruction sheets that give step-by-step instructions on completing the examples in this paper. In addition, annotated versions of the spreadsheets referred to in the paper are available at this address. It is possible to complete most of the applications discussed in the paper and the web supplement on all three of the leading spreadsheet packages (Microsoft Excel, Lotus 1-2-3, and Corel Quattro Pro), but only the Excel 97 commands are used. [3]

   From a practical perspective, spreadsheet software such as Excel is a natural choice to use in exploring economic models because it is widely available on most campuses. This availability eliminates the task of seeking funding for the purchase and support of specialized software packages. In addition, spreadsheet software is relatively easy to use, and its flexibility makes it useful in many different courses at all levels of the traditional economics curriculum. [4] Most economics students almost certainly will use it after graduation in both career and personal settings. Most important, it minimizes black-box features that characterize much computer-assisted learning software.

   Writing spreadsheet applications does take considerable time and effort. While it is often easy to show someone how to perform a certain operation, writing the sequence of commands can be difficult. [5] Finding and remedying the quirks of certain spreadsheet programs can be likewise burdensome. Yet, we believe these start-up costs are worth enduring because spreadsheets can do more than change the look of teaching, they can change the substance as well, in often surprising ways. To ease these start-up costs, the web supplement provides step-by-step student instructions for the applications. However, we provide these handouts with two notes of caution. First, reliance on these instructions may encourage students to follow the steps blindly without understanding the material. Second, the level of detail in the instructions may make the Excel commands seem more complicated and tedious than they really are.

   The applications in this paper can complement many different approaches to teaching, from traditional lecture formats to more active-learning formats including discovery-oriented lab sessions. [6] It is possible to tailor many of the applications to suit the level, ability, and time constraints of students. Specifically, the instructor may choose to have students build the spreadsheets from scratch based on printed instructions, instruct students on how to build the spreadsheet in a lecture or lab setting, distribute completed or half-completed spreadsheets to students, or any combination of the above. Further, the instructor may choose to protect certain spreadsheet cells to prevent students from manipulating them. [7]

   With regards to our own teaching, one of us has used spreadsheet applications primarily in a lab setting, and the other mainly in problem sets completed outside of class. We have found the use of spreadsheets enhances both teaching styles. When using either approach, the instructor is cautioned to carefully work out the exercises ahead of time, paying close attention to commands, options, and features that students may find confusing, perhaps distributing a handout to minimize confusion. [8] As students gain more experience, these problems naturally become fewer and fewer. Instructors can exploit this learning curve by, for example, giving detailed technical instructions for initial, simple assignments and giving fewer instructions for later more complicated exercises.

2. Utility Functions

   A significant obstacle in microeconomics for many students is the connection between a utility function and the indifference map it generates. Excel can help to clarify these connections because of its ability to create three-dimensional (3-D) surface charts as well as the topographical or contour maps associated with 3-D functions. [9]

   Excel's Data/Table command can generate the underlying data for the following exercise in plotting utility functions. Figure 1 shows the first step in the setup. To use the Data/Table command, you must form a table of cells in which the top row is the range of x values over which the utility function is to be evaluated, and the left-hand column represents the range of y values. The cells in the middle of the table will contain the utility assigned to each combination of x and y. The spreadsheet in Figure 1 is set up to evaluate a utility function over the range of x and y values from 0 to 20 in increments of 1 unit.

   The Data/Table command requires that somewhere on the worksheet there must be starting values for x and y. Using references to the cells containing the starting values, type the utility function into the corner of the table (as shown in Figure 1). For example, to generate data pertaining to a perfect substitute utility function U = 2x + y, use the formula 2*B1+B2. Then, the entire table of cells must be selected (cells 86 through W27). Implementing the Data/Table command spreads the utility function formula throughout the empty cells of the selected table by systematically replacing the starting value of x by each of the x values residing in the first row of the matrix. Similarly, Excel systematically replaces the starting y value with each of the y values residing in the first column of the table. The x values in this case are the "row input" and the y values are the "column input". To implement the Data/Table command, type into the Data/Table window the cell addresses associated with the starting values fo r the row and column inputs. Given the setup of Figure 1, Figure 2 shows the proper way to fill in the Data/Table window. Note that the response to the "Row input cell" prompt is cell B1 (the starting x value cell), while the proper response to the "Column input cell" prompt is cell B2 (the starting y value cell).

   Implementing the Data/Table command generates the data set shown in the upper half of Figure 3. Unfortunately, Excel 97's Chart Wizard cannot plot this block of data unless the upper-left corner cell is deleted. [11] To make this deletion in a way that will not inhibit additional utility function simulations, copy the data and paste it to another location on the worksheet using the Edit/Paste Special/Paste Link sequence of commands. The Paste Link option appears in the Edit/Paste Special window shown in Figure 4. The Edit/Paste Special/Paste Link command sequence will create a new table in which each cell contains a formula that sets the cell equal to its counterpart cell in the original table. Delete the cell in the upper-left corner of this duplicate table (B31), and then select the entire table as shown in the lower half of Figure 3.

   Once the data are selected, use the Surface Chart option of Excel's Chart Wizard (use the Insert/Chart command) to construct a plot as shown in Figure 5. Selecting the upper-left option in the Chart subtype matrix gives a multicolor, 3-D look at the utility function. Different colors or shadings denote combinations of x and y falling into certain utility level ranges. [12] To view the graph at different angles, click on and drag a corner of the graph or adjust the settings in the Chart/3-D View window. Figure 6 shows the 3-D surface chart associated with the data in Figure 3.

   As the legend in Figure 6 illustrates, each shaded region represents a set of (x, y) coordinates that generate utility values falling into a certain range. For example, the lowest region shows all the combinations that are assigned a utility index between 0 and 10, the next region shows all the combinations of x and y that are assigned a utility index between 10 and 20, and so on. Therefore, the dividing line between these first two regions shows all the combinations of x and y that are assigned a ranking of 10.

   Plotting the data in Figure 3 using the lower-left option in the Chart subtype matrix shown in Figure 5 generates a 2-D topographical map of the data--in other words, a portion of the indifference map for the utility function. [13] The dividing lines between the shaded regions represent indifference curves. [14] Figure 7 shows the indifference map for the data in Figure 4.

   The main advantage of exploring utility functions with a spreadsheet is that once an initial data table is set up and each graphical perspective created, different...