Find and Analyze Demand Function Curve

Revision as of 17:30, 5 August 2016 by Kipkis (Kipkis | contribs) (importing article from wikihow)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

In this article, you'll learn how to reverse the X's and Y's in a chart as you Find and Analyze Demand Function Curves. Analyzing a curve is very important -- to know the function of a curve solves a lot of the mystery behind it and often makes it renderable by machine.

Steps

  • Become familiar with the basic image to create:

The tutorial

  1. 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. 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.
  2. Create the Column Headings and columns for the Data Findings per Research data subset of the Regression worksheet:
    • Into cell A2, enter #.
    • 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.
  3. Input the Prices at which Demand was found:
    • Into cell A3, enter 1 and select cell range A3:A16 and do Edit Fill Series Column Linear Step Value 1, OK. 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.
  4. Enter the number of Units Sold ("UNIT FINDS") per research at the above prices into column C:
    • From C3:C16, top to bottom, enter the following units: 60, 58, 57, 56.5, 51.75, 48.5, 46, 43, 40.5, 39.25, 32.5, 32, 31.25 and 27.
  5. 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.
    • 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.
  6. Find slope m, 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".
    • 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".
  7. 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, top to bottom: 0, 10, "=Y_BAR", 35, "=-$E$19/$E$18".
    • Select cell range E24:E28 and enter to E24 w/o quotes the formula "=m*D24+b".

Explanatory Charts, Diagrams, Photos

  • (dependent upon the tutorial data above)
  1. Create the Chart "Regression Line Demand Function Curve per Research Findings"
    • Copy E23:E28 and Paste to A23:A28. 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.
  2. Next, we'll do the data and chart for a curve that is not a straight line, using Multi-Variable Analysis, via pieces until we arrive at a curve that's an acceptable percentage within difference from the findings.
  3. Enter the Preliminary Headings and Data:
    • Into cell A34, enter n. Into cell A35, enter 1 and select cell range A35:A51 and do Edit Fill Series Column Linear Step Value = 1, OK. 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%.
  4. 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.
    • 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.
  5. 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, using the Chart Wizard or Charts from the Ribbon, select Charts, All/Other, Scatter, Smoothed Line Scatter. 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.

Video Assistance

Helpful Guidance

  1. 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, Geometric and/or Trigonometric Art, Charting/Diagramming and Algebraic Formulation.
    • For more art charts and graphs, you might also want to click on Microsoft Excel Imagery, Mathematics, Spreadsheets or 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.

Related Articles

Sources and Citations

  • The workbook used for this article was "Various Demand Functions or Curves.xlsx"
Retrieved from "https://kipkis.com/index.php?title=Find_and_Analyze_Demand_Function_Curve&oldid=23778"