How To Draw Demand Curve In Excel

Empirical Project 7 Working in Excel

Part 7.1 Drawing supply and demand diagrams

Learning objectives for this part

catechumen from the natural logarithm of a number to the number itself

draw graphs based on equations.

Starting time download the information on the watermelon market. Read the Data dictionary tab and make sure you know what each variable represents.
Download the newspaper 'Suits' Watermelon Model' on the watermelon market.

The information is in natural logs: for example, the numbers in the price cavalcade are the logs of the prices of watermelons in each year, rather than the prices in dollars. Before plotting supply and demand curves we will first practise converting natural logarithms to numbers. In Part seven.2 we volition discuss why it is useful to express relationships between variables (for example, price and quantity) in natural logs.

To make charts that look like those in Figure 1 in the paper, yous demand to convert the relevant variables to their actual values. Excel's EXP function does the inverse of the LN part, converting the natural log of a number to the number itself. (See Excel walk-through iv.3 for an example of the use of the LN function.)

Create two new variables containing the actual values of P and Q.

Plot carve up line charts for P and Q, with time (in years) on the horizontal centrality. Brand certain to characterization your vertical axes appropriately. Your charts should look the same as Figure 1 in the paper.

At present nosotros volition plot supply and demand curves for a simplified version of the model given in the paper. Nosotros will define Q as the quantity of watermelons, in millions, and P every bit the toll per thousand watermelons, and assume that the supply curve is given by the following equation:

Technical annotation

Whenever log (or ln) is used in economics, it refers to natural logarithms. Since this equation shows the cost in terms of quantity (instead of quantity in terms of price), it is technically referred to as the inverse supply curve. Still, we volition be using the terms 'supply curve' and 'demand curve' to refer to both the supply/need curve and the inverse supply/need bend.

Using the same notation, the post-obit equation describes the demand curve:

To plot a curve, nosotros demand to generate a series of points (vertical axis values that represent to particular horizontal axis values) and bring together them up. First we volition work with the variables in natural log format, and and then we volition convert them to the bodily prices and quantities then that our supply and demand curves volition be in familiar units.

In a new tab on your spreadsheet:

Create a table as shown in Figure vii.2. The first cavalcade contains values of Q from 20 to 100, in intervals of v. (Recall that quantity is measured in millions, so Q = 20 corresponds to 20 million watermelons.)

Q	Log Q	Supply (log P)	Demand (log P)	Supply (P)	Demand (P)
20
25
…
95
100

Figure 7.2 Computing supply and demand.

Convert the values of Q to natural log format (2d column of your table) and utilise these values, along with the numbers in the equations above, to calculate the corresponding values of log P for supply (third column) and demand (fourth column).

Use Excel's EXP part to convert the log P values into the actual prices, P (fifth and sixth columns).

Plot your calculated supply and need curves on a line chart, with price (P) on the vertical centrality and quantity (Q) on the horizontal axis. Make certain to characterization your curves (for example, using a legend).

exogenous: Coming from outside the model rather than beingness produced by the workings of the model itself. Meet too: endogenous.

During the time period considered (1930–1951), the market for watermelons experienced a negative supply shock due to the Second World War. Supply was limited because production inputs (land and labour) were beingness used for the war effort. This shock shifted the entire supply curve because the crusade (2d Earth State of war) was not part of the supply equation, merely was external (also known as being exogenous). Before doing the side by side question, draw a supply and demand diagram to illustrate what you lot would wait to happen to price and quantity as a effect of the shock (all other things being equal). To see how oil shocks in the 1970s caused past wars in the Middle East shifted the supply curve in the oil market, see Section 7.13 in Economy, Society, and Public Policy.

At present we will use equations to evidence the furnishings of a negative supply shock on your Excel chart. Suppose that the supply curve after the stupor is:

Add the new supply curve to your line chart and interpret the outcomes, as follows:

Create a new cavalcade in your table from Question 2 called 'New supply (log P)', showing the supply in terms of log prices afterward the shock. Brand another column called 'New supply (P)' showing the supply in terms of the bodily price in dollars.

Add together the New supply (P) values to your line nautical chart and verify that your chart looks as expected. Make certain to label the new supply curve.

Consumer and producer surplus are explained in Sections seven.vi and 7.11 of Economy, Club, and Public Policy.

From your chart, what can you say nearly the change in total surplus, consumer surplus, and producer surplus as a effect of the supply shock? (Hint: You lot may detect the following information useful: the old equilibrium point is Q = 64.5, P = 161.3; the new equilibrium point is Q = 55.0, P = 183.7).

Part 7.2 Interpreting supply and demand curves

Learning objectives for this part

give an economical interpretation of coefficients in supply and need equations

distinguish between exogenous and endogenous shocks

explain how nosotros tin can use exogenous supply/demand shocks to place the demand/supply curve.

You may be wondering why it is useful to limited relationships in natural log form. In economics, we do this because there is a convenient interpretation of the coefficients: in the equation log Y = a + b log X, the coefficient b represents the elasticity of Y with respect to X. That is, the coefficient is the percentage change in Y for a 1 per cent change in 10. To look at the concept of elasticity in more particular, see Section 7.viii of The Economy.

Supply curve:

Demand curve:

Employ the supply and demand equations from Part seven.1 which are shown here, and carry out the following:

Summate the price elasticity of supply (the percentage change in quantity supplied divided past the percentage modify in price) and comment on its size (in accented value). (Hint: Yous volition have to rearrange the equation so that log Q is in terms of log P.)

Summate the price elasticity of need in the same way and comment on its size (in absolute value).

Now we will use this information to have a closer look at the model of the watermelon market in the newspaper and interpret the equations.

The paper assumes that in practise farmers determine how many watermelons to grow (supply) based on last season'south prices of watermelons and other crops they could grow instead (cotton wool and vegetables), and the current political weather that support or limit the amount grown. The reasoning for using last season's prices is that watermelons take time to grow and are besides perishable, so farmers cannot wait to come across what prices will be in the next season before deciding how many watermelons to plant.

The estimated supply equation for watermelons is shown below (this is equation (1) in the paper):

dummy variable (indicator variable): A variable that takes the value 1 if a sure status is met, and 0 otherwise.

Here, C and T are the prices of cotton wool and vegetables, and CP is a dummy variable that equals i if the government cotton-acreage-resource allotment plan was in effect (1934–1951). This program was intended to prevent cotton prices from falling past limiting the supply of cotton, then farmers who reduced their cotton wool product were given government compensation according to the size of their reduction. WW2 is a dummy variable that equals 1 if the United states was involved in the Second World State of war at the fourth dimension (1943–1946).

Yous tin can read more about the authorities farm programs for cotton during this time period on pages 67–69 of the report 'The cotton industry in the United States'.

exogenous: Coming from exterior the model rather than being produced by the workings of the model itself. See likewise: endogenous.

endogenous: Produced past the workings of a model rather than coming from exterior the model. See too: exogenous

In this model, the dummy variables and the prices of other crops are exogenous factors that affect the decisions of farmers, and hence too affect the endogenous variables P and Q that are determined by the interaction of supply and demand. The supply bend (right-paw console of Figure 7.3) shows that if the cost rose with no modify in exogenous factors, so the quantity supplied by farmers would rise, along the supply curve. But if at that place is an exogenous daze, captured past a dummy variable, it shifts the entire supply curve past changing its intercept (left hand panel). This changes the supply price for any given quantity. (In this specific case of watermelons, the vertical centrality variable would be the log toll in the previous flow, and the horizontal axis variable would be the quantity in the current period).

Supply curve: Dummy variables shift the entire curve (left-hand panel) while changes in endogenous variables move along the curve (right-hand panel).

Figure 7.3 Supply bend: Dummy variables shift the entire curve (left-hand panel) while changes in endogenous variables motility along the bend (right-hand panel).

With reference to Figure vii.four, for each variable in the supply equation, requite an economical interpretation of the coefficient (for instance, explicate the outcome on the farmers' supply decision) and (where relevant) relate the coefficient to an elasticity.

Variable	Coefficient	95% conviction interval
P (price of watermelons)	0.580	[0.572, 0.586]
C (price of cotton)	–0.321	[–0.328, –0.314]
T (cost of vegetables)	–0.124	[–0.126, –0.122]
CP (cotton plan)	0.073	[0.068, 0.077]
WW2 (Second World War)	–0.360	[–0.365, –0.355]

Effigy 7.4 Supply equation coefficients and 95% confidence intervals.

Now we will look at the need curve (equation (3) in the paper). The paper specifies per capita demand () in terms of price and other variables. () is the demand curve intercept:

Using the demand equation and Figure 7.5, give an economic estimation of each coefficient and (where relevant) relate the coefficient to an elasticity.

Variable	Coefficient	95% confidence interval
P (cost of watermelons)	–i.125	[–ane.738, –0.512]
Y/N (per capita income)	one.750	[0.778, 2.722]
F (railway freight costs)	–0.968	[–1.674, –0.262]

Figure seven.5 Need equation coefficients and 95% confidence intervals.

Before, we mentioned that exogenous supply/demand shocks shift the unabridged supply/need bend, whereas endogenous changes (such every bit changes in price) result in movements along the supply or demand curve. Exogenous shocks that only shift supply or only shift demand come in handy when nosotros try to guess the shape of the supply and demand curves. Read the information on simultaneity below to empathise why exogenous shocks are important for identifying the supply and demand curves.

simultaneity: When the right-hand and left-hand variables in a model equation touch each other at the aforementioned time, so that the management of causality runs both ways. For example, in supply and demand models, the market price affects the quantity supplied and demanded, only quantity supplied and demanded can in turn affect the marketplace price.

The simultaneity problem Why we need exogenous shocks that shift just supply or need

In the model of supply and need, the toll and quantity we discover in the data are jointly determined by the supply and demand equations, meaning that they are chosen simultaneously. In other words, the market toll affects the quantity supplied and demanded, only the quantity supplied and demanded can in turn affect the marketplace price. In economics we refer to this trouble as simultaneity. We cannot estimate the supply and demand curves with price and quantity data alone, because the correct-hand-side variable is non independent, but is instead dependent on the left-hand-side variable.

In the watermelon dataset, the price and quantity we find for each year is the equilibrium of supply and need in that yr. The changes in the equilibrium from year to year happen equally a result of both shifts and movements along the supply and demand curves, and nosotros cannot disentangle these shifts or movements of the supply and demand curves without additional information. Figure 7.6 illustrates that in that location can be many dissimilar supply and need curve shifts to explicate the aforementioned information.

Figure vii.half dozen Many possible supply and demand curves tin explicate the data.

To address this issue, we demand to find an exogenous variable that affects one equation but not the other. That way we can be certain that what we observe is due to a shift in one curve, holding the other curve fixed. In the watermelon market, we used the Second World War equally an exogenous supply shock in Role 7.1. The war afflicted the amount of farmland dedicated to producing watermelons, but arguably did not affect demand for watermelons.

Effigy vii.7 shows how we can employ the exogenous supply shock to acquire about the demand curve. The solid line shows the function of the demand curve revealed past the supply shock. Under the supposition that the demand curve is a directly line, we can infer what the rest of the curve looks like. If we had more data, for example if the size of the shock varied in each period, and so we could utilise this information to learn more nigh the shape of the demand curve (for example, check whether it is actually linear). We apply similar reasoning (exogenous demand shocks) to identify the supply curve.

Figure 7.vii Using exogenous supply shocks to place the demand curve.