Can someone help me translate this into a Excell sheet

INSTRUCTIONS STEP 1 RETRIEVING A PREVIOUSLY SAVED FILE

Instead of building your worksheet from scratch, you will be adding information to a worksheet that we have already started. Your first step is to download your starter worksheet from the Blackboard site for Problem Set 04. The top portion of Sheet 1 looks like this:

PS-04: PRICE ELASTICITIES OF DEMAND AND SUPPLY

QUANTITY DMD-P

0 $ 150.00 2 $ 140.00 4 $ 130.00 6 $ 120.00

% CHANGE IN QUANTITY

200% 67% 40%

% CHANGE IN DMD-P

-7% -7% -8%

D-ELAS TOT REV $ -

(29.00) $ 280.00 (9.00) $ 520.00 (5.00) $ 720.00

STEP 2 CALCULATING THE PRICE ELASTICITY OF DEMAND

You will be calculating price elasticities in three steps. First, in column D you will enter the % change in quantity. Second, in column F you will enter the % change in price. Third, in column H you will use these values to calculate the elasticity. To get started, the first formula that you need is presented below. You should see a direct relationship between this formula and our

04-2

discussion of the percentage change in quantity in the introduction to this problem set. Use this formula in cell D6.

=(A6-A5)/((A5+A6)/2)

Notice that you cannot find a percentage change for cell D5, since you have no numerical value in cell A4. Also, while the result of your calculation is entered in your worksheet in cell D6, you are really calculating the percentage change in quantity as it varies between 0 and 2.

Be careful with the placement of parentheses in the expression. Parentheses are very important when defining an expression in equation form because they define the order of operations for the computer. Remember to use the copy procedure to fill all of the cells in the column quickly. The last entry, located in cell D20, should be 0.0690, or 7%. (Your computer may not display the same number of decimal places.)

Go to cell F6 and calculate the percentage change in price. You should be able to work out the proper Excel equation using the percentage change in quantity as a model. After you have entered the formula, copy it down column F to row 20. The first entry, located in cell F6, should be 0.069, or 7%. The last entry, located in cell F20, should be 2.000, or 200%.

The data in these cells would be easier to read if we just use two decimal places.

Highlight cells D6 to F20 on your worksheet.

Click on the comma in the Number menu above your worksheet in the Home tab. (It is to the right of the %.)

Your data should now be displayed to two decimal places only. The parentheses in column F indicate that the entries in this column are negative numbers. This is enough precision for us at this point.

Now you are ready to calculate the price elasticity of demand. You have the pieces that you need, go to cell H6. Take your percentage change in quantity (cell D6) and divide it by your percentage change in price (cell F6). Copy this through the column to cell H20.

While the column of numbers is still highlighted, click on the comma symbol to change the entries to two decimal places.

Your final entry in cell H20 (rounded to two decimal places) should be (0.03).

Notice that all of your price elasticities of demand are negative. This is because there is an inverse relationship between price and quantity demanded. Depending upon whether there is an increase or a decrease in price, either the percentage change in quantity or the percentage change in price will be negative, and the other percentage change will be positive. Therefore, either the numerator or the denominator (but never both) of the price elasticity of demand fraction will be negative. This guarantees that the price elasticity of demand will always be a

04-3

negative number. (There are very rare exceptions to this rule, but we will not concern ourselves with them now.)

Because of the way we set up our data and equation, all of the percentage changes in price are negative. Since economists know that the price elasticity of demand is negative, we are most concerned with the absolute value, or absolute size, of the coefficient. For example, from your spreadsheet the price elasticity of demand between 8 and 10 units is 2.33. If you were to report this finding to your colleagues, you might just say that the elasticity is 2.33; everyone would know that it is a negative number. The most interesting finding is that, in this price range, if the price goes up by 1%, the quantity demanded will fall by more than 1%, or by 2.33%.

Finally, remind yourself that the price elasticity of demand is unit-free. That is, there are no percentages, dollars, pounds, or other units attached to the elasticity coefficient. The great advantage to this is that we can compare elasticities across items that are measured in very different ways. For example, it does not matter that apples are often measured by the pound or by the bushel and shoes are sold in pairs. We can use elasticities to compare consumer responsiveness in these two markets.

STEP 3 CALCULATING THE PRICE ELASTICITY OF SUPPLY

In the introduction to this problem set, we discussed the fact that all elasticities measure the responsiveness of market agents or variables to a change in some other variable. The only difference between the price elasticity of demand and the price elasticity of supply is the agent whose behavior is being observed. The price elasticity of supply measures the responsiveness of sellers to changes in price. Remember, with the price elasticity of demand, we were focusing on how buyers react to price changes in the market.

As with price elasticity of demand, you will calculate the price elasticity of supply in three steps. You will calculate the percentage change in quantity supplied in column D, the percentage change in price in column F, and then you will use this data to calculate the supply elasticity in column H. Lets go!

Highlight cells D6:D20 and click on the Copy icon.

Move your cursor to cell D26 and click on the Paste icon.

Notice how your formula automatically gets updated to the new cell addresses!

Repeat this procedure for columns F and H.

Notice that all of your supply elasticities are 1. What does this mean? You will have to answer this question on the quiz that accompanies this problem set.

04-4

STEP 4 CALCULATING TOTAL REVENUE FOR THE FIRM

Totalrevenueistheamountofmoneythatafirmbringsinfromthesaleofitsproduct. Note that total revenue is NOT the same as profit. To find profit, we would have to subtract all of the costs related to production and sales from the revenue figure. If at each price the firm just meets the demand for its product, total revenue would equal quantity (in column A of your spreadsheet) times the demand-price at that quantity (found in column B of your spreadsheet). Because firms are interested in their profitability, they need to be able to determine their revenues at any level of sales.

To calculate total revenue in your spreadsheet:

Go to cell J4 and type the label TOT REV. Go to cell J5 and type a formula for total revenue. Copy this equation to cells J6 through J20.

While your data is still highlighted, click on the $ in the menu at the top of your spreadsheet to change the format of your revenue data.

Now place % signs in front of your percentage changes in quantity and your percentage changes in price.

STEP 5 GRAPHS

Construct three different X-Y (Scatter) graphs. Plot your demand curve, plot your supply curve, and plot a total revenue curve. Use separate charts for each. Recall from your prior work in excel that you use the insert command to create a graph. Highlight the values you want to graph, and then choose the x-y graph function (scatter).

Take a look at your demand and supply curves...can you determine their slopes? Start thinking about the relationship between elasticity and slope. Students often have difficulty distinguishing between these two concepts. Notice that your demand and supply curves are linear, which means they have constant slope. Why is the price elasticity of demand notconstant? Why is the price elasticity of supply constant but not equal to the slope?

STEP 6 COMPLETING THE PROBLEM SET

Make sure that you save the work that you have done! Print your worksheet and your graphs if you need to. You will need access to your worksheet and graphs to complete the quiz that accompanies this Problem Set.

04-5