In economics, equilibrium price is the price at which the quantity demanded by consumers equals the quantity supplied by producers. At this price, there is no excess demand or excess supply in the market. Equilibrium quantity is the quantity that corresponds to the equilibrium price.
One way to find the equilibrium price and quantity is to use a demand and supply curve, which shows the relationship between price and quantity demanded or supplied. The point where the two curves intersect is the equilibrium point. The price and quantity at this point are the equilibrium price and quantity.
However, sometimes we may not have the demand and supply functions, or we may want to find the equilibrium price and quantity without using math formulas. In this case, we can use Excel to help us. Excel is a powerful tool that can perform various calculations and data analysis tasks. In this article, we will show you how to use Excel to identify equilibrium price and quantity without math formula.
Procedures
The basic idea is to use Excel to create a table that lists different possible prices and the corresponding quantities demanded and supplied. Then, we can use Excel to compare the quantities demanded and supplied at each price and find the price where they are equal or closest to each other. This price is the equilibrium price, and the quantity at this price is the equilibrium quantity.
Here are the steps to follow:
- Create a new Excel workbook and name it as Equilibrium.xlsx.
- In the first worksheet, enter the following data in cells A1 to C11:
| Price | Quantity Demanded | Quantity Supplied |
|---|---|---|
| 10 | 100 | 20 |
| 20 | 80 | 40 |
| 30 | 60 | 60 |
| 40 | 40 | 80 |
| 50 | 20 | 100 |
| 60 | 10 | 120 |
| 70 | 5 | 140 |
| 80 | 2 | 160 |
| 90 | 1 | 180 |
| 100 | 0 | 200 |
This data represents a hypothetical market where the price and quantity demanded or supplied are linearly related. You can use your own data if you have it, or you can generate some random data using Excel functions.
- In cell D2, enter the formula
=ABS(B2-C2). This formula calculates the absolute difference between the quantity demanded and the quantity supplied at the price in cell A2. Drag the formula down to cell D11 to fill the column with the differences for each price. - In cell E2, enter the formula
=MIN(D2:D11). This formula finds the minimum difference among all the prices. This difference represents the smallest gap between the quantity demanded and the quantity supplied in the market. The price that corresponds to this difference is the equilibrium price, or the closest approximation to it. - In cell F2, enter the formula
=MATCH(E2,D2:D11,0). This formula finds the position of the minimum difference in column D. This position is the same as the row number of the equilibrium price in column A. - In cell G2, enter the formula
=INDEX(A2:A11,F2). This formula returns the value in column A at the position found in cell F2. This value is the equilibrium price. - In cell H2, enter the formula
=INDEX(B2:C11,F2,1). This formula returns the value in column B or C at the position found in cell F2 and the column number 1. This value is the quantity demanded at the equilibrium price. - In cell I2, enter the formula
=INDEX(B2:C11,F2,2). This formula returns the value in column B or C at the position found in cell F2 and the column number 2. This value is the quantity supplied at the equilibrium price.
The final table should look like this:
| Price | Quantity Demanded | Quantity Supplied | Difference | Min Difference | Position | Equilibrium Price | Equilibrium Quantity Demanded | Equilibrium Quantity Supplied |
|---|---|---|---|---|---|---|---|---|
| 10 | 100 | 20 | 80 | 0 | 3 | 30 | 60 | 60 |
| 20 | 80 | 40 | 40 | |||||
| 30 | 60 | 60 | 0 | |||||
| 40 | 40 | 80 | 40 | |||||
| 50 | 20 | 100 | 80 | |||||
| 60 | 10 | 120 | 110 | |||||
| 70 | 5 | 140 | 135 | |||||
| 80 | 2 | 160 | 158 | |||||
| 90 | 1 | 180 | 179 | |||||
| 100 | 0 | 200 | 200 |
Explanation
From the table, we can see that the equilibrium price is 30 and the equilibrium quantity is 60. This means that at this price, the quantity demanded by consumers is equal to the quantity supplied by producers. There is no excess demand or excess supply in the market.
We can also see that the difference between the quantity demanded and the quantity supplied is 0 at the equilibrium price. This means that there is no gap between the demand and supply curves at this point. The equilibrium point is the intersection of the two curves.
We can also check the results by plotting the demand and supply curves in a chart. To do this, follow these steps:
- Select cells A1 to C11 and click on the Insert tab in the ribbon.
- Click on the Scatter icon and choose the Scatter with Straight Lines option.
- A chart will appear on the worksheet. Right-click on the chart and select Select Data.
- In the Select Data Source window, click on the Add button under the Legend Entries (Series) section.
- In the Edit Series window, enter Demand in the Series name box and select cells B2 to B11 in the Series X values box. Click OK.
- Repeat steps 4 and 5 for the supply series, but enter Supply in the Series name box and select cells C2 to C11 in the Series Y values box.
- Click OK to close the Select Data Source window.
- The chart will show the demand and supply curves. You can format the chart as you like, such as adding titles, labels, gridlines, etc.
