AD1080
asked on
Price Elasticity Chart
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)
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)
ASKER
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?
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?
ASKER
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
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
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
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
ASKER
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
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
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
ASKER
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
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
Perhaps as in the attached file.
Patrick
demand-price-elasticity-01.xls