How to Find and Analyze Demand Function Curve
To find the Demand Curve and Function for a single product, one must understand that in standard Economics, the relationship is inverse: Price Y determines Units Demanded X, or f(y) = x., Create the Column Headings and columns for the Data Findings...
Step-by-Step Guide
-
Step 1: To find the Demand Curve and Function for a single product
But Excel only accepts x values first in the left most column, then however many dependent y variable columns one wants.
However, Excel allows one to reverse the columns when charted, so that it all works out for cases like this one in which x is the dependent variable.
We will be using tricks like taking the standard formula for a line, y = mx + b, and standing it on its head to become x = mP +b where P=y; we can do so because the dependent and independent variable have exchanged roles.
Open an Excel workbook and create 3 worksheets:
Regression, Demand Curves and Saves.
We'll be using Demand Curves in the next article. -
Step 2: one must understand that in standard Economics
Into cell B1, enter y, and into cell B2, enter P=Y.
Into cell C1, enter x, and into cell C2, enter X=UNIT FINDS.
Into cell D1, enter m, and into cell D2, enter Numerator. m = slope.
Into cell E1, enter m, and into cell E2, enter Denominator.
Format Cell Range columns A:
E bold.
Format column B blue-purple and format cells number number currency $ symbol decimal places
2.
Format column C red.
Format columns C:
E decimal places
2. , There were 14 observations upon which we're basing our demand function model.
From B3 to B16, top to bottom, enter the following prices: 3, 6,
6.5, 7,
12.5, 13, 16, 19,
21.5, 25, 29, 30, 32 and
35.
These are the prices your item sold at over various quantities, per research below.
Select column B and Insert Name Define Name p to column $B.
Select column B and Insert Name Define Name py to column $B.
Enter MEAN to cell A17 and into cell B17 input the formula w/o quotes "=AVERAGE(B3:
B16)".
Copy this formula and do Paste Special Formula to cell C17.
Insert Name Define Name X_Bar to cell $C$17 and Insert Name Define Name Y_Bar to cell $B$17. ,, Select cell range E3:
E16 and enter to E3 w/o quotes the formula "=(C3-X_BAR)^2" and Edit Fill Down.
Format cell D17 Border red bold outline and input the formula w/o quotes "=SUM(D3:
D16)" and copy and paste this formula and cell format to cell E17. , Enter b into cell D19 and into cell E19 enter the formula w/o quotes "=Y_BAR-E18*X_BAR".
Select D18:
E19 and Insert Name Create Names in Left Column, OK.
Format cell range E18:
E19 decimal places
6.
Enter to cell E20 X' = mP + b; P=Y and format font red.
Enter to cell D21 Forecast Unit Sales per MEAN Price, aligned right, and enter to cell E21 w/o quotes the formula "=m*Y_BAR+b". , Select cell range E24:
E28 and enter to E24 w/o quotes the formula "=m*D24+b". , Copy D23:
D28 and Paste to B23:
B28.
Select A23:
B28 and choose Charts, All/Other, Scatter, Smooth Line Scatter from the Ribbon or Chart Wizard.
Add the above title to the Chart.
Add the Vertical Axis Title PRICE and the Horizontal Axis Title UNITS DEMANDED per Research Findings.
Copy C2:
C17 and Paste to F2:
F17.
Copy B2:
B17 and Paste to G2:
G17.
Click in the chart's Plot Area and do menu item Chart Add Data.
In response to the query, select F3:
G17, OK.
This may not occur as desired and what you want is the series to appear in the formula bar as follows: "=SERIES(,Regression!$F$3:$F$17,Regression!$G$3:$G$17,2)" w/o quotes.
Select Chart Layout, Series 2, Format Selection.
Do Line No Line, Marker Style round dot size 8, Marker Fill red.
Copy the data and a picture of the chart with the Shift Key depressed to the Saves worksheet. ,, There are 17 Price vs.
Units Sold observations in this example.
Depending on your population size, a sample size of anywhere from
2.5% to
11.125% will give you a statistically good to extremely high confidence level that your curve accurately reflects your true economic situation, with tolerable error bounds.
2.5% applies to large populations,
11.125% applies to smaller populations.
Into cell B33, enter y, and into cell B34, enter py.
Enter the following Prices, top to bottom, into cell range B35:
B51: 1, 5,
7.5, 8,
8.5,
9. 15,
15.5, 16, 20, 26,
26.5, 27, 30, 31, 32 and
35.
Into cell C33, enter x, and into cell C34, enter X=UNIT FINDS.
Enter the following Unit Sales found per research into cell range C35:
C51: 85,
63.75,
48.25,
45.75,
43.25, 41,
23.75, 23, 22, 17, 13,
12.75,
12.5,
11.5, 11,
10.75 and
10.
Go to cell C52 and Format Cell Border red bold outline and input the formula w/o quotes, "=SUM(C35:
C51)".
Copy this cell C52 to cell range D52:
E52.
Go to cell D53 and Format Cell Border red bold outline and input the formula w/o quotes "=D52/C52" and format cell number number percentage 2 decimal places, and format fill bright yellow.
This is the percentage cell we want to be lower than a certain upper bound, say 4%. , Enter to cell E33, aligned left, the note of the final formula arrived at:
X = 60/SQRT(py+2) + 95*(0.85^py)
- (48/(py^2+1)) and format the columns it runs over format fill yellow and format the font of cell range E33:
H51 blue-purple, or 'electric blue' it's sometimes called.
Enter to E34 SUM of:, enter to F34 60/SQRT(py+2), enter to G34 +95*(0.85^py) and enter to H34
-(48/(py^2+.5)).
Note that the sum of 60+95-48 = 107, or 100% of the market units of 85, plus a large fudge factor because of the action of the denominators.
If the denominators take back about
6.5% overall, it'll work out.
Mine take back about 9%
-- off by
2.7323% actually, which is acceptable as curves go for our purposes.
Goal seeking can easily remedy the situation.
This is found by taking column C *1.07 * a variable called Rate, and summing the results, then Goal Seeking versus either C52 or E52 and changing the Rate cell until the difference in Sum to UNIT FINDS or Sum of: is zero.
The average of all the rates over all py's, taking them as =(60/107)/SQRT(py+2), =95/107*(0.85^py) and =-(48/107)/(py^2+5) (less the top rate which skews the results) is
23.23%, or 107-85, approx.
By multiplying this total rate by 107, we can come close to the figure under the Sum of: column used to create the curve.
We're saying that we can account for ALL the buyers' willingness to buy a given quantity of units at a given price
-- the market is summed altogether in the chart.
Select cell range E35:
E51 and enter to cell E35 the formula w/o quotes "=SUM(F35:
H35)" and Edit Fill Down.
In the first formula, 60/SQRT(py+2), we're basically saying that 60% of the market is influenced by a factor which is diminished by the square root of (price +$2), the effect of which is to account for
34.64 of the 85 units sold (almost half) but
9.86 of the bottom 10 units sold, as the formula's effect indeed increases with fewer units sold.
So this effect is like a bargain-hunter's effect and is telling us how price-sensitive the units are in terms of elasticity.
It's saying nearly half to all of the purchasing decision is based on price alone.
Enter to cell range F35:
F51 into cell F35 the formula w/o quotes "=60/SQRT(py+2)" and Edit Fill Down.
In the second formula, 95*(0.85^py), we're basically saying that 95% of the market is influenced by another big factor: competition
-- what items are out there which are near-substitutes for our item.
We are competing well on price, but we are also competing on relative value in a dynamic economy.
This factor is very important over large sales of our unit, accounting for
80.75 of the 85 units, but diminishes in importance to only .32 of the last 10 units.
Therefore, the effect of the denominator as a factor on the 95 grows steadily less and less until it's near zero.
Enter to cell range G35:
G51 into cell G35 the formula w/o quotes "=95*(0.85^py)" and Edit Fill Down.
In the last and final formula of
-(48/(py^2+0.5)), we have a take-back where we are losing business to our competition on marketing savvy, packaging, discounting, sales force motivation, competitive compensation etc.
-- on mega sales only.
Notice that we lose 32 unit sales at the level of 85 unit sales but only
1.88 unit sales just below it at
63.75 unit sales.
Evidently, There's some sort of magic large quantity sale we're missing the boat on that doesn't much affect the vast majority of our sales.
Perhaps 48 is the number of sales reps we have and we just need 2 more or something.
You play with it and figure it out! In the meantime, enter it first by entering to cell range H35:
H51 into cell H35 the formula w/o quotes "-(48/(py^2+0.5))" and Edit Fill Down. , Delete the series which is for columns B and D, leaving only 2 series, one for B and C and one for B and E.
Click in the plot area, select Series 1 of the two plot series via Chart Layout. If it is for B and C, insert the Chart Title ""Findings" via editing the series formula in the formula bar until it reads as follows: "=SERIES("Findings"
Regression!$C$35:$C$51,Regression!$B$35:$B$51,1)" w/o quotes.
Do Chart Layout Current Selection Series "Findings" and Format Selection Line
- No Line, Marker Style red dot size 8, Marker Fill red.
Then do Current Selection Series 2 and edit it in the formula bar until it reads as follows: "=SERIES("Demand Function Curve"
Regression!$E$35:$E$51,Regression!$B$35:$B$51,2)"
w/o quotes and do Format Selection, Smoothed Line Black 2 pt.
No Markers.
However, Price is on the bottom and Units are along the vertical, which is backwards
-- we need to fix that.
Now here's the tricky part:
Select either series and do menu item Chart Source Data ... and click on each series and exchange manually the X-values' column letters for the Y-values' column letters.
For example, if the X value read =Regression!$B$35:$B$51, change them to read =Regression!$C$35:$C$51, and do the opposite for the Y-values, and then do the other series.
Demand Function Curve should end up with the X values reading "=Regression!$E$35:$E$51" and the Y-values reading "=Regression!$B$35:$B$51"
-- in both cases, the Y-values should refer to B column.
Lastly, add the above title, as shown in the picture, and the axes labels, font size
16. , For more art charts and graphs, you might also want to click on Category:
Microsoft Excel Imagery, Category:
Mathematics, Category:
Spreadsheets or Category:
Graphics to view many Excel worksheets and charts where Trigonometry, Geometry and Calculus have been turned into Art, or simply click on the category as appears in the upper right white portion of this page, or at the bottom left of the page. -
Step 3: the relationship is inverse: Price Y determines Units Demanded X
-
Step 4: or f(y) = x.
-
Step 5: Create the Column Headings and columns for the Data Findings per Research data subset of the Regression worksheet: Into cell A2
-
Step 6: enter #.
-
Step 7: Input the Prices at which Demand was found: Into cell A3
-
Step 8: enter 1 and select cell range A3:A16 and do Edit Fill Series Column Linear Step Value 1
-
Step 9: Enter the number of Units Sold ("UNIT FINDS") per research at the above prices into column C: From C3:C16
-
Step 10: top to bottom
-
Step 11: enter the following units: 60
-
Step 12: 31.25 and 27.
-
Step 13: Enter the slope m's Numerator and Denominator column formulas: Select cell range D3:D16 and enter to D3 w/o quotes the formula "=(C3-X_BAR)*(B3-Y_BAR)" and Edit Fill Down.
-
Step 14: Find slope m
-
Step 15: x intercept b and the Forecast Unit Sales based on the Mean Price: Enter m to cell D18 and into cell E18 enter the formula w/o quotes "=D17/E17".
-
Step 16: Determine the coordinates of the Regression Line of the Demand Function Curve per research: Enter to cell D23 Line P=Y and enter to cell E23 Regression Line X Enter the following Prices to cell range D24:D28
-
Step 17: top to bottom: 0
-
Step 18: "=Y_BAR"
-
Step 19: "=-$E$19/$E$18".
-
Step 20: Create the Chart "Regression Line Demand Function Curve per Research Findings" Copy E23:E28 and Paste to A23:A28.
-
Step 21: we'll do the data and chart for a curve that is not a straight line
-
Step 22: using Multi-Variable Analysis
-
Step 23: via pieces until we arrive at a curve that's an acceptable percentage within difference from the findings.
-
Step 24: Enter the Preliminary Headings and Data: Into cell A34
-
Step 25: enter n. Into cell A35
-
Step 26: enter 1 and select cell range A35:A51 and do Edit Fill Series Column Linear Step Value = 1
-
Step 27: Enter the rest of the Multi-Variable Data Headings and Formulas: Enter to cell D34 Diff and enter to cell range D35:D51 into the cell D35 the formula w/o quotes "=C35-E35" and Edit Fill Down.
-
Step 28: Create the Chart of "Multi-Variable Analysis of Demand Function Curve Into Pieces w/ Acceptable Percentage Difference < 3%" Select the cell range B35:E51 and
-
Step 29: using the Chart Wizard or Charts from the Ribbon
-
Step 30: select Charts
-
Step 31: All/Other
-
Step 32: Scatter
-
Step 33: Smoothed Line Scatter.
-
Step 34: Make use of helper articles when proceeding through this tutorial: See the article How to Create a Spirallic Spin Particle Path or Necklace Form or Spherical Border for a list of articles related to Excel
-
Step 35: Geometric and/or Trigonometric Art
-
Step 36: Charting/Diagramming and Algebraic Formulation.
Detailed Guide
But Excel only accepts x values first in the left most column, then however many dependent y variable columns one wants.
However, Excel allows one to reverse the columns when charted, so that it all works out for cases like this one in which x is the dependent variable.
We will be using tricks like taking the standard formula for a line, y = mx + b, and standing it on its head to become x = mP +b where P=y; we can do so because the dependent and independent variable have exchanged roles.
Open an Excel workbook and create 3 worksheets:
Regression, Demand Curves and Saves.
We'll be using Demand Curves in the next article.
Into cell B1, enter y, and into cell B2, enter P=Y.
Into cell C1, enter x, and into cell C2, enter X=UNIT FINDS.
Into cell D1, enter m, and into cell D2, enter Numerator. m = slope.
Into cell E1, enter m, and into cell E2, enter Denominator.
Format Cell Range columns A:
E bold.
Format column B blue-purple and format cells number number currency $ symbol decimal places
2.
Format column C red.
Format columns C:
E decimal places
2. , There were 14 observations upon which we're basing our demand function model.
From B3 to B16, top to bottom, enter the following prices: 3, 6,
6.5, 7,
12.5, 13, 16, 19,
21.5, 25, 29, 30, 32 and
35.
These are the prices your item sold at over various quantities, per research below.
Select column B and Insert Name Define Name p to column $B.
Select column B and Insert Name Define Name py to column $B.
Enter MEAN to cell A17 and into cell B17 input the formula w/o quotes "=AVERAGE(B3:
B16)".
Copy this formula and do Paste Special Formula to cell C17.
Insert Name Define Name X_Bar to cell $C$17 and Insert Name Define Name Y_Bar to cell $B$17. ,, Select cell range E3:
E16 and enter to E3 w/o quotes the formula "=(C3-X_BAR)^2" and Edit Fill Down.
Format cell D17 Border red bold outline and input the formula w/o quotes "=SUM(D3:
D16)" and copy and paste this formula and cell format to cell E17. , Enter b into cell D19 and into cell E19 enter the formula w/o quotes "=Y_BAR-E18*X_BAR".
Select D18:
E19 and Insert Name Create Names in Left Column, OK.
Format cell range E18:
E19 decimal places
6.
Enter to cell E20 X' = mP + b; P=Y and format font red.
Enter to cell D21 Forecast Unit Sales per MEAN Price, aligned right, and enter to cell E21 w/o quotes the formula "=m*Y_BAR+b". , Select cell range E24:
E28 and enter to E24 w/o quotes the formula "=m*D24+b". , Copy D23:
D28 and Paste to B23:
B28.
Select A23:
B28 and choose Charts, All/Other, Scatter, Smooth Line Scatter from the Ribbon or Chart Wizard.
Add the above title to the Chart.
Add the Vertical Axis Title PRICE and the Horizontal Axis Title UNITS DEMANDED per Research Findings.
Copy C2:
C17 and Paste to F2:
F17.
Copy B2:
B17 and Paste to G2:
G17.
Click in the chart's Plot Area and do menu item Chart Add Data.
In response to the query, select F3:
G17, OK.
This may not occur as desired and what you want is the series to appear in the formula bar as follows: "=SERIES(,Regression!$F$3:$F$17,Regression!$G$3:$G$17,2)" w/o quotes.
Select Chart Layout, Series 2, Format Selection.
Do Line No Line, Marker Style round dot size 8, Marker Fill red.
Copy the data and a picture of the chart with the Shift Key depressed to the Saves worksheet. ,, There are 17 Price vs.
Units Sold observations in this example.
Depending on your population size, a sample size of anywhere from
2.5% to
11.125% will give you a statistically good to extremely high confidence level that your curve accurately reflects your true economic situation, with tolerable error bounds.
2.5% applies to large populations,
11.125% applies to smaller populations.
Into cell B33, enter y, and into cell B34, enter py.
Enter the following Prices, top to bottom, into cell range B35:
B51: 1, 5,
7.5, 8,
8.5,
9. 15,
15.5, 16, 20, 26,
26.5, 27, 30, 31, 32 and
35.
Into cell C33, enter x, and into cell C34, enter X=UNIT FINDS.
Enter the following Unit Sales found per research into cell range C35:
C51: 85,
63.75,
48.25,
45.75,
43.25, 41,
23.75, 23, 22, 17, 13,
12.75,
12.5,
11.5, 11,
10.75 and
10.
Go to cell C52 and Format Cell Border red bold outline and input the formula w/o quotes, "=SUM(C35:
C51)".
Copy this cell C52 to cell range D52:
E52.
Go to cell D53 and Format Cell Border red bold outline and input the formula w/o quotes "=D52/C52" and format cell number number percentage 2 decimal places, and format fill bright yellow.
This is the percentage cell we want to be lower than a certain upper bound, say 4%. , Enter to cell E33, aligned left, the note of the final formula arrived at:
X = 60/SQRT(py+2) + 95*(0.85^py)
- (48/(py^2+1)) and format the columns it runs over format fill yellow and format the font of cell range E33:
H51 blue-purple, or 'electric blue' it's sometimes called.
Enter to E34 SUM of:, enter to F34 60/SQRT(py+2), enter to G34 +95*(0.85^py) and enter to H34
-(48/(py^2+.5)).
Note that the sum of 60+95-48 = 107, or 100% of the market units of 85, plus a large fudge factor because of the action of the denominators.
If the denominators take back about
6.5% overall, it'll work out.
Mine take back about 9%
-- off by
2.7323% actually, which is acceptable as curves go for our purposes.
Goal seeking can easily remedy the situation.
This is found by taking column C *1.07 * a variable called Rate, and summing the results, then Goal Seeking versus either C52 or E52 and changing the Rate cell until the difference in Sum to UNIT FINDS or Sum of: is zero.
The average of all the rates over all py's, taking them as =(60/107)/SQRT(py+2), =95/107*(0.85^py) and =-(48/107)/(py^2+5) (less the top rate which skews the results) is
23.23%, or 107-85, approx.
By multiplying this total rate by 107, we can come close to the figure under the Sum of: column used to create the curve.
We're saying that we can account for ALL the buyers' willingness to buy a given quantity of units at a given price
-- the market is summed altogether in the chart.
Select cell range E35:
E51 and enter to cell E35 the formula w/o quotes "=SUM(F35:
H35)" and Edit Fill Down.
In the first formula, 60/SQRT(py+2), we're basically saying that 60% of the market is influenced by a factor which is diminished by the square root of (price +$2), the effect of which is to account for
34.64 of the 85 units sold (almost half) but
9.86 of the bottom 10 units sold, as the formula's effect indeed increases with fewer units sold.
So this effect is like a bargain-hunter's effect and is telling us how price-sensitive the units are in terms of elasticity.
It's saying nearly half to all of the purchasing decision is based on price alone.
Enter to cell range F35:
F51 into cell F35 the formula w/o quotes "=60/SQRT(py+2)" and Edit Fill Down.
In the second formula, 95*(0.85^py), we're basically saying that 95% of the market is influenced by another big factor: competition
-- what items are out there which are near-substitutes for our item.
We are competing well on price, but we are also competing on relative value in a dynamic economy.
This factor is very important over large sales of our unit, accounting for
80.75 of the 85 units, but diminishes in importance to only .32 of the last 10 units.
Therefore, the effect of the denominator as a factor on the 95 grows steadily less and less until it's near zero.
Enter to cell range G35:
G51 into cell G35 the formula w/o quotes "=95*(0.85^py)" and Edit Fill Down.
In the last and final formula of
-(48/(py^2+0.5)), we have a take-back where we are losing business to our competition on marketing savvy, packaging, discounting, sales force motivation, competitive compensation etc.
-- on mega sales only.
Notice that we lose 32 unit sales at the level of 85 unit sales but only
1.88 unit sales just below it at
63.75 unit sales.
Evidently, There's some sort of magic large quantity sale we're missing the boat on that doesn't much affect the vast majority of our sales.
Perhaps 48 is the number of sales reps we have and we just need 2 more or something.
You play with it and figure it out! In the meantime, enter it first by entering to cell range H35:
H51 into cell H35 the formula w/o quotes "-(48/(py^2+0.5))" and Edit Fill Down. , Delete the series which is for columns B and D, leaving only 2 series, one for B and C and one for B and E.
Click in the plot area, select Series 1 of the two plot series via Chart Layout. If it is for B and C, insert the Chart Title ""Findings" via editing the series formula in the formula bar until it reads as follows: "=SERIES("Findings"
Regression!$C$35:$C$51,Regression!$B$35:$B$51,1)" w/o quotes.
Do Chart Layout Current Selection Series "Findings" and Format Selection Line
- No Line, Marker Style red dot size 8, Marker Fill red.
Then do Current Selection Series 2 and edit it in the formula bar until it reads as follows: "=SERIES("Demand Function Curve"
Regression!$E$35:$E$51,Regression!$B$35:$B$51,2)"
w/o quotes and do Format Selection, Smoothed Line Black 2 pt.
No Markers.
However, Price is on the bottom and Units are along the vertical, which is backwards
-- we need to fix that.
Now here's the tricky part:
Select either series and do menu item Chart Source Data ... and click on each series and exchange manually the X-values' column letters for the Y-values' column letters.
For example, if the X value read =Regression!$B$35:$B$51, change them to read =Regression!$C$35:$C$51, and do the opposite for the Y-values, and then do the other series.
Demand Function Curve should end up with the X values reading "=Regression!$E$35:$E$51" and the Y-values reading "=Regression!$B$35:$B$51"
-- in both cases, the Y-values should refer to B column.
Lastly, add the above title, as shown in the picture, and the axes labels, font size
16. , For more art charts and graphs, you might also want to click on Category:
Microsoft Excel Imagery, Category:
Mathematics, Category:
Spreadsheets or Category:
Graphics to view many Excel worksheets and charts where Trigonometry, Geometry and Calculus have been turned into Art, or simply click on the category as appears in the upper right white portion of this page, or at the bottom left of the page.
About the Author
Ruth Lee
Ruth Lee is an experienced writer with over 6 years of expertise in lifestyle and practical guides. Passionate about sharing practical knowledge, Ruth creates easy-to-follow guides that help readers achieve their goals.
Rate This Guide
How helpful was this guide? Click to rate: