Modelling Working Capital (monthly model)

Hello everyone, and thank you very much for your help.

I'm creating a Working Capital sheet for the company I work for.

We have many different income streams with very different expected payment dates. I have successfully created a sheet using the OFFSET formula. Please see the attached Excel sample file Working Capital (2).xlsx (I updated the file to make it more clear).

The way in which the Cash-Flows are projected is by estimating a % of payments per period (row 13). So, in this particular case, we expect to collect 60% of the total revenue in the same month that it is sold, 25% in the next, and so on. The problem I'm having is that currently the excel is using the sum of all income streams (the gray line in row 6), and I would like it to be for each income line separately, and then add them. I know I can create a different table for each stream of income and then add up the total, but that's what i'm trying to avoid. We have over 150 different income streams, and doing so is not practical.

Is it possible to have, in the same row, the payments of an entire income stream without creating a separate table? Maybe It can't be done, not sure. It's what I'm trying to do in Sheet 2. Thank you very much for your help

Hello danieltoro and Welcome to Excel Forum.
This proposal adds two empty columns to the existing sheet so that working capital 1 is now in column G.
The formula for G24 across and down is:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.