You are on the Home/Excel/Charts/Supply & Demand page
Google
Web This Site

Drawing Supply and Demand curves in Excel

Introduction to Demand and Supply curves

Creating the market Demand and Supply curves from the preferences of individual producers and suppliers

How the step graph for a small market becomes a smooth curve for a larger market

 

Introduction to Demand and Supply curves

Supply and Demand curves play a fundamental role in Economics.  The supply curve indicates how many producers will supply the product (or service) of interest at a particular price.  Similarly, the demand curve indicates how many consumers will buy the product at a given price.  By drawing the two curves together, it is possible to calculate the market clearing price.  This is the intersection of the two curves and is the price at which the amount supplied by the producers will match exactly the quantity that the consumers will buy.

The process is illustrated in Figure 1.  The downward sloping line is the demand curve, while the upward sloping line is the supply curve.  The demand curve indicates that if the price were $10, the demand would be zero.  However, if the price dropped to $8, the demand would increase to 4 units.  Similarly, if the price were to drop to $2, the demand would be for 16 units.

The supply curve indicates how much producers will supply at a given price.  If the price were zero, no one would produce anything.  As the price increases, more producers would come forward.  At a price of $5, there would be 5 units produced by various suppliers.  At  a price of $10, the suppliers would produce 10 units.

The intersection of the supply curve and the demand curve, shown by (P*, Q*), is the market clearing condition.  In this example, the market clearing price is P*= 6.67 and the market clearing quantity is Q*=6.67.  At the price of $6.67, various producers supply a total of 6.67 units, and various consumers demand the same quantity.


Figure 1

There is no reason why the curves have to be straight lines.  They could be different shapes such in the examples below.  However, for the sake of simplicity, we will work with straight line demand and supply functions.

 

Creating the market Demand and Supply curves from the preferences of individual producers and suppliers

Price Product bought
by consumer
Total demand
for product
More than 20 None 0
20 A 1
15 B 2
10 C 3
8 D 4
3 E 5
Table 1

In the examples above, the chart contained smooth curves.  While such a curve is an excellent approximation when there are many producers (or consumers), each of the curves is actually made up of many small discrete steps.  Each of these steps represent the decision of a single individual (or company).  We will see next how these curves are constructed based on the decisions made by individual entities.

We construct the demand and supply curves for a very small market.  Suppose there are just 5 consumers and each demands one unit of the product.  However, they have distinct prices at which the product is valuable enough for them to buy it.  Table 1 shows the price at which each individual will buy the product.

 

Creating the curves in Excel

By now, the attentive reader may have noted a quirk specific to the analysis of demand and supply.  In the analysis, it is the price that is first set (i.e., it is the independent variable) and the quantity is the result of the analysis (i.e., the dependent variable).  However, on a Demand and Supply graph, the quantity is shown on the horizontal axis and the price on the vertical axis.  This reverses the norm for charting, in which the horizontal axis represents the independent variable and the vertical axis the dependent variable.

Excel sticks to the norm and expects that in a two-column XY Scatter chart, the first column is the independent variable to be shown on the horizontal (x) axis.  In our analysis, we put the price -- the independent variable -- in the first column, but then plot it on the vertical axis.  The easiest way to handle this 'difference in expectations' is with an extra column as shown on the right.  Note that column D is a copy of column A.  It is possible to plot the data without use of the extra column but it requires a little extra work.

 

 

Once the price data set is duplicated in column D, plot columns C and D in a XY Scatter chart as shown on the right. 

 

Next, create the steps needed to complete the chart by adding X and Y error bars.

First, add the data for the Y error bars.  In F2, enter the formula =D2-D3.  Copy F2 down all the way to F6.

To add the data for the X error bars, in G3, enter the formula =C3-C2, and copy G3 all the way down to G7.  The data for the error bars should look as below.

Double click the plotted series.  In the resulting Format Data Series dialog box, set up the X-error and Y-error bars as shown on the right.

 



 

Double click any of the error bars and choose the pattern that does not have the cross-bar.  Finally, double click any of the series markers and format the series pattern to have no line and no marker.

Both the dialog box and the result are shown on the right.

 



 

Similar to the demand curve, the supply curve starts with the data in the table below.  It shows the prices at which different producers find it profitable to supply the product.  For the sake of simplicity, we assume each producer makes just one unit.  The graph on the right shows the supply curve on the same chart as the demand curve.  Each data point also has a label, which indicates which consumer (or producer) will demand (or supply) at that price.
 
Price Product produced
by supplier
Total supply
of product
Less than 2 None 0
2 V 1
5 W 2
10 X 3
12 Y 4
15 Z 5

The two graphs intersect at a price of $10.  At that price, three consumers (A, B, and C) will buy the product, and three producers (V, W, and X) will make it.  Consequently, at (P*=10, Q*=3) the market will clear.  Two consumers, D and E, who value the product at less than $10 will not buy anything and two producers, Y and Z, whose production costs exceed $10 will chose to not supply any product.

How the step graph for a small market becomes a smooth curve for a larger market

In a truly competitive market, there will be many consumers and producers.  The first graph below shows the demand curve for a market with 20 consumers.  The second graph shows the demand curve for a market with 50 consumers, the third a market with 100 consumers and the last a market with 1000 consumers.  As more consumers participate in the market, the demand curve takes on an increasingly smooth look.


Demand Curve for market with 20 consumers


Demand Curve for market with 50 consumers

 


Demand Curve for market with 100 consumers


Demand Curve for market with 1000 consumers