Suppose that the demand curve for chocolate covered ants (CCA) is P=50-5QD, where P is the Price per pound (in dollars) of CCA and QD is the quantity (in pounds) demanded of CCA in a period....

Suppose that the demand curve for chocolate covered ants (CCA) is P=50-5QD, where P is the Price per pound (in dollars) of CCA and QD is the quantity (in pounds) demanded of CCA in a period. Also, suppose that the supply curve for CCA is P=5QS, where QS is the quantity supplied (in pounds) in a period.

In this context, using Excel Charts, how can I set up a diagram showing the supply curve, the demand curve, the equilibrium price, and quantity?

If the Government puts in place a price ceiling (i.e., a legal maximum price) of $10 per pound, how can I create a new diagram that shows how this will affect the quantity supplied, the quantity demanded, and the actual amount bought and sold in this market?

Expert Answers
pnrjulius eNotes educator| Certified Educator

We're specifically asked to draw the diagram in Excel charts. To do that, we need to first make a spreadsheet in Excel, then use the charting function to actually make our charts.

Excel likes to draw charts as scatter plot graphs; so we need to find a way to draw our supply and demand curves as scatter plots.

A good way to do that is to first make a column of values for our independent variable (quantity), over a suitable range. Since the demand is P = 50 - 5 Q, it makes sense to go from Q = 0 to Q = 10, and steps of 1 make sense. (If it had been 500 - 5 Q for example, we might have wanted to do Q = 0 to Q = 100 in steps of 10 instead.) Let's make that column A.

Then make a column for supply and a column for demand, and enter the supply and demand curves as formulas, and copy those into each cell in the appropriate column.

For supply the equation "P = 5 Q" becomes the Excel formula "= 5*A2".

For demand the equation "P = 50 - 5 Q" becomes the Excel formula "= 50 - 5*A2".

As you copy-paste the formula from cell to cell, notice how it changes the "A2" to "A3", etc. as necessary to make it correspond to the column next to it. Excel will do this automatically; if you don't want it to do this, use dollar signs, e.g. "$A$2".

Now graph these, and the result should come out something like the first part of the image I posted (it may vary depending on your precise graph settings and colors). Make sure to choose a line graph and set the first column as the label.

Now we want to add the price ceiling. That should be drawn as a flat line at P = 10, so just make a whole column for the price ceiling that is 10 all the way down. The result should look like the second part of the image I posted.

To complete our diagrams, we should also add some labels, which we can do right within Excel using text boxes. I've put the word "equilibrium" at the point which is the equilibrium quantity and price. These versions form the third and fourth part of my image.

Customize these graphs by changing the color schemes or adding more labels.

In terms of the actual economics, notice how the price ceiling reduces the quantity sold from 5 to 2 while reducing the price from $25 to $10. It also creates a shortage, because the demand at $10 is 40 but only 5 are actually available for sale. This is in general what a binding price ceiling will do; it reduces the quantity sold and creates shortages.

Images:
This image has been Flagged as inappropriate Click to unflag
Image (1 of 1)