Footing and Cross Footing an AR Aging with Monarch

We are very pleased to have our first guest contributor; Mr. David Gross. David’s company, www.MrMonarch.com, provides Monarch Training and consulting to a wide range of clients, David also has a course designed specifically for Commercial Lenders, please feel free to contact him at 440.646.0332 or [email protected].

Below is David’s post on re-aging and re-footing an AR report received from a client:

Monarch is a widely used data mining and extraction program that takes text based reports or files and extracts or strips the data out of the report and puts into a table that can then be filtered or recalculated. The first main function of Monarch is data extraction, taking the data out of the report. Once this data is extracted you can then either generate reports based upon the data or export it to Excel or Access for further manipulation.

An example of what Monarch can do is re-footing an AR Detail report. Prior to lending funds Commercial Lenders need to quantify eligible collateral. The first set of criteria is the age of each account. Certainly the client will supply you with their version of the data, more than likely in a report of AR Detail. It is your job to verify this data, you cannot and should not rely upon the subtotals of the report, in addition, you may have to re-age it as of the invoice date and not the due date or vice versa.. With Monarch you can easily do this. From a high level view the steps would be:

1. Extract the data, that is the invoice #, amount, date invoiced, total amount due and any payments made. Now you would have a table with the following information:
   1. Date of Invoice
   2. Due Date of Invoice
   3. Invoice Amount
   4. Customer Number
   5. Customer Name if supplied on the report.
   6. Payments made.
   7. Payment date.
   8. Run Date of the report, used for aging. The run date was pulled from the page header of the report; this is a standard field on AR detail reports.
2. Extracting data from a standard AR Detail report is not that cumbersome via Monarch and can be done quite quickly, these types of reports are not that complicated.
3. Now that you have that data you need to re-age it. Within Monarch you can supply the date you want to re-age from and do the calculation of days outstanding and have a resulting column (or field) of days outstanding per invoice. For out example we are going to create a DaysOut field that is the Rundate – Invoice date, giving us an aging per the actual date the report was run. We could also use the due date instead of the invoice date should our contract with the client call for that. This is done via a Calculated Field dialog box after we have the data in Table view. The resulting calculated field within Monarch would look like this: (formula in yellow).

1. Now you have a table with the following information:

1. Next step is to create some Calculated fields that are Aging buckets. For the 31-60 day bucket you would create a Calculated field with this formula:

The result of the Expression above would populate a field named BA31-60 (“BA” being a combination to let the user know this is a calculated field) with the Total amount of the order if it fulfilled the IF statement. You would replicate this formula, changing the day spans, for each new bucket, aptly named. The end result would be all new columns in your table with the amounts per column, per aging bucket.

1. Now the fun stuff. Monarch has a report writer, they call them Summaries. You first select the fields you want on the Summary, what are the Key fields (how the report is ordered) and the Measure fields (the numeric computed result). Your Summary Fields dialog box looks like this:

1. Once you click O.K. you will get this Summary: (this is just a partial screen shot)

You can also drill up the summary to get just one line, the bottom line is the Grand Total line, for Summary I hide the Key Column and changed the font style of the Grand Total line so it was more visible.

And there you go, the first step done towards computing your eligible loan amount. From this point on you can filter out AR detail based upon your particular ineligible criteria.

Conclusion:

Monarch is a very powerful tool for data extraction and data scrubbing. Within the Commercial Lending arena Monarch can be used to do the following:

1. Check for Contra accounts.

2. Check for irregular or large amounts.

3. Group together like clients or vendors.

4. Filter out transactions based upon specific characters or transaction type.

5. Export out data to Excel Pivot Tables (Monarch 10.5)

6. Conditional formatting in Summaries.

Thank you David!

There is much more Monarch can do, the above are just some of the highlights. Monarch is a great tool; it generates very reliable data with a minimal amount of effort. David’s firm offers several training options that will enable you to become proficient in Monarch’s capabilities.

For information on training classes offered by Mr.Monarch including a two day training class, either on-site or web based, please feel free to contact him at 440.646.0332 or [email protected]. Thank you.