Forum Discussion
Cash amortization table
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.
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.
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.
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.
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.
=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.