How to create a data series for exponential decrease diagram

Question

I want to create a pricing model this way:

If you need this product for 50 people, you should pay 2500 (units) per person.
.
.
.
.
If you need this product for 10,000 people, you should pay 300 (units) per person.

This pricing model encourages the sales of the product, in higher quantities. Because lower quantities are way more expensive.

Thus I created a sheet, and two columns, with a single row of header (a simple table-like sheet). One is People Count, the other one is Price.

However, I'm stuck at how to fill in the series of price, to make an exponentially decreasing diagram like any curve in this diagram:

I'm using Google Spreadsheets (I might also fall back to Microsoft Excel), and I'm trying to find out what functions should I use. But due to the lack of mathematical knowledge, I can't even find the correct terms to search.

How should I fill the series? How can I set the change rate, or diagram slope?

Answer 1

On cell B3 put this:

=$B$2*EXP(-A3/1000)+230

This formula uses a product of:

• The initial price $B$2: 2500
• And the decaying factor: EXP(-A3/1000)

plus a floor factor to avoid getting to zero (230).

Now, drag this to copy to all the rows.

The results are:

  Count    Price

   50       2500
  100       2492
  500       1746
 1000       1150
 2000        568
 2500        435
 5000        247
 5500        240
 9000        230
10000        230

From around 9000 it gets to the floor and from then, the price will be the same (230).

You have to play with the decaying factor and the floor to get the results that you want.

Answer 2

The algebraic formula for exponential decay is a•e^kt where k is less than zero.

In your example, a is 2526.78 (your price for 0 count), t will be column A. For k here is the formula

=-LN(2526.78/300)/10000

I would place your high value in C2, your low value in D2 and the corresponding high count into E2, that way you only need to change then there to modify your rate of decay. So the formula for k would look like this:

        F2
=-LN($C$2/$D$2)/$E$2

I would also put this formula into F2 so that you only have to reference that cell in your main formula.

In the price column the formula will be:

           B
=round($C$2*exp($F$2*$A2),2)

$C$2 is the value of your 0 price (2526.78).

$F$2 is your decay constant.

A2 is the value of t (In your case the number of customers)

Enter the above formula into B2 and copy down. This will give you the following spreadsheet:

Count   Price        High Price    Low Price   High Count          k
 50   $2,500.00     2526.78        300        10000     -0.0002130918569
100   $2,473.51             
150   $2,447.29             
200   $2,421.36             
250   $2,395.69             
300   $2,370.30             
350   $2,345.18             
400   $2,320.33             
...      ...

I did this all on Google Sheets, as you said that it was what you wanted to use, so it should work there, but Excel has all the corresponding functions should you need to use Excel.