Building a Price Elasticity Model in Excel: A Step-by-Step Guide

Are you ready to delve into the world of economics and data analysis? Today, I'll walk you through the step-by-step process of creating a Price Elasticity Model in Excel. This powerful tool will help you understand how changes in price impact consumer behavior and revenue.

Step 1: Set Up Your Data

Open a new Excel spreadsheet. In the first column, label it "Price." In the second column, label it "Unit Sold." Also calculate "Revenue" (Revenue = Price * Unit Sold)

Price Unit Sold  Revenue
100	50	 5,000 
150	49	 7,350 
200	48	 9,600 
250	47	 11,750 
300	46	 13,800 
350	45	 15,750 
400	44	 17,600 
450	43	 19,350 
500	42	 21,000 
550	41	 22,550 
600	40	 24,000 
650	39	 25,350 
700	38	 26,600 
750	37	 27,750 
800	36	 28,800 
850	35	 29,750 
900	34	 30,600 
950	33	 31,350 
1000	32	 32,000 
1050	31	 32,550 
1100	30	 33,000 
1150	29	 33,350 
1200	28	 33,600 
1250	27	 33,750 
1300	26	 33,800 
1350	25	 33,750 
1400	24	 33,600 
1450	23	 33,350 
1500	22	 33,000 
1550	21	 32,550 
1600	20	 32,000 
1650	19	 31,350 
1700	18	 30,600 
1750	17	 29,750 
1800	16	 28,800 
1850	15	 27,750 
1900	14	 26,600 
1950	13	 25,350 
2000	12	 24,000 
2050	11	 22,550 
2100	10	 21,000 
2150	9	 19,350 
2200	8	 17,600 
2250	7	 15,750 
2300	6	 13,800 
2350	5	 11,750 
2400	4	 9,600 
2450	3	 7,350 
2500	2	 5,000         

Here's a summary of the information from the table:

• The Price starts at $100 and increases by $50 increments up to $2500.
• As the Price increases, the Unit Sold decreases.
• The Revenue is calculated by multiplying the price by the Unit Sold

Change in Demand /change in Price

The "Change in Demand / Change in Price" column shows the change in quantity demanded divided by the change in price. It's used to calculate the price elasticity of demand.

To calculate Change in Demand /change in Price we have to divide the difference of current and past unit sold by the difference of current and past price.

Formula =(C4-C3)/(B4-B3)

P/Q

The "P/Q" column shows the ratio of price to quantity, which is used in the midpoint formula to calculate price elasticity.

Formula =B3/C3)

EP

The "Ep" column represents the calculated price elasticity of demand using the midpoint formula.

Formula =ABS(E4*F4)

Marginal Revenue

The "Marginal Revenue" column calculates the change in revenue as quantity changes by one unit.

To calculate Marginal Revenue we have to divide the difference of current and past Revenue by the difference of current and past Unit Sold.

Formula =(D4-D3)/(C4-C3)

After doing all the above instruction will will have something like this

From this table, you can observe the following:

• At lower prices, the demand is relatively inelastic. Changes in price have a smaller impact on quantity demanded and revenue.
• As the price increases, the demand becomes more elastic. Changes in price start to have a larger impact on quantity demanded and revenue.
• There is a point where the price elasticity of demand is exactly 1, indicating unitary elasticity. This occurs at a price of $1350.

This table provides a useful illustration of how price elasticity changes along the demand curve as prices and quantities vary. It's a practical way to understand the responsiveness of consumers to price changes and its impact on revenue.

We can see from the the chart that where the EP is 1 we have the highest Revenue with Price being 1350.