How to Maximize Revenue Per Price Via Excel

Step-by-Step Guide

1. 1

   Step 1: Open a new Excel Workbook and create 2 worksheets

   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 LifeGuide Hubs.
2. 2

   Step 2: Data and Saves.

   D(p) = y) In cell C1, enter E(p) 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). , 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. , 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). , 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. , 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. ,, D(p) =
   -.01p^2
   - .03p + 600  .
   
   Final Image: , 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.
   
   Final Image:
3. 3

   Step 3: Create the Data worksheet Column Headings: In cell A1

4. 4

   Step 4: enter p = x In cell B1

5. 5

   Step 5: enter D of p  = y (i.e.

6. 6

   Step 6: Given the Demand Function D(p) = 975 - 39p

7. 7

   Step 7: find the price that maximizes revenue by finding the price at which demand is unit elastic.

8. 8

   Step 8: 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

9. 9

   Step 9: 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.

10. 10

    Step 10: Create the Chart: Select cell range A2:C27 and using the Chart Wizard or Chart on the Ribbon

11. 11

    Step 11: select Charts

12. 12

    Step 12: All/Other

13. 13

    Step 13: Scatter

14. 14

    Step 14: Smooth Line Scatter.

15. 15

    Step 15: 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.

16. 16

    Step 16: Practice with some example problems: Find the price at which demand is unit elastic for: D(p) = 60e^-.1p where D'(p) = -6e^-.1p  .

17. 17

    Step 17: Make use of helper articles when proceeding through this tutorial: Find and Analyze Demand Function Curve

18. 18

    Step 18: Recognize a Demand Function Curve Among Standard Types

19. 19

    Step 19: Do Cost Volume Profit Analysis

20. 20

    Step 20: 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

21. 21

    Step 21: Geometric and/or Trigonometric Art

22. 22

    Step 22: Charting/Diagramming and Algebraic Formulation.

Detailed Guide

About the Author