Spreadsheet - The Citadel



11. Demand, Price, and Revenue in Excel

Problems

1. Make an excel spreadsheet showing the demand function and the various variables related to demand. Use the spreadsheet to calculate the simple demand function, the price function, the revenue function, the marginal revenue function, and the point price elasticity of demand function.

2. Use the excel spreadsheet to create schedules for price, revenue, marginal revenue, and point price elasticity of demand. Make the spreadsheet so that the initial quantity and the increment by which quantity increases can be easily changed.

3. Use the excel spreadsheet to calculate the revenue maximizing level of output and show the price, revenue, marginal revenue, and point price elasticity of demand associated with that quantity.

The Demand Function

First, type in the demand function.

Qd = -20,000 - 20P + .5A^.5 - 50Pc +.1Ps + 5Y

Each number should be in its own cell. The other symbols can be placed in the same sell. For example, in A1, you would put Qd = , but in B1 you would put 20000 , then in C1 you would put -20 and in D1 you would put P + and so on.

| |A |B |C |D |E |F |G |H |I |J |K |L |M |

|1 |Qd = |20,000 |-20 |P + |0.5 |A^ |0.5 |-50 |Pc + |0.1 |Ps + |5 |Y |

Next, type in the names for the various variables except for the goods own price, like :

A =

Pc =

Ps =

Y =

These would be in A2, A3, and so on.

Each name goes in its own cell and all of these should be in the first column.

Then type in the initial values of the variables in the second column next to the appropriate name. For example 1000000 goes next to A =. Don’t put any “m” or “b” next to the numbers. These would be in B2, B3, and so on.

| |A |B |C |D |E |F |G |H |I |J |K |L |M |

|1 |Qd = |20,000 |-20 |P + |0.5 |A^ |0.5 |-50 |Pc + |0.1 |Ps + |5 |Y |

|2 |A= |1,000,000 | | | | | | | | | | | |

|3 |Pc = |10 | | | | | | | | | | | |

|4 |Ps = |600 | | | | | | | | | | | |

|5 |y = |9000 | | | | | | | | | | | |

Then type in “Qd =” in a cell below. This would be in about A6. In the cell to the right (B6) you type in:

=

Then click your mouse on the first number in the equation (20000). Then type in + and click on the coefficient for advertising. That is .5. (Yes, you skip the good’s own price.) Then type in * (for multiply) and click on the value for advertising. That is the 1000000 in the second column. Then type in ^ (which means raise to the power) and click on the .5 in cell G1. Type in + for addition and the click on the –50 (in H1). Then type in * for multiply and click on the initial price of the complement good (B3). Continue until you have typed in all the formula. It will look something like:

=B1+E1*B2^G1+H1*B3+G1*B4+J1*B5+L1*B6

Then you hit enter. This will be in the cell next to the one where you have Qd=. It will be in the second column. The result will be a number.

The B2, B3, B4... are the location of the initial values of advertising, the price of the complement and so on.

In the third column click on the coefficient (number) in the cell to the left of P (the good’s price.). In the fourth column type in P.

The simple demand function should appear before you spread over four columns.

| |A |B |C |D |

|7 |Qd = |65060 |-20 |P |

The price function is found on the spreadsheet by first typing P = in the first column. It should be about A7. Then in the next cell (B8) type in - and immediately click you mouse on the intercept term in the simple demand function. In the example, that would be 65060 and you would see –B7. Then type in / (divide) and immediately click on the coeeficient for price. That is -20 and you should see C6. And hit enter. Before hitting enter, you should see –B7/C7. After hitting enter, you will see 3253. In the next cell (third column) type in =1/ and then click on the price coefficient in the simple demand function. In the example, that would be -20. You would see =1/C7. Then in the next cell (fourth column) type in Q.

|P = |-B7/C7 |=1/C7 |Q |

When done, you see the price function--

|8 |P = |3253 |-0.05 |Q |

Next, place the revenue function. Revenue is price times quantity. Type in R= in A9 and then in B9 type in = and then click on B8. It should look like =B8 but when you hit enter, it will be 3253. In C9, put in Q. In D9, type in equal and click on C8. It will look like =C8 and when you hit enter, it will be -.05. In E9, type in Q^2.

|9 |R = |=B8 |Q |=C8 |Q^2 |

|9 |R = |3253 |Q |-.05 |Q^2 |

Next, put in Marginal revenue in row 10. In A10, type in MR=, then in B10, type = and then click on B9. In C10 type in =2*D9. And then type in Q in D10.

|10 |MR= |=B9 |=2*D9 |Q |

After you hit enter, it will look like:

|10 |MR= |3253 |-.1 |Q |

Now let’s put in the point price elasticity of demand in row 11.

In A11, type in Ep =. Then in B11, type in equals and then clique on the –20 in C7. Then in C11, type in P/Q.

|11 |Ep= |=C7 |P/Q |

After you hit enter, it will look like:

|11 |Ep= |-20 |P/Q |

Now for the schedule. Type in a 1 in A12. Then in A 13, type in Q (for quantity or output.)

And in A14, type in a zero. It will look a bit like the following:

| |A |

|12 |1 |

|13 |Q |

|14 |0 |

Now down one more row, again in the first column. Type in = and click on the cell above with the zero and immediately type in + and click on the cell above the Q where there is a one. Assuming the zero was in A14, you will have--

=A14+A12

Hit enter. The result will be 1. Click on the one twice. Add a dollar sign between the A and the 8. It will look like:

=A14+A$12

This is called anchoring the formula to A12. The purpose of this is so that when you copy this formula, the numbers always increase by one, or whatever other number you choose to put over the Q.

Select the cell with the one (well, you are probably already there.) Hit the copy button. It looks like two sheets of paper--one blue--one black. Select the cell immediately below the one. Hold down the left mouse button and drag down about 100 cells. Then hit the paste button. (It looks like a clipboard.)

The column one should look like:

1

Q

0

1

2

3

4.

This is the column of quantities. You can change the increments by which quantity increases by replacing the one that is above the Q (A8) with a different number--10, 100, 1,000, or whatever.

You can also change the zero below the Q to whatever value you want to adjust your initial starting place.

In the next column (column B) put P (for price) next to the Q. In the cell below the P, and immediately to the right of 0, type in = and then select the intercept on the price function. In this example it would be 3253 and you would see =B7. Immediately type in = and hit the coefficient on quantity from the price function. That would be -.05 , and you would see = C7. Immediately type in * (for multiply) and then select on the zero that is right below the Q. That would be A10. So before you hit enter you will see:

+B7+C7*A14

Click on the cell twice and add some dollar signs between the B and 7 and the C and 7 (but not between A and 10). You will have:

=B$7+C$7*A14

This is so that when you copy this formula the intercept and the coefficient in the price function will remain the same, but you will find the value of the function for each quantity.

| |A |B |

|12 |1 | |

|13 |Q |P |

|14 |0 |=B$7+C$7*A14 |

| | | |

Push the copy button. The click on the cell right below (next to the 1) and hold down the left mouse button and drag down about 100 cells. Then hit the paste button.

The result is a demand schedule. You have a series of prices and the quantities that would be purchased at the price. Still, it is organized backwards. The quantities run from zero to 100 and the prices are the highest ones that could be charged and still sell that quantity.

In C13, put in R for revenue and then in C14, type in = and click on A14 type in * and click on B14. That’s because revenue is equal to price times quantity. Copy and paste into 100 cells. That’s the revenue schedule.

For the Marginal revenue, follow the same procedure as for demand. In D13, type in MR. Then in D14 type in

=B$10+C$10*A14

Copy and paste on down.

For point price elasticity of demand, put Ep in E13. In E14, type in

=B$11*B14/A14.

Copy and paste down.

Graphing

To graph the demand schedule and show the firm’s demand curve, you select the Q using your mouse, hold down the left mouse button and drag over one column to the right and then down 100 cells. All the prices and quantities are highlighted as well as the P and the Q.

Push the button for chart wizard. Choose a scatter diagram with lines. Be sure to put the graph on its own page. Notice it looks like a demand curve.

The same procedure is used for the revenue curve, but this time push control so that you can skip the prices and just get revenue.

Finally, you can graph demand and marginal revenue together, by highlighting the quantities and prices, hit control to skip revenue, and then highlight the marginal revenue.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download