Calculate repeat sales

[y3kesprit]

Hi guys,

I am running a table to estimate the amount of sales I can generate.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]June[/TD]
[/TR]
[TR]
[TD]Unit Sold in current month[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]16[/TD]
[TD]18[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Unit Sold from previous months[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Unit Price ($)[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[James006]

Hi,

In cell G5 ... you could test :

=(OFFSET(G5,-1,-5)*0.4)+(OFFSET(G5,-1,-4)*0.45)+(OFFSET(G5,-1,-3)*0.5)+(OFFSET(G5,-1,-2)*0.55)+(OFFSET(G5,-1,15)*0.6)

Hope this will help