How to Lag weekly payments

[ThatExcelLife]

Hey All

Got a question for you. I am attempting to model a 52 week cash flow model. What I would like to do is, on the input tab, to have the first week the payment will take place, then an input for lagged weeks and the expense itself. See below. The model is run weekly so 25 is the first week for this model. I am trying to figure out how I can, without a helper tab, be able to have the functionality of selecting the first week of the payment (even for next week's cash flow, Week 26, 25 may still be the first week) and then every 4 weeks (or whatever I choose) out, the model will will push out the payment.

For example below, the payments are every 4 weeks, so 28, 32 and beyond. Not sure how to work around the fact that after 52 weeks, the model will reset to 1. I do have mapping tab for dates, should that be needed.

Thanks All!

Input tab
[TABLE="width: 500"]
<tbody>[TR]
[TD]Lagged Week[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Start Week[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Expense[/TD]
[TD]$10,000[/TD]
[/TR]
</tbody>[/TABLE]

Cash Flow tab
[TABLE="width: 500"]
<tbody>[TR]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fazza]

hello

the task sounds straightforward

I suggest the delay in anyone answering is that the set up - and maybe some details/limits - are not understood

some further clarity would help you get some help

regards, Fazza

 

[ThatExcelLife]

Agreed! Yes thanks for the suggestion.

It sounds straight forward, but I cannot seem to think of a solution.

The idea is simple: to create a model in which simply by specifying the first payment date and how many "lag" weeks, the model will automatically populate the proper week.

Lets say I have the first week of payment being 25. I want the payments to lag 4 weeks. the result of the model should look like:

INPUT SHEET
[TABLE="width: 500"]
<tbody>[TR]
[TD]Lag Week[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]First Payment week[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]amount[/TD]
[TD]$10,000[/TD]
[/TR]
</tbody>[/TABLE]

The model, which started Week 26, would look like this:
CF SHEET
[TABLE="width: 500"]
<tbody>[TR]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD]34[/TD]
[TD]35[/TD]
[TD]36[/TD]
[TD]37[/TD]
[TD]38[/TD]
[TD]39[/TD]
[TD]40[/TD]
[TD]41[/TD]
[TD]42[/TD]
[TD]43[/TD]
[TD]44[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[/TR]
</tbody>[/TABLE]

Now lets say instead of a 4 week lag, I want 3. The model output would be:
CF SHEET
[TABLE="width: 500"]
<tbody>[TR]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD]34[/TD]
[TD]35[/TD]
[TD]36[/TD]
[TD]37[/TD]
[TD]38[/TD]
[TD]39[/TD]
[TD]40[/TD]
[TD]41[/TD]
[TD]42[/TD]
[TD]43[/TD]
[TD]44[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0

[/TD]
[/TR]
</tbody>[/TABLE]


Lets roll the model over to next week, or Week 27. With the same assumptions as above:
INPUT SHEET
[TABLE="width: 500"]
<tbody>[TR]
[TD]Lag Week[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]First Payment week[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]amount[/TD]
[TD]$10,000[/TD]
[/TR]
</tbody>[/TABLE]

The model would look like this:
CF SHEET
[TABLE="width: 500"]
<tbody>[TR]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD]34[/TD]
[TD]35[/TD]
[TD]36[/TD]
[TD]37[/TD]
[TD]38[/TD]
[TD]39[/TD]
[TD]40[/TD]
[TD]41[/TD]
[TD]42[/TD]
[TD]43[/TD]
[TD]44[/TD]
[TD]45[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$10,000

[/TD]
[/TR]
</tbody>[/TABLE]


There are two glaring issues:

1) Once the model goes to week 52, it resets to 1; and,
2) What if the operator keeps the inputs as week 25 with lag week 3, and we are in the 29th week: How could the model recognise that this week is behind the model's first week of 29, but still show a payment in week 31 (25 + 3 = 28 + 3 = 31)?

@Fazza Let me know I need to explain further?

 

[Fazza]

have a look at the MOD function : calculate a week number MOD lag week
this will be zero every time you want a payment

try something like this, with cell references to suit your set up
=NOT(MOD(FirstPaymentWeek-the week you want to check, LagWeek))*Amount

adjust as required for start dates, end dates, whatever

if you show the cells that you're using when asking the question it is easier for anyone trying to answer

 

[ThatExcelLife]

GOT IT! OK great great yes I didnt even know about the MOD function. I will try this out now.

Yes you are right. Thanks for the suggestions to better my queries. Always grateful for the help I get on this forum.

Thanks again @Fazza!

 

[Fazza]

you're welcome
all the best