Hi guys,
I am running a table to estimate the amount of sales I can generate.
<tbody>
</tbody>
The 2nd row is where I am struggling.
I am looking for a formula that allows me to apply a different % to the sales generated in previous months based on some assumptions I have. This will provide an estimate of sales that are generated as repeat sales through earlier months' customers.
For e.g., under the June column, where n = current month:
Cell "X" will be the sum of the below lines:
n-1: 60% (60% * 18 per May)
n-2: 55% (55% * 16 per Apr)
n-3: 50% (50% * 14 per Mar)
n-4: 45% (45% * 12 per Feb)
n-5: 40% (40% * 12 per Jan)
I am intending to build a two year view. Thus there will be 23 lines in the above table, although my intention is that n-6 onwards will be 20% flat.
Appreciate some guidance here, thanks!
I am running a table to estimate the amount of sales I can generate.
Jan | Feb | Mar | Apr | May | June | |
Unit Sold in current month | 10 | 12 | 14 | 16 | 18 | 20 |
Unit Sold from previous months | X | |||||
Unit Price ($) | 4 | 4 | 4 | 4 | 4 | 4 |
<tbody>
</tbody>
The 2nd row is where I am struggling.
I am looking for a formula that allows me to apply a different % to the sales generated in previous months based on some assumptions I have. This will provide an estimate of sales that are generated as repeat sales through earlier months' customers.
For e.g., under the June column, where n = current month:
Cell "X" will be the sum of the below lines:
n-1: 60% (60% * 18 per May)
n-2: 55% (55% * 16 per Apr)
n-3: 50% (50% * 14 per Mar)
n-4: 45% (45% * 12 per Feb)
n-5: 40% (40% * 12 per Jan)
I am intending to build a two year view. Thus there will be 23 lines in the above table, although my intention is that n-6 onwards will be 20% flat.
Appreciate some guidance here, thanks!
Last edited: