Forum Discussion

RPINEDA2's avatar
RPINEDA2
Copper Contributor
Aug 28, 2024

Cash amortization table

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. 

  • 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.

  • mathetes's avatar
    mathetes
    Silver Contributor

    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.

    • RPINEDA2's avatar
      RPINEDA2
      Copper Contributor
      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.
      • mathetes's avatar
        mathetes
        Silver Contributor

        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.

Resources