Account Aging Metrics / Utilization spreadsheet

[Duke_Nukem]

---

@Anonymous wrote:

@Duke_Nukem all your authorized user accounts are counting on versions 8 and 8 right?

---

Yes, all of our AU's count on each other's reports so no problem for the calculations shown.

[ccquest]

• Can you add version information to the filename?
  • Yes, but probably rather include it in the file so I can keep updating the file in DropBox and have it overwrite and keep the same link.
  • You don't want it to be version 93 by the end of the week??
• Imagine an option for open, closed, chargeoff, and collection. Collection and chargeoff balances would add to total balances, but again we don’t know what to do with limits for chargeoffs and closed with a balance. (no limits for collections.)
  
  • Going to need a cliff notes on the changes we'd want and how to calculate them. Overall I'm thinking moving the results to a different sheet and leaving the inputs on another one so it can be a full screen with it all together.
  • Are chargeoffs and collections handled differently? Not familiar with anything about them. Are you trying to see just $ amounts or ages? 
• I'm always confused with AU accounts, so do AU accounts need to have any kind of separate partitioning or AAoA (perhaps others) calculated?
  • Based on how I've seen Birdman7 posting about it in some threads, I'm going to guess it should be an option to factor in. Like Open Accounts with and without AUs because .
  • AUs can only be for revolvers though, correct?
  • Might be able to work something in, possibly. Would need another column to indicate AU or not though.
  • Can AUs impact even if they are closed or does it automatically get removed??
• Hey can we get a percentage of revolvers with a balance metric in a cell? And a # of revolvers with a balance metric in a cell? Then want to do the same thing again for accounts?
  • That should be easy enough. Just to make sure though, one for revolvers and another for all accounts correct? Nothing separated out for installments (by default I suppose open ones have a balance).
• and it's very important we have one for open and a second for open + closed, because we have evidence that the metric includes closed revolvers at least on one version. 
  
  • Same as above

• but is it adding installment limits to total limits even when it's closed without a balance?

  • Yes. It's a blanket sum of everything.

@Anonymous  @Anonymous 

[Anonymous]

---

@ccquest wrote:

• Can you add version information to the filename?
  • Yes, but probably rather include it in the file so I can keep updating the file in DropBox and have it overwrite and keep the same link.
  • You don't want it to be version 93 by the end of the week??

The problem with keeping it in the file properties is that I can't see which version is up there on Dropbox before I download it.

The Major.Minor.PatchLevel version scheme in the filename lets me know quickly if I am making corrections to an older version that I already downloaded.

Adding features will only increment the minor version - major will be 2 for quite a while.

Correcting typos/text/formatting will increment the patch level.

So it would be something like 2.93.2, after adding around 93 features to version 2, and fixing errors 2 times in the 2.93 release.

The rest of your answers to Birdman7's questions are great. I would be confused about much of the AU/charge-off stuff.

It's so easy to track every conceivable aging value in Excel, so I say just put everything in there.

[ccquest]

---

@Anonymous wrote:

The problem with keeping it in the file properties is that I can't see which version is up there on Dropbox before I download it.

---

It doesn't open to the preview of the file for you? For me it does and I'm thinking either just below the title or to the right of it. If you want to PM your email I can share it with you as editable so you can see the version history too.

I know how the versioning works, just joking how many edits it'll be getting over the week probably lol.

[Anonymous]

---

@ccquest wrote:

• Can you add version information to the filename?
  • Yes, but probably rather include it in the file so I can keep updating the file in DropBox and have it overwrite and keep the same link.
  • You don't want it to be version 93 by the end of the week??
• Imagine an option for open, closed, chargeoff, and collection. Collection and chargeoff balances would add to total balances, but again we don’t know what to do with limits for chargeoffs and closed with a balance. (no limits for collections.)
  
  • Going to need a cliff notes on the changes we'd want and how to calculate them. Overall I'm thinking moving the results to a different sheet and leaving the inputs on another one so it can be a full screen with it all together.
  • Are chargeoffs and collections handled differently? Not familiar with anything about them. Are you trying to see just $ amounts or ages? 
• I'm always confused with AU accounts, so do AU accounts need to have any kind of separate partitioning or AAoA (perhaps others) calculated?
  • Based on how I've seen Birdman7 posting about it in some threads, I'm going to guess it should be an option to factor in. Like Open Accounts with and without AUs because .
  • AUs can only be for revolvers though, correct?
  • Might be able to work something in, possibly. Would need another column to indicate AU or not though.
  • Can AUs impact even if they are closed or does it automatically get removed??
• Hey can we get a percentage of revolvers with a balance metric in a cell? And a # of revolvers with a balance metric in a cell? Then want to do the same thing again for accounts?
  • That should be easy enough. Just to make sure though, one for revolvers and another for all accounts correct? Nothing separated out for installments (by default I suppose open ones have a balance).
• and it's very important we have one for open and a second for open + closed, because we have evidence that the metric includes closed revolvers at least on one version. 
  
  • Same as above

• but is it adding installment limits to total limits even when it's closed without a balance?

  • Yes. It's a blanket sum of everything.

@Anonymous  @Anonymous 

---

@ccquest Yes chargeoffs (COs) are different than collections (CAs). COs balances add to balances and to the numerator of revolver utilization. Its unknown how the CL is applied to the denominator, so I suggest multiple results until we find the correct method. 

CAs also apply to balances and to the numerator of utilization, but not to the denominator. 

I'd make CO and CA options and then:

1. Add CO (chargeoff) and CA balances to total open revolving balances. 

2. leave “total limits” without any closed or chargeoff limits. realized you have open and closed limits, dont include CAs anywhere

3. put an additional cell that says “Total Open rev Limits + closed rev with a balance limits” (TORL+ CL from closed revolvers with a balance.)

4. put a second additional cell saying “TORL + chargeoff limits” (TORL + reported CO CLs)

5. Put a 3d saying "TORL + closed and CO limits" (TORL + CL from closed revolvers with a balance + CO CL)

6. and a 4th that says "TORL with closed account and chargeoff balances as their limits." (TORL + closed revolver balances + CO balances.)

7. Then put 4 more cells, each with utilization calculations using total revolving balances over the results from 3, 4, 5, & 6  

8. COs and CAs I believe totally count on aging metrics. 

9. AUs count towards age until removed. Towards balances and utilization if open and counting. Yes only revolver. 

10. Yes The revolver metric is only for revolvers and the account metric includes other account types such as loans. We need number and percentage on revolver but only percentage on accounts. 

11. if an account is closed without a balance its limits should not be included in total limits. realized you have open and closed limits, dont include CAs anywhere

@ccquestEdited  10.6.20 10:07pm- I made a correction or 2 and tried to make it more readable.

[Anonymous]

---

@ccquest wrote:

---

@Anonymous wrote:

The problem with keeping it in the file properties is that I can't see which version is up there on Dropbox before I download it.

---

It doesn't open to the preview of the file for you? For me it does and I'm thinking either just below the title or to the right of it. If you want to PM your email I can share it with you as editable so you can see the version history too.

I know how the versioning works, just joking how many edits it'll be getting over the week probably lol.

---

Version number in a cell would be great! Yes, I can see the preview clearly.

I sort of thought you already knew the version format. And 93 edits is probably just getting started. lol

Before you know it, there will be a Space Invaders worksheet:

[Anonymous]

I really think the easiest way to filter it would be to add it to the list: open, closed, chargeoff, collection

As far as authorized users go, I wouldn’t touch it. I would simply allow people to copy the worksheet, if theirs doesn’t count on the new versions; then they have a second worksheet without the authorized user accounts for 8 & 9. 

Stated differently, First worksheet for 542 and the second one for 8 & 9, without AUs, for those where the authorized user accounts are not counting on the new versions. 

@ccquest Edit: if you read my earlier post explaining how to implement it, that was before I realized how much simpler this solution was. if you'll notice I edited that post. And even this one, im a habitual editor. Lol. 

[Anonymous]

A simple AAoA calculator: https://aaoa-calculator.org/

That's been around a few years. Might help someone that doesn't have Excel.

[ccquest]

Made some of the changes in this version, didn't really get any time in recent days though and this still doesn't account for things regarding CA/CO accounts.

https://www.dropbox.com/s/lxbd3bs7g65lpmp/Account%20Aging%20Metrics%20%28for%20not%202019%20v2.1%29....

[Anonymous]

I can’t wait to look at it! I wish you gave us a change log ... the suspense is killing me, lol!

Lotta people are already using it and I referred a couple people to use it, its going to help a lot of people!