How to Calculate Revenue From Charge Records

This document explains how to identify which charge records should be used to calculate total revenue and which charge records are used for unbundling purposes only. This information is often needed when using a report writer to calculate revenue.

NOTES:

Before proceeding with this chapter, the user is strongly encouraged to first become familiar with the structure of the charge record and the meaning and possible values that can appear in each field. The manual chapter Print Charge Records explains this.
While the examples used here are taken from a closed and posted RA, the concepts apply equally to open and closed/unposted RAs as well as to Reservations.

When inclusive rates are not used, every non-canceled charge record is part of the total revenue of the RA or Res. Simple addition of every non-canceled record will result in the total revenue.

When inclusive rates are used, some charge records exist solely for the purpose of calculating the extraction amounts -- that is the revenue for each charge item that has been included in the TIME charge. This is because the when printing the Accounting DBR and posting to the General Ledger, the revenue is extracted, or unbundled, for each item that was included in the TIME revenue and posted to the appropriate account for that item. These "extraction records" cannot be must be appropriately filtered when calculating total revenue on a Res or RA that has an inclusive rate.

There are two types of extraction records, which can be identified as follows:

Retail and Tour Voucher RAs

Inclusive flag = I (Inclusive)
Tour voucher sequence number = 0 (Charge not part of a voucher)
Wholesale flag ≠ I (Insurance) [not equal to insurance]

Insurance Replacement RA

Inclusive flag = I (Inclusive)
Wholesale flag = I (Insurance)

From the definitions above, it can be seen that:

On tour voucher rentals, extraction occurs only on the time revenue paid by the renter. Time revenue paid by the voucher is unbundled from the start.
On insurance replacement rentals, extraction occurs on both the renter and the voucher time revenue. Time revenue is bundled on all vouchers.

In the example RA shown below, the highlighted charge records are the "extraction records". The revenue on those records is already included in the bundled time revenue of $1400.00.

28-08-XX 19:28:30 XXX    XXX        00                  THERMEON DEMO COMPANY                   PRTFILE     Report #9754   Page 0001

                                             P R I N T   C H A R G E   R E C O R D S

SELECTIONS----------
Ra number:          ABC00001
------------------------------------------------------------------------------------------------------------------------------------
Code  Desc       Days   Weeks    Mths   Xdays            St DBR Loc  Date   Time  CR Date Time  Empl  Mod Date Time  Empl   Invoice
 Opt ID          Rate    Rate    Rate    Rate            Pst Vch Inc Strt date time  End date time   Asset# RspPty VchFop Ic
------------------------------------------------------------------------------------------------------------------------------------
TIME  TIME           4                           1400.00    CAN50  30-06-13 1753 26-06-13 1652 RIGO   01-07-13 0242 APOST
D   2361 4100  350.000                                    Y                                                 956409         0
DROP  DROP OFF                                              CAN50  30-06-13 1753 26-06-13 1652 RIGO   01-07-13 0242 APOST
F  37834 4160    5.000                                    Y                                                 956409         0
FUEL  FUEL                                        353.15    CAN50  30-06-13 1753 26-06-13 1652 RIGO   01-07-13 0242 APOST
F   2332 4170   10.090          353.15   10.09            Y                                                 956409         0
TAX   SALES TAX                                    38.85    CAN50  30-06-13 1753 26-06-13 1652 RIGO   01-07-13 0242 APOST
P  37537 2210   11.000                                    Y                                                 956409         0
AFEE  AIRPORT                                               CAN50  30-06-13 1753 26-06-13 1652 RIGO   01-07-13 0242 APOST
P  38798 4014   18.000                                    Y                                                 956409         0
SCHR  SERV.CHR                                     26.00    CAN50  30-06-13 1753 26-06-13 1652 RIGO   01-07-13 0242 APOST
F  38919 4032   26.000                                    Y       I                                         956409         0
AFEE  AIRPORT                                     188.43    CAN50  30-06-13 1753 26-06-13 1652 RIGO   01-07-13 0242 APOST
P  38798 4014   18.000  188.42                            Y       I                                         956409         0
LDW4  CDW-2          4                               .04    CAN50  30-06-13 1753 26-06-13 1652 RIGO   01-07-13 0242 APOST
D  37490 4023  325.000                                    Y       I                                         956409         0
TAX   SALES TAX                                   138.74    CAN50  30-06-13 1753 26-06-13 1652 RIGO   01-07-13 0242 APOST
P  37537 2210   11.000  135.87                            Y       I                                         956409         0
MILE  MILE         355                                      CAN50  30-06-13 1753 26-06-13 1657 RIGO   01-07-13 0242 APOST
F   2351 4110                                             Y                                                 956409         0

                                                   -- End of report --

In the example below, there are no extraction records. All records are used to calculate the total revenue. Even though there was an inclusive rate on the rental, since the voucher paid all of the time revenue, there is no unbundling to be done. The only charges paid by the renter are those not included in the voucher.

28-08-XX 19:28:07 XXX    XXX        00                  THERMEON DEMO COMPANY                   PRTFILE     Report #8628   Page 0001

                                             P R I N T   C H A R G E   R E C O R D S

SELECTIONS----------
Ra number:          ABC00002
------------------------------------------------------------------------------------------------------------------------------------
Code  Desc       Days   Weeks    Mths   Xdays            St DBR Loc  Date   Time  CR Date Time  Empl  Mod Date Time  Empl   Invoice
 Opt ID          Rate    Rate    Rate    Rate            Pst Vch Inc Strt date time  End date time   Asset# RspPty VchFop Ic
------------------------------------------------------------------------------------------------------------------------------------
TIME  TIME                                                  CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
D   2361 4100  368.100 2064.29          368.10            Y                                                 957301         0
MILE  MILE         481                                      CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
F   2351 4110                                             Y                                                 957301         0
DROP  DROP OFF                                              CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
F  37834 4160    5.000                                    Y                                                 957301         0
FUEL  FUEL                                        512.00    CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
F   2332 4170   32.000                                    Y                                                 957301         0
TAX   SALES TAX                                   195.75    CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
P  37537 2210   11.000                                    Y                                                 957301         0
AFEE  AIRPORT                                     175.50    CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
P  38798 4014   18.000                                    Y                                                 957301         0
SCHR  SERV.CHR                                              CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
F  38919 4032   26.000                                    Y       I                                         957301         0
AFEE  AIRPORT                                               CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
P  38798 4014   18.000                                    Y       I                                         957301         0
TAX   SALES TAX                                             CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
P  37537 2210   11.000                                    Y       I                                         957301         0
AFEE  AIRPORT                                     147.79    CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
P  38798 4014   18.000  147.79                            Y   1   I  28-06-13  1320  01-07-13  1320         BEST07 BEST07  0
SCHR  SERV.CHR                                     26.00    CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
F  38919 4032   26.000                                    Y   1   I  28-06-13  1320  01-07-13  1320         BEST07 BEST07  0
TAX   SALES TAX                                   109.43    CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
P  37537 2210   11.000  106.57                            Y   1   I  28-06-13  1320  01-07-13  1320         BEST07 BEST07  0
TIME  TIME           3                            821.08    CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
D   2361 4100  821.080                                    Y   1   I                                         BEST07 BEST07  0
LDW4  CDW-2          3                            975.00    CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
D  37490 4023  325.000                                    Y                                                 957301         0
PRA   RD ASSIS       3                            117.00    CAN50  01-07-13 0325 28-06-13 1321 ERBU   02-07-13 0245 APOST
D  38934 4025   39.000                                    Y                                                 957301         0

                                                   -- End of report -

As mentioned earlier, if a report writer is being used to report and/or calculate revenue on a res or RA, it is critical to know how to process each record in the charge file.

Using the first RA shown above (RA #ABC00001, which is a retail RA with an inclusive rate):

To calculate the gross revenue of the RA, the charge on all of the non "extraction" records would be added:

Time    1400.00
Fuel     353.15
Tax       38.85
        -------
TOTAL   1792.00

Below is an example of how to do this in IQ:

MOVE 0 TO CALC-REV SCALE 12.2.
#
ADD RAC-CHARGE TO CALC-REV GIVING CALC-REV.
#
IF RAC-REC-TYPE = 'I' 
   AND RAC-TOUR-VCHR-SEQ-NO = 0 
   AND R-WSALEFLG <> 'I' 
   AND RAC-CANCEL-FLAG = ' '
SUBTRACT RAC-CHARGE FROM CALC-REV GIVING CALC-REV.
#
IF RAC-REC-TYPE = 'I' 
   AND R-WSALEFLG = 'I' 
   AND RAC-CANCEL-FLAG = ' '
SUBTRACT RAC-CHARGE FROM CALC-REV GIVING CALC-REV.

To show the revenue for each item on the RA (as it would be reported on the DBRA), the charge on the "extraction" records is subtracted from the time and then added to any amount for the same charge that was paid by the renter (i.e. not included in Time):

Time    1046.79  (1400.00 - 26.00 - 188.43 - 0.04 - 138.74)
Fuel     353.15
SCHR      26.00
AFEE     188.43
LDW4       0.04
Tax      177.59  (38.85 + 138.74)
        -------
TOTAL   1792.00

Below is an example of how to do this in IQ:

MOVE 0 TO TIME-CHG SCALE 6.2.
IF OPTFIL-TYPE = 'R' 'X' 'A' 'U' 'P' 
   AND RAC-CANCEL-FLAG = ' '
ADD RAC-CHARGE TO TIME-CHG.
#
MOVE 0 TO MILE-CHG SCALE 6.2.
IF OPTFIL-TYPE = 'K' 
   AND RAC-CANCEL-FLAG = ' '
ADD RAC-CHARGE TO MILE-CHG.
#
MOVE 0 TO FUEL-CHG SCALE 6.2.
IF OPTFIL-TYPE = 'F' 
   AND RAC-CANCEL-FLAG = ' '
ADD RAC-CHARGE TO FUEL-CHG.
#
MOVE 0 TO DROP-CHG SCALE 6.2.
IF OPTFIL-TYPE = 'D' 
   AND RAC-CANCEL-FLAG = ' '
ADD RAC-CHARGE TO DROP-CHG.
#
MOVE 0 TO CDW-CHG SCALE 6.2.
IF OPTFIL-TYPE = 'L' 
   AND RAC-CANCEL-FLAG = ' '
ADD RAC-CHARGE TO CDW-CHG.
IF OPTFIL-TYPE = 'L' 
   AND RAC-REC-TYPE = 'I' 
   AND R-WSALEFLG <> 'I' 
   AND RAC-TOUR-VCHR-SEQ-NO = 0
   AND RAC-CANCEL-FLAG = ' ' 
SUBTRACT RAC-CHARGE FROM TIME-CHG.
IF OPTFIL-TYPE = 'L' 
   AND RAC-REC-TYPE = 'I' 
   AND R-WSALEFLG = 'I' 
   AND RAC-CANCEL-FLAG = ' ' 
SUBTRACT RAC-CHARGE FROM TIME-CHG.
#
MOVE 0 TO CVG-CHG SCALE 6.2.
IF OPTFIL-TYPE = 'C' 
   AND RAC-CANCEL-FLAG = ' '
ADD RAC-CHARGE TO CVG-CHG.
IF OPTFIL-TYPE = 'C' 
   AND RAC-REC-TYPE = 'I' 
   AND R-WSALEFLG <> 'I' 
   AND RAC-TOUR-VCHR-SEQ-NO = 0
   AND RAC-CANCEL-FLAG = ' ' 
SUBTRACT RAC-CHARGE FROM TIME-CHG.
IF OPTFIL-TYPE = 'C' 
   AND RAC-REC-TYPE = 'I' 
   AND R-WSALEFLG = 'I' 
   AND RAC-CANCEL-FLAG = ' ' 
SUBTRACT RAC-CHARGE FROM TIME-CHG.
#
MOVE 0 TO OPT-CHG SCALE 6.2.
IF OPTFIL-TYPE = 'O' 
   AND RAC-CANCEL-FLAG = ' '
ADD RAC-CHARGE TO OPT-CHG.
IF OPTFIL-TYPE = 'O' 
   AND RAC-REC-TYPE = 'I' 
   AND R-WSALEFLG <> 'I' 
   AND RAC-TOUR-VCHR-SEQ-NO = 0 
   AND RAC-CANCEL-FLAG = ' '
SUBTRACT RAC-CHARGE FROM TIME-CHG.
IF OPTFIL-TYPE = 'O' 
   AND RAC-REC-TYPE = 'I' 
   AND R-WSALEFLG = 'I' 
   AND RAC-CANCEL-FLAG = ' '
SUBTRACT RAC-CHARGE FROM TIME-CHG.
#
MOVE 0 TO MISC-CHG SCALE 6.2.
IF OPTFIL-TYPE = 'M' 
   AND RAC-CANCEL-FLAG = ' '
ADD RAC-CHARGE TO MISC-CHG.
IF OPTFIL-TYPE = 'M' 
   AND RAC-REC-TYPE = 'I' 
   AND R-WSALEFLG <> 'I' 
   AND RAC-TOUR-VCHR-SEQ-NO = 0 
   AND RAC-CANCEL-FLAG = ' '
SUBTRACT RAC-CHARGE FROM TIME-CHG.
IF OPTFIL-TYPE = 'M' 
   AND RAC-REC-TYPE = 'I' 
   AND R-WSALEFLG = 'I' 
   AND RAC-CANCEL-FLAG = ' '
SUBTRACT RAC-CHARGE FROM TIME-CHG.
#
... continues in this manner until all possible charges are reported categorized as desired.

NOTE: For information on the process by which CARS+ extracts the included charges from the Time revenue, please refer to section "V. How Rates are Unbundled" of the CARS+ Manual chapter Overview - Inclusive Rates.