9 Replies Latest reply: May 15, 2014 9:54 AM by Winn _ RSS

    adding counts to aging reports

    RalphB _

      I have created vendor aging report in Monarch and am wanting to show how many vendors have outstanding invoices for each period of 0-30 days, 31-60 days, 31-90 days and over 90 days outstanding.  I don't need to know how many invoices are outstanding, just the number of vendors. We also have multiple accounts showing on this one report and need to know the counts broken down by this. I can do this in excel, but it is time consuming. I was hoping someone out here has done something similar and can help me.

        • adding counts to aging reports
          Michael Jul Hansen

          Without seeing your report, its difficult to comment, however if you make calculated fields for each date range and then have on key field down and the date ranges across, and then the count as the summary, that should give you what you want - you can send me a sample of your report, and I'll create the model for you

          • adding counts to aging reports
            Grant Perkins

            I think Michael is correct to say it is difficult to suggest a solution without knowing what the report is like.

             

            However attempting to 'guess' got me to here.

             

            If the report is sorted by Vendor and under each vendor lists the outstanding invoices and for each invoice it is possible to see (or calculate) which 'bucket' of due days it falls into;

             

            AND;

             

            There is no such analysis of summary total of invoices outstanding for each vendor;

             

            Then maybe a 2 stage solution is appropriate.

             

            Stage one selects the invoice information lines as detail lines and adds the vendor info as an append. Produces a simple table of vendor and qty of invoices in each time 'bucket'. Export the table to an external file. (I am assuming your Monarch version can link to external files).

             

            The second pass would probably have the VENDOR os the detail line and link to the previous export to identify of there are ANY outstanding invoices for the vendor. At the next level you could look at outstanding in each 'bucket' and so include the vendor in the count of vendors for the bucket.

             

            Presumably a similar concept could be used for the 'multiple accounts' but I would prefer to understand the implications of that issue  before offering any form of suggested solution!

             

            I too would be happy to play with an example of the report if you have something available.

             

            There may well be other ways - the concept of using the lookup tables in a recent posting from Winn comes to mind as an alternative to the 2 stage process with an export.

             

            It may even be possible to do it in one pass but the report format would be key to that being an option.

             

            Grant

             

             

            Originally posted by RalphB:

            I have created vendor aging report in Monarch and am wanting to show how many vendors have outstanding invoices for each period of 0-30 days, 31-60 days, 31-90 days and over 90 days outstanding.  I don't need to know how many invoices are outstanding, just the number of vendors. We also have multiple accounts showing on this one report and need to know the counts broken down by this. I can do this in excel, but it is time consuming. I was hoping someone out here has done something similar and can help me. /b[/quote]

            • adding counts to aging reports
              RalphB _

              i originally tried using Lookup tables like Winn proposed but it really doesn't work in this instance.  For one thing the aging report I am working with has over 1,000 vendors on it and I wan to automate things as much as possible. 

               

              I am currently using a 3 stage process.  The first step sums up the totals for each vendor so I can eliminate ones that zero out on this report.  Step 2 links the first extraction to this and calculates the number of days outstanding and assigns which 'bucket' each invoice belongs in and counts how many are in each 'bucket'.  Step 3 joins step 2 extract and calulates which 'bucket' to assign to all invoices for the vendor.  Mangement wants to see all invoices dumped together in the youngest 'bucket'.  ie: if a vendor has an invoice that fall in the 90 day bucket, 60 day bucket and the 30 day bucket, they want all showing up in the 30 day bucket. Hence the 3 step process.

                 

              I can easily have it count the number of invoices in each bucket, but management just wants the number of vendors only for each bucket and for each account, hence the dilemma.

               

              I currently have a macro script that does the 3 step process for me and extracts to an Excel spreadsheet.  I then go in manually and do a 'dcounta' function in Excel to give me the vendor counts by the buckets by the different accounts.

               

              I was hoping someone would know how to have Monarch give me the counts that I want.  I am using Monarch 6.01 Pro network version.

               

              Hope this clairfies things.  Any suggestions?

              • adding counts to aging reports
                Grant Perkins

                Ralph,

                 

                Rather than me guessing can you confirm how the 'accounts' fit into the scenario. Is it something whereby the vendor invoice is allocated to an internal account within your company or where you have multiple accounts with a vendor or something different altogether?

                 

                Eg for each vendor you might have

                 

                Vendor

                 

                    Invoice , Account 1  Date   Value

                    Invoice , Account 2  Date   Value

                 

                etc.

                 

                OR

                 

                Vendor

                 

                    Account xxxxx

                 

                         Invoice,  Date,  Value

                         Invoice,  Date,  Value

                 

                    Account yyyyy

                 

                         Invoice, Date,   Value

                         Invoice, Date,   Value

                 

                Or something different to both of these.

                 

                Not sure if it makes much difference but it would help to give me to get a visual image of the report.

                 

                Grant

                • adding counts to aging reports
                  RalphB _

                  It is more like

                   

                  Account

                   

                  Vendor1, Invoice, Date, Value

                  Vendor1, Invoice, Date, Value

                  Vendor1, Invoice, Date, Value

                   

                  Vendor2, Invoice, Date, Value

                  Vendor2, Invoice, Date, Value

                  Vendor2, Invoice, Date, Value

                  Vendor2, Invoice, Date, Value

                   

                  Account

                   

                  Vendor3, Invoice, Date, Value

                  Vendor3, Invoice, Date, Value

                   

                  Does this help?

                  • adding counts to aging reports
                    Grant Perkins

                    Hi Ralph,

                     

                     

                    I have been playing around with this based on the outline file layout.

                    I have come to the conclusion that, unless there is something

                    interesting in the way of field titles on the real report,

                    the multi stage process is about all you can do. However I reckon you can

                    keep everything in Monarch PROVIDED I have not forgotten something,

                    had a brain failure (quite possible with this little problem

                    I reckon) or completely missed the point of the exercise!

                     

                    Taking your description at its word (I'm being cautious because it is so

                    easy to have a misunderstanding on these things)

                    I have concluded that you need one report with a row for

                    each ACCOUNT and another output that simply gives a NUMBER (COUNT)

                    of how many vendors fall into each Invoice Date 'bucket'.

                    Something as simple as;

                     

                    Aging Bucket:       0-30     31-60     61-90     90+

                     

                    Qty Vendors:     245     350     512     15

                     

                    Does that seem correct? If so, read on. (And I apologise for the length of the

                    post but I have tried to retain as much flexibility as possible since you

                    will doubtless have a few additions to make).

                     

                     

                    Taking the report layout that you outlined, the basic traps and

                    conversion to a table are quite simple. Invoice lines as detail and

                    Account Number as an Append. All the Fields as you show them.

                    (Plus of course anything else you need from the 'real' report)

                     

                    Once you have the table, create a new calculated field to identify which

                    bucket the invoice line will fall into.

                    I used a nested 'If' formula.

                     

                    [font="courier"]  if((Date()-[Invoice Date]<=30),30,(if((Date()-[Invoice Date]<=60),60,(if((Date()-[Invoice Date]<=90),90,100)))))[/font][/quote]Seems to work OK. This one sets up a value of 30, 60, 90 or 100 in

                    the new field. The value you choose to use is entirely your own

                    choice of course and you might wish to adapt the formula if you have

                    other factors to consider. Obviously it would be nicer to have 91+

                    or something for everything over 90 days but I have used a numeric

                    field rather than complicate things. Anyway I think for the purpose

                    of the output (based on my assumptions) it really won't matter

                     

                    I called the field 'Aging Bucket'.

                     

                    Next create a summary using the fields Vendor Number, Aging Bucket

                    and Value (Value of the invoice line)

                     

                    I set mine up with the following ;

                     

                    Key fields:     Vendor Number, Account Number, Aging Bucket

                    Item fields:     

                    Summary fields:     MIN(Aging Bucket), SUM(value)

                     

                     

                    MIN(Aging Bucket) is the Aging Bucket field used as a Summary and

                    with the CALCULATION (right mouse click on the field in the summary

                    window) set to use the MIN function to derive a value.

                     

                    SUM(Value) will total the invoice values and should be able to

                    indicate which vendors have net zero value invoices.

                     

                    With luck you should get something like this. (At the top of the

                    drill levels)

                    [font="courier"]Vendor Number              MIN(Aging Bucket)          Value

                    1001                                 30              12000.00

                    1002                                 60                  0.00

                    1003                                 30              18000.00

                    Summary                              30              30000.00[/font][/quote]This gives 2 results. You have the lowest value Aging bucket for

                    each vendor AND you can see whether the vendor has a net zero

                    invoice value. (Note I did not seek net negative invoice values but

                    you may need to consider them?)

                     

                    Export this table, preferably without the Summary row as we don't

                    need it. I think this part will be familiar to you.

                     

                    Start another  Monarch session and read the exported file in

                    as a database. Create a filter to exclude the Vendors with net

                    zero invoice value and the vendor number "Summary"!

                     

                    Create a summary using MIN(Aging Bucket) as the key field and

                    COUNT as the summary. That should give you your number of vendors

                    in each Aging Group.

                     

                    Next:

                     

                    Link the same exported summary file back to your original report

                    (linked by Vendor number ) and add the MIN(Aging Bucket)  and

                    VALUE fields to your table. You can now filter out any records

                    with for vendors with ZERO value. Or you can leave them in for

                    now and do it at the next stage. (NB. Once the whole model is

                    established when you next run the process you may have left you

                    residual summary export in place. Having exported the new summary

                    simply refresh the Database Join to proceed. This will repopluate

                    and update all the rows.)

                     

                    Create another SUMMARY from your data table based on ACCOUNT NUMBER.

                    I included a count field (as a form of simple 'does it look

                    about right' data check) and the INVOICE VALUE  field as I elected

                    to filter at the end of the process. It offers a little more

                    flexibility in my way of thinking.

                     

                    The Summary description gives this;

                     

                    Key fields:     Account Number, MIN(Aging Bucket), Vendor Number

                    Item fields:     

                    Summary fields:     SUM(count), SUM(value)

                     

                     

                    Running it you should get you something like this.

                     

                    [font="courier"] 

                       Account Number      MIN(Aging Bucket) Vendor Number             count          Value

                                10001                     30           1001                 3         6000.00

                                10001                     30           1003                 3        18000.00

                                10001                     60           1002                 3            0.00

                                10002                     30           1001                 3         6000.00

                                10002                     60           1002                 3            0.00

                    Summary                                                               15        30000.00[/font][/quote]Export this to a new file, again it would be better without the

                    summary line.

                     

                    Now you can start another Monarch session and use the new file as a

                    simple database once again. (Filter for zero value and the Summary

                    row again if not previously excluded).

                     

                    Create a SUMMARY that looks something like this.

                     

                    Key fields:     Account Number, MIN(Aging Bucket), Vendor Number

                    Item fields:     

                    Summary fields:     SUM(count), SUM(value)

                     

                    (Once again there are a couple of fields in there as data validity

                    checkers - Vendor Number and SUM(Value))

                     

                     

                    Set the ACCOUNT NUMBER data to display DOWN and the MIN(Aging Bucket)

                    figures to display across and at the TOP LEVEL (no drill down used)

                    you will get a column with all qualifying Account numbers, up to

                    four columns (depending on data and filters) with numbers (Vendor Counts)

                    in the 'buckets' and a SUM for the total number of vendors

                    appearing in each account.

                     

                    Presumably a vendor can exist in more than one account?

                    If not, the summary row also gives you the total number of vendors

                    that fall into each bucket.

                     

                     

                    Process in Summary

                     

                    1. Create data table from the report.

                    1.1 Link the

                    previously exported summary database back to the original report

                    by VENDOR NUMBER.

                     

                    2. Produce a Summary giving Vendor, Aging Bucket and Value and export it.

                    2.1 Use the exported Summary as the basis for a NEW Monarch Process

                    to provide the QTY of Vendors per Aging Bucket.

                     

                    3. From the ORIGINAL PROCESS, create a summary by ACCOUNT and Export it.

                     

                    4. Use the BY ACCOUNT export as a database in another Monarch Session

                    and create a Summary with AGING BUCKET as the Key to obtain the required

                    analysis by Account

                     

                     

                    Somehow I suspect there must be an easier route to this - but at the

                    moment I can't see it. However at least it avoids Excel (unless you

                    export to Excel, as I normally do, if I want to re-read the export

                    as a database.) And I suppose the process could be scripted or

                    otherwise automated if required.

                     

                    I hope this helps,

                     

                    Grant

                     

                     

                       Originally posted by RalphB:

                    It is more like

                     

                    Account

                     

                    Vendor1, Invoice, Date, Value

                    Vendor1, Invoice, Date, Value

                    Vendor1, Invoice, Date, Value

                     

                    Vendor2, Invoice, Date, Value

                    Vendor2, Invoice, Date, Value

                    Vendor2, Invoice, Date, Value

                    Vendor2, Invoice, Date, Value

                     

                    Account

                     

                    Vendor3, Invoice, Date, Value

                    Vendor3, Invoice, Date, Value

                     

                    Does this help? /b[/quote]

                     

                    [size="1"][ March 23, 2003, 07:48 AM: Message edited by: Grant Perkins ][/size]

                    • adding counts to aging reports
                      Winn _

                      Grant and Ralph,

                       

                         I have been looking at this problem for a couple of days, because I can apply the solution here at work. Ralph's solution is the best one that I can see at the moment.

                         Grant, your solution has a significant flaw.

                      If you are given the following common situation

                       

                      [font="courier"] Vendor           Aging          Value

                        1001             30           -1000.00

                        1001             60            2000.00

                        1001             90            1000.00 /font[/quote]You will get the following in the first part of your process.

                       

                      [font="courier"]Vendor          Min(Aging Bucket)        Value

                        1001              30                   2000.00[/font][/quote]which is incorrect since the 1000.00 and -1000.00 offset, leaving 2000.00 in the 31-60 day bucket. This is the main problem with trying to automate this process, as it requires some human intelligence to tell what should be counted and what should'nt be.

                       

                      I am looking at this and may have a simpler solution in a week or so. I have to run some things by the accountant who tracks aging in my area to make sure I get all bases covered.

                      • adding counts to aging reports
                        Grant Perkins

                        Good Point Winn! That's why I don't like working 'blind'. Real data has has a nice habit of uncovering the things you haven't considered.

                         

                        I figure (hurriedly I'm afraid, things to do) that you may be able to ignore the negative values altogether in terms of aging? Just make use of them for the net zero value filter. Yes I know it can be more complex than that BUT, given what is being asked for here is not so very detailed, it may be enough.

                         

                        If correct I think you could add an extra criteria to the IF statement to deselect the negatives (Say to bucket 0 maybe?)

                         

                        Something like;

                         

                        [font="courier"] if(((Date()-[Invoice Date]<=30).and.(Invoice value>0)),30,(if((Date()-[Invoice .... etc. /font[/quote]Not sure I have included the correct number of parentheses there but you get the idea.

                         

                        With an extra nest IF statement to catch the negative value invoices if required. If not required they can probably be ignored?

                         

                        Does that cover a few more bases?

                         

                        Grant

                         

                         

                        Originally posted by Winn:

                        Grant and Ralph,

                         

                           I have been looking at this problem for a couple of days, because I can apply the solution here at work. Ralph's solution is the best one that I can see at the moment.

                           Grant, your solution has a significant flaw.

                        If you are given the following common situation

                         

                        [font="courier"] Vendor           Aging          Value

                          1001             30           -1000.00

                          1001             60            2000.00

                          1001             90            1000.00 /font[/quote]You will get the following in the first part of your process.

                         

                        [font="courier"]Vendor          Min(Aging Bucket)        Value

                          1001              30                   2000.00[/font][/quote]which is incorrect since the 1000.00 and -1000.00 offset, leaving 2000.00 in the 31-60 day bucket. This is the main problem with trying to automate this process, as it requires some human intelligence to tell what should be counted and what should'nt be.

                         

                        I am looking at this and may have a simpler solution in a week or so. I have to run some things by the accountant who tracks aging in my area to make sure I get all bases covered. /b[/quote]

                        • adding counts to aging reports
                          Winn _

                          OK, here is what I came up with. It is a two part process that can be easily automated once it is set up. It relies on using some Summary options that are rarely used, but which can do some interesting things. Here is what you need to do to set up the process.

                           

                          1. Add a parameter field named  Current Date[/b] as a date type. Check the Non-blank value is required box pn the Runtime Parameter tab. Note that this could be replaced by reading the report date.

                           

                           

                          2. Add a formula field named  Days back[/b] with a numeric field type. The formula is:

                           

                          If(Amount>0,[Current Date]-Date,1000) /b

                           

                          This will make sure that the first invoice is found, and not any payments or credit memos.

                           

                          3. Create a new summary with the following fields:

                           

                          Key fields: Account, Vendor

                          Summary fields: Min(Days Back)

                           

                          When setting up the Vendor field, click on the Summary field, select Amount, and set it for at least 0.01. In the Accumulate unspecified values as box, type <=0.

                           

                          This will put all the Vendors that zero out into one line per account.

                           

                          4. Export the summary to a fixed length text file.

                           

                          5. Open the file you just created in Monarch.

                           

                          6. Set up the template and fields for the report.

                           

                          7. Add a filter named  Current Vendors[/b] with the following following expression:

                           

                          Vendor<>"<=0" /b

                           

                          8. Create a new summary with the following fields:

                           

                          Key fields: Account, Days back

                          Summary fields: Sum(count)

                           

                          The Days back field is set up as follows:

                           

                          On the Display tab-

                          Direction: Across

                           

                          On the matching tab-

                          Upper limit values: 30, 60, 90

                          Accumulate unspecified values as: 91+

                           

                           

                          This will give you a table that you can print or export to a file. This will also work on Standard versions of Monarch.