|
Developing
the data
 We
begin with a simple linear
demand and supply graph in which the
range of "X" values is selected before any analysis is conducted. One
point requires attention immediately: Demand and supply curves are most
easily developed when the "inverse" curves are used:
PD
= f(Q) and PS = g(Q).
|
| All
material below is based on the workbook Demand_and_Supply.xls.
Open that workbook by clicking on the link in the previous sentence
or at the right and following the development below. |
|
|
|
To select the
chart type, click on the chart wizard or the "Insert/Chart" menu entry.
The icon for the chart wizard is as below.
 For
most purposes, the solid
line graph (the one selected at the right)
gives the best results. Data points can be indicated if desired.
Changing the way graphs are represented is discussed below. Selecting
"smoothed lines" rather than "lines" can improve appearance
for nonlinear functions, but can also result in peculiar behavior for
some
lines. This dialog box and a few other displays below are cropped to
save space.
A
simple graph. The figure immediately below
shows the graph that follows from simply
clicking on the selection at the right. Beside the graph are the data
that generate it. These data come from the following equations
| Demand: P =
300 - 5Q |
Supply:
P = 10 + 2Q. |
We repeat: Graphing is much
simpler when the inverse demand and supply curves are used.
This graph's appearance can be
improved. Consider each of
the following concerns:
- The grid line serves little
purpose.
- The
curves
are not distinct
against the background.
- The axes are not labeled.
- The legend uses space that
could be
showing more detail.
- The demand
and supply curves
do not cross at a tabled value. This is more of an issue when the graph
is used to show equilibrium or other critical values.
The
first four of
these points involves changing the graph. The fifth
involves changing the data range and will be addressed separately.
 Using
Chart Options.
To access "Chart Options," either right-click on
the chart or select "Chart/Chart Options" from the menu. (To activate
the Chart menu, click anywhere on the chart.)
Click
on the
Titles tab. In the Chart title window, type "$ per unit" and in the
Value (X) axis window, type "Quantity." (We appropriate the
title for use as the Y axis label because on occasion the dedicated
"Value (Y) axis" label can
truncate the label.) Also, we prefer the horizontal display that this
use of the chart title provides.
Click on the Gridlines tab and remove the check that results in the
horizontal gridlines.
The
resulting graph is at the right. Some more adjustments are
indicated. The remaining
changes are made directly in the graph by liberally using the
right-click key.
- Right-click on the Chart Title
at the top
of the box. Select "Format Chart Title." The dialog box at the right,
below the graph, appears. Select Size 10 and click so that "Auto scale"
is off.
(Auto scale causes the font to change if the picture size changes.)
 Right-click
on the legend. A
dialog box
like the one at right appears. Select bold and Size 9.- Click on the Chart Title
again. Hold the
key down and drag the title to the upper left-hand corner of the chart.
- Click on the legend again, and
drag it to
the top center of the box.
- Click on the legend again and
use the
handles to make it wider and less tall.
- Right click on the Plot Area
(near but on
one of the two lines). The "Patterns" tab appears. Select "None" for
both Border and Area.
- Click on the Plot Area and use
the
handles to expand to the right and upward. Move as far right as the
chart allows. The vertical distance should be selected such that the
graph is as large as possible without having the Y-axis label or the
legend interfere with viewing the variables.
- Right-click on the X
(quantity) axis,
select the Format tab, and set the font at Bold, Size 9. Also turn off
the Auto Scale; leaving this option on results in changes in the size
of the labels when the graph size changes.. (The dialog box is cropped
to save space.)
 Do
the same for the Y ($ per
unit) axis.- Right-click on the demand
curve, and
select "Format Data Series." The dialog box at the right appears. Click
on the down- arrow at the right of the "Color" window and select a
Blue.
Click on "Weight" and increase the width of the line.
- Do the same with the supply
curve, but
make it dashed and wider than it currently is. Select Black.
The resulting
graph is below. The graph now has less clutter, allowing the
demand
and supply curves to appear more distinctly.
A bit of lost space at the
right could have been regained
by setting
the X data range from 0 to 60 (or 50). Setting data ranges will be
discussed below.
 Also,
the equilibrium
could have been moved closer to
the center.
In this example, we selected the data range or quantity and then
posited functions
for demand and supply that would result in equilibrium over that range.
Below,
we show how to make the data range depend on the equilibrium
value and indicate some advantages of that approach.
Before
moving
to more substantive material, consider a bit more formatting. The
graph in Figure 7 contains the same content as the one in Figure 4, but
is more
readable and a bit more informative. The changes are achieved as
follows:
- Select all columns that appear on
the
screen (and perhaps a few more--we selected A:Z). Click on the "Fill
Color" icon and select color (we use light turquoise). (The same
result can be achieved via the "Format/Cells/Patterns" menu entry or by
right-clicking on the selected cells and selecting "Format
Cells/Patterns." a color (we use light turquoise). (The same
result can be achieved via the "Format/Cells/Patterns" menu entry or by
right-clicking on the selected cells and selecting "Format
Cells/Patterns
 Select
all columns that appear
on the
screen (and perhaps a few more--we selected A:Z). Click on the "Fill
Color" icon and select a color (we use light turquoise). (The same
result can be achieved via the "Format/Cells/Patterns" menu entry or by
right-clicking on the selected cells and selecting "Format
Cells/Patterns."
. 
- Type
"Demand Price" and
"Supply Price" in
place of "Demand" and "Supply," Select these two cells, and right-click
on the selection. Under "Format Cells" select "Alignment" and turn on
"Wrap Text." [See Source Data]
 In
the cell two cells above
"Demand
Price" type "Points on Demand" and in the cell directly above "Demand
Price" type "and Supply Curves." Then select this cell and the one at
its right and merge them. This can be done with the "Merge and Center"
icon. Alternatively, "Format Cells/Alignment/Merge Cells" yields the
same result.
 Select
the cell directly above
"Demand
Price" and the one directly above "Supply Price" and combine the two.
- Merge
the two cells above "Quantity."
- Insert grids and border.
To do this, use the "Borders" icon. First click on the grid and then on
the bold border.
- To
remove the grid line within "Points on Demand
and Supply Curves," select the two cells and "Format Cells." Select the
"Border"
tab, and click until the line disappears.
- Change
labels on "Demand Price" to "Demand," likewise "Supply." To do this,
right-click on the graph and select "Source Data” and then “Series."
Click on "Demand Price" and type "Demand" into the cell to the right of "Name."
Repeat
for "Supply
Price."
|
|
|
|
Critical
Values and Data Range Selection
The example above depicts a
demand and supply curve. It does not
directly address equilibrium, disequilibrium, or the effects of change.
All of these require attention to specific values of the variables in
question. The section addresses three sets of issues related to these
concerns: how to depict critical values, how to allow change to occur,
and how to adjust the data set to accommodate the preceding two
concerns.
We begin by representing the equilibrium value for quantity and price.
The added variable "For showing P & Q" consists of a series
of "=if" statements like this one: =IF(B5<=D$19,D$20,-99999).
The first entry is in Cell D5 and equals the equilibrium price if Q is
less than or equal to the equilibrium quantity. Otherwise, a large
negative number is returned. Note that the reference to quantity (B5)
is relative--that is, the next cell down refers to C5. In contrast, the
references to D19 and D20 (equilibrium values of Q and P) are absolute.
(Actually, mixed, since the row reference is fixed, but the column
reference is relative.)
The
graph reveals one bothersome feature: The dashed line that shows
equilibrium does not work quite right. This concern is addressed
shortly.
|
|
 One
change must be made for
these data to be graphed. The Y-axis must not be allowed to display
large negative values. The Format Axis menu selection allows scale to
be
set. The dialog box is at the right. "Auto" is turned off for the
minimum value, and a value of zero is inserted.
The X axis is formatted in an
identical fashion.
Briefly consider how the other
tabs in the Format Axis dialog box might be used:
- Patterns: Sometimes the axes do not show up clearly when
graphs are printed. The lines can be made heavier using this tab. Other
changes can be made to the appearance of the axis.
- Font: Font and font size can be selected. Also, the "Auto
scale" option can be activated or turned off.
- Number: The default is to use the number category (general,
number with decimals specified, currency, and so forth). In most cases
"general" is the best option.
- Alignment: Sets the alignment of the text. The defaults are
horizontal for the X axis and vertical for the Y axis.
This selection of range option
can be useful for other purposes, in
particular to focus on a range of values that are of particular
interest.
|
|
Curve
Shifters, Critical Values, and the Data Range
The figure below replicates
the one above with one exception. The user is now given a way to shift
the demand curve. As with the case above, the dotted line intended to
show the equilibrium P, Q pair does not work quite right.
Consider the failure of the reference
line to "drop" at the equilibrium quantity. The reason for this failure
is clear. The
equilibrium value is between 35 and 40. Q = 35, is the largest value
for which the "if" statement is true, so the reference line drops at Q
= 35 rather than at the equilibrium Q (37.14286), a value that is not
in the
data table..
Before repairing this problem,
consider a couple of other changes in
the sheet. First, the user may now specify the P-intercept for the
demand curve. Adding this change requires changing the way variables
are created. The inverse demand curve is now defined as Q =
=$D$22-5*B5, where $D$22 is the cell containing the P-intercept, and B5
is the quantity for this row. Also, the cells containing the
equilibrium
quantity and the equilibrium price are adapted to allow for P-intercept
to change.
 Data range selection. The
difficulty involved in displaying the equilibrium value is easily
repaired. Set one of the
values in the Demand Price column equal to the equilibrium price and
then create other values in relationship to these values. The table at
the right
shows the formulas that are used to create the relevant data set.
The
cell with "=D18"
contains two "flags." The one in the left corner warns this cell's
formula is unlike those adjacent to it. The one in the right corner
indicates the presence of a comment. See Comments
We now
extend the analysis in three directions, to:
- allow the user to select the
height of the demand curve.
- allow the user to select the
price. The user can specify a price. This price may or may not be the
equilibrium price. The graph shows the resulting quantity demanded and
quantity supplied at that price, and it shows the equilibrium values
- amend the data set to ensure
that both prices (equilibrium
and user-selected) and the associated quantities can be indicated with
appropriate reference lines. This involves solving for the values
analytically and placing those values into the data set, in the same
way
that the equilibrium value was placed there in this display.
Making room for the additional
information can cause the screen display
to become quite cluttered. According, we move the data to be graphed
off the screen, placing it in Columns AA and following. Doing so can
require some lost time in moving between the graph and the data. To
facilitate this movement back and forth, we add one more topic:
- Navigation links within the
workbook.
This
extension
involves a number of steps that are not inherently related but that
appear together as we develop this material.
|
User
Selection of Values.
Users may be allowed to select values in any of a number of ways. We
describe these three:
- Insertion of the value,
- Use of a scroll bar to select a value, and
- Selection of a value from a pre-defined menu of valid
values.
The easiest option, of course,
is simply to let the user insert a value
into a designated cell (P-intercept on the demand curve, for example).
This could have the drawback of having values entered that result in
nonsensical solutions (for example, P-intercept on the demand curve
below that of the supply curve, resulting in an "equilibrium" of a
negative quantity). We do not use this option. Rather, we use a scroll
bar to designate values over which the demand intercept can range, and
a valid list for choices of prices that users may specify.
 Clicking
on
the forms menu
allows selection of a scroll bar. Once the bar is placed on the screen,
it can be reoriented and re-sized. Right-click on the bar, and then
click on a handle to re-size or reorient it. Then it can be assigned to
a cell and
it defines the range of values that the user can place into the cell.
In this case, the minimum value
for the P-intercept is 100 (higher than the Supply P-intercept of 20),
and the maximum value is 400. Clicking on the scroll bar changes the
P-intercept in increments of $50. The format control on the right
accomplishes this result.
 In
some cases, the values that users may select need to be less than 1.
Suppose that Cell D4 contains an elasticity (stated as a positive
number). To allow elasticity values between, say 0.1 and 2.5, specify
some other cell (say Cell Z1) as the "Cell link" and then put a
formula in cell D4. In the present hypothetical case, format Cell Z1 as
follows: Minimum value = 10, Maximum value = 250, Incremental change =
10. Then place "= Z1/100" in Cell D4. If you prefer to state elasticity
as a negative number, then "= -Z10" becomes the formula in Cell D4.
Sometimes, the better way
to provide the user a set of values from which to choose is to create a
list of valid values and make that list available. The list may be part
of the data table, or it may be specially created for the task at hand.
In this case we created a table. If a cell refers to a list of valid
data, users may not insert values, but must select one from the list.
To create such a list, click on
the cell and select menu option "Data/Validation."
 The
figure at the right
shows the dialog box
for a list of valid data, which appear in Cells AC20:AC30. This list is
created as follows: AC20 contains the minimum valid price (where Q S
= 0), AC30 contains the maximum valid price (where Q D
= 0), and the remaining cells contain values in equal increments
between the two. Clicking on the "Input Message" tab allows adding a
title for the list of legal values and for providing a brief
description of that list.
|
 The
cell in which
this list appears now contains the value $160.00. A
comment has been entered in order to call attention
to the cell. Clicking on the cell causes a list of values to drop down.
It also causes a title (Price) and a message (Click on the down arrow
and select a price from this list. The note under "Price" appears next
to the cell with $160.00 in it; we moved the note to the position shown
in order to keep all information visible.)
This figure shows the graph and
related material that results from a
choice of P-intercept and price level. One
aspect of this figure warrants attention. Three values are now
indicated
by reference lines: the equilibrium quantity (and price), and the
quantities demanded and supplied at the user-specified price.
 To
ensure
that the reference lines work properly, the Quantity data are
reconfigured slightly. See the table at the right. The first flagged
entry (Q = 28 in this case) is
the minimum of the range D9:D11 (Equilibrium Q, Q DS
at selected P, and Q S at selected P). The
entry in the flagged cell contains "=min(d9:d11)." The second of the
three flagged cells (Q = 40 here) contains =median(d9:d11" and the
third flagged cell contains "=max(d9:d11." Other cells are filled in
with formulas like this one for the cell immediately after the median
value: "=AA15+(AA$18-AA$15)/3." Dealing with more than three critical
values would require logical (=if) statements.
The
table at the right contains part of the data used to draw the graph.
Including all of the
data (specifically that required to draw the reference lines, in this
example) would make the screen unduly cluttered. In many cases, moving
the data entirely off screen improves the presentation. We typically
move data to columns AA and following. Doing so creates a problem,
however, in that one often wishes to quickly compare graph and table.
This can be facilitated for the creation of ways to allow rapid
movement among points on the sheet (or among sheets).
Navigation
can be
facilitated by the use of either macros or links.
Links have a couple of advantages over macros. First, they are simpler
to implement. In addition, one can place a comment in a cell in which
the link is embedded, thus providing the user with an indication of
what to do and what will result from clicking on the link.
Creating
a navigational link is
described here. An alternative approach is to use buttons and to assign
navigational macros to the buttons. See Macros In
Cell Z4, just to the left of the data, we insert a link to Cell A1.
Its display says "View Graph." The dialog box is at the right. To
invoke the dialog box, click on the cell and select Hyperlink.
A similar
link is placed
in Cells B12:C12 (which have been merged). This link takes the user to
the table of data. Another link returns to the menu sheet. See Links
|
|
The figure above shows how a user
can be allowed to select the height of the inverse
demand curve. It shows the demand curve for the selected P-intercept.
It does not, however, allow comparison of two demand curves. Below, we
show how a user can shift a demand curve
via a change in a variable affecting demand and how the new demand
curve compares to the initial one.
 The
figure at the right applies the
techniques developed above to show how to allow a
shifting demand curve.
One
issue remains before we consider other types of graphs. In this
example, using linear demand and supply curves makes finding the
equilibrium values of Q and P easy. Once those values are in hand,
setting up the data range is straightforward. Suppose, however, that
the demand curve is nonlinear ( e. g.,
constant elasticity demand curve). In that case, finding the
equilibrium values via analytical techniques is not easy and might not
be possible.
Fortunately, Excel's Solver
tool allows a numerical approach to finding a very close approximation
to the equilibrium values. The figure just below shows a graph in which
Solver has
been used to analyze a market in which the demand curve is of the
constant elasticity sort. See Solver
 The
figure at the right shows a constant elasticity demand curve coupled
with a linear supply curve. Obtaining the equilibrium values becomes
problematic because setting the equation for the inverse demand curve
equal to that for the inverse supply curve does not result in an
generally-solvable equation. We invoke Solver at this point, having
Solver find the quantity at which the absolute difference between the
demand price and the supply price is minimized. The figure at the right
shows the results. An excerpt from the Solver dialog box appear below.
Note that in this case no constraint is imposed (other than an implicit
restriction that the value in Cell $D$12 be non-negative).
 The
graph at the right
shows two demand
curves. Users may have changed PY and/or income. The parameters are set
in the preceding sheet
and are copied to this one. See Copying
& Moving Sheets
Solver must be run twice (once in each of the two sheets) to show the
two equilibria.
|
|
Line and Area Graphs
The
scatter plot graph offers
great flexibility and can be used to
illustrate many, perhaps most, of the principles of economics. In some
cases, however,
the scatter plot will not do. It cannot directly show area, though
areas can be described by reference to the graph, and it cannot be
extended to three dimensions. This section develops line and area
graphs. The following section extends the analysis to three dimensions.
The figure here shows a graph of the effect of an excise tax. Relevant
areas
show up distinctly in that graph.
 At
first pass, this graph looks
much like the one in the worksheet
"CleanedUp" except for the areas. Adding area charts requires a
complete revision of the chart types, however.. Before considering
that, however,
note that the graph contains two types of graphs. The first four are
Area
charts, and the last three are Line charts (which look like the graphs
drawn earlier). For now, focus on the area charts. With the area charts
appearing as they do, we can easily see the following:
- tax revenue,
- deadweight loss due to the tax,
- remaining consumer surplus and
producer
surplus,
- and the prices paid by buyers and
received by sellers.
To show these requires some
attention. One must first create the area
graphs. This is straightforward, and is treated briefly below. In
addition, one must set the variables in a specific order. To do this,
right-click on any of the areas and select, "Format Data Series/Series
Order." The lower-order series lies above those listed above it and
masks those areas. Thus,
the whole area under the demand curve is blue but most of it is masked
by other areas. "New P&Q Area" masks part of "Demand Area" and in
turn part of "New P&Q Area" is masked by "Net P&Q Area"
and, finally by "Supply Area." "Supply Area" would normally be white,
since the area
under the supply curve is relatively uninteresting for this
illustration. We color it and insert texture here to call attention to
its presence and role in the graph.
It is not necessary to have
"Supply Area" and "Supply" separately, as
is done above (likewise, for "Demand"). Not doing so, however, can add
strange looking lines to the graph. If "Supply Area" had a border made
up of the dark dashed line segments that denote Supply, then this
border would appear at the right-hand side of the graph.
 Achieving
the areas, as noted
above, requires substantial revision of
the sheet. First, the new variables were added. Once they were added,
the "Chart Type" menu was opened and the graph type indicated in Figure
22 was selected.
This selection cause a problem, however, because Area
chart and X/Y (Scatter) charts cannot be combined. They handle the X
axis differently. The X/Y chart treats the X values as numbers, while
the Area and Line charts take the X values to be integers (1, 2, 3,
...). Figure 23 shows this is done. In
the present
example, the values that appear on the X axis are in cells
B5:B17. The values in these cells must be of equal interval length,
unlike the values in X/Y (Scatter) charts.
Line charts treat the
variables the same way. Accordingly, all
variables that had been graphed as X/Y charts in previous graphs are
now
converted to line
charts.
Having the X-axis values appear at equal intervals causes a problem
regarding data entry. If a single critical value is to determine the
data range, then the data must be entered so that the intervals
are a
constant fraction of
that critical value. If two critical
values are used to
determine the data, then the intervals between all data points must be
a constant fraction of the difference between the two data points. In
general, more
than two critical values cannot be accommodated (unlike X/Y plots,
which
can accommodate any number of such values).
In the current example, we avoided this issue altogether. Rather than
dealing with critical values, we specified the values of the tax in
increments of $35, so that the change in Q is always 5 units. (The
slope of the excess demand curve is -7 and the data are entered in
increments of 5; hence, the $35 increment.)
In the next section, which
examines three-dimensional graphs, we use
the critical values of X and Y as the points around which to build the
data.
|
Three
Dimensions
Dealing with three-dimensional
graphs is much like dealing with
area or line graphs. The material below illustrates the use of 3-D
graphs by showing how to set up a data set
and graph a "utility hill."
 As
with other chart types,
begin
with the "Chart Type" dialog box. Select as indicated at the right. The
usual options regarding legends and axis labels are available.
In the present case, we wish to
maximize utility subject to a budget
constraint. A familiar Cobb-Douglas utility function is used for the
illustration. The figures below contains the requisite information. We
set PY ≡
1, so that Spending = PX*X + Y. The Solver dialog
box, which indicates that the problem is to select X and Y values that
maximize utility subject to Spending = Income.
The cell to the right of "Utility =" is Cell $C$5, the value to be
maximized. This maximization occurs by changing the contents of Cell
$C$6 and $C47, or X and Y. This is subject to the constraint that
Spending (Cell $C$11) equals income (Cell $C$8). Values of X and Y are
entered as equal increments of the optimal values, and the resulting
utility levels appear in the table below. The resulting values generate
the graph.
|
A final point
relative to developing 3-D graphs involves the formulas. Figure 28
shows the first 3 values of X and Y. As stated above X & Y are
created by
beginning at zero and adding 1/10 of the critical values. Note the use
of relative and absolute cell references.
In the formulas for utility, all references to the function parameter α
(Cell C10) are absolute. Regarding X, the row is
absolute (always row 3), and
the column is relative. With Y, these reverse: column references are
absolute (all Y values are in Column E) and X references are relative.
|
Glossary,
Notes, and Navigation
An alternative to using links is to create macros and
assign buttons to execute them. To
insert a button, click on the button icon and then click on a location
for the button. The button can be re-sized, text can be added and
edited, and the button can be assigned to the macro.
Copying
and Moving Sheets
 To
copy or move a sheet,
right-click on the tab at the bottom of the sheet. Then move the cursor
to the name of the sheet which the copied or moved sheet is to precede.
To move the sheet, click "OK"; to copy the sheet, click "Create a copy"
and then "OK."
Cell
Protection
Excel can protect specified cells
from accidental changes. Having critical cells protected is a good way
to prevent having to reconstruct models. The following way of selecting
a subset of cells to protect while allow change in other cells (e. g.,
to allow curve shifting) is the following. It appears, along with
others, at the following site:
http://reviews.cnet.com/5208-6130-0.html?forum
ID=7&thread
ID=28596&message ID=323651.
By default, all Excel worksheet cells have locked definitions
associated to them. This function will take effect only if the
worksheet has been protected, and it covers all cells in the protected
worksheet or workbook.
If you want to protect only specific cells, such as those with
formulas, you would first need to unlock all the cells on the
worksheet, lock the cells that you want to protect, and protect the
worksheet.
Steps:
- Select all of the worksheet (hold down the Ctrl
key and A) or all of the working cells in your sheet (hold down the
Ctrl, then select Cells).
- When the selection has been made, right-click
the mouse and select Format Cells from the drop-down window.
- Click the Protection tab, deselect the Locked
check box, and click OK.
- Now click the specific cells that you want to
protect. You can select them all at once by holding down the Ctrl
button on your keyboard while you click each cell.
- Once all cells are selected, right-click the
mouse again, go to Format Cells, click the Protection tab, and this
time select the Locked check box and click OK.
- The final stage is to protect the worksheet.
Click Tools, go down the menu to Protection, click Protect Sheet, and
then
click OK. A password is optional here. If you want to password-protect
it, enter a password (you will need to reenter it to confirm), then
click OK. Don't forget your password because there is no way to
retrieve it.
- Save your work!!
Now your worksheet is protected, and locked cells cannot be tampered
with unless you follow step 6 to Unprotect sheet or open it with the
protection password you assigned to it.
Comments
Placing comments in cells can be useful
both to the developer of the workbook and to users. For the developer,
these comments serve as notes about how the workbook was developed. For
the user, the comments can contain information either about the
mechanics of the spreadsheet or about the theory involved. The graph
below shows a comment in a cell. This comment appears whenever the
cursor is placed on the cell.
To enter a comment, right-click on a
cell. The comments may be edited
at any time. Right-click on a cell and add content or format.
The otherwise gratuitous reference to Cobb Douglas is inserted to show
a couple of the changes. The original input was
Q = ALaK(1-a).
Selecting "a" and right-clicking brings
up a menu. Selecting "Format
Comment" allows creating superscripts and conversion to the Symbol
font. The same was done for (1-a).
Data
Validation
One way to
constrain the input
that a user can provide to the spreadsheet is to create a list of valid
data. These data should be chosen in a way that best serves the purpose
of the workbook being used and which will not lead to analytically
meaningless values (e. g. negative quantities). For an example of data
validation see the text
on this
subject.
Links
Links within the
workbook can
facilitate movement, allowing quick comparison of information in graphs
with that in tables, or information in various sheets. Links may be
established in either of two ways:
- by inserting a Hyperlink and entering the appropriate
information, as indicated in the text, or
- by recording a macro.
If the material in the cell to which the link refers is changed, the
link must be changed as well. Excel
will not make these changes automatically.
Macros
and Buttons
Any series of steps that are to
be repeated may be recorded as a macro. The macro can be associated
with a "button." Buttons can be added to the workbook by opening the
"Forms" menu under "View/Toolbars." Once created a button can be
re-sized and moved. The name of the button can be changed by
right-clicking on the button and selecting "Edit Text." A macro may be
assigned to (activated by) a button; right-click on the button, select
"Assign Macro," and select the appropriate macro from the list of
recorded macros.
 Macros
may be used to facilitate navigation within a workbook or to
achieve other ends like resetting a set of values in specified cells.
With
both links and macros, the cell
references are not adjusted by Excel if rows or columns are added or
deleted. For macros, the adjustment requires editing the macro, which
involves opening the Visual Basic program. One need not understand
Visual Basic to achieve the needed changes. Right-click on the button,
select "Assign Macro" and "Edit." The cell range that is currently
activated can be changed. Once the change is made, select "File/Close
and Return to Microsoft Excel."
Excel
must be allowed to activate macros. Excel
has four levels of security. The default is to delete macros before
opening a workbook. The medium security level, the one suggested here,
prompts the user before allowing macros. Set this level using the menu
entry "Tools/Macros/Security." Select Medium. (The lowest level accepts
all macros.)
Return
to
text
Number format
For most purposes using
one of the first three options works well for displaying economic data.
We often select "Number" and specify 3 decimal places for quantities
and "Currency" with either 0 or 2 decimal places for prices and
costs.
 In
one important case, however, selecting "Custom" can be of value.
This is when an equation results in negative numbers, but negative
numbers make no economic sense. For display purposes, it is easy to
make negative numbers disappear. In the case above, positive numbers
appear in the form $1.23 (zero as $0.00) and negative numbers do not
appear. Adding a second semicolon (as in the entry above the
highlighted one) would cause zero values to disappear.
The negative
numbers are still visible to Excel, however, and will appear in graphs
unless the
data range for the relevant axis has been defined so as to exclude
them. For more details enter "custom number format" in the "Type a
question
for help" box in a spreadsheet.
For an example in which custom formatting is used to hide negative
values, see Index of
Excel Modules at
http://www.idbsu.edu/econ/lreynol/web/excel_index.htm.
Return
to text
Solver
The Solver tool in
Excel can be
used to determine the value at which an equilibrium occurs, or the
values of a set of goods that maximizes utility (among many other
uses). In the former case, the Solver problem is an unrestricted
one--find the quantity at which the absolute vertical difference
between the demand curve and the supply curve is minimized. In the
second case, maximization is subject to the income constraint. Knowing
the optimal value(s) of variable(s) is useful in graphing, because the
data range can be constructed in a way that includes the relevant
value(s).
Approximation without
Solver. On occasion, Solver might not be the best way to
approximate the
optimal (or equilibrium) value of a variable. One might not wish to
take the time to introduce the user to Solver. A rather tedious routine
can yield an approximately correct solution, but it does not  result
in
the correct solution being part of the data range. We illustrate with
the demand/supply example. Follow these steps, as illustrated in the
figure at the right:
- Define a variable equal to the absolute value of the demand
price less the supply
price (AbsDiff here).
- In a separate cell (probably the one below this variable in
the
data set), type =min(X7:X57)--assuming that the variable defined above
is in column X and has 50 data points, as we do in this illustration.
- Create a new variable in column Y with this statement
=IF(X7=X$58,(V7+W7)/2,-999). This assumes that the inverse demand
(price) data
are in column V and the inverse supply data are in column W. The
approximate equilibrium price is the average of these two. Be sure to
include the absolute reference to cell X58,
where the minimum value of the absolute difference is stored. This
variable (called Price here) will have a single positive value.
- Store the maximum value (the only positive one) of this
variable in cell Y58.
- Create one more variable in column Z (called Price &
Quantity here) as
follows: =IF(W7<=Y$58,Y$58,-999999). Again, use the absolute
reference. This says that if the supply price is less than or equal to
the equilibrium price, place the equilibrium price in the cell. As soon
as this ceases to be true, a large negative number is entered. Adding
this variable to the graph creates the requisite "drop" (approximately)
at the equilibrium values of price and quantity.
The line drawn this way will not be exactly right, so the number of
values should be fairly large, probably 50 or so equally-spaced values
of Q. In this example, the Solver approximation yielded a price
of $436.78 and a quantity of 208.392 units. The crude
approximation created as indicated here yielded corresponding values
of $437.63 and 208.815.
Source
Data
 The
Source Data dialog box (right-click on the graph or click on the graph
and select Chart/Source Data from the menu) allows for a number of
changes. The variable name can be changed here, over-riding the name in
the spreadsheet. Variables can be removed from the graph, or added to
it. (It' probably easier to add variables by clicking on the graph and
then on Chart/Add Data.) Also, variable ranges can be changed. Suppose
that it would improve the graph if the first two observations for
demand were deleted (this is especially likely to be helpful when
inverse functions are being graphed). Change the X-Values from
$AA7:$AA19 to $AA9:$AA19. Do
the same for the Y-Values. Failing to change both will shift the
demand curve.
If the demand curve had been entered with Price as the independent
variable and
Quantity Demanded and Quantity Supplied as the dependent variables,
Source Data could be used to invert the curves. This is much more
tedious
and subject to error, however, than initially specifying inverse demand
and supply curves. It does happen occasionally that switching the axis
as indicated here cannot be avoided.
Return to text
Topics
covered
For an excellent set of macroeconomic
material (and a good example of the use of Visual Basic) see the
following site:http://www.wabash.edu/EconMacro/home.htm.
For a larger set of material that we have
developed see [to be added later]. Most of the material at this site
involves microeconomic topics. Also
included is a set of international trade material.
Return
to text
Visual Basic
For an excellent example of the use of
Visual Basic in a workbook (and an extensive set of macroeconomic
material) see the following site:http://www.wabash.edu/dept/economics/MacroExcel/home.htm
Return
to
text
Author information to be added |
