Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Collection Recovery Rates on Purchases
I'll try to explain this the best that I can. Here's a basic example of what
I'm working with... I currently have a large file of all suits from last year. This file contains the date filed and the balance of the account. I have a second file that contains the payment date, payment amount and suit file date for all payments last year. Based on the date purchased and the payment dates, we want to figure out what the recovery rate on our purchase is in 30 day increments. Below is a shortened 6 month version of the layout with play numbers. Total $ of Suits Jan Feb Mar Apr May Jun Total 500 450 750 600 550 400 3250 Total $ Collected Days from File Jan Feb Mar Apr May Jun Total 0-30 10 15 10 20 15 15 85 31-60 15 20 15 20 15 85 61-90 20 30 15 25 90 91-120 20 25 15 60 121-150 15 20 35 151-180 10 10 Total % Recovered Days From File Jan Feb Mar Apr May Jun Total 0-30 2% 3% 1% 3% 3% 4% 2.6% 31-60 3% 4% 2% 3% 3% 2.6% 61-90 4% 7% 2% 4% 2.8% 91-120 4% 6% 2% 1.8% 121-150 3% 4% 1.1% 151-180 2% 0.3% The percentages come from the amount collected over the amount filed that month. All we really want are the Total Recovery % without having to manually fill in everything. Is there any function that will singlehandly give us the Total Recovery % without having to do use several different equations to get the monthly collected, total collected, monthly filed, total filed, etc.? I originally built access queries but they became too complex after the 421-450 date range. My manager was hoping there is some statistical or financial function to do this. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with data collection! | New Users to Excel | |||
breakdown purchases,sales combined? | New Users to Excel | |||
Template for scheduleing office supply purchases | Excel Discussion (Misc queries) | |||
purchases and invoices | New Users to Excel | |||
Does anybody have a CD collection template | Excel Discussion (Misc queries) |