Determine the market price and quantity of a good

Assignment Help Microeconomics
Reference no: EM13861115

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

Reference no: EM13861115

Questions Cloud

Ratios across countries and then compare these ratios : If economists wish to determine relative factor abundance across countries, why don't they simply calculate wage/rate ratios across countries and then compare these ratios?
The depreciation of currency : The depreciation of currency will: Worsen a country's comparative advantage. Have no impact on a country's comparative advantage.
Which contractionary monetary policy works : 1.Which of the following is the path through which contractionary monetary policy works? Money down implies interest rate down implies investment up implies income down. Money down implies interest rate up implies investment up implies inco..
Greg bought 5 cans of tunafish : Greg bought 5 cans of tunafish at $1 each and 4 packages of pasta at $2 each. Hismarginal utility from tuna is 25 units of utility per can and his marginal utility from pasta is 60 units ofutility per package. Next week he'll have the same amount of ..
Determine the market price and quantity of a good : 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. ..
Banking system will cause an increase in the money supply : 1.If the reserve requirement is 20 percent, and banks keep no excess reserves, an increase in an initial inflow of $100 into the banking system will cause an increase in the money supply of: $500 $20
The stated assumptions for your answer to be valid : a.  Suppose school funding is controlled by annual referenda over funding increase and decrease. What would you expect the school funding level to be? b. What would you need to assume about the preferences of the population members beyond the stated ..
Introductory econometrics : ECN 425: Introductory Econometrics ____________________________ NAME Exam #1 (100 Points) I. DERIVING OLS ESTIMATORS AND THEIR PROPERTIES (For full credit, you must show all your work) Suppose the population regression function can be written as: i i..
Explain how the organism in the diagram has evolved : Explain how the organism in the diagram has evolved physiologically to become suited to its environment

Reviews

Write a Review

 

Microeconomics Questions & Answers

  What would policy-makers do if economy is slumping

Suppose the economy is slumping into recession and needs a fiscal policy boost. Voters, however, are opposed to larger federal deficits. What would policy-makers do

  Explain significance of well-developed compensation plan in

w. edwards deming often referred to as the leading quality guru in the united states and psychologist alfie kohn

  At what range of prices will the firm supply zero output

Identify the firm"s supply curve on your graph. d. At what price would the firm supply exactly 6 units of output

  Describe the legal aspects of health care administration

Margie has just attended the funeral of her son William, who died this week after several years of poor life quality in the same nursing facility. William's first stroke happened 3 years prior; two more strokes followed, and he lingered in poor he..

  Statistically significant regression coefficient

Determine which of the following is most likely to indicate statistically significant regression coefficient? Assume the price elasticity of the supply of cheese is 0.80. If the price of cheese rises by .20 percent,

  Explain which of the policies is most effective

Many developing countries in the "Global South" turned to socialism in the past as a means to solve their economic problems. Now, in the light of the evident failure of socialism, many of these countries seek to create fast growth

  1 if a consumer purchases a combination of commodities x

1. if a consumer purchases a combination of commodities x and y such that muxpx 20 and muypy 10 to maximize utility

  What was the effect on the wages earned by the labor force

Identify an example in which the competitive environment affected the relationship between labor and management. How was the relationship affected?

  Services of a commercial painter in painting the family home

Which of the following are included in this year’s GDP? Explain your answer in each case. The services of a commercial painter in painting the family home. An auto dealer’s sale of a new car to a non business customer.

  Explain why sink cost is neither priceless nor free

Master Card has a series of cute commercials that list a series of accounting items and costs leading to a priceless product. Cell phones are often advertised as being free. In economics, it is said that nothing of value is either free or priceles..

  What is the average product of labor

Suppose the production function is given by Q= 5K + 6L. What is the average product of labor when 8 units of capital and 10 units of labor are employed?

  What is total variable cost

What is total variable cost when 100 units of output are produced and what is average fixed cost when 150 units of output are produced?

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd