Maximize Revenue Per Price Via Excel

Given a Demand Function, D(p), you can find the price that maximizes revenue (but not necessarily profits) by finding the price at which demand is unit elastic, which is an important consideration for strategizing about market share.

Steps

  • Become familiar with the image to create:

The tutorial

  1. Open a new Excel Workbook and create 2 worksheets, Data and Saves. You will need to understand how to take derivatives in calculus to find this article useful. You can learn of the process a bit here and more from the articles in Related WikiHows.
  2. Create the Data worksheet Column Headings:
    • In cell A1, enter p = x
    • In cell B1, enter D of p  = y (i.e. D(p) = y)
    • In cell C1, enter E(p) [i.e. the Elasticity of Demand at price p. Usually Greek letter eta, for which you can use n. When n < 1, demand is inelastic and a price increase causes revenue to increase, and when n > 1, demand is elastic and a price increase causes revenue to decrease. When n = 1, demand is unit elastic or has unit elasticity and is passing through a maximum from where the revenue function is increasing to decreasing. The Revenue function is R=p*D(p) = xy. Using the Product Rule of Calculus gives us R' = 1*D(p) + p*D'(p). Setting R' to 0 and dividing through by D(p) gives us -p*D'(p) / D(p) = 1 so E(p) = 1.]
    • In cell D1, enter D Prime p (i.e. D'(p), the derivative of D(p).)
    • In cell E1, enter R=p*D(p)=xy (i.e. the Revenue Function).
  3. Given the Demand Function D(p) = 975 - 39p, find the price that maximizes revenue by finding the price at which demand is unit elastic.
    • Find the Derivative D'(p) (or D Prime p). This = -39.
    • Find E(p) = -p*D'(p) / D(p) = p*39 / (975 - 39p)
    • Set E(p) = to 1:  p*39 / (975 - 39p) = 1
    • Solve for p:  39p = 975 - 39p; 78p = 975; p = 12.5 
    • Revenue is maximized when price p = $12.50 and demand is unit elastic.
  4. Create the Chart Data:
    • Edit Go To cell range A2:A27 and enter to A2 5 and Edit Fill Series Column Linear Step Value .5, OK. Insert Name Define Name p for column A.
    • Edit Go To cell range B2:B27 and enter the formula w/o quotes "=975-39*p" and Edit Fill Down. Insert Name Define Name D_of_p to column B.
    • Edit Go To cell range C2:C27 and enter the formula w/o quotes "=-p*D_Prime_p/D_of_p" and Edit Fill Down. This column is next because it is in chart order next to y and easier that way.
    • Edit Go To cell range D2:D27 and enter -39 and Edit Fill Down. Insert Name Define Name D_Prime_p to column D.
    • Edit Go To cell range E2:E27 and enter w/o quotes the formula "=p*D_of_p" and Edit Fill Down.
    • Format Cell Fill yellow for cell range A17:E17 where p=12.5. D(p) = 487.50, E(p) = 1, D'(p)=-39 and R = $6.093.75 (at a local maximum of Revenue).

Explanatory Charts, Diagrams, Photos

  • (dependent upon the tutorial data above)
  1. Create the Chart:
    • Select cell range A2:C27 and using the Chart Wizard or Chart on the Ribbon, select Charts, All/Other, Scatter, Smooth Line Scatter. Click on the bottom series 1 or 2 and do Chart Layout  Format Selection, Axis - Plot series on Secondary axis, OK. Edit the chart series title in the formula bar so that it reads "=SERIES("p=x,E(p)=y",Data!$A$2:$A$27,Data!$C$2:$C$27,1)". Click on the other series and edit it in the formula bar until it reads "=SERIES("p=x,D(p)=y",Data!$A$2:$A$27,Data!$B$2:$B$27,2)".
    • Select Chart Layout Axes and select Primary Horizontal Axis - Default Axis (or whichever axis aligns the 1 with the grid lines). Also set the horizontal scale to major unit 2.5. Set the Vertical Axis to Default Axis.
    • Select data point {12.5,1) and make marker square dot, black fill size 8. Select data point (12.5, 487.50) and make marker round circle red dot red fill size 8. Add left-aligned data labels to each pertinent data point.
  2. Interpret the chart as follows:
    • Find 1 on the left vertical where the Elasticity Function E(p) scale is and read to the right until you find where 1 is on the E(p) curve. Read down and find that a E(p)=1, price p =12.5, so then move up the price gridline to the Demand Function D(p) and find the red dot and read that 487.5 units will be demanded at the price of $12.50, for a Total Revenue of $6,093.75 (per the yellow highlighted row #17). At this point, Marginal Revenue = $0.
  4. Practice with some example problems: Find the price at which demand is unit elastic for:
    • D(p) = 60e^-.1p where D'(p) = -6e^-.1p  [Answer:  p = 10].
    • D(p) = -.01p^2 - .03p + 600  [Answer to the nearest dollar is about $140].
    • Final Image:

Video Assistance

Helpful Guidance

  1. Make use of helper articles when proceeding through this tutorial: Find and Analyze Demand Function Curve, Recognize a Demand Function Curve Among Standard Types, Do Cost Volume Profit Analysis, Do Economic Order Quantity Analysis
    • 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.
    • Final Image:

Related Articles

Sources and Citations

  • The workbook used for this article was "Find Price To Max Rev.xlsx"
  • "Business Calculus Demystified", Rhonda Huettenmueller, McGraw Hill, 2006, San Francisco, Chapter 12 - "Elasticity of Demand", p. 322 problems.
Retrieved from "https://kipkis.com/index.php?title=Maximize_Revenue_Per_Price_Via_Excel&oldid=343900"