Cash amortization table

[RPINEDA2]

Hello,

I am trying to figure which formulate would be best to populate the chart. I am currently doing basic manual formulas for each row but can be very time consuming. 

For each week I have total sales. I am trying to figure how much I can expect to collect each week. For example, the cells in yellow are indicating to me in which week to expect to collect the totals sales from week number 1, $500,000

Any suggestion greatly appreciated. 

 

[#2]

@RPINEDA2 

I am trying to figure which formulate would be best to populate the chart. I am currently doing basic manual formulas for each row but can be very time consuming. 

For each week I have total sales. I am trying to figure how much I can expect to collect each week. For example, the cells in yellow are indicating to me in which week to expect to collect the totals sales from week number 1, $500,000

Well, at present it's hard (might be impossible), I would submit, for any of us--those here in the forum who might be inclined to help you-- to figure out the basis for what appear to be entirely arbitrary percentage figures in the range B14:B18. I see that they all add up to 100%, but why that 100% is distributed as it is is a total mystery. Where did those figures come from? Not only are they are hard-coded; they are hard-coded to the 13th decimal place. "Arbitrary" doesn't do it justice! 

Without a sensible explanation of those numbers, I'd say there is no sensible (i.e., reliable, trustworthy, formula-based) way to figure out what you're trying to figure out.

So please back up and explain where those numbers come from and what bearing they have on the whole matter of collection. It would help also if you could explain the bigger context. What's the business itself? What's being sold? Why aren't the proceeds collected in the same week as the sale? And so forth.

Those are not arbitrary questions, by the way. If you want a formula that has integrity, the whole process that is being automated (programmed) needs itself to have integrity. "Integrity" in the sense of holding together as a whole. I'm not impugning your honesty here, just looking for a reliably consistent way to understand the whole process.

[#3]

Hello,

Sorry for not thoroughly explaining. The percentages shouldn't change. These percentages are calculated based on how we expect to collect weekly payments. We have many customers that are on payment terms such as upon invoice, 7 days, 14 days etc. We are currently trying to get an idea on how much to expect to be collected every week based on the 5 percentage groups.

For example, we collect 14.4% of total sales in week #1. In week #2 we collect 14.4% of week #2 sales sales plus 47% of week #1 sales. In week 3 we collect 14.4% of week #3 sales, plus 47% of week #2 sales plus 22.8% of week #1 sales.

[#4]

@RPINEDA2 

You still haven't explained; all you've done, in effect, is to reiterate "these are the numbers," but that part is clear. Yes, those are the numbers.

You do say this: These percentages are calculated based on how we expect to collect weekly payments.

But HOW are they calculated, and out to the 13th decimal place?!! There must be some really precise calculations going on based on individual customers, some of whom pay in 7 and a half days, others 12 days, others who knows what....   You see my point. You say they're calculated numbers but those numbers certainly appear (to me at any rate) to be absurdly precise (AKA "arbitrary") results of un-explained (to this point) calculations.

We are currently trying to get an idea on how much to expect to be collected every week based on the 5 percentage groups.

So why aren't you satisfied just playing out the numbers based on those 5 percentage groups? What is missing that leads to dissatisfaction?

I really want to help, but what you've said so far still seems like you're trying to make sense (come up with formulas) in a system that inherently has no true coherence.

[#5]

@mathetes 

Thank you for trying to help me. Would it be best if I change the percentages to whole numbers for sample purposes? 

B6:H10 contains the data that I would like the chart to show. I am just trying to figure a way to do it without having to manually insert a formula in every row/column to follow the logic of planned cash collection. 

[#6]

@RPINEDA2 

Would it be best if I change the percentages to whole numbers for sample purposes? 

Not really, since it still leaves me curious how such numbers were calculated in the first place. But OK if you don't want to explain. I've come up with a formula. Here it is:

=LET(pcnt,VLOOKUP(B$5,$A$15:$B$19,2,0),sales,HLOOKUP($A6,$B$1:$H$2,2,0),IFERROR(pcnt*sales,0))

This uses a relatively recent Excel function, LET, so it will require that you have Excel 2021 or newer.

It seemed to me that to have a single formula, it made sense to rearrange how you display the data, so what this now shows is, row by row, how each week's sales are collected in the first, second, third, fifth and seventh week from initial sale. The collection of the  sales of week 1 are all accounted for in the first row; sales of week 2 are accounted for in the second row. And so on. It looks like this:

The advantage of this layout, I think, is that you could continue the first two rows for a long way and just extend the table with the calculations downward. That would require some minimal revision of the HLOOKUP portion of the formula.

Let me know if this works for you.

[#7]

@RPINEDA2 

=LET(

pcnt,VLOOKUP(B$5,$A$17:$B$21,2,0),

sales,HLOOKUP($A6,$B$1:$H$2,2,0),

IFERROR(pcnt*sales,0)

)

Allow me to explain the LET function. At its core, LET enables you to name temporary variables and use them in a formula. The idea is to make formulas more legible. In this case,

• I create a variable called "pcnt" which uses VLOOKUP to determine the percentage appropriate for the nth week since sales (drawing on the row heading for that week).
• I then create a variable called "sales" which uses HLOOKUP to determine the sales figure for the week in question, drawing on the column heading for that week.
• Then, finally, the formula is a simple pcnt*sales but, because there are weeks when the pcnt is non-existent, I surround pcnt*sales with an IFERROR function so that zero appears instead of an error message. Hence, the formula IFERROR(pcnt*sales,0)

And that formula, the entire LET formula, will continue to work with more weeks worth of sales, though frankly I'd suggest changing it to an ongoing set column of weekly sales and convert the HLOOKUP to a VLOOKUP that covers the growing list.

[#8]

@mathetes Thank you for trying to help me. I have played with formula but unfortunately it is not capturing how much we are expecting to collect each week. I accept that it may be because of my confusing explanation. 

Using week # 4 as example, we would be expecting to collect a total of $510,500

-In week #4 we would be attempting to collect 14% of week #4 sales ($510k)

-In week # 4 we would also be attempting to collect 47% of week #3 sales ($630k). This is because week # 4 would be the 2nd week to collect the sales for week #3

-In week # we would also be attempting to collect 22% of week #2 sales ($650k). This is because week # 4 would be the 3rd week to collect the sales for week #2

Note: On my original sample I miscalculated the amounts by doing wrong formula on row #7. Apologies for this. I have corrected it. 

[#9 · Accepted solution]

@RPINEDA2 

Inspired by a video posted by @PeterBartholomew1 on Youtube some years ago, I thought I could apply the technique covered in it on your cash projection problem. It's in the attached file and you don't need a row for each week. One single formula in B3 gives you the total projected collections for each week.

I wouldn't be surprised if Peter can come-up with an even better solution with some more years of Excel developments behind us.

[#10]

@Riny_van_Eekelen 

One single formula in B3 gives you the total projected collections for each week.

One "single formula" made up of functions I've never before seen used! Good job, @Riny_van_Eekelen , but can you explain it? Or do I need to view Peter's video?

I'm still at the level of Excel where I like to see intermediate steps, steps I can understand. But I always appreciate seeing these powerful examples of how much there is yet to learn.

[#11]

@RPINEDA

I have played with formula but unfortunately it is not capturing how much we are expecting to collect each week. I accept that it may be because of my confusing explanation. 

Actually your explanation was clear enough; it is more likely that my solution didn't fit within your existing framework because I changed the way the results are arrayed, for reasons I'll mention below. The weeks going across in my approach are, for each week, the nth week since the start of that week's collections. So the first column next to week 4 is the first week's collection for week 4 sales; the second column is the second week's collection for week 4, and so on. 

My solution (granted it's not in your format, and it's less simple elegant than that single row solution offered by @Riny_van_Eekelen) DOES give you that same total for those weeks in question. I've highlighted them below and show the total as well.

I just changed the array.  And I did that in great measure because I wasn't at all sure how your approach would work as you go on to weeks 8 through 88. You can't keep going horizontally ad infinitum. The solution by my on-line friend Riny will continue to work cleanly and clearly, so I'd suggest you go with that. But I do want you to understand the difference, and why I did what I did.

SO MUCH, so very much, will depend on what you plan to do with all this on an ongoing way; that part has never been clear. All we've been dealing with (so far as I can tell) are seven hypothetical weeks; it seemed strange to me that you were using such precise percentage figures for each of the seven weeks--which was behind my original questions to you--when surely in the real world the actual collections will vary considerably, so at best we're working with rough projections, which may be helpful for budgeting reasons, budgeting cash flow coming into the organization.....but what happens beyond week 7? To me that requires some definition too.

That--how you are going to use the solution to your presenting question--still may determine which approach makes the most sense. And it might well end up being some hybrid of what you've now received.  Best wishes.

[#12]

@mathetes 

Hi John!

Hope all is OK with you.

Yes, please look at Peter's video. It says it all 🙂

R

[#13]

@Riny_van_Eekelen 

Hope all is OK with you.

Thankfully, we are all doing well.

Yes, please look at Peter's video. It says it all 🙂

I've looked, and bookmarked the video to watch (along with the several others that Peter has done). One viewing, however, shows me that it's possible, but oh, my, I'd have to go through those steps again, repeating them with my own data (if I can find a situation where I'd use it) before fully understanding it.

So glad that there are people like you and Peter here on this site helping me to keep on learning.

[#14]

@Riny_van_Eekelen 

Hi Riny, I had just about forgotten recording that one.  Perhaps I should try some more.

The formula has stood the test of time reasonably well but, just to show there are other options available,

 = LET(
    array, TAKE(WRAPROWS(TOCOL(EXPAND(wkly_sales * perc,,m+n,0)),m+n-1),m,n),
    BYCOL(array, SUM)
  )

where n and m are the count of sales and percentages respectively.  The successive collection rows are shifted to the right by wrapping the rows of 14 values to an array 13 wide.