+ Reply to Thread
Results 1 to 5 of 5

Determining Percentage of Customers by $ Value Paying in X Days

  1. #1
    Registered User
    Join Date
    06-25-2011
    Location
    Chicago, IL
    MS-Off Ver
    MS 365 apps for business
    Posts
    63

    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!
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Determining Percentage of Customers by $ Value Paying in X Days

    Hi zpenacho,

    There doesn't seem to be a correlation between amt and time in your data. See the attached showing the data grouped in a Pivot Table using Sum, Count and PerCent of Tot. Does this show what you want?
    No Correlation Amt vs Time to Pay.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    06-25-2011
    Location
    Chicago, IL
    MS-Off Ver
    MS 365 apps for business
    Posts
    63

    Re: Determining Percentage of Customers by $ Value Paying in X Days

    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.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Determining Percentage of Customers by $ Value Paying in X Days

    Hi zpenacho,

    See this link to see the correct instructions.
    http://probiztechnology.com/blog/201...number-ranges/

  5. #5
    Registered User
    Join Date
    06-25-2011
    Location
    Chicago, IL
    MS-Off Ver
    MS 365 apps for business
    Posts
    63

    Re: Determining Percentage of Customers by $ Value Paying in X Days

    Very helpful, thank you MarvinP!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 09-23-2019, 04:40 AM
  2. Replies: 0
    Last Post: 07-29-2019, 09:05 AM
  3. Replies: 2
    Last Post: 02-26-2019, 01:45 PM
  4. Percentage of on time customers in a list by the hour
    By ashcrroft752 in forum Excel General
    Replies: 3
    Last Post: 11-02-2017, 08:32 AM
  5. Replies: 0
    Last Post: 11-21-2014, 05:23 PM
  6. percentage of customers reply
    By MCsam97 in forum Excel General
    Replies: 7
    Last Post: 08-06-2011, 06:25 PM
  7. Replies: 1
    Last Post: 05-15-2006, 10:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1