Simulate Reoccuring Buys

[prosyp]

Hi!

So far I was just a still follower, now i have problem where my mind currently is blocked. What I want to do:

I'm building currently a business case for our startup. My timescale is monthly (C2 = Jan2018, D2 = Feb 2018.... What i need to simulate is:

• If we onboard a new customer in Jan18 he will do one purchase in Jan2018 (C3 =1)
• But he will also do purchases later in the processs
• This LATER should be definied as variables in the model that can be changed easily.
• The variables are:
• -- Number of total buys (e. g. 10)
• -- Number of month between the buys (e.g 2 month between all the buys
• So following this logic every 2 third month should be one additionl buy from an existing customer (e. g. in Cell e4,h4,...)

Does anyone have an idea how to model that using formulars?

THanks!

Best regards,

Sebastian

 

[cerfani]

A screenshot of what the result should look like would be helpful. From what I understand though, depending on the variable values this table will vary in size so I would just have a macro create it. Are you just trying to put a 1 under each month a purchase is made and each row represents a customer?

Sub ListPurchases()
    Dim totalBuys As Long, monthsBetween As Long, totalMonths As Long, i As Long, monthsWaited As Long
    Dim d As Date

    d = DateValue("Jan 1, 2018")
    totalBuys = Range("A1")
    monthsBetween = Range("A2")
    monthsWaited = monthsBetween [COLOR=#008000]'set this so the first loop sets a purchase, see logic below inside For loop[/COLOR]
    totalMonths = totalBuys + monthsBetween * (totalBuys - 1)

    For i = 1 To totalMonths
        d = DateAdd("m", i - 1, d)
        Cells(2, 2 + i) = d 'list months on row 2 starting at C

        If monthsWaited = monthsBetween Then [COLOR=#008000]'whenever the months waited equals the months between, it means to list a purchase on row 3
[/COLOR]            monthsWaited = 0 [COLOR=#008000]'reset months waited[/COLOR]
            Cells(3, 2 + i) = 1 [COLOR=#008000]'list purchase[/COLOR]
        Else
            monthsWaited = monthsWaited + 1 [COLOR=#008000]'increment months waited by 1 month[/COLOR]
            Cells(3, 2 + i) = 0 [COLOR=#008000]'no purchase listed[/COLOR]
        End If
    Next i
End Sub

it is a rough draft and untested but it seems to follow the logic you described. You can modify this to suit your needs or maybe get an idea from it.

this would take the input variables from A1 and A2 and generate a table of purchases on rows 2 and 3, starting at column C

 

[shg]

How about a formula?

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

1​

Customer​

Qty​

Interval​

Start​

DoNotUse

Jan-18​

Feb-18​

Mar-18​

Apr-18​

May-18​

Jun-18​

Jul-18​

Aug-18​

Sep-18​

Oct-18​

Nov-18​

Dec-18​

2​

Alan

5​

2​

Jan-18​

1​

0​

1​

0​

1​

0​

1​

0​

1​

0​

0​

0​

3​

Barb

3​

3​

Feb-18​

0​

1​

0​

0​

1​

0​

0​

1​

0​

0​

0​

0​

4​

Cain

2​

4​

Mar-18​

0​

0​

1​

0​

0​

0​

1​

0​

0​

0​

0​

0​

In F2 and copied across and down,

=IF(F$1 < $D2, 0, --AND(SUM($E2:E2) < $B2, MOD(DATEDIF($D2, F$1, "m"), $C2) = 0))