Pricing Analytics: Optimizing Price

The “best” price for a product or service is one that maximizes profits, not necessarily the price that sells the most units. This presentation uses real-world examples to explore how Excel’s Solver functionality can be used to calculate the optimal price for any product or service.
Downloadable slides are available from SlideShare at goo.gl/yGmGfq

[@ronaldtroncoso3859]

This is a really good example of a business solution that is quite counter intuitive. I will suggest this video for any economics or business school students.

[@Erynzindulge]

thank you so much, this made it very clear to me
-need an equation linking to your quantity and cost for the objective
-having costs set as changing variable

[@wilfergutierrez1486]

You can also use the formula p*= (A+Bc)/(2B) where A is the intercept (15,000 in the example) and B is the trend (133.33). That is p*= (15,000 + 133.33*59)/(2*133.33) = 85.75

[@mr_shanes]

AWESOME! thank you for sharing the formula, this will save me from doing the solver all the time!

[@mjaworsky2010]

Thank you, I had only ever done optimization with IBM CPLEX software, and didn't realize you could use Excel.

[@lukkash]

you optimise Total Profit cos after transformations you get a QUADRATIC function which as a curve has a max point.
If you study economic course then definitely read *_Managerial Economics_* by Samuelson, Marks. There is a whole chapter about that kind of optimisation calcs using demand curve.

[@Drzmabelle]

Thank you! I was able to do my homework after I saw this!

[@lautanax]

Awesome!

[@ThePorschefan]

I sugest complementing this with a video of simple linear regression and efficient frontier. you will picture in your head what the solver function is doing

[@DaddyF2P]

Efficient frontier? Are you implying a "short sell" on printers and "longing" the cartridges? That is a weird way to visualise this. I understand it as simply picking a point on the line that results in the biggest squared area in the chart from that point to (0,0).

[@CS-ci2gh]

For some reason, I get really wierd values for the second example and don't get 525,112.42 as the total profit

[@elphau]

hi, this may be 1 year later, I think the reason is the formula at 6:30. if you enter 15000-133.3*B7, instead of 15000-133.33*B7 (note the decimal places), you would get 525, 112.42

[@callahanelzey7655]

I love the video, although I have a question... where are we getting p=75.75 and d=4900?

[@williamtourot5601]

rice elasticity = 2 that means if the price increases 1%, the quantity decreases 2%. Price increases 1% = 75.75 and the quantity decreases 2% = 4900

[@emma.data-life]

@@williamtourot5601 Thank you so much William!

[@Aviver31]

How come the other point on the demand curve was 75.75 and quantity of 4900? why not 80 and 4800

[@yensebi137]

price elasticity = 2 that means if the price increases 1%, the quantity decreases 2%. Price increases 1% = 75.75 and the quantity decreases 2% = 4900

[@waynerisner4974]

thank you

[@ThePorschefan]

Watch how simple regression works, that's what is called "the slope", there are very good videos on youtube about it.

[@jackiechan3757]

where does the '$8/$9' change to '$8$7' come from?

[@Jotita22]

What if you have 5% of people that are able to get the lower price, i.e. cannibalization? How do you represent this in Solver?

[@merrelljohnson]

Can you tell me how you came up with the Demand formula?

[@excelworx8712]

current price P0 1.5
current qnty Q0 2000
new price P1 1
new qnty Q1 4000

Q1-Q0 2000
Q1+Q0 6000
(Q1-Q0)/(Q1+Q0) 0.333333333 A

P1-P0 -0.5
P1+P0 2.5
(P1-P0)/(P1+P0) -0.2 B

A/B -1.666666667

[@lukkash]

start from y = ax + b
Y = B - Ap ; p-price, a,b-coefficients
cos it's linear model and higher price causes a decrease in demand.
Use at least two points (price,quantity) but in real cases you may need more data to see some pattern.

[@Klesk1985]

Boh da queste parti.. 👟