Determining Percentage of Customers by $ Value Paying in X Days

I'm seeking to forecast when a business should receive cash in the working capital cycle. For a year I expect ~$225k in cash from a customer base. But it's unknown which customer will require work/how much work. And each customer takes a different length of time to pay their invoice (some 0 days from receipt and some over 100). I'm building a model to forecast how much cash is required in the working capital account for any day of the year (+/- a margin of error).

How should I build the distribution formula to determine what % of the $225k should be paid in 0 days vs X days, vs Y days, etc.?

Sample dataset attached.

Appreciate any thoughts/help!

Hi MarvinP,

Thanks for your reply/review. I suppose I'm asking for something that doesn't make sense with this dataset. Like you said, there doesn't seem to be a correlation between days to pay and average invoice for a customer (the RSQ is less than 1%). I probably just need to apply the total revenue across a percentage breakdown for days to pay (e.g., 32% of the revenue is received in 0 days).

How did you create the grouping of a range of 10 in your pivot table rows? That seems to be a very useful feature.

Very helpful, thank you MarvinP!