The BJU3BH_BILL Expense Billing batch job calculates fees and premiums and produces a report of the fees and premiums payable. Once authorisation of the calculated fees and premiums has taken place, the necessary accounting transactions will be generated.
The process is as follows:
- The batch selection for expenses is captured on the batch schedule (under the batch group of EXPENSES) with a selection period
- The expense batch job calculates the premiums based on the criteria entered as well as the scheme rules effective for the period, and stores the data. This data can be viewed under Income Administration.
Note:
If when the Expense Billing batch job is scheduled, the MEMBER LEVEL is selected, and the pricing method defined for the investment medium is “Same Day”, then the unit price with an Effective Date equal to the Transaction Date will be used when calculating the number of units to be realized.
If when the Expense Billing batch job is scheduled, the MEMBER LEVEL is selected, and the pricing method defined for the investment medium is Forward, then the unit price with an Effective Date equal to the next working day immediately after the Transaction Date will be used when calculating the number of units to be realized.
If when the Expense Billing batch job is scheduled, the MEMBER LEVEL is selected, and the pricing method defined for the investment medium is “Historic”, then the unit price with an Effective Date equal to the first working day prior to the Transaction Date will be used when calculating the number of units to be realized.
This report is generated by a batch run.
Log in to the system from the Logon page.
Click processes, then click expense billing.
The following screen is displayed.
Select Expense Billing from the top menu.
The following sub-menu will be displayed on the left.
Select New.
Note:
Transactional security is applied when New is selected. The following message will be displayed if the User is not authorised for the transaction:
Error
User is not authorised for this transaction.
Click OK to remove the message.
When New is selected, the JAJV Maintain Batch Schedule screen will be displayed.
Create a new JU3BH_BILL batch run or change the effective date of an existing batch run. For more information refer to Maintain Batch Schedule under
Infrastructure
Batch
The JU3BH_BILL \ BJU3BHO0.BAT batch job for EXPENSE BILLING will be run.
Once the batch job has successfully run, it can be viewed and authorised under the Select Expenses menu option.
In the JU3BH_BILL Expense Billing batch job, when the member level expenses are calculated, if there is more than one Income Type associated to the Expense Type the system will read the Calculation Method on the Expense Rule.
If it is PROPORTION, the amount of the expense to be deducted from each Income Type will be calculated as follows:
- The market value of the Income Types associated to the Expenses Type will be calculated. The system will retrieve the unit balances on the Member’s INVSTMEMUNIT account for the Portfolios for which the Type of Investment is UNITISED and find the latest unit price and multiple the unit balance by the unit price. The system will retrieve the monetary balances on the Member’s INVESTMEMB account for the Portfolios for which the Type of Investment is BONUS. The market values will be summed per Income Type.
- The market values will be summed.
- If the fee amount is greater than the sum of the market values, a message will be written to the error log and the next member will be processed.
- If the fee amount is less than the sum of the market values the fee amount per Income Type will be calculated as follows:
Fee Amount / Sum of Market Values (B) x Market Value of Income Type (A)
- If the fee amount is less than the sum of the market values, Business Transactions (BT’s) will be created with the proportionate amounts per Income Type.
If it is SEQUENTIAL, the fee amount to be deducted from each of the Income Types associated to the Expense Type will be calculated as follows:
- The system will retrieve the unit balances on the Member’s INVSTMEMUNIT account for the Portfolios for which the Type of Investment is UNITISED for the Income Type with the lowest sequence number. The system will find the latest unit price and multiple the unit balance by the unit price, and retrieve the monetary balances on the Member’s INVESTMEMB account for the Portfolios for which the Type of Investment is BONUS for the Income Type with the lowest sequence number. The values will be summed.
- The fee amount will be deducted from the sum of the investment market values (A)
- If the fee amount is less than the sum of the market values, the BT’s will be created with the fee amount and the Income Type of the lowest sequence number apportioned per Portfolio.
- If the fee amount is greater than the sum of the investment market values (A), the difference will be calculated and the BT’s will be created with the sum of the market values of the Income Type with the lowest sequence number apportioned per Portfolio.
- The system will calculate the difference between the fee amount and the sum of the market values for the Income Type with the lowest sequence number i.e. the remaining fee amount still to be deducted.
- The system will calculate the market values for the Income Type linked to the Expense Rule with the second lowest sequence number and deduct the net fee amount remaining to be deducted (E).
- If the net fee amount (E) is less than the sum of the market values calculated in F, the BT’s for the net fee amount (E) will be created with the Income Type linked to the Expense Rule with the second lowest sequence number apportioned by Portfolio balance.
- If the net fee amount (D) is greater than the market values for the next Income Type (F) the difference will be calculated.
- BT’s will be created for the sum of the market value of the Income Types with the second lowest sequence number apportioned by Portfolio balance.
- The system will calculate the market values for the Income Type linked to the Expense Rule with the third lowest sequence number and deduct the net fee amount remaining to be deducted (H).
- If the net fee amount (H) is less than the sum of the market values calculated in J, BT’s will be created for the net fee amount with the Income Type of the third lowest sequence number apportioned by Portfolio.
- If the net fee amount (H) is greater than the sum of the market values calculated in J, BT’s will be created for the sum of the market values with the Income Type of the third lowest sequence number apportioned by Portfolio.
- The system will continue as above for each Income Type until the full fee amount has been deducted.
- If the market value of all of the Income Types linked to the Expense Rule is less than the fee amount, no BT’s will be created for the Member and a message will be written to the Error Log and the next member will be processed.
In both cases the system will create the applicable Scheme level BT’s for the sum of the Member BT’s with the Accounting Activities applicable to the Global Group Type (FEE EXPENSE, PREMIUM EXPENSE, EXPENSE TYPE) for the Expense Type.
If it is MAX OF INTEREST, the system will retrieve the BTs in the member’s INVESTMEMB account with a Process of BONUS ALLOCATION and a Due Date month equal to the Effective Date of the Expense Billing run and sum the amounts for all Portfolios and Income Types. If the value calculated for admin fee for the Member is greater than the sum of the BONUS ALLOCATION BT’s retrieved then the expense amount is set to the value for the sum of the BT’s.
For more information refer to the Scheme Expense Rule section under
Product launch Requirements
Product Update
Product Update Type: Expenses
Note:
The Expense Billing batch job will only stop if the error is as a result of rule data not being found e.g. an Expense Rule not found for the Expense Type or for a Membership Group, and not as a result of individual Member data .e.g. Member does not have an investment balance.
If an individual Member error is encountered, an error message will be written to the Error Log and processing will continue.
Expense Billing cannot be run for the same Expense Type more than once for the same effective date and therefore any individual member errors must be fixed manually i.e. the fee calculated and apportioned manually and journals passed to update the member’s record.
Rounding
For Products in countries where decimal places are rounded, any premium amounts collected via debit order and any payment values will have their financial transactions values rounded to the nearest 5 cents.
When calculating the premium on the Policyholder Details screen, the system will read the value for Rounding Method on the Benefit Product linked to the Benefit Package with an Event Category of OTHER and a Benefit Event of PREMIUM RATES CALC and linked to the Benefit Membership Group selected. If it is NEAREST 0.05 the calculated premium amount will be rounded to the nearest 0.05 e.g. if the value calculated is 95.03 it will be rounded to 95.05, if it is 95.02 it will be rounded to 95.00, if it is 95.06 it will be rounded to 95.00, etc.
Calculation of Stamp Duty
The following processing applies to the calculation of Stamp Duty for a new Endowment Policy for Barclays Africa:
When the Expense Billing batch job runs and the value for Formula Applied to is SUM ASSURED, the system will read the value for the latest Member Values record for the Membership with a Type ASSURANCE DETS and Sub Type SUM ASSURED and an Effective Date less than or equal to the Expense Billing run To Date.
If the Frequency is ADHOC, the system will find the Memberships for which the CAR Start Date is less than or equal to the Expense Billing To Date and greater than or equal to the Expense Billing From Date i.e. only calculate the expense once off in for the month when the policy started.
The system will apply the To Amount and From Amount in the Scale linked to the Expense Rule (JU1DZ Expense Rule Scale screen)to the Sum Assured value.
Unitised Portfolios
For unitised portfolios, Expense Billing will create a BT in the Member’s INVESTMEMB account and the BT in the Member’s INVSTMEMUNIT account will be created by UNITISATION when the unit price is available.
If there is a change in the unit price and the market value is less than the market value that was calculated during the Expense Billing run, it would result in a negative balance in the Member’s INVSTMEMUNIT account. However the Unitisation batch job will not create the negative unitisation BT (UNITISATNEG) and will write a message to the Error Log. This is done to ensure that no negative unit balances are created on investment accounts.
If there is only one Income Type associated to the Expense Type, the BT will be created for the fee amount with the associated Income Type.
If there is no Income Type associated to the Expense Type, the BT will be created for the fee amount with the Income Type RCS (Employer Contribution).
Different fee rates for investment portfolios
When the BJU3BH_BILL Expense Billing batch job runs and the Type of Scale on the Expense Rule is SLIDING TOT MV, the system will calculate the market value of all of the Member’s Investment Portfolios and proportion the scale for each Portfolio.
Note:
This is done as there can potentially be different rates per Portfolio and therefore the fee cannot be calculated on the total market value and then be proportioned between each Portfolio.
The system will retrieve the unit balance by Effective Date for each of the Member’s INVSTMEMUNIT accounts per Portfolio as at the Effective Date of the Expense Billing run and find the unit prices with an Effective Date equal to the Effective Date of the run per Portfolio.
The market value per Portfolio will be calculated and the market values will be summed.
The system will read the scale and find the Percentages applicable to each amount range based on the total market value of all of the Portfolios for a Member for the Portfolio to which the Expense Rule is linked.
The system will calculate the portion of each Portfolio to which the applicable Percentage must be applied for each range of the scale as follows:
(To Amount on Scale – From Amount on Scale) / Total Market Value x Market Value of Portfolio
The fee per range of the scale will be calculated based on the Percentage for that range.
If the Formula Type is ANNUAL PERCENT, the fee will be calculated in the same way as when the Formula Type is PERCENTAGE.
The system will check the Frequency and if it is not ANNUAL it will divide the result by the 12 if the Frequency is MONTHLY, by 4 if the Frequency is QUARTERLY and by 2 if the Frequency is BI-ANNUAL.
Example
Formula Type: ANNUAL PERCENT
Frequency: MONTHLY
Fee scale:
Portfolio 1 & 2 Portfolio 3
0 - 500,000 0.30% 0.60%
500,000 - 1,000,000 0.25% 0.50%
1,000,000 - 3,000,000 0.20% 0.40%
>3,000,000 0.10% 0.20%
Member’s Investment Market Value: Portfolio 1 400,000
Portfolio 2 600,000
Portfolio 3 800,000
Total 1,800,000
Scales applicable to Member’s total market value of 1,800,000:
1 500,000
2 500,000 – 1,000,000
3 1,000,000 – 3,000,000
Scales and percentages applicable to each portion of each Portfolio:
Portfolio 1 500,000 x 400,000 / 1,800,000 = 111,111.11 0.30%
1,000,000 x 400,000 / 1,800,000 = 222.222.22 0.25%
3,000,000 x 400,000 / 1,800,000 = 666,666.67 0.20%
Portfolio 2 500,000 x 600,000 / 1,800,000 = 166,666.67 0.30%
1,000,000 x 600,000 / 1,800,000 = 333,333.33 0.25%
3,000,000 x 600,000 / 1,800,000 = 1,000,000.00 0.20%
Portfolio 3 500,000 x 800,000 / 1,800,000 = 222,222.22 0.60%
1,000,000 x 800,000 / 1,800,000 = 444,444.44 0.50%
3,000,000 x 800,000 / 1,800,000 = 1,333,333.33 0.40%
Fees per Portfolio:
Portfolio 1 (111,111.11) x 0.30 / 100 /12 = 27.78
(222,222.22 – 111,111.11) x 0.25 / 100 /12 = 23.15
(400,000.00 – 222,222.22) x 0.20 / 100 / 12 = 29.63
______ 80.56
Portfolio 2 (166,666.67) x 0.30 / 100 /12 = 41.67
(333,333.33 – 166,666.67) x 0.25 / 100 / 12 = 34.72
(600,000.00 – 333,333.33) x 0.20 / 100 / 12 = 55.56 ______ 131.95
Portfolio 3 (222,222.22) x 0.60 / 100 / 12 = 111.11
(444,444.44 – 222,222.22) x 0.50 / 100 / 12 = 92.59
(800,000.00 – 444,444.44) x 0.40 / 100 / 12 = 118.52 ______ 322.22
534.73
The system will calculate the VAT on the admin fee amount as follows:
If there is an Expense Type linked to the Scheme with a Global Group Type of EXPENSE TYPE and a Code of VAT, the system will read the VAT Number on the Administrator to which the Scheme is linked and if found, will read the Percentage on Global Percentage where the Percentage Type is VAT.
The VAT amount will be calculated as follows:
Admin Fee Amount X VAT Percentage / 100
VAT will not be calculated if no VAT number is found.
If MEMBER LEVEL is selected from the criteria when the Expense Billing batch job is scheduled via the Maintain Batch Schedule menu, the system will create the MEMEXPENSE Business Transactions (BT’s) for the sum of the fees calculated for each Portfolio with the relevant Expense Type, and will create the MEMEXPREAL BT’s for the fee calculate per Portfolio and with the relevant Portfolio and Contribution Income Type.
The system will create Member MEMEMEXPENSE BT’s for the sum of the VAT calculated per Member with the Expense Type VATA (VAT on Admin Fees) and create MEMEXPREAL BT’s for the individual VAT amounts calculated per Portfolio with the relevant Contribution Income Type.
The system will create Scheme BT’s with the following Accounting Activities with the Expense Type VATA for the sum of all of the VAT amounts calculated:
EXPENSE BILLING VATONADMFEES FUND DR VAT EXPENSE
CR VAT PAYABLE
The EXPENSE BILLING / DISINVESTMNT BT’s are created for the sum of the MEMEXPREAL BT’s per Portfolio and therefore this will be taken care of automatically.
For annuitants where the annuitants are being paid from a Retirement Fund or from a stand-alone Annuity Fund, then when the BJU3BH Expense Billing batch job runs:
If there is an Expense Type for the Scheme with an Expense Rule with a value for Formula Type of AMOUNT and a value for Formula Applied to of ANNUITY TYPE, the system will find the Memberships with a Membership Status of ANNUITANT or LIVING ANNUITY and find the latest Membership Payment Detail record with an Effective Date less than the run Effective Date and linked to a Membership Payment record with a Benefit Type Code equal to the Expense Type Code and the Membership Payment Detail records for the Beneficiaries linked to a Membership. The system will read the value on the Scale linked to the Expense Rule and calculate a fee equal to the value found, for each Membership Payment Detail record.
If there is an Expense Type for the Scheme with an Expense Rule with a value for Formula Type of PERCENTAGE and a value for Formula Applied To of ANNUITY TYPE, the system will find the latest Membership Payment Detail record with an Effective Date less than the run Effective Date and linked to a Membership Payment record with a Benefit Type Code equal to the Expense Type Code and for which the End Date is null or greater than the run Effective Date and a Membership Payment Detail records for the Beneficiaries linked to the Membership.
The system will read the Scale with a Type of Scale equal to FREQUENCY linked to the latest Expense Rule with an Effective date less than or equal to the run Effective Date.
If the Payment Frequency on the Membership Payment Detail record is MONTHLY, the system will read the Scale with a Type of Factor equal to MONTHLY and read the values for Percentage and Factor and calculate a fee for each Membership Payment Detail record as follows:
Regular Payment Amount x Percentage / 100 / Factor
If the Payment Frequency is ANNUAL, the system will calculate the fee as follows:
Regular Payment Amount x Percentage / 100 / 12 / Factor
If the Payment Frequency is BI-ANNUAL, the system will calculate the fee as follows:
Regular Payment Amount x Percentage / 100 / 6 / Factor
If the Payment Frequency is QUARTERLY, the system will calculate the fee as follows:
Regular Payment Amount x Percentage / 100 / 4 / Factor
The fee must be calculated irrespective of the Status of the Membership Payment Detail record i.e. include the records for which the Status is SUSPENDED.
Capital budget
When the JU3BH_BILL Expense Billing batch job is run the system will read the Expense Rules for the Expense Types for which the Global Group Type is CAPITAL BUDGET in addition to the Expense Types currently included in the Expense Billing batch job.
Where there is a Rate Table Type on the Expense Rule and there is a value for Maximum on the Scale linked to the Rate Table, the Member expense will be calculated based on the Expense Rule and the Scale. If the value calculated is greater than the value for Maximum on the Scale, the calculated value will be set to the Maximum value.
Similarly if there is a value for Minimum on the Scale and the calculated value is less than the value for Minimum on the Scale, the calculated value will be set to the Minimum value.
If the Calculation Method is CAPITAL BUDGET, the system will retrieve the BT’s in the Member’s CAP BUDGET account with a Due Date greater than the first day of the current year. If no BT is found, a value will be calculated for the Membership and proportioned for the period from the Membership CAR Start Date to the last day of the current year as follows:
Calculated value / 12 x number of months from CAR Start Date to last day of current year
If the Global Group Type for the Expense Type is CAPITAL BUDGET, Business Transactions (BT’s) will be created with the MEMBERBUDGET Accounting Activity. A Fund level BT will not be created.