Price Elasticity Chart

AD1080
AD1080 used Ask the Experts™
on
Hi,  

I am trying to figure out a way to chart price elasticity on an array of items.  I've included a formula below for price elasticity, which works fine for one items data.  Maybe a pivot table with a calculated field?  

My data looks like

ITEM_NO     QTY_SOLD      DOLLAR_SALES
10001          25                    200.00
10001          12                    350.00
10002          20                    200.00
10002          15                    300.00

Price Elasticity of Demand = % change in demand / % change in price

     Change in Demand  = (Quantity2 - Quantity1) / ((Quantity1 + Quantity2)/2)
     Change in Price % = (Price2 - Price1) / ((Price1 + Price2)/2)



Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AD1080,

Perhaps as in the attached file.

Patrick
demand-price-elasticity-01.xls

Author

Commented:
Hi Patrick,

I don't think this is achieving the goal I am after.

I see item number 10002 twice with two elasticity values.  

Having achieved my aim, there would only be one value for each item number.  Do you know how I could do this, and chart it?  
AD1080,

Perhaps as in the attached file.

Patrick
demand-price-elasticity-02.xls
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Author

Commented:
Hi Patrick,

I apologize as I didn't include an important detail to my original question.

I will have 26 rows of data for each item number.

Having added that complication, do you see a way to achieve my goal?  

Thanks
Ariel
Ariel,

Please upload your file with the data.

Patrick
Ariel,

Please also let me know the way in which the elasticity should be calculated for 26 data points?

For example should it be:

Change in Demand  = (MaxQuantity2 - MinQuantity1) / ((MaxQuantity1 + MinQuantity2)/2)
Change in Price % = (MaxPrice2 - MinPrice1) / ((MaxPrice1 + MinPrice2)/2)

or some other basis?

Patrick

Author

Commented:
Hi,

Here is a file with some of the sales data I am working with.  

I have 26 sales periods.  Using the sales quanity and unit price from each of these periods, I want to calculate the overall elasticity of each unique item number.

Does that clarify my intent well enough?  I am not sure if your example above is applicable to my goal or not.  
26-Week-Sales-Data.xls
Ariel,

You still have not told me how you have only one number from 26 points of data for elasticity. Until you do that I cannot extract those numbers.

Patrick

Author

Commented:
Hi Patrick,

Would you be willing to suggest what you would consider the most effective means to determine the elasticity of demand relative to price, considering the 26 sales periods, and the volume and retail price during each of those periods?  

Ariel
Ariel,

I haven't clue how it's meant to be done! I will attmpt to do whatever you consider to be correct. Over to you...

Patrick
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
AD1080,

I think that part of the issue here is that it seems like you, yourself, are grappling with how this should be done.

Than coupled with the fact that I know patrickab as one of the top Excel Experts here.
So if patrickab is having difficulty doing anything, we must take a closer look at the way this question is being framed.
;-)

So perhaps we should go back to the beginning...

If we take a look at your original post you state that:
  "I've included a formula below for price elasticity, which works fine for one items data"

Then with your "new" data:
  "26 sales periods, and the volume and retail price during each of those periods"

Then question then becomes:
   Is your original formula still valid for the new data (structure)?

Another missing piece of this puzzle is what, exactly, the final chart supposed to look like?
By this I mean that we do not need a written "description" of the chart, we need to see a "graphical" representation of the output you are expecting, based on your sample data.

Thanks

;-)

JeffCoachman

Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
One way of looking at this problem is to get the slope of the demand vs price curve, then multiply that by the average price over average demand. The sample workbook shows a scatter chart with the least squares trend line plotted and its equation. The data is not particularly convincing--so do take the calculated price elasticity with a grain of salt.

The slope of the least squares trend line may be returned by the SLOPE function:
=SLOPE(D2:D9,F2:F9)
and the corresponding price elasticity would be returned by:
=-SLOPE(D2:D9,F2:F9)*AVERAGE(F2:F9)/AVERAGE(D2:D9)

I generalized the above formula for a product code stored in cell J2 with this monster of a formula:
=-SLOPE(OFFSET(INDEX(B$2:B$1000,MATCH(J2,B$2:B$1000,0)),0,2,26),OFFSET(INDEX(B$2:B$1000,MATCH(J2,B$2:B$1000,0)),0,4,26))*AVERAGE(OFFSET(INDEX(B$2:B$1000,MATCH(J2,B$2:B$1000,0)),0,4,26))/AVERAGE(OFFSET(INDEX(B$2:B$1000,MATCH(J2,B$2:B$1000,0)),0,2,26))

Your best bet would be to build a PivotTable to generate a list of the unique item numbers, then to apply a formula like above to find the price elasticity. The second worksheet in the sample workbook shows how that might work.
26-Week-Sales-DataQ26548442.xls
Ariel,

I have taken the min amd max of volume and price by Item Code and barcharted the outcome. It's in the attached file.

Patrick
demand-price-elasticity-03.xls

Author

Commented:
Thanks Patrick, Jeff, and Byundt,

While my question was poorly phrased, you managed to understand what I was trying to do.

Really appreciate it.

Ariel
Ariel - Thanks for the points - Patrick
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Thanks,

My interest here was only in the "Theory" behind your question.
My hope was that my request for clarification would help other Experts to pitch in.

I also have a keen interest in Charts/Graphs form my MS Access experience.

FWIW, I have no issue with you giving my share of the points back to patrickab and byundt

;-)

Jeff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial