BAR 101-2006-Customer Accounts

-Accounts Receivable -Cashiering

Presenter: Tim Henry, CPAGreen River Community College

Financial Analyst Accounting Instructor

Overview

For today’s training we have tried to add some of both…

Frame work-broad

Purpose of Customer Accounts How charges get onto a customer’s account Some of the tables associated with Customer

Accounts How accounting entries are created from

Customer Accounts Customer Accounts reports Updating Customer Accounts and Ledgers

Overview

….some of both

Important detail information that is………

Fee Calculation-Linda and Becky

Sub Ledgers-Mary and Teresa

Overview

Goals for this part of the training Basic understanding of Customer Accounts

Stimulate ideas and questions that you can take back and work on.

Provide a couple of examples Debt type table Charge status table Transaction code table

Overview of Customer Accounts.

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

What is the purpose?

To record charges, payments, and refunds

To provide an account balance and history

To generate invoices and reports

Customer OrdersCustomer OrdersCustomer OrdersCustomer Orders

Customer Accounts

Walk up orders

Mailed in orders

Phoned in orders

On-line orders

Customer Orders = ChargesCustomer Orders = ChargesCustomer Orders = ChargesCustomer Orders = Charges

Student Management System (SMS)

SM7001

FMS Customer Accounts

Cashiering Screen

BM1600

Customer Activity Maintenance Screen

BM1620

Fee Calc

Tables

purpose… to record charges…how do charges get on the CA

Tables

Customer PaymentsCustomer PaymentsCustomer PaymentsCustomer Payments

WEB Credit Card or Echeck (Cashiering

Batch)

FMS Customer Accounts

Cashiering Screen BM1600

Customer Activity Maintenance Screen

BM1620

purpose… to record payments…how do payments get on the CA

Customer AccountCustomer AccountCustomer AccountCustomer Account

Extended the customer credit?

FMS Customer Accounts

Refused to ship the order?

Order shipped but partial balance still owing

What if the charges (orders) are not paid?

3rd Party

Voucher /Letter

Accounts Receivable

Unofficial Enrollment

Dropped from Classes

Unofficial Enrollment

Partial Payment (?)

Accounts Receivable

Customer RefundsCustomer RefundsCustomer RefundsCustomer Refunds

FMS Customer Accounts

The customer returns the order...drops the class…decides not to take the test…overpaid and is due money back

SMS refund process 100%-80%-40%

Cashiering Screen-credit charge created

Cashiering Screen-prepayment

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Account Balance and History

The CA database provides for gathering all if this information (charges, payments, refunds) into one customer’s record or account.

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Account Balance and History

Balance or history since last purge date (archive/purge process…)

Balance or history by year/quarter

Balance or history by fee code

Primarily BM 1625-Customer Account Activity

BM 1620-Customer Activity Maintenance

BM1600-Cashiering Screen-balance

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Definition…tables

Fee Code-two character abbreviation for account code (more detail in fee calculation training)

Debt Type-two character designation that determines transaction code

Transaction Code-two character code that determines general ledger debit (s) and credit (s)

Charge Status-Two character designator of where the charge is in sequence (cycle)

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Definition…tables

Unofficial charge-unpaid charge that has not been recorded in the accounting records. The charge only exists in Customer Accounts Database.

It is like the order has been placed, but no payment or arrangement for payment has been made. Are we going to deliver the service or not?

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Accounting Entries

A student registers for class and the charges generated from the fee calculation are posted to the customer account….initial charge status is unofficial

Scenario 1. They pay the amount due

Scenario 2. They have a third party sponsor

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Accounting Entries

Charge Status Table--BM1025

System Defined

User Defined

Add new charge status or change charge status

UO (Unofficial) must always be first and PD (Paid) must always be last in sequence (cycle)

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Charge Status: Scenario 1

By processing the payment you are changing the status:

FROM: UO-Unofficial: No accounting entries have been created for this charge

To: PD-Paid: Accounting entries created

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

When the status of the charge changes, the Debt Type Table-BM1020 determines the transaction code by looking at the change from charge status UO to PD (and takes into consideration the

fiscal year) and identifies 023 as the Transaction Code.

Default debt type 01

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

The Transaction Code Table-GA1093 identifies 1110 (cash) as the GL to debit, and 3210 (cash revenue) as the GL to credit.

A transaction has been prepared and batched to record the revenue (D batch-Cashier)

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Accounting Entries

Transaction Code Table—GA1093

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Simple example,

Follow this flow from charge status to debt type to transaction code, to batch.

Recognize that there are other components of these tables that I have not touched on. It would take hours to go through the detail.

Think framework, add detail from user manual

Scenario 1 Student Registers

Fee Calculation

Charges Posted to

Customer Account

Payment Posted to

Customer Account

Scenario 1

Q: What is the Debt Type? A: 01

(None in BM1620, BM1626, BM1001)

Go to default 01 Debt Type Table

Scenario 1 Debt Type Table

Q: What was the Charge Status? A: Unofficial

Q: What is the status being changed to? A: Paid

Q: Is it in this fiscal year? A: Yes

Transaction Code is 023

Go to Transaction Code Table

Scenario 1 Transaction Code table

Q: What is the fund type? A: 5

Debit GL 1100 and Credit GL 3210

Q: Are the other transaction edits okay? A: Yes

Q:Should this be posted to files other than the GL ? A: Yes

Batch transactions in D?

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Accounting Entries

A student registers for class and the charges generated from the fee calculation are posted to the customer account….initial charge status is unofficial

Scenario 2. They have a third party sponsor

A little more on Charge Status

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Charge Status: Accounts Receivable

UO-Unofficial: No accounting entries have been created for this charge

UB-Unbilled: Accounting entries have been created but no invoice has been prepared

IN-Invoiced: Accounting entries have been created and invoice prepared

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Charge Status: Accounts Receivable

The charge has a status of UO on the student account.

The charge is transferred from the student account to the 3rd party account.

The debt type of the 3rd party determines the initial status when adding a new charge –debt type 12 = UB.

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Charge Status: Accounts Receivable

UB-Unbilled: Accounting entries have been created, debt type table = 051-1318/3205 FA or D? batch, but no invoice has been prepared

The charge status remains UB until ?...billing date, end of refund period, quarterly….

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Accounting Entries

Debt type table identifies 051 as the transaction code

Transaction code table identifies GL debit/credit….

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Charge Status: Accounts Receivable

When you are ready to bill this 3rd party, there is a job running every night (or you can turn it on/off) that will:

Identify and extract the charge

Change the status to IN (creating accounting entries)-assign invoice date and number.

Prepare an Invoice and Report

FG088DCustomer Accounts Billing This job group extracts customer accounts charges and produces various billing documents

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Charge Status: Accounts Receivable

The Billing Schedule Table-BM1023 (as determined by hierarchy) determines which charges get included in the job

This one is the quarterly schedule and runs everyday to pick up any A563 charges.

When we bill the bulk of the 3rd parties near the end of the quarter we will change that over to A564

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Charge Status: Accounts Receivable

When you are ready to bill this 3rd party, there really is only one right way IN-Invoiced: Accounting entries have been created and invoice prepared

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Charge Status: Accounts Receivable

When you are ready to bill this 3rd party, there really is only one right way IN-Invoiced: Accounting entries have been created and invoice prepared

Scenario 2 Student Registers

Fee Calculation

Charges Posted to

Customer Account

Charges Transferred

from Student Account

to 3rd Party Account

(Auto App-Manual)

Scenario 2

Q: What is the Debt Type? A: 12

(None indicated in BM1620)

BM1626 Debt Type is 12,

Go to 12 Debt Type Table

Scenario 2 Debt Type table

Q: What was the Charge Status? A: Un billed

Q: What is the status being changed to? A: Invoiced

Q: Is it in this fiscal year? A: Yes

Transaction Code is 052

Go to Transaction Code Table

Scenario 2 Transaction Code table

Q: What is the fund type? A: 5

Debit GL 1312 and Credit GL 1318

Q: Are the other transaction edits okay? A: Yes

Q:Should this be posted to files other than the GL ? A: Yes

Batch transactions in 24-..

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

UO UB IN

Transfer charge –FA or D batch

or Change charge status-23 batch

Change status to IN and invoice-24 batch

Now what…..?

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

UO UB IN I2 DQ

Accounts Receivable aging and follow up

I2-Second Invoice-no accounting entries

DQ-Delinquent-no accounting entries

CA-Collection-no accounting entries

WF-Write off-accounting entries

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Charge Status:

Payment Schedule BM1021

Automatic update of charge status-generates report BM1759 and creates batch 23, if accounting entries are needed!

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

At GRCC we said, we want

A report each day that identifies the accounts that remain unpaid 30 days after the invoice date…

And 45 days,….and 90 days

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

This automatic update will identify and extract charges that fit into the payment schedule parameters, and change the status as indicated.

The debt type table determines no accounting transactions (blank)

Report is used to identify accounts for follow up.

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Charge Status

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

How do we select charges to be include in the automatic update of charge status job?

Payment Schedule

BM1620

BM1001

BM1626

….drives to BM1021-Payment Schedule and follows the sequence from there

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Accounting Entries

Debt Type Table--BM1020

From Status To Status Current Deferred

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Accounting Entries

Debt Type Hierarchy

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

At GRCC we said, we want

A report each day that identifies the accounts that remain unpaid 30 days after the invoice date…

And 45 days,….and 90 days

Customer AccountsCustomer AccountsCustomer AccountsCustomer Accounts

That Payment Schedule will bring a charge to your attention three times if it remains unpaid.

Daily report BM1759

Together with the aging report-BM1710

= effective AR monitoring and follow up

Customer Accounts ReportsCustomer Accounts ReportsCustomer Accounts ReportsCustomer Accounts Reports

Customer Accounts Aging Report-BM1710

What do you want to see?

BM1022-Aging Format Table

Reference Dates

Age by invoice date not charge date (system assigns)

Charge Status

Customer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts DatabaseCustomer Accounts Database

Charge Status:

UO-Unofficial: No accounting entries have been created for this charge

UB-Unbilled: Accounting entries have been created but no invoice has been prepared

IN-Invoiced: Accounting entries have been created and invoice prepared

Customer Accounts ReportsCustomer Accounts ReportsCustomer Accounts ReportsCustomer Accounts Reports

Customer Accounts Aging Report-BM1710

What do you want to see?

What

How many different reports?

Charge status

Customer Accounts ReportsCustomer Accounts ReportsCustomer Accounts ReportsCustomer Accounts Reports

Customer Accounts Aging Report Job-BM1710J

What do you want to see?

Sort

Format

Select by Debt Type…. One report to include only student accounts—make all student charges debt type SA on charge in BM1620

Customer Accounts ReportsCustomer Accounts ReportsCustomer Accounts ReportsCustomer Accounts Reports

Customer Accounts Aging Report Job-BM1710J

What do you want to see?

Sort

Format

Select by Debt Type….

The story so far.pdf

CA SL GLCA SL GLCA SL GLCA SL GL

Updating records

Customer Accounts is updated immediately-real time-right now—If the change in charge status results in accounting entries, then the entries will be batched and manually or automatically released.

Cashiering = D? Batch ID

Customer Accounts Maintenance = ?? (individual’s) Batch ID

Billing Schedule = 24 Batch ID

Payment Schedule = 23 Batch ID

Third Party Sponsorship = FA Batch ID (Auto App)

CA SL GLCA SL GLCA SL GLCA SL GL

Updating records

Accounts Receivable Sub Ledger is updated once batch is released.

General Ledger is updated during the nightly posting routine.

CA CA = = SL SL == GL GLCA CA = = SL SL == GL GL

The three should balance…

..everything in the AR Sub Ledger is supported by the Customer Accounts (by Customer Account number)

..everything in the AR General Ledgers is supported by the AR Sub Ledger (by APR/PRG/ORG.)

CA CA = = SL SL == GL GLCA CA = = SL SL == GL GL

It takes a lot of work….

Summary level balance, does SL = GL?

Reconcile by GL

total 1312, 1318…

Reconcile by Apr-Prg-Org and GL

CA CA = = SL SL == GL GLCA CA = = SL SL == GL GL

Timing differences-

Consider what is in CA that would not be on AR SL or GL…Unofficial charges and those that have been written off.

Rolling the AR SL up by Apr-Prg-Org to the GL

Rolling the AR SL down by customer ID to the CA

Sub LedgerSub LedgerSub LedgerSub Ledger

Later, Mary and Teresa are going to talk about Sub Ledgers. One of the keys to reconciling is to ensure that all charges that should purge are doing so. There are many reasons why a charge won’t purge, some very simple, others not so much. Keeping the SL “clean” greatly reduces the problems and time spent reconciling.

BAR 101-2006-Customer Accounts-Accounts Receivable -Cashiering

Purpose…..Charges, payment ,refundsBalance and historyInvoicing and reports

Tables…………Charge StatusDebt TypePayment ScheduleBilling Schedule

BAR 101-2006

Reports…..BM1759-Change of Charge StatusBM1710-Customer Accounts Aging

Accounting entries.Combination of debt type and charge status

Batched Record updating