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

Shortages

Surplus

Date

Loan #

Name

Description

Amount

Date

Loan #

Name

Description

Amount

5/7/2019

12345

Borrower 1

CORRECTING ENTRY FOR DIFF. AMT

$1,412.66

4/30/2019

123456

Borrower 11

Interest did not post and Escrows

$722.88

5/20/2019

13218

Borrower 2

CORRECTION RAN WRONG WAY

$3.90

5/10/2019

131693

Borrower 12

DUAL HABITAT

$23.00

3/1/2019

14091

Borrower 3

Interest Ran For Different Amount

$81.65

5/24/2019

139930

Borrower 13

INTEREST DID NOT POST

$896.80

4/15/2019

14964

Borrower 4

INTEREST DID NOT POST

$985.00

5/11/2019

148167

Borrower 14

INTEREST DID NOT POST

$53.68

5/24/2019

15837

Borower 5

INTEREST CORRECTION NOT CARRIED

$168.84

3/13/2019

156404

Borower 15

INTEREST DID NOT POST

$242.48

5/29/2019

16710

Borrower 6

NEW LOAN DIFFERENCE

$0.06

5/24/2019

164641

Borrower 16

INTEREST DID NOT POST

$744.93

5/30/2019

17583

Borrower 7

Interest Ran For A Different Amount

$181.90

5/24/2019

172878

Borrower 17

INTEREST DID NOT POST

$223.60

6/7/2019

18456

Borrower 8

Interest Ran For A Different Amount

$0.32

5/24/2019

181115

Borrower 18

INTEREST DID NOT POST

$212.08

5/30/2019

19329

Borrower 9

Entry without reversal

$40.16

5/24/2019

189352

Borrower 9

INTEREST DID NOT POST

$296.64

6/3/2019

20202

`

Borrower 10

CHARGEBACK - ACCT CLOSED

$560.96

5/24/2019

197589

Borrower 110

INTEREST DID NOT POST

$191.07

6/11/2019

205826

Borrower 210

INTEREST DID NOT POST

$191.07

5/24/2019

214063

Borrower 140

HFS

$934.39

6/7/2019

222300

Borrower 810

HFS

$1,081.61

5/28/2019

230537

Borrower 113

INTEREST DID NOT POST

$159.36

6/1/2019

238774

Borrower 185

City Error

$23.00

5/28/2019

247011

Borrower 164

Interest Ran For Different Amount

$723.38

6/7/2019

255248

Borrower 523

County Error

$23.00

5/29/2019

263485

Borrower 110

General Error

$1,109.59

6/3/2019

271722

Borrower 987

PAYMENT ERROR

$20,000.00

5/30/2019

279959

Borrower 5846

PAYMENT ERROR

$1,500.00

5/30/2019

288196

Borrower 5478

PAYMENT ERROR

$863.00

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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

Shortage Side	Count
Newly Added	12	Date Equals A2 Any Amount
Starting Count	6	Date Other than A2 Any Amount
Total	18
Cleared	7	Date other than A2 Amount equals $0.00
Shortage New Total	11
Surplus Side	Count
Newly Added	24	Date Equals A2 Any Amount
Starting Count	17	Date other than A2 Any Amount
Total	41
Cleared	7	Date other than A2 Amount and equals $0.00
Surplus New Total	34

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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

Starting Numbers Days On Report Shortages Surplus 0 to 7 Days 8 to 14 Days 15 to 21 Days 22 to 29 Days 30 Days and Over Ending Numbers Days On Report Shortages Surplus 0 to 7 Days 8 to 14 Days 15 to 21 Days 22 to 29 Days 30 Days and Over <colgroup><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

 

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