How to Draw Price Elasticity of Demand Curve in Excel TUTORIAL

Empirical Project vii Working in Excel

Part 7.1 Cartoon supply and need diagrams

Learning objectives for this function

• convert from the natural logarithm of a number to the number itself
• draw graphs based on equations.

• Kickoff download the data on the watermelon market. Read the Data dictionary tab and brand sure y'all know what each variable represents.
• Download the paper 'Suits' Watermelon Model' on the watermelon market.

The information is in natural logs: for instance, the numbers in the price column are the logs of the prices of watermelons in each yr, rather than the prices in dollars. Before plotting supply and demand curves we volition starting time practise converting natural logarithms to numbers. In Office 7.2 nosotros will discuss why it is useful to express relationships between variables (for example, price and quantity) in natural logs.

1. To brand charts that look like those in Effigy 1 in the paper, yous need to convert the relevant variables to their bodily values. Excel's EXP function does the inverse of the LN role, converting the natural log of a number to the number itself. (See Excel walk-through 4.three 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 axis. Make sure to label your vertical axes appropriately. Your charts should look the same equally Figure 1 in the newspaper.

At present we volition plot supply and demand curves for a simplified version of the model given in the paper. We will define Q as the quantity of watermelons, in millions, and P as the cost per thousand watermelons, and assume that the supply curve is given by the post-obit equation:

Technical notation

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 cost), information technology is technically referred to as the inverse supply bend. However, we volition be using the terms 'supply curve' and 'demand curve' to refer to both the supply/demand curve and the inverse supply/demand curve.

Using the same annotation, the post-obit equation describes the need curve:

To plot a curve, we demand to generate a series of points (vertical axis values that correspond to detail horizontal axis values) and join them up. First we will piece of work with the variables in natural log format, and then we will convert them to the actual prices and quantities then that our supply and demand curves volition be in familiar units.

1. In a new tab on your spreadsheet:

• Create a table as shown in Effigy vii.2. The first column contains values of Q from 20 to 100, in intervals of 5. (Remember that quantity is measured in millions, so Q = 20 corresponds to xx meg watermelons.)

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

Figure seven.2 Computing supply and need.

• Convert the values of Q to natural log format (second column of your table) and use these values, along with the numbers in the equations to a higher place, to calculate the corresponding values of log P for supply (third column) and need (fourth column).

• Use Excel'south EXP function to convert the log P values into the bodily prices, P (fifth and sixth columns).

• Plot your calculated supply and demand curves on a line nautical chart, with toll (P) on the vertical axis and quantity (Q) on the horizontal centrality. Brand sure to label your curves (for case, using a legend).

exogenous

Coming from outside the model rather than existence produced by the workings of the model itself. See also: endogenous.

During the time period considered (1930–1951), the marketplace for watermelons experienced a negative supply shock due to the Second World War. Supply was limited because product inputs (land and labour) were being used for the war effort. This shock shifted the unabridged supply curve considering the crusade (2nd World War) was not office of the supply equation, but was external (as well known as being exogenous). Before doing the next question, depict a supply and demand diagram to illustrate what y'all would expect to happen to price and quantity as a upshot of the shock (all other things existence equal). To see how oil shocks in the 1970s caused by wars in the Eye Due east shifted the supply curve in the oil market, see Section 7.13 in Economy, Gild, and Public Policy.

Now we will use equations to bear witness the effects of a negative supply shock on your Excel nautical chart. Suppose that the supply curve after the shock is:

1. Add the new supply bend to your line chart and interpret the outcomes, equally follows:

• Create a new cavalcade in your table from Question two chosen 'New supply (log P)', showing the supply in terms of log prices after the shock. Make another cavalcade called 'New supply (P)' showing the supply in terms of the actual cost in dollars.

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

Consumer and producer surplus are explained in Sections 7.six and 7.11 of Economy, Gild, and Public Policy.

• From your chart, what can y'all say about the modify in full surplus, consumer surplus, and producer surplus equally a result of the supply shock? (Hint: You lot may detect the following information useful: the old equilibrium point is Q = 64.5, P = 161.three; the new equilibrium indicate is Q = 55.0, P = 183.7).

Part 7.2 Interpreting supply and need curves

Learning objectives for this part

• give an economical interpretation of coefficients in supply and demand equations
• distinguish between exogenous and endogenous shocks
• explicate how we can employ exogenous supply/demand shocks to identify the demand/supply curve.

You may exist wondering why information technology is useful to limited relationships in natural log form. In economic science, we do this because there is a convenient estimation of the coefficients: in the equation log Y = a + b log Ten, the coefficient b represents the elasticity of Y with respect to X. That is, the coefficient is the percentage modify in Y for a 1 per cent alter in X. To look at the concept of elasticity in more than detail, see Department 7.8 of The Economic system.

Supply curve:

Demand bend:

1. Use the supply and demand equations from Function 7.i which are shown here, and carry out the post-obit:

• Summate the price elasticity of supply (the percentage change in quantity supplied divided by the percentage alter in price) and comment on its size (in absolute value). (Hint: Y'all will take to rearrange the equation then that log Q is in terms of log P.)

• Summate the price elasticity of demand in the same way and annotate on its size (in absolute value).

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

The paper assumes that in practice farmers decide how many watermelons to grow (supply) based on last season's prices of watermelons and other crops they could grow instead (cotton wool and vegetables), and the electric current political conditions that back up or limit the corporeality grown. The reasoning for using terminal flavour's prices is that watermelons take time to grow and are too perishable, and so farmers cannot wait to come across what prices will be in the side by side season before deciding how many watermelons to found.

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

dummy variable (indicator variable)

A variable that takes the value ane if a certain status is met, and 0 otherwise.

Here, C and T are the prices of cotton and vegetables, and CP is a dummy variable that equals 1 if the government cotton-acreage-resource allotment program was in issue (1934–1951). This program was intended to prevent cotton wool prices from falling past limiting the supply of cotton, so farmers who reduced their cotton product were given government compensation according to the size of their reduction. WW2 is a dummy variable that equals 1 if the US was involved in the 2d Earth War at the time (1943–1946).

You lot tin read more than nigh the authorities farm programs for cotton fiber during this time period on pages 67–69 of the written report 'The cotton industry in the United States'.

exogenous

Coming from outside the model rather than existence produced by the workings of the model itself. Come across also: endogenous.

endogenous

Produced by the workings of a model rather than coming from outside the model. See also: exogenous

In this model, the dummy variables and the prices of other crops are exogenous factors that bear upon the decisions of farmers, and hence also affect the endogenous variables P and Q that are adamant by the interaction of supply and demand. The supply bend (right-paw console of Effigy 7.3) shows that if the price rose with no change in exogenous factors, then the quantity supplied by farmers would rise, along the supply bend. But if in that location is an exogenous shock, captured by a dummy variable, it shifts the entire supply curve by irresolute its intercept (left hand panel). This changes the supply cost for any given quantity. (In this specific example of watermelons, the vertical axis variable would be the log price in the previous menses, and the horizontal axis variable would be the quantity in the current period).

Figure 7.iii Supply curve: Dummy variables shift the unabridged curve (left-paw panel) while changes in endogenous variables move forth the bend (right-paw panel).

1. With reference to Figure vii.four, for each variable in the supply equation, give an economic interpretation of the coefficient (for example, explain the outcome on the farmers' supply conclusion) and (where relevant) chronicle the coefficient to an elasticity.

Variable	Coefficient	95% confidence interval
P (price of watermelons)	0.580	[0.572, 0.586]
C (toll of cotton)	–0.321	[–0.328, –0.314]
T (price of vegetables)	–0.124	[–0.126, –0.122]
CP (cotton wool program)	0.073	[0.068, 0.077]
WW2 (Second Globe State of war)	–0.360	[–0.365, –0.355]

Effigy 7.four Supply equation coefficients and 95% confidence intervals.

At present we volition look at the demand curve (equation (three) in the newspaper). The newspaper specifies per capita demand () in terms of price and other variables. () is the need curve intercept:

1. Using the demand equation and Figure seven.five, give an economical estimation of each coefficient and (where relevant) relate the coefficient to an elasticity.

Variable	Coefficient	95% confidence interval
P (toll of watermelons)	–1.125	[–one.738, –0.512]
Y/Due north (per capita income)	1.750	[0.778, 2.722]
F (railway freight costs)	–0.968	[–1.674, –0.262]

Figure 7.5 Need equation coefficients and 95% confidence intervals.

Earlier, we mentioned that exogenous supply/demand shocks shift the entire supply/need bend, whereas endogenous changes (such as changes in price) upshot in movements forth the supply or demand curve. Exogenous shocks that only shift supply or only shift demand come in handy when we try to approximate the shape of the supply and demand curves. Read the information on simultaneity below to understand why exogenous shocks are important for identifying the supply and demand curves.

simultaneity

When the right-mitt and left-hand variables in a model equation affect each other at the same time, so that the direction of causality runs both ways. For example, in supply and demand models, the marketplace price affects the quantity supplied and demanded, but quantity supplied and demanded can in turn touch on the marketplace price.

The simultaneity problem Why nosotros demand exogenous shocks that shift only supply or need

In the model of supply and demand, the price and quantity we notice in the data are jointly determined past the supply and demand equations, meaning that they are chosen simultaneously. In other words, the market price affects the quantity supplied and demanded, only the quantity supplied and demanded tin in turn bear on the market cost. In economic science nosotros refer to this problem every bit simultaneity. We cannot gauge the supply and demand curves with cost and quantity data alone, because the right-hand-side variable is not independent, simply is instead dependent on the left-hand-side variable.

In the watermelon dataset, the price and quantity we observe for each year is the equilibrium of supply and demand in that yr. The changes in the equilibrium from yr to twelvemonth happen as a event of both shifts and movements along the supply and demand curves, and we cannot uncrease these shifts or movements of the supply and demand curves without additional information. Figure 7.6 illustrates that at that place can be many different supply and demand curve shifts to explicate the same data.

Effigy 7.6 Many possible supply and demand curves can explain the data.

To address this result, we need to find an exogenous variable that affects one equation but not the other. That way we can be sure that what we observe is due to a shift in one curve, holding the other curve stock-still. In the watermelon market, nosotros used the Second World War as an exogenous supply daze in Role seven.one. The state of war afflicted the corporeality of farmland dedicated to producing watermelons, but arguably did not impact demand for watermelons.

Figure seven.7 shows how we can use the exogenous supply shock to learn about the need bend. The solid line shows the role of the demand curve revealed by the supply shock. Under the supposition that the demand bend is a straight line, we tin can infer what the residual of the bend looks like. If we had more than information, for instance if the size of the shock varied in each period, so we could utilize this information to acquire more nigh the shape of the demand curve (for example, cheque whether it is really linear). We utilise similar reasoning (exogenous demand shocks) to identify the supply curve.

Figure seven.7 Using exogenous supply shocks to identify the demand bend.

1. Given the supply and demand equations in the watermelon model, give two examples of an exogenous need shock and explain why they are exogenous.

DOWNLOAD HERE

How to Draw Price Elasticity of Demand Curve in Excel TUTORIAL

Posted by: samprelf1944.blogspot.com