Dashboard Using Dates, Date Ranges and an Aging Matrix

[richmcgill]

I need some help.

I have a spreadsheet that has two sides.
A shortages side and a Surplus side. The numbers on the two sides two will always remain seperate
Basically a Debits and Credits Accounting type setup.

Each side of the spreadsheet is an every growing and shrinking with the number of loans we are tracking.
At the top of the spreadsheet is a fixed date that is manually changed.

Date: 06/14/2019 cell A5

[TABLE="width: 1466"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 9"]Shortages[/TD]
[TD] [/TD]
[TD="colspan: 9"]Surplus[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD] [/TD]
[TD]Loan #[/TD]
[TD] [/TD]
[TD]Name[/TD]
[TD] [/TD]
[TD]Description[/TD]
[TD] [/TD]
[TD]Amount[/TD]
[TD] [/TD]
[TD]Date[/TD]
[TD] [/TD]
[TD]Loan #[/TD]
[TD] [/TD]
[TD]Name[/TD]
[TD] [/TD]
[TD]Description[/TD]
[TD] [/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]5/7/2019[/TD]
[TD] [/TD]
[TD]12345[/TD]
[TD] [/TD]
[TD]Borrower 1[/TD]
[TD] [/TD]
[TD]CORRECTING ENTRY FOR DIFF. AMT[/TD]
[TD] [/TD]
[TD]$1,412.66[/TD]
[TD] [/TD]
[TD]4/30/2019[/TD]
[TD] [/TD]
[TD]123456[/TD]
[TD] [/TD]
[TD]Borrower 11[/TD]
[TD] [/TD]
[TD]Interest did not post and Escrows[/TD]
[TD] [/TD]
[TD]$722.88[/TD]
[/TR]
[TR]
[TD]5/20/2019[/TD]
[TD] [/TD]
[TD]13218[/TD]
[TD] [/TD]
[TD]Borrower 2[/TD]
[TD] [/TD]
[TD]CORRECTION RAN WRONG WAY[/TD]
[TD] [/TD]
[TD]$3.90[/TD]
[TD] [/TD]
[TD]5/10/2019[/TD]
[TD] [/TD]
[TD]131693[/TD]
[TD] [/TD]
[TD]Borrower 12[/TD]
[TD] [/TD]
[TD]DUAL HABITAT[/TD]
[TD] [/TD]
[TD]$23.00[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD] [/TD]
[TD]14091[/TD]
[TD] [/TD]
[TD]Borrower 3[/TD]
[TD] [/TD]
[TD]Interest Ran For Different Amount[/TD]
[TD] [/TD]
[TD]$81.65[/TD]
[TD] [/TD]
[TD]5/24/2019[/TD]
[TD] [/TD]
[TD]139930[/TD]
[TD] [/TD]
[TD]Borrower 13[/TD]
[TD] [/TD]
[TD]INTEREST DID NOT POST[/TD]
[TD] [/TD]
[TD]$896.80[/TD]
[/TR]
[TR]
[TD]4/15/2019[/TD]
[TD] [/TD]
[TD]14964[/TD]
[TD] [/TD]
[TD]Borrower 4[/TD]
[TD] [/TD]
[TD]INTEREST DID NOT POST[/TD]
[TD] [/TD]
[TD]$985.00[/TD]
[TD] [/TD]
[TD]5/11/2019[/TD]
[TD] [/TD]
[TD]148167[/TD]
[TD] [/TD]
[TD]Borrower 14[/TD]
[TD] [/TD]
[TD]INTEREST DID NOT POST[/TD]
[TD] [/TD]
[TD]$53.68[/TD]
[/TR]
[TR]
[TD]5/24/2019[/TD]
[TD] [/TD]
[TD]15837[/TD]
[TD] [/TD]
[TD]Borower 5[/TD]
[TD] [/TD]
[TD]INTEREST CORRECTION NOT CARRIED[/TD]
[TD] [/TD]
[TD]$168.84[/TD]
[TD] [/TD]
[TD]3/13/2019[/TD]
[TD] [/TD]
[TD]156404[/TD]
[TD] [/TD]
[TD]Borower 15[/TD]
[TD] [/TD]
[TD]INTEREST DID NOT POST[/TD]
[TD] [/TD]
[TD]$242.48[/TD]
[/TR]
[TR]
[TD]5/29/2019[/TD]
[TD] [/TD]
[TD]16710[/TD]
[TD] [/TD]
[TD]Borrower 6[/TD]
[TD] [/TD]
[TD]NEW LOAN DIFFERENCE[/TD]
[TD] [/TD]
[TD]$0.06[/TD]
[TD] [/TD]
[TD]5/24/2019[/TD]
[TD] [/TD]
[TD]164641[/TD]
[TD] [/TD]
[TD]Borrower 16[/TD]
[TD] [/TD]
[TD]INTEREST DID NOT POST[/TD]
[TD] [/TD]
[TD]$744.93[/TD]
[/TR]
[TR]
[TD]5/30/2019[/TD]
[TD] [/TD]
[TD]17583[/TD]
[TD] [/TD]
[TD]Borrower 7[/TD]
[TD] [/TD]
[TD]Interest Ran For A Different Amount[/TD]
[TD] [/TD]
[TD]$181.90[/TD]
[TD] [/TD]
[TD]5/24/2019[/TD]
[TD] [/TD]
[TD]172878[/TD]
[TD] [/TD]
[TD]Borrower 17[/TD]
[TD] [/TD]
[TD]INTEREST DID NOT POST[/TD]
[TD] [/TD]
[TD]$223.60[/TD]
[/TR]
[TR]
[TD]6/7/2019[/TD]
[TD] [/TD]
[TD]18456[/TD]
[TD] [/TD]
[TD]Borrower 8[/TD]
[TD] [/TD]
[TD]Interest Ran For A Different Amount[/TD]
[TD] [/TD]
[TD]$0.32[/TD]
[TD] [/TD]
[TD]5/24/2019[/TD]
[TD] [/TD]
[TD]181115[/TD]
[TD] [/TD]
[TD]Borrower 18[/TD]
[TD] [/TD]
[TD]INTEREST DID NOT POST[/TD]
[TD] [/TD]
[TD]$212.08[/TD]
[/TR]
[TR]
[TD]5/30/2019[/TD]
[TD] [/TD]
[TD]19329[/TD]
[TD] [/TD]
[TD]Borrower 9[/TD]
[TD] [/TD]
[TD]Entry without reversal[/TD]
[TD] [/TD]
[TD]$40.16[/TD]
[TD] [/TD]
[TD]5/24/2019[/TD]
[TD] [/TD]
[TD]189352[/TD]
[TD] [/TD]
[TD]Borrower 9[/TD]
[TD] [/TD]
[TD]INTEREST DID NOT POST[/TD]
[TD] [/TD]
[TD]$296.64[/TD]
[/TR]
[TR]
[TD]6/3/2019[/TD]
[TD] [/TD]
[TD]20202[/TD]
[TD]`[/TD]
[TD]Borrower 10[/TD]
[TD] [/TD]
[TD]CHARGEBACK - ACCT CLOSED[/TD]
[TD] [/TD]
[TD]$560.96[/TD]
[TD] [/TD]
[TD]5/24/2019[/TD]
[TD] [/TD]
[TD]197589[/TD]
[TD] [/TD]
[TD]Borrower 110[/TD]
[TD] [/TD]
[TD]INTEREST DID NOT POST[/TD]
[TD] [/TD]
[TD]$191.07[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6/11/2019[/TD]
[TD] [/TD]
[TD]205826[/TD]
[TD] [/TD]
[TD]Borrower 210[/TD]
[TD] [/TD]
[TD]INTEREST DID NOT POST[/TD]
[TD] [/TD]
[TD]$191.07[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5/24/2019[/TD]
[TD] [/TD]
[TD]214063[/TD]
[TD] [/TD]
[TD]Borrower 140[/TD]
[TD] [/TD]
[TD]HFS[/TD]
[TD] [/TD]
[TD]$934.39[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6/7/2019[/TD]
[TD] [/TD]
[TD]222300[/TD]
[TD] [/TD]
[TD]Borrower 810[/TD]
[TD] [/TD]
[TD]HFS[/TD]
[TD] [/TD]
[TD]$1,081.61[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5/28/2019[/TD]
[TD] [/TD]
[TD]230537[/TD]
[TD] [/TD]
[TD]Borrower 113[/TD]
[TD] [/TD]
[TD]INTEREST DID NOT POST[/TD]
[TD] [/TD]
[TD]$159.36[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6/1/2019[/TD]
[TD] [/TD]
[TD]238774[/TD]
[TD] [/TD]
[TD]Borrower 185[/TD]
[TD] [/TD]
[TD]City Error[/TD]
[TD] [/TD]
[TD]$23.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5/28/2019[/TD]
[TD] [/TD]
[TD]247011[/TD]
[TD] [/TD]
[TD]Borrower 164[/TD]
[TD] [/TD]
[TD]Interest Ran For Different Amount[/TD]
[TD] [/TD]
[TD]$723.38[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6/7/2019[/TD]
[TD] [/TD]
[TD]255248[/TD]
[TD] [/TD]
[TD]Borrower 523[/TD]
[TD] [/TD]
[TD]County Error[/TD]
[TD] [/TD]
[TD]$23.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5/29/2019[/TD]
[TD] [/TD]
[TD]263485[/TD]
[TD] [/TD]
[TD]Borrower 110[/TD]
[TD] [/TD]
[TD]General Error[/TD]
[TD] [/TD]
[TD]$1,109.59[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6/3/2019[/TD]
[TD] [/TD]
[TD]271722[/TD]
[TD] [/TD]
[TD]Borrower 987[/TD]
[TD] [/TD]
[TD]PAYMENT ERROR[/TD]
[TD] [/TD]
[TD]$20,000.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5/30/2019[/TD]
[TD] [/TD]
[TD]279959[/TD]
[TD] [/TD]
[TD]Borrower 5846[/TD]
[TD] [/TD]
[TD]PAYMENT ERROR[/TD]
[TD] [/TD]
[TD]$1,500.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5/30/2019[/TD]
[TD] [/TD]
[TD]288196[/TD]
[TD] [/TD]
[TD]Borrower 5478[/TD]
[TD] [/TD]
[TD]PAYMENT ERROR[/TD]
[TD] [/TD]
[TD]$863.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


I need to build a dashboard for the two sides of the spreadsheet.

First step a starting count
Count All Dates on the Shortage side that IS equal to the date in A2 and has any amount in the amount column = Call This "Newly Added"
Count All Dates on the Shortage side that are NOT equal to the date in A2 and has any amount in the amount column = Call This "Starting Count"
Total Counts
Count All Dates on the Shortage side that are NOT equal to the date in A2 and has an amount of $0.00 in the amount column = Call This "Cleared"
Subtract From the above total

Same thing for the Surplus Side

Something Like This

[TABLE="width: 642"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Shortage Side[/TD]
[TD]Count[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Newly Added[/TD]
[TD]12[/TD]
[TD]Date Equals A2 Any Amount[/TD]
[/TR]
[TR]
[TD]Starting Count[/TD]
[TD]6[/TD]
[TD]Date Other than A2 Any Amount[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cleared[/TD]
[TD]7[/TD]
[TD]Date other than A2 Amount equals $0.00[/TD]
[/TR]
[TR]
[TD]Shortage New Total[/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Surplus Side[/TD]
[TD]Count[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Newly Added[/TD]
[TD]24[/TD]
[TD]Date Equals A2 Any Amount[/TD]
[/TR]
[TR]
[TD]Starting Count[/TD]
[TD]17[/TD]
[TD]Date other than A2 Any Amount[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cleared[/TD]
[TD]7[/TD]
[TD]Date other than A2 Amount and equals $0.00[/TD]
[/TR]
[TR]
[TD]Surplus New Total[/TD]
[TD]34[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Second I need Date Range Counts

I need a matrix based on aging outlined below. Still Broken up by Shortage and Surplus. These two numbers will never mix.

The Starting Numbers matrix is a count of all the loans with a date that DOES NOT matches A2 regardless of the amount on the loan. You still need to use the date in A2 to age the loans.

The Ending Numbers matrix is a count of any date on all the loans unless the amount is $0.00.

Break up the counts based on the days past due range.

Something like this

[TABLE="width: 496"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 345"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"] Starting Numbers[/TD]
[/TR]
[TR]
[TD]Days On Report[/TD]
[TD]Shortages[/TD]
[TD]Surplus[/TD]
[/TR]
[TR]
[TD]0 to 7 Days[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8 to 14 Days[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15 to 21 Days[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22 to 29 Days[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30 Days and Over[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="colspan: 3"] Ending Numbers[/TD]
[/TR]
[TR]
[TD]Days On Report[/TD]
[TD]Shortages[/TD]
[TD]Surplus[/TD]
[/TR]
[TR]
[TD]0 to 7 Days[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8 to 14 Days[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15 to 21 Days[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22 to 29 Days[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30 Days and Over[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

You should use Tables for each set of data.
You can then use a couple of different techniques depending on how you want your dashboard laid out.
Old School would use different SUMPRODUCT formulas with Table references.
You could use Data Model or Pivot table and the subsequent use of CUBEFORMULAs or PowerView.

You could also go full Microsoft Dashboard with Power-BI Desktop.

 

[richmcgill]

Thank you!

I do not have Power BI or MS Dashboard. I will have to check and see if my company will let me have access to them or not.