Using Excel to Learn about Supply and Demand
Overview
This assignment reviews how supply and demand interact to determine the market price and quantity of a good or service. You have been given an Excel spreadsheet with data on different variables determining demand and supply for a given product (e.g. avocados).
To make the spreadsheet easier to read, highlight all of the columns, click on "Home" at the top of the screen, then click on "Format", then click on "Column Width" and type in "20". You may also wish to go under "Alignment" and center the entries.
From these data, you will calculate the total quantity demanded (Qd) and quantity supplied (Qs) for every given price. You'll also graph supply and demand and use your graph to showhow changes in factors underlying demand and supply lead to changes in market prices and quantities and examine the impact of government-set price controls.
The supply function takes the form:
Qs = a*Number of sellers + b*Wage + c*Price of energy + d*Technology + e*Own price
The demand function takes the form:
Qd = f*Income + g*Price of substitute + h* Price of complement + i*number of buyers- j*Own price
Part A: Filling in the Spreadsheet
To generate quantity supplied (Qs) we use both "absolute" and "relative" cell references.
- A relative cell reference is when you copy or click and drag a cell (e.g. the cell C1), which creates a new series of cells that change relative to the initial cell C1. So if C1 was obtained using the formula =A1+B1, then copying that cell from row 1 to row 2, the formula will become =A2+B2.
- An absolute cell reference does not change when copied or filled, it remains constant. So it is not dependent on the cell in which the formula appears. To create an absolute cell reference, you place a $ in front of the column and row designation: C1 is a relative cell reference while $C$1 is an absolute cell reference. You can also have the row be absolute and the column be relative (e.g. C$1) or the opposite (e.g. $C1).
- Once you have a cell highlighted, you can press the F4 key on your keyboard to switch between relative and absolute cell references. This is an easy way to quickly insert an absolute reference.
To generate the column of data for Quantity supplied (Qs), in the first cell of quantity supplied column, type the formula for the supply function=$B$2*$D$2+$B$3*$D$3+$B$4*$D$4+$B$5*$D$5+$B$6*I2
Note that I2 is a relative cell reference to price (which is a variable that changes from cell to cell) while $B$2 is an absolute reference to value of the parameter "a" (0.08).
What happens to Qs when the measure of technology is increased by 100? What if the price of energy is increased by 100?
Part B: Some Study Questions
1. Generate the column of data for Quantity demanded (Qd). What is quantity demanded when price equals 170?
2. Suppose that income increases by 1000. What happens to the quantity demanded for every price? Is this good inferior or normal?
3. Go back to the initial levels of variables. What are the equilibrium price and quantity?
4. Assume the price of energy rises to $1000. Find the new equilibrium price and quantity. For that you need to use the Goal Seek tool in Excel:
a. Create a new column to the right of the column for Qd and name it Excess Supply. Enter the formulafor the excess supply (hint: Qs-Qd). In equilibrium, this excess supply must be equal to 0.
b. Click on "Data" and in the "Data Tools" group, click on "What-if Analysis" (or click directly on "What-if-analysis" if you have the latest version of Excel), then click on "Goal Seek".
c. Your objective is to find the price such that Qs-Qd=0. In the first box (Set cell) enter the cell reference of the first entry for excess supply (e.g.L2). In the second cell (To value) enter 0 (which is the value you want to assign to that cell). In the third and last box (By changing cell), enter the reference for price (e.g.I2). This tells Excel to adjust price so that Qs-Qd=0. Then click OK. This line will now display the equilibrium price and quantity such that Qs-Qd=0. Click "Cancel" to change the cells back.
Part C: Create Graphs
Set the variables back to their initial values
For a PC
On the bottom left, click to generate a new blank sheet. Right click where it says "Sheet2", select
"Rename", and name it "Graph". On the new sheets, go to "insert" on the toolbar and click on "scatter" and choose the chart with straight lines connecting the dots. Right click on the blank area of the chart, then click on "select data", then click on "add". Under 'series name" type "Supply", highlight the values of Qs for "Series X values", highlight the values of P for the "Series Yvalues",. Click on "edit" to correct any mistakes you might have made.
Make sure the scale minimum is set to zero for each axis. (This restricts the plot to the positive quadrant.) To do that, right click on the axis and select "Format axis". Under "Axis Options" click on "Fixed" and select the appropriate minimum (or maximum).
If the dots on your graph are too large, double left click on one of them to highlight the entire series and bring up the "Format Data Series" menu, then select "Marker Options", then under "marker type" click "built in" and select number corresponding to a smaller size.
To label your axes, go to "Layout", then "Axis titles" and enter your labels. Use "Chart title" to add a title to your graph.
For a Mac
On the bottom left, click to generate a new blank sheet. Right click where it says "Sheet2", select
"Rename", and name it "Graph". Having the data available to Excel, click the Chart Wizard icon on the toolbar. A chart wizard will be presented, providing you with some options. Select "Scatter with straight lines".
Click "Select Data" and then click "Add". Write "Supply" in the field for "series name". For Series X Values, highlight the numbers for Qs and for Series Y values, highlight the numbers for P. Then click "OK". Set the scale minimum to zero for the horizontal axis. (This restricts the plot to the positive quadrant.) To do that, in the "Chart tools" click on "Format". In the "Current Selection" group, click the arrow next to "Chart Area" and click on "Vertical (value) Axis". Then click "Format Selection" below, and enter 0 for "Minimum".
If the dots on your graph are too large, double left click on one of them to highlight the entire series and bring up the "Format Data Series" menu, then select "marker options", then under "marker type" click "built in" and select number corresponding to a smaller size.
Click on "Layout", then "Chart Title" to add a title, and "Axis Title" to label your axes.
Part D: More Study Questions (for both Mac and PC users)
1. Add the demand curveto your graph following the instructions above
2. Assume that the price of a complementary good increases. In what direction does demand shift? Does it mean the demand increases of decreases?
3. Assume the price of the substitute good increases. In what direction does demand shift? Does it mean that demand increases or decreases?
4. Set the variables back to their initial values. Suppose that the government imposes a price floor of $120. What would happen in this market? (It may be useful to refer to your spreadsheet as well)
5. Suppose that the price floor is removed and a price ceiling is imposed at $70. What would happen in this market? (It may be useful to refer to your spreadsheet as well)
When you've finished answering the questions, make sure you've set all supply variable values and demand variable values back to their initial values (this is important if your assignment is graded).
Helpful tips
1. Use keyboard shortcuts. I find Ctrl Z (undo), Ctrl C (copy) and Ctrl S (save) especially helpful.
2. If you can't figure out how to do something, type your question into google or another search engine. Chances are someone else has asked the same question and the answer is already online!
Using Excel to Learn about Supply and Demand
Part A: Filling in the Spreadsheet
Fill in the spreadsheet for Qs and Qd, following the instructions given.
Part B: Some Study Questions
1. When price equals 170 the quantity demanded is equal to ………………….
2. Suppose that income increases by 1000. The quantity demanded for every price …….….…… This good is ………………….
3. Go back to the initial levels of variables. The equilibrium price is …………… and the equilibrium quantity is …………………
4. Assume the price of energy rises to $1000. The new equilibrium price is ………………….. and the new equilibrium quantity is ………………………….
Part C: Create Graphs
See Excel sheet
Part D: More Study Questions (for both Mac and PC users)
1. See excel sheet
2. Assume that the price of a complementary good increases. Demand shifts to ………………… It means the demand ………………………… (increases/decreases)
3. Assume the price of the substitute good increases. Demand shifts to ………………… It means the demand ………………………… (increases/decreases)
4. Set the variables back to their initial values. Suppose that the government imposes a price floor of $120. In this market there is………………………………..
5. Suppose that the price floor is removed and a price ceiling is imposed at $70. In this market there is ……………………………………………
Attachment:- Copy of Supply_demand_exercise_version1.xls