Two types of price exist in market:
- Equilibrium market price: which is determined by market forces, i.e. demand and supply
- Regulated market price: which is determined by the government
The equilibrium market price is one where both suppliers and consumers are willing and able to exchange a quantity of goods for money. This is at the intersection of the downward sloping demand curve and the upward sloping supply curve as shown below.
Let us see how to find out the equilibrium market price using MS Excel.
The Total Demand and Supply for a particular grade of cement (in bags) is shown below.
|Demand Price||Demand Quantity||Supply Price||Supply Quantity|
Plot the Graph of Demand and Supply in the same graph sheet and find out the Equilibrium Price and Equilibrium Quantity. Also calculate the surplus and shortage of goods if the price changes from 40 to 50.
Let us see how to plot this using MS Excel.
Rearrange the data as shown below.
Select the data in MS Excel go to INSERT TAB > CHART GROUP > SCATTER, > SCATTER WITH STRAIGHT LINES as shown below (if you hover over the icon, the full description is shown)
A chart will then appear with the familiar shape of the Supply and Demand diagram. However, the Price values are, by default, shown on the X-axis. The usual convention is to put the Price on the Y-axis and the following steps show how to switch the values around.
Right-click on the chart and choose Select Data from the mini menu.
Highlight the first row of data (Demand Quantity in this example) and then click on Edit.
Delete the contents of the boxes for the X and Y axes.
With the cursor in the box for the Y-axis values, highlight the price values in your table.
Now click into the box for the X-axis values and highlight the supply values in the table (Demand Quantity in the example shown) and click OK.
Repeat above steps for the second row of data (Supply Quantity) and when you click on OK, you will see that the chart has been rearranged with values for Price listed on the Y-axis.
The final result is as shown below with Y Axis as Price and X Axis as both Supply and Demand Quantity.
The Equilibrium Price and Equilibrium Quantity can be found from the confluence of both the curves on Y and X Axis respectively.
The Equilibrium Price = 42
Equilibrium Quantity = 6200 units
Shortage at 40 = 6800 – 5700 = 1100 units
Surplus at 50 = 7600 – 4700 = 2900 units