Repeating Audits-EnergyCAP Logic for Database Table Updates

Audits are designed to check the accuracy of EnergyCAP utility billing information. Audits may be run:

• During manual bill entry

• Following electronic bill import

• In other stages of bill processing

The purpose of using audits during manual bill entry is to minimize data entry errors by identifying potential issues prior to saving the bill.  Then the data entry person can return to the entered bill to confirm that the information is correct. If there was a data entry error, the bill can be corrected immediately and saved.  The audit can be run again to verify that the corrected bill passes. If there was no data entry error, but the bill still fails the audit, a Bill Message, and/or a Bill Issue can be created so that the bill can be identified as requiring further review. An audit task is also available for running audit groups external to the EnergyCAP program.

Running audits after electronic bill import can help confirm the accuracy of imported data. Other audits (or the same audits in different audit groups with different audit variance settings) can be used for energy management and analysis tasks.

Audit results can be reviewed from the Work Flow Manager (Accounting > Work Flow Manager) by clicking on the associated bill from the Problem folder. Bill messages generated by failed audits can be displayed using the BL20 report from the Report Manager.

The Audit Manager (Analysis/Audits) provides an interface for the user to create audit groups useful for the unique organization work flow and quality control. About 50 different audits are built into the EnergyCAP audit installation. These audits can be run individually or assigned to an audit group. The latter is the more common use. After the user has defined audit groups in the Audit Manager, a group can be assigned to the bill entry work flow via the Workflow Wizard (Tools/Work Flow Wizard). Then, the audit group will be automatically run during the bill entry process. Individual and multiple bills can also be audited from the Workflow Manager (Accounting/Work Flow Manager), and bills can be audited individually from the Create, View and Edit Bill windows by clicking the Audit button.

The Audit Manager can be used to set or change global Audit Variances for individual audits assigned to Audit Groups. Group audit variances can, in turn, be overridden at the meter or account level.

Re-Audit Notes

Audits may be run multiple times. The same audit may be part of multiple audit groups. Running an audit more than once on a bill will cause the bill to be re-audited, regardless of the results of the previous audit.

If the bill failed previously, the audit result record will be updated with the new result.

The audit result table (called BillMessageLog) does not record the Audit Group; i.e. the Audit Group is irrelevant.  For additional information on re-auditing, see Repeating Audits-EnergyCAP Logic for Database Table Updates.

Audit Installation

Audit installation is not part of the EnergyCAP program installation. If you do not see audits in the Audit Manager when attempting to create audit groups, follow these instructions to install the audit package:

• Open EnergyCAP and navigate to the Audits Manager in the Analysis Module (Analysis > Audits).

• From the Audit Menu, select Install.

• Check the Remove Orphans checkbox.
  
  NOTE: This is a 'housekeeping' feature in EnergyCAP to remove outdated audit .dll (Dynamic Link Library) files from your computer. It will not adversely affect your ability to run EnergyCAP audits.

• Click on the OK button to install the EnergyCAP audits.

• When the audits have been installed, click the Close button.

When you create an audit group, you are creating a folder to contain specific audits that you want to execute together.

1. Select Analysis>Audits. The Audit Manager appears.

2. From the Audit menu, select New>Group. The Create Audit Group window appears.

3. In the Code field, enter the unique, user-defined identifier of the audit group.

4. In the Display field, enter the name to appear throughout the system.

5. Click OK. to save the new Audit Group and close the Create Audit Group window. The new group will appear in the Audit Manager Tree View.

Adding Audits to the Audit Group

1. To add audits to the Audit Group, right-click on the Group and select the New Audit option from the popup menu. A list of available audits appears.

    

2. Click to select the desired audit from the list. The Create Audit window will open, displaying a description of the audit in the General tab.

3. Click the Settings tab to reveal the audit settings.

4. If changes to default settings are desired, click the Edit icon . The Create Audit Setting window will open.

5. Input the new settings in the fields provided, then click OK to save changes and exit the Create Audit Setting window. Or click the Use Default button to change field values to default settings. Or click Cancel to close the Create Audit Setting window without saving changes.

6. When satisfied with Audit settings, click OK from the Create Audit window to save the audit and exit the Create Audit window.

7. When satisfied with Audit settings, click OK from the Create Audit window to save the audit and exit the Create Audit window. Or click Cancel to exit without saving the audit. Saved audits are displayed in a subfolder corresponding to the type of audit (Bill or Channel). 

1. Select Analysis>Audits. The Audit Manager appears.

2. Click to navigate to the audit group to modify.

3. Select the audit group to modify.

4. From the File menu, select Properties. The Audit Group Properties dialog box appears.

5. Modify the code by clearing the Code field then entering the new code.

6. Modify the display by clearing the Display field then entering the new display.

7. After making your selections, click OK.

When you delete an audit group, you also delete all audits contained in that audit group folder.

1. Select Analysis > Audits. The Audit Manager appears.

2. Click to navigate to the audit group to delete.

3. Select the audit group to delete.

4. From the Audit menu, select Delete > Group. The Confirm window appears.

   • Select Yes to delete the audit group.

   • Click No to cancel the operation.

As mentioned in the overview, it can be useful to  run audits as bills are being keyed into EnergyCAP. To set this up, first you must create an audit group and select one or more audits to be members of this group.

1. From the Audit Menu, select  New>Group or right-click on the Audit Groups node of the Tree View and select New Group.
2. Enter a Code and Display for the Audit Group.  Since this will be the Audit Group used to check the bill entry data quickly, you may want to make the Code = QUICKCHECK and the Display = Quick Check.
   
   
3. Select the Audit Group node on the tree.
4. Click on the New Audit button, located in the top right corner of the screen, and select Audit #46 "Bill Entry Quick Check." See the topic on Audit #46 for a description of it and the tests it performs.
5. On the Settings tab, the default variance settings are shown. It is possible to alter these settings to make the audit less stringent (by increasing the variance values) or more stringent (by decreasing the variance values). To edit the settings, click on the desired setting, then click on the Properties button (located on the right side of the window), and enter the new value.

• The window  above shows that the audit is being made less stringent, by setting the Over Limit variance to 50%, rather than using the 20% default value. Therefore, the billing data being evaluated must be more than 50% greater than the value it is being compared against for the audit to indicate that there is a potential data entry problem.

• Click the OK button to save any changes to the Audit Settings.

• Click the OK button to save the audit within the Audit Group.

Automating a Quick Check Audit

To audit automatically during manual bill entry, update the Work Flow settings as follows:

1. From the Tools Menu, select  Work Flow Wizard.
2. On the second page of the Work Flow Wizard, select the Use quick check audit option.
3. Select the Audit group you want to execute to check the manual bill entry
   
4.  Click the Next button until the Work Flow Wizard – Finished window is displayed.
5. Click the Finish button to save changes to the Work Flow Settings.
6. Click the OK button for the message box reminding you that your Work Flow setting changes will not take effect until the next time you log into EnergyCAP
7. Exit EnergyCAP, then log back into EnergyCAP

When a bill is manually entered into EnergyCAP, the Quick Check Audit Group will execute when the user clicks the Save button. If the bill data passes the audits, it will be saved and the data entry operator can enter the next bill. If the bill data does not pass the audits, an Audit Results window will list the audits the bill data failed.  See below:

 

If the bill still fails any audits, there are three options to choose from:

1. View/Edit Bill - This will display the bill entry screen to make changes to the bill data or add Bill Messages and/or Bill Issues (Issue Tracker).
2. Save Bill Ignore Problem - This will save the bill and mark the problems as Ignored. The problems will NOT appear in Work Flow Manager’s problem folders and will not appear if an audit is run on this bill again in the future.
3. Save Bill w/Problem Flag - This will save the bill, and the problems WILL appear in Work Flow Manager’s problem folders.

Audit #46 is a single bill entry super audit that is extremely fast and powerful! This single audit is a good choice for testing bill entry. Its internal logic has these 26 individual tests.

* Secondary test: Use ONLY when same month last year (LY) test is used. If a bill fails a LY test, re-test it using the MR (most recent) bill. If it passes that test, it is okay. Logic – We want to prevent false alarms. If a bill fails when compared to LY, we then compare it to last month. If it does not fail when compared to last month, we conclude that (1) last year was an abnormal bill itself, or, (2) the nature of the account has changed (maybe more area has been added) and since it looks like last month, we conclude it is not a keying error.

** Load Factor = actual usage/theoretical usage = [usage/(demand * days * 24)]

In some cases, it may be advantageous to override a particular audit variance for a specific account or meter.  To do this, open up the Properties window for an account or meter and click the Audit tab. 

Meter Audit Override

Account Audit Override

To override the default variance from the Meter Properties or Account Properties window:

1. Click the Audits tab.
2. Select the audit from the Audit list.
    
3. Click/select the audit property to be changed.
   
4. Click the Properties icon ( ). The Edit Override Value window will open.
5. Change the Audit value of the variance in the Edit Override Value window.
   

NOTES: After the value has been changed, the Properties list box will display the Value Type as Override to indicate the change:

Override values will be used whenever audits run regardless of the values set for the audit in the audit group.  In other words, the audit values set at the account and meter level supercede any and all other audit variance settings.

The Quick Check Audit checks for large data entry errors.  Additional Audit Groups can be created to review specific data or to provide a more stringent check of the entered information.

There are two different ways to select bills that you want to audit, based upon your Work Flow Wizard settings:

• If you have turned on the Bill Approval option, all saved bills will be placed in the Unapproved folder within the Work Flow Manager\Bill folder
• If you are not using the Bill Approval option, the saved bills will appear in the Last Thirty Days folder within the Work Flow Manager\Bill folder (this folder may have a different name, such as Within Last 7 Days, depending on what Options you selected from the Work Flow Manager).

1. Navigate to the Audit Manager (Analysis > Audits) in EnergyCAP.
2. From the Audit menu, select  New > Group OR right click on the Audit Groups node of the tree view and select New Group from the popup menu.
3. Enter a Code and Display for the Audit Group.  As this will be an Audit Group used to more closely review the entered bill data, you may want to make the Code = REVIEWDATA and the Display = Review Data.
   
4. Select the Audit Group node on the tree. Click the New Audit button, located in the top right corner of the screen, and select the audit or audits desired when reviewing the data. Audit #46 may be run again with tighter variance, or several individual audits may be included, such as:
1. Audit #09 – Use/Day Exceeds Last Year
2. Audit #10 – Cost/Day Exceeds Last Year
3. Audit #28 – Consecutive Zero Bill Use
4. Audit #34 – Abnormal Bill Period Length
5. Audit #35 – Bill Load Factor
6. Audit #38 – Cost/Unit Exceeds Last Year.
5. As you save the audits to the Audit Group, review the variance settings, shown in the Settings Tab, to determine if variances should be adjusted.
   
   
   NOTE: If audit variances have been set at the account or meter level (Properties > Audits), these lower-level variances will take priority over group variance settings.
6. OPTIONAL: If using the Bill Approval option, the Workflow must be altered to accommodate it:
1. From the Workflow Manager, go to the Tools menu and select the Workflow Wizard option.
2. On the third page of the Workflow Wizard (shown below), select Use bill approval system option. We highly recommend that if you are using the Bill Approval System, that you also select to Confirm edit/delete of approved bills
   NOTE: EnergyCAP, Inc. recommends initially using the Bill Approval System in manual mode (no automatic bill approval) to understand and appreciate how many and what kinds of bills are being tagged with a Bill Message.  During the initial use of the combined Bill Approval System and audits, it may be necessary to adjust the variance settings to reduce the number of 'false alarms.' Once you are comfortable with the variance settings, you may choose the Enable automatic approval process Work Flow option.
   
   The Enable automatic approval process option allows you to setup an audit group whose results will determine if the bill is approved or not.  The settings of this feature are:
1. Audit Group – Choose the audit group that contains the audits you would like to use in determining if the bill is approved or not.
2. Severity Level – Choose the severity that will act as the cutoff for bill approval.  Example:  Selecting a severity of Low would mean that ONLY bills that either pass all the audits or have an audit problem with a severity of Low or Pass will be approved.  All other severity settings will leave the audit Unapproved. Click for more information on Audit Severities.
3. Perform Audits Automatically – Choose the time at which the audit group is to be run against the bill.  It is recommended that the audits are run After Entry or After Batch.  After Batch would be preferable if you are also using a Quick Check audit group as part of your Work Flow Manager settings.
7. The process to execute an Audit Group to audit selected bills is the same whether you are using the Bill Approval System or not. If you are using the Bill Approval System, you will navigate to the Unapproved folder within the Work Flow Manager\Bill folder to begin the process. Otherwise, navigate to the Last Thirty Days folder within the Work Flow Manager\Bill folder in the Accounting module to begin the process.
8. When you select the appropriate folder, bill records should appear in the top right window.
9. In this window, the Column Headers can be used to sort the bill records quickly. For example, if you are primarily concerned with auditing bills with a monthly cost over $5,000, you can click on the Cost column header to sort the bills in descending cost order. Click on the Cost column header again to sort the bills in ascending cost order.
10. Select the bill records you want to audit by clicking on the first bill record and using the shift+click or ctrl+click functions to select multiple records.
    
11. Right-click on a highlighted bill record, then select the Audit option from the menu.
12. Select which Audit Group you desire to execute. Enable the Show results option, if you would like EnergyCAP to provide you with a list of bill records that failed an audit once the Audit Group has finished executing. Click the OK button to execute the Audit Group.
    
13. Bill records that fail an audit will be copied to the Problem folder within the Work Flow Manager\Bill folder.
14. Navigate to the Problem folder and select a bill record to determine which audit(s) it failed.  Any Bill Messages and Audit results will be listed in the lower right-hand window.
15. Double-click on the bill record in the top right-hand window to view the bill entry screen and begin resolving the problem. If you edit the bill while resolving the problem, right-click on the bill record and select the Mark problems fixed option from the menu. This will allow EnergyCAP to re-audit the bill to confirm that the problem has been fixed. If you determine that there is a valid reason why the bill is out of variance, or that the bill amount is too small to bother with, right click on the bill and select the Mark problems ignore option from the menu. This will tell EnergyCAP to ignore this problem bill when executing the audit that the bill initially failed.
16. Once you have marked the Problem bills as being fixed or ignored, return to either the Unapproved or the Last Thirty Days folder.
17. Select the bill records again and execute the Audit Group to confirm that the fixed bill records now pass the audits. If not, return to the Problem folder, edit the bill, and either enter a Bill Message and/or create a Bill Issue detailing the issue.
18. If using the Bill Approval System, select the bill records within the Unapproved folder that have either passed the audits or have been marked to ignore the audit results. Right-click one of these selected bill records, and select the Approve option from the pop-up menu. These bills are now available to be reported on.
19. If not using the Bill Approval System, leave the bill records in the Last Thirty Days folder. These bills will disappear from the folder when their Date Created is more than 30 days prior to today’s date (based on the system date of the computer).

Every audit is given a severity setting for when the audit is failed.  The severity is associated with the problem message that the audit creates with the bill when the audit conditions are met.  There are six different audit severities:

0 – Pass

1 – Low

2 – Moderate

3 – Severe

4 – Fail

5 – Export Hold

The main use of severities is in setting up Auto Approval based on audit results for a bill.  So if you setup an Audit’s severity as Severe, and have Auto Approval set to approve anything Moderate or better, that means Pass, Low and Moderate would be approved, but Severe, Fail, and Export Hold would not.  In this manner, the more important audits can be set to a higher severity when the audit is failed.  The less important audits can be configured with a lower severity so even when the audit is failed, the bill would still be approved.

NOTE: The Export Hold severity has a second purpose beyond bill approval.  In addition to that functionality, the bill which fails an audit of this severity will have its Export Hold flag set to True.  Bills marked with this status will appear in the Work Flow Manager in the Holding for Export folder.

 

Bills in this folder will not be exported to AP or GL when the Bill Export process is run.  They are withheld until the flag is toggled off.  To toggle the Export Hold flag, simply right-click on the bill and select the Toggle Export Hold option.

Below is a sample command line to use for the EnergyCAP Audit Task applet to audit the bills or to import channel/interval data. This task should be scheduled to run on either the Web server, database server, or an application server to centralize automated tasks, avoid network traffic issues, and provide a quicker audit.

The Audit Task applet command line will execute the audits in a specified Audit Group. Therefore, you will need to create at least one Audit Group in EnergyCAP and the EnergyCAP User indicated in the command line MUST have permission to execute audits. If the system user is the one you want to use, you will need to edit the security user role or the system user to grant permission to execute audits.

The command line to pass to the Audit Task applet is defined below. In the command line, we have the choice of either providing the start date and end date or providing the number of days (prior to the system date) to limit the data being audited. If you are planning to run this as a scheduled task, it is recommended that you use the number of days option. In the example below, the start date and end date option are used. The command line format is as follows:

Audittsk –d [DSN] –u [User] –p [pwd] –s [start date] –e [end date] –c [channel(s)] –a [account(s)] –g [audit group] –l [logfile] –b [batch]

Not all options are required, depending on what type of audit you are running. If you are running billing audits, the account number would be used and not the channel option. If you do not enter dates, channels, or accounts, it will run for all. The required fields are the dsn, user, pwd, start date, end date, audit group and logfile.

Each field in the command line is described below:

• -d [DSN] is the ODBC Datasource name (demodata)
• -u [User] is an EnergyCAP Enterprise User (system). Remember, that the user must have rights to run audits or it will not work.
• -p [pwd] is the password for the EnergyCAP Enterprise User above.
•  -s [start date] is the date [yyyy-MM-dd] or [number]d to begin auditing data (the number of days is assumed to be negative. To audit the previous 2 days of data, the format should be ‘–s 2d’). The dates used will look at the bill dates or channel dates (not the date it was entered, etc).
•  -e [end date] is the date [yyyy-MM-dd] or [number]d to stop auditing data (the number of days is assumed to be negative. To end the audit two days previous to the system date, the format should be ‘–e 2d’). If you are running this task overnight, either have the end date 1 day prior to the system date, or do not include this switch in the command line.
•  -a [account(s)] allows you to specify one or more accounts to audit
• -g [audit group] defines what audit group to run (TEST1, for example)
•  -l [log file] is the name and path, in quotes, for a log file to capture error messages (“c:\temp\bjAuditLogs\AuditLog_[DATETIME].log”). The switch is a lower case “l” for log. During testing, we determined that the [DATETIME] above was not being replaced with the system date and time, as it should. This bug has been corrected and will be available with the next release of EnergyCAP Enterprise.

• -b [batch] This parameter allows you to include the name of the batch that you would like the audits to run against.

Using the above sample data, the command line would be:

Audittsk –d demodata –u system –p system –s 2004-12-06 –e 2004-12-28 –g TEST1 –l “c:\temp\bjAuditLogs\AuditLog_[DATETIME].log”

To run the command line format, open a command window (in Windows, click on the Start button, click on Run, and enter the text: "cmd"), change directories to “c:\Program Files\EnergyCAP Enterprise” (or the EnergyCAP installation directory). Next, type in the above command line changing the flags to fit your installation.

Data that fails the audit will appear in the Workflow Manager. To review the audit results, expand the Channels folder or Bill folder and select the Problems folder. The channels or bills with data that needs to be reviewed will appear on the right side of the screen and explanations of why the data failed the specific audit are provided in the lower window. You may need to select Edit and/or Refresh to display any new problems found by the audits if you are already in the application.

This topic provides a description of each individual audit available through the Audit Manager. It also provides recommendations for efficient use of available audits.

Audits are arranged numerically in two groups; bill audits and channel audits. Gaps in the numerical sequence for one group may indicate that the desired audit is located in the other group.

The following are recommended bill entry audits: 3, 8 and 46

Audits 1-2, 5-7, 9-10, 14-19, 30-35, and 37-39 are covered by consolidated Audit #46 and therefore are not recommended for individual use.

Bill Audits

1. Abnormally Long Billing Period: Billing period is longer than recent bills. Alert created if variance from historical bills exceeds user defined variance.

2. Abnormally Short Billing Period: Billing period length is shorter than recent bills. Alert created if variance from historical bills exceeds user-defined variance.

3. Bill Cost Duplicate: The cost on this bill is the same as the most recent bill for this account. Check for duplicate. Alert created if the cost is the same amount as the last bill.

4. Invoice Numbers Duplicate: Invoice number is a duplicate of a prior bill. Check for duplicate. (only when invoice number <> null).

5. Gap in Billing Dates: Compares billing start date for the billing period with most recent bill in the system for the same account. Alert is created if variance between the dates exceeds the user-defined variance.

6. Billing Ends After Today: Billing period end date is later than today’s date.

7. Abnormal Bill Due Date: Due date (if not null) is abnormal. Either it is earlier than the bill date OR exceeds end date by more than the user-defined variance.

8. Possible Estimated Bill: Alert created if the bill is not marked as an estimate and either the total usage is the same OR the use/day is the same for a user-defined number of bills in a row.

9. Use/Day Exceeds Last Year: Calculates current average daily use for each meter and compares it with the same billing period last year. Alert created if variance from historical bills exceeds user-defined limit. A negative variance indicates less than historical use.

10. Cost/Day Exceeds Last Year: Calculates current average daily cost and compares it with the same billing period last year. Alert created if variance from historical bills exceeds limit. A negative variance indicates less than historical value.

11. Highest Cost/Day: Compares the cost/day with the previous highest cost/day for a user-defined number of billing periods. Alert created if variance from historical bills exceeds user-defined variance and current total cost exceeds minimum. A negative variance indicates less than historical cost.

12. Highest Use/Day: Compares the use/day for each meter with the previous highest use/day for a user-defined number of billing periods. Alert created if variance from historical bills exceeds user-defined variance and current total cost exceeds user-defined minimum.

13. Bill Overdue: Calculates the expected entry date of the next bill for an account. Alert is created if today is more than a user-defined number of days past the expected entry date and the total cost exceeds the user-defined minimum.

14. Highest Cost/Unit: Compares the cost/unit for each meter with the previous highest cost/unit for a user-defined number of billing periods. Alert created if variance from historical bills exceeds user-defined variance and current total cost exceeds user-defined minimum.

15. Demand/Day Exceeds Last Year: Calculates current average billed demand/day for each meter and compares it with the same billing period last year. Alert created if variance from historical bills exceeds user-defined limit. A negative variance indicates less than historical value.

16. Highest Demand/Day: Compares the billed demand/day for each meter with the previous demand/day for a user-defined number of billing periods. Alert created if variance from historical bills exceeds user-defined variance and current total cost exceeds user-defined minimum.

17. Abnormal Billing Period: The assigned billing period is outside of the service dates of the bill.

18. Use/Day Under Last Year: Calculates current average daily use for each meter and compares it with the same billing period last year. Alert created if variance from historical bills exceeds limit. A negative variance indicates less than historical use.

19. Cost/Day Under Last Year: Calculates current average daily cost and compares it with the same billing period last year. Alert created if variance from historical bills exceeds limit. A negative variance indicates less than historical value.

21. Bill Maximum-Minimum Cost: Total cost for each bill is compared with defined high/low limits or baseline year data.

22. Bill Maximum-Minimum Use: Total consumption for each bill period is compared with defined high/low limits or baseline year data.

23. Bill Period Demand: Compares current demand with historical bills. Alert created if variance from historical bills selected exceeds user-defined variance limit. Severity 1 – 4 fails with results. Use Type Class to specify Demand type.

25. Average Daily Cost by Bill Period: Calculates average daily cost (total cost divided by number of days in the billing period) and compares it with historical bills. Alert created if variance from historical bills exceeds user-defined variance. Severity 1 – 4 fails with results. The audit will always check and compare the same billing period from last year, as well as the immediate prior month and any ‘lookback’ month(s) specified as -1 or -2, etc.

26. Average Daily Use by Bill Period: Calculates average daily use (total usage divided by number of days in the billing period) and compares it with historical bills. Alert created if variance from historical bills exceeds user-defined variance limit. Severity 1 – 4 fails with results. The audit will always check and compare the same billing period from last year, as well as the immediate prior month and any ‘lookback’ month(s) specified as -1 or -2, etc.

27. Bill Maximum-Minimum Demand: Unique demand values for each bill period are compared with defined high/low limits or baseline year data.

28. Consecutive Zero Bill Use: If the use/consumption is zero for X months in a row, the account may be inactive. This alert identifies accounts that may be inactive and monthly service charges may be wasting money.

29. Same Average Daily Bill Use: If the average daily usage is exactly the same for a number of bills in a row, this is an indicator that the bill is estimated, indicating the meter failed or is blocked. Often times, vendors over-estimate bills.

30. Average Unit Cost by Bill Period: Calculates average unit cost (total cost divided by total usage) and compares it with historical bills. Alert created if variance from historical comparisons exceeds user-defined limits.

31. Bill Unit Cost High-Low: The average unit cost (total cost divided by total usage) for each bill is compared with a defined high/low limit or baseline year data.

32. Overlapping Bill Periods: Two consecutive bills have billing period dates that overlap by more than the specified number of days. Normally, dates should never overlap because the end date of one bill is the start date of the next. Possible data entry or billing error.

33. Multiple Bills in Period: Normally one bill will exist in each billing period. The application defines a bill period as monthly, quarterly, semi-annual or other. This alarm sounds if there is more than one bill in a period.

34. Abnormal Bill Period Length: The billing period length is shorter or longer than normal length. The normal length is set for each bill during setup (monthly, semi-annual, etc.). This audit flags any bills with periods that exceed the normal period by user-defined number of days.

35. Bill Load Factor: In theory, the load factor can never exceed 100%, so this is often an indicator of a data entry or billing error, in which either (1) the billed demand is too low, or (2) the electricity usage is too high.

36. Bill Cost Total: Compares the bill total (pay amount) to the sum of all line item costs.

37. Demand/Day Under Last Year: Calculates current average billed demand/day for each meter and compares it with the same billing period last year. Alert created if variance from historical bills exceeds user-defined limit. A negative variance indicates less than historical value.

38. Cost/Unit Exceeds Last Year: Calculates current/cost unit for each meter and compares it with the same billing period last year. Alert created if variance from historical bills exceeds user-defined limit. A negative variance indicates less than historical use.

39. Cost/Unit Under Last Year: Calculates current cost/unit for each meter and compares it with the same billing period last year. Alert created if variance from historical bills exceeds user-defined limit. A negative variance indicates less than historical use.

41. Meter Reading Reconciliation: Meter template tracks start/end meter readings and billed usage. Alert created if billed usage varies from usage calculated via meter readings. A negative variance indicates calculated use less than entered use. This audit requires a special setup in order for it to function properly. Meters that you want to use this audit on should be setup with a Bill Template which has the following Observation Types for the Start Reading, End Reading, Multiplier (optional), and Usage.

Start Reading Observation Type should be: STARTREADING

End Reading Observation Type should be: ENDREADING

Multiplier Observation Type is: MULTIPLIER

Usage Observation Type should be: USE

When a bill has been entered on a meter which has these observation types, the audit will calculate the difference between the Start and End readings, multiply by the multiplier value, and calculate the usage. If the calculated value violates the variance set for the audit, the audit will report the problem.

For more information about setting up Bill Templates, see the Bill Templates topic group.

42. Rate Schedule: Reconciles billed cost with calculated cost.

43. Cost via Unit Cost: Meter template tracks cost per unit and usage. Alert created if billed cost varies from the cost calculated from the unit cost and usage. A negative variance indicates calculated cost less than entered cost.

46. Bill Entry: A single multi-purpose audit that checks each bill multiple ways to catch bill entry errors.

For additional details regarding Audit 46, see the Audit 46 topic.

47. Duplicate Control Codes: Looks for duplicate entries in the control code field for all accounts. This is important when the control code field is used to hold the file name of the scanned image file.

48. Actual vs. Budget: Compares actual values to the values set in the budget version that you select in the audit.

49. Bill Flag: Lets you flag bills with certain line items on them such as Late Fees, Deposits, Adjustments, etc. This audit looks at the underlying Observation Type of the line item on the bill to see if it exists. This audit also contains a cost parameter.

50. Continuous service account date tests: Similar to Audit 46, but designed to check intermittent continuous service accounts (rental apartments) for reasonableness of dates and total cost. Audit 50 does not check for usage or unit cost. There is no minimum cost filter. Includes PayAmount maximum test option (default $100) to help avoid keying errors.

51. Line Item Existence by Caption. Similar to Audit 49, but provides caption search. EXAMPLE: Tax-exempt organization wants to flag bills with Caption of "tax" so if any bills come through with taxes, they know to work with that vendor to claim their tax exempt status.

52. Stored Procedure Execution. Enables automatic execution of a stored procedure, which can be a convenient way to execute user-defined database processes. Can be used in multiple audit groups to facilitate sequential procedures.

Channel Audits (Interval Data)

20. Meter Channel Maximum-Minimum: Unique channel values for each meter are compared with defined high/low limits or baseline year data.

24. Meter Channel Change from Historical: Compares current channel data with historical. Alert created if variance from historical data exceeds user-defined variance limits.

40. Channel Limit Exceeded: Values in a channel are totaled in a start/end date range. Alert created if total value exceeds a maximum limit.

44. Channel Setback: The channel data before and after the setback time is examined to see if the setback was performed and the difference is greater than the setback amount. Alert created if difference is less than expected amount.

45. Channel Startup: The channel data before and after the startup time is examined to see if the startup was performed and the difference is greater than the startup amount. Alert created if difference is less than expected amount.

How Percent Variance is Handled in EnergyCAP

There are 18 Auditors with a “Percent Variance” property.

Two of these do a one-way comparison – assumed always positive

• 01_DaysTooMany: Only compares one way
• 02_DaysTooFew: Only compares one way

The remainder do a two-way comparison – Greater Than when the Percent Variance value is Greater Than Zero and Less Than when the Percent Variance value is Less Than zero.

• 09_UPDOvrYrAgo: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 10_CPDOvrYrAgo: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 11_HghCostPDay: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 12_HghstUsePDay: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 14_HighCstPUnit: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 15_DmdPDOvrYrAgo: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 16_HighDmndPDay: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 18_UPDUdrYrAgo: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 19_CPDLssYrAgo: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 23_BILL_PD_DEM: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 25_BILL_PRD_ADC: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 26_B_PERIOD_ADU: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 30_BILL_AVUNTCST: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 37_DmdPDLssYrAgo: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 38_UCost_YrAgo: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0
• 39_UCstUndrYrAgo: Compares GT if Pct Variance > 0 and LT if Pct Variance < 0

Audit #49 enables the user to flag bills for which certain line items exist, such as Late Fees, Deposits, Adjustments, etc.

This audit observes the underlying Observation Type of the bill line item on the bill to see if it exists. Up to five different observation types may be defined for this audit.

A cost parameter and audit severity may be selected as well.

NOTE: The BL16 (Bill Line Item) report can also be used for tracking bills containing line items associated with a particular observation type. An Observation Type Name filter and an Observation Type Code filter may be used when generating the BL16 report. the Line Item value associated with the Observation Type is included on the final column of the report.

A user asks:

How do I use Audit 49 to monitor Special Charge line items?” (This applies for all Special Charges that are shown in the Special Charges editor)

ANSWER:

Audit 49 finds occurrences of Observation Types, not special charges. A special charge can be thought of as a “named observation type.” So to limit the audit to one particular charge, it is necessary to create an associated Observation Type that is UNIQUE.

For example, EnergyCAP has a pre-configured special charge called “Deposit.” However the underlying observation type that is associated with this special charge (see Deposit properties in the Special Charges Editor) is “charge.” In essence, “Deposit” is the same as a generic “charge” but with a special name. But since Audit 49 does not search for special charges, it will return results for other line item "charges" besides Deposits.

To narrow the focus of the audit, it is possible to create a new observation type called “deposit” and then edit the existing Special Charge called “Deposit” by removing “charge” as the underlying observation type and substituting the new ‘deposit’ observation type.

There are four or five prerequisites (depending on your current organization configuration) for successfully using Audit 49 to effectively flag bills with specific Special Charges:

1. The Special Charge must be created.
2. The Special Charge must be associated with a unique observation type (see below) using the Unit System Editor. If an appropriate Observation Type does not yet exist, it must be created.
3. The Special Charge must be fully defined using the Special Charge Editor.
4. The Special Charge Observation Type must be added to the Audit 49 Properties (see below).

Creating a New Observation Type

Once a Special Charge has been created (see link above), it is possible to associate it with a specific Observation Type using the Unit System Editor. Follow the instructions below to create a new Observation Type and then associate it with the Special Charge. The example provided assumes a cost value and not a consumption value. The example also demonstrates how to create a NEW Observation Type, if an appropriate Observation Type does not yet exist.

1. Click Tools/Unit System. The Unit System Editor window will open.
2. Click the Types tab. 
   
3. Click the "Plus" icon. The Create Type window will open. 
   
4. Enter an appropriate Code and Display name for the new Observation Type. Then use the drop-down menu choices for Class, Time of use, Origin, and Applied to fully characterize the new Observation Type. In this example, use the values shown above (e.g. Time of Use-NONE, Origin-NONE, Applied-CREDIT-PROVIDED).
5. When done, click OK. The Create Type window will close and the new Observation Type will appear in the list box.

Associating a Special Charge with a specific Observation Type:

 To associate a Special Charge with a specific Observation Type:

1. Click Tools/Unit System. The Unit System Editor window will open.
2. Click the Equivalencies tab.
3. Select the Commodity that shares a Class with the desired Observation Type (usually "Charge"). Make certain that the Unit Type and Default Unit are appropriate for the Commodity. If they are not, it may be necessary to create a new Commodity.
   
4. Click the Properties icon. The Equivalence Properties window will open.
5. In the Relationships pane, make certain that the desired Observation Type has a check mark in the associated checkbox. Also verify that the desired Unit has a check mark in the associated checkbox.
6. When done, click OK to save the settings and close the Equivalence Properties window.

Defining Special Charge Properties using the Special Charge Editor

1. Click Accounting/Accounts. The Account Manager will be displayed.
2. Click Account/Editors/Special Charges. 
   
   
   The Special Charge Editor window will open. 
3. Select the desired Special Charge ("Deposit" in this example). Then click the Properties icon.  
   
   
   The Special Charge Properties window will open. 
   
4. Add/update desired information for the Special Charge, using the fields and drop-down choices provided. The Help tag will be displayed when the user 'mouses over' the caption during bill entry. Make sure that the Commodity, Type and Unit information is correct. Then click OK to save the Properties and exit the Special Charge Properties window.
5. Click Close to exit the Special Charge Editor window.

Adding an Existing Observation Type to Audit #49

To add an existing Observation Type to Audit #49:

1. Click Analysis/Audits. The Audit Manager will be displayed.
2. Define an Audit Group that includes Audit #49.
3. Right-click Audit #49 from the Audit Group and select Properties from the popup menu options. The Audit Properties window will open.
4. Click the Settings tab and select one of the Observation Type lines (1-5).
5. Click the Properties icon. 
   
   
   The Create Audit Setting window will open.
6. Select the Audit Value from the drop-down list.
7. When done, click OK to save the changes and exit the Create Settings window.
8. Click OK to exit the Create Audit window. The Audit has been modified to include the criteria you selected.

In some cases, it may be advantageous to edit/update the audit variance for an audit or audits in a specific Audit Group. To do this:

1. Open the Audit Manager (Analysis > Audits).
2. Select the desired audit group.
3. Select the desired audit. from the Audit Group.
   NOTE: The Set Variance button will not work unless a specific audit is selected (highlighted).
4. Click the Set Variance button from the Audit Manager title bar. The Audit Properties window will open.
5. Click the Settings tab. Current variance settings will be revealed.
6. Click the line item that needs to be updated. Then click the Edit icon . The Create Audit Setting window will open.
7. Input the desired audit value in the field indicated. Then click OK to close the Create Audit Setting window and save the revised variance value.

NOTE: The audit values set at the account and meter level supercede any and all other audit variance settings.

Audits may be run as frequently as desired in EnergyCAP.

When an audit is first run for a new bill, a failed audit result or results will populate the BillMessageLog table in the EnergyCAP database. Information from repeat audits is appended to the BillMessageLog table, with updates per the following logic. 

NOTE: The Status column indicates the status of the bill prior to running the repeat audit.