Title: 2
Author: Miles B. Cahill

Utility Functions
Student Instruction Sheet

Introduction
In this exercise, we will construct two- and three-dimensional graphs of utility functions using Microsoft Excel 97.  This will allow us to explore the properties of utility functions.  These instructions are a step-by-step guide to constructing the graphs.

We will start by setting up the perfect substitute utility function U = 2x + y.  This function represents a somewhat unusual situation where an individual likes good x twice as much as good y regardless of how much x she already has.  This means that she is always willing to give up two units of good y for one unit of good x.  Alternatively, if she must give up a unit of good x, she always wants two units of good y in return before she will consider it an even trade.

We will construct a table in Excel to contain the utility values for every possible combination of goods x and y from 0 to 20 that she may consume.  We will then graph the data in the table.  This exercise will make use of Excel’s Data/Table command and its 3-D graphing capabilities.

Instructions

	Setting up the table

1.	The first step is to assign two cells to hold starting values for x and y.  In column A rows 1 and 2 respectively, enter “starting value of x” and “starting value of y”.  (If you can’t format the italics, don’t worry about it.)  It will be worthwhile to expand the size of column A so the text will fit.  In the corresponding rows of column B, type in 0 for each.  Since we want to graph each variable from 0 to 20, zero makes sense as a starting value in each case, but other values can be used.
2.	The table should be set up to cover the space B6 through W27.  This is enough space for 21 x and y values (0-20) and the row and column values.  We need to put the utility function at the top left corner of the table, in cell B6.  In cell B6, type in the formula for the utility function U = 2x + y, making sure that you refer to the cells containing the “starting values” that you set up in step 1.  (The formula should be =2*B1+B2.)  Note that a 0 appears; this is because 2*0+0=0.
3.	Row 6 will contain the possible x values.  To label these columns, type “x values” in row 4, column M.  In row 6, columns C through W, type in the numbers 0 to 20 in increments of 1.  (Hint:  you may wish to use the Edit/Fill/Series command to fill in the numbers for you.)
4.	In column A, row 17, type in the text “y values” to label the rows.  Column B will contain the y values.  In cell B7, start with a value of 0, and continue down the column through 20 (by increments of 1).  20 should be in row 27. (Again, you may find it helpful to let the Edit/Fill/Series command fill in the numbers for you; just make sure that you select the option that allows you to create a series in a column.)
5.	We are ready to use the Tools/Data command to apply the utility function to all the possible combinations of the 21 values each of x and y.  Select the table area (B6 through W27) using the mouse.  (Click on cell B6, hold down the left mouse button, and move the mouse to cell W27.)
6.	Click on the Data menu, then select Table.  A window should appear, asking you for a “Row input cell” and a “Column input cell”.  In other words, Excel want to know which starting value cell should be replaced by the row of data that you created, and which starting value cell should be replaced by the column of data that you created.  Recall in step 2 you typed in the formula =2*B1+B2.  Cell B1 referred to the x value, and cell B2 referred to the y value.  Since the x values were created in a row (row 6), and the y values were created in a column (column B), you should type in B1 for “Row input cell”, and B2 for “Column input cell”.  Then, click OK.  You should see the table fill itself in.  The values in the table are the utility values associated with each combination of x and y.  To make sure you understand how to use both a utility function and the table, compute the level of utility for the combination (x = 12, y = 6) by hand, and look up the answer on the table.  Both methods should give an answer of U = 30.
7.	Our goal is to graph these utility values and the indifference curves associated with this function Unfortunately, Excel 97 cannot graph tables like this directly; we need to copy the table’s values first.  The easiest way to do this is to select the table (if it is not selected already), click on the Edit menu, and select Copy.  Now click on cell B31.  Select the Edit menu again, and click on Paste Special…  On the window that appears, click on the Paste Link button.  The table should replicate itself.  Now delete the utility function formula from the copied table (it is in cell B31).  We are now ready to graph the data generated by this utility function.

Graphing the data

8.	Again select the entire copied table.  Click on the Insert menu, then on Chart.  A window will appear.  Under the “Chart type” heading select “Surface”.  Selecting the upper left option in the “Chart sub-type” window gives a multi-color, three-dimensional look at the utility function.  Combinations of x and y falling into certain utility level ranges are denoted by different shading.  Click on the Next button twice, then type in titles; you want to label the x, y, and z axes as x, y, and U, respectively.  In the last step of the Chart Wizard, select the option to place the chart on a “new sheet”.
9.	Once the 3-D surface chart appears, a minor adjustment must be made to the way in which the x-axis is constructed.  Select (click on) the x-axis in the chart, then click on the Format menu, and then click on Selected Axis.  Click on the Scale tab of the Format Axis window and remove (click on) the check mark from the “Value (Z) axis crosses between categories” option.
10.	To adjust the angles at which the graph is viewed, click and drag on a corner of the graph or adjust the settings in the Chart/3-D View window (click on the Chart menu, then on 3-D View).  You can drag a corner by moving the mouse over a corner of the graph; when the little “Corners” text appears, click the mouse button.  Note that a small black square appears over each corner of the graph.  When you move your (un-clicked) mouse over one of the corner squares, the pointer changes its shape to a + sign.  When the + sign appears, press the mouse button and move it slightly in any direction; a “frame” picture of the graph should appear.  As you continue to move the mouse (while holding down the button), the graph will rotate.  Release the mouse button when the graph attains the perspective you want to explore.  Alternatively, you can click on the Chart menu, then 3-D View.  From this window, you can press buttons to change the viewing angle.
11.	On the 3-D chart each shaded region represents a set of (x, y) coordinates that generate utility values falling into a certain range.  For example, the lowest region (usually blue) shows all the combinations that are assigned a utility index number 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 utility value of 10.  This line is an indifference curve.  You are used to seeing indifference curves drawn on a 2-dimensional x-y graph; this is the 3-D version.  If you plot the data again (select the table, click on Insert/Chart) with the surface chart lower left sub-option, you will get an indifference map.  Alternatively, if you rotate the 3-D graph carefully so that you are looking at it from above, you also see the traditional indifference map.  This rotation is Elevation 90, Rotation 0, Perspective 0 in the Chart/3-D View window.

Graphing other utility functions

12.	Now let us compare different utility functions.  To avoid having the original graphs replaced by the new work, select all three sheets (the data sheet and the two graphs) simultaneously by holding down the Control (Ctrl) key and clicking on the tab that contains the name of each sheet.  Then choose Edit/Move or Copy Sheet.  Be sure the box labeled “Create a Copy” (in the lower left corner of the resulting dialog box) is checked.  By copying the sheets in this way, the graphs are linked to the new (copied) data sheet, not the old sheet.  A different utility function can then be entered into the corner (cell B6) of the new data sheet and the graphs will adjust.

Utility function | Description
U = x + y | Perfect one-to-one substitutes
U = min(x, y) | Perfect complements (y = x)
U = min(5x, y) | Perfect complements (y = 5x)
U = xy | Cobb-Douglas (|MRS| = y/x)
U = x^2y | Cobb-Douglas (|MRS| = 2y/x)
| Quasilinear preferences
U = x^2 + y^2 | Concave preferences
| Convex preferences but x is undesirable