Google Sheets: Formula needed to work out when to pay for inventory (cashflow forecasting)

So the spreadsheet shows the cost of goods sold line from a 52 week profit and loss account. The COGS are calculated based off the date that the sale occurred.

What I am trying to work out is a formula that I will use for COGS/Inventory purchases for the cash flow forecast.

The variables I am looking at in respect of this are Material Purchase & manufacturing lead time, credit terms from supplier and weeks worth of stock to purchase.

I feel that the first two elements are fairly straight forward and it would be a simple offset formula, but the issue comes into play when trying to have a dynamic formula whereby I can change the "weeks worth of stock to purchase" figure and it will still work all the way along.


Hopefully the above makes sense.

Thanks in advance

Hi Ali,

Yes, the original sheet is on google sheets.

Thanks,