DEDUCTIONS
MANAGEMENT
AUTOFILE
FEBRUARY 19, 2020
Client Pharmaceuticals, Inc.
1
Table of Contents
Deductions Management Auto-file (DMA)
3
Introduction
3
Format
4
Worksheets
5
Index
5
On Account
5
Customer sheets
6
Field Legends
6
Field Headers
6
Analysis (Row 3)
9
How to input data in customer worksheets?
10
AR Aging Bucket - Detail
11
Dropdowns
11
Consolidated
12
Other important areas
14
Adding new customers to the workbook
14
Backups
14
Access Controls
14
2
Deductions Management Auto-file
(DMA)
Introduction
The AR team at the Client manages deductions by using an Excel workbook. The
workbook is carried forward from G&W Laboratories (now Client) and is maintained in
a non-standardized form. The legacy file was handy to track deductions and make
modifications, but it was difficult to reconcile these open deductions with AR aging.
There was no standard process of updating the file, and the data was not reporting
friendly and was exposed to the risk of corruption. Client required a standardized version
of their existing Deductions Management file until a system-based Deduction
Management process is developed and functional.
The Deduction Management Auto-file is a standardized, semi-automated, Macro-
enabled Excel workbook which aims to streamline the process of deductions
management and make deductions data accessible for reconciling AR balances. The
DMA improvises and simplifies the existing process and provides transparency in overall
AR reconciliation and management.
3
Format
As of 2/17/2020, DMA consists of 54 sheets which include -
o 1 Index Sheet having links to navigate to all other sheets.
o 1 On Account sheet and
o 49 Customer sheets.
o 1 AR Aging Bucket - Detail sheet (which is an input sheet)
o 1 Dropdowns sheet
o 1 Consolidated sheet (on which Macro does consolidation)
All customer sheets and consolidated sheet follow the same format with 26
columns (field headers). An example is demonstrated in illustration 1 and 2.
Each of these sheets are elaborated further in the upcoming sections.
Client name -
Amerisource
BACK TO INDEX
Field header legends -
Data entry
Dropdown
Formula based
Field
Formula Mandatory
Formula
Formula
Mandatory
Formulaif Column G = Yes,
Mandatory
Optional
Mandatory
Mandatory
Mandatory
Mandatory
requirement
Mandatory
Analysis >
-
-
-
-
-
-
-
-
-
-
-
-
Sr. No.
Customer
Invoice No. Original invoice
Status of
Type of Deduction
RA#
RA#
Customer's ref no.
Customer
Date of
Outstanding deduction Mode of
Receipt ref. no.
name
amount
invoice
applicable?
Comments
deduction amount ($)
cash receipt
402
Amerisource
675193
$
19,650.96
Open
Expired / Shortdated Return Yes
NA
WAG67444
28-Sep-18
$
17,957.38
ACH
28-Sep-18
403
Amerisource
675193
$
19,650.96
Open
Chargeback
No
61186242137-001
28-Sep-18
$
173.99
ACH
28-Sep-18
404
Amerisource
675193
$
19,650.96
Open
Chargeback
No
61186264173-001
28-Sep-18
$
173.99
ACH
28-Sep-18
406
Amerisource
675497
$
28,072.80
Open
Expired / Shortdated Return
Yes
70001261
GA091845445
Expired / Shortdate
05-Oct-18
$
31.61
ACH
05-Oct-18
407
Amerisource
675497
$
28,072.80
Open
Expired / Shortdated Return
Yes
70001261
KSP091829046
Expired / Shortdate
05-Oct-18
$
2,611.32
ACH
05-Oct-18
408
Amerisource
675497
$
28,072.80
Open
Expired / Shortdated Return
Yes
70001261
WGC100140870173
Expired / Shortdate
05-Oct-18
$
141.20
ACH
05-Oct-18
409
Amerisource
675497
$
28,072.80
Open
Recall
Yes
NA
004105186
RECALL
05-Oct-18
$
3,830.40
ACH
05-Oct-18
410
Amerisource
675497
$
28,072.80
Open
Unidentified
No
NA
0014007721
WITHDRAWAL FRO
05-Oct-18
$
364.88
ACH
05-Oct-18
411
Amerisource
675497
$
28,072.80
Open
Expired / Shortdated Return
Yes
NA
016100195
05-Oct-18
$
14,523.71
ACH
05-Oct-18
412
Amerisource
675497
$
28,072.80
Open
Expired / Shortdated Return
Yes
NA
0538085532
05-Oct-18
$
68.56
ACH
05-Oct-18
413
Amerisource
675497
$
28,072.80
Open
Chargeback
No
61186381696
05-Oct-18
$
0.01
ACH
05-Oct-18
416
Amerisource
675470
$
163,416.84
Open
Shortage
Yes
75000075
675470Q
Shortage
05-Oct-18
$
10,003.58
ACH
05-Oct-18
417
Amerisource
675480
$
299,665.20
Open
Shortage
Yes
75000079
675480Q
Shortage
05-Oct-18
$
9,127.01
ACH
05-Oct-18
419
Amerisource
676161
$
150,446.64
Open
Shortage
Yes
75000073
676161Q
25-Oct-18
$
26,786.44
ACH
25-Oct-18
420
Amerisource
676165
$
325,286.28
Open
Shortage
Yes
70001305
676165Q
25-Oct-18
$
12,061.89
ACH
25-Oct-18
423
Amerisource
676370
$
26,494.08
Open
Shortage
Yes
NA
676370Q
25-Oct-18
$
3,202.47
ACH
25-Oct-18
424
Amerisource
676582
$
375,878.04
Open
Expired / Shortdated Return
Yes
NA
AIT1018094101
Expired / Shortdate
25-Oct-18
$
523.93
ACH
25-Oct-18
425
Amerisource
676582
$
375,878.04
Open
Expired / Shortdated Return Yes
NA
IGL101807961
Expired / Shortdate
25-Oct-18
$
641.12
ACH
25-Oct-18
426
Amerisource
676582
$
375,878.04
Open
Expired / Shortdated Return Yes
NA
SAM100190740063
Expired / Shortdate
25-Oct-18
$
532.25
ACH
25-Oct-18
Illustration
1
if Column R = Issued /
Linked Mandatory
Formula
Linked Mandatory
Linked Mandatory
Linked Mandatory
Optional
Optional
Optional
Mandatory
Formula
Formula
Partially issued, Mandatory
to Column R
based
to Column R
to Column V
to Column V
84
16
-
-
-
-
-
-
Customer PO Status in
AR Remarks
CM status
CM#
CM Amount ($)
Disputed
Deduction paid
Deduction payback Amount paid back
% of open
Amount
Oracle
(dropdown)
amount
back?
date
deductions
unpaid
Disputed
17,957.38
No
91%
17,957.38
027153212
SUE
Disputed
173.99
No
1%
173.99
027153212
SUE
Disputed
173.99
No
1%
173.99
021712673
Issued
678055
$
31.61
-
0%
-
021712673
Issued
678055
$
2,611.32
-
0%
-
021712673
Issued
678055
$
141.20
-
0%
-
021712673
Disputed
3,830.40
No
14%
3,830.40
021712673
Disputed
364.88
No
1%
364.88
021712673
Disputed
14,523.71
No
52%
14,523.71
021712673
Disputed
68.56
No
0%
68.56
021712673
SUE
Disputed
0.01
No
0%
0.01
BOOKED
Partially issued
679502
$
210.72
9,792.86
No
6%
9,792.86
012127663
Sent to Barbara
Pending
-
No
3%
9,127.01
024634012
CM to be applied by Steve
Issued
688914
$
27,263.16
-
0%
-
018162886
Pending
-
No
4%
12,061.89
016146884
Pending
-
No
12%
3,202.47
028154575
Disputed
523.93
No
0%
523.93
Disputed
641.12
No
0%
641.12
Disputed
532.25
No
0%
532.25
Disputed
174.58
No
0%
174.58
Illustration 2
4
Worksheets
Index
The index sheet a navigational tool that helps to navigate through all the other sheets.
All other sheets have a link to navigate back to Index.
On Account
This sheet helps in recording all payments that have been recorded as on account
payment from the customers that cannot be applied to any invoice or deduction. The
format is simple and enables AR team to capture necessary data for reusing as necessary.
The format includes following fields -
Customer name
Date (of payment)
Mode of payment
Payment reference no. (ACH date / Cheque no.)
Amount
Customer’s reference no.
AR comments
The format is represented by illustration 3 below -
Customer nam
Date Mode of payme Payment referenc Amount
Customer reference n
AR comments
Amerisource
01-Mar-18
$
5,147.94
po 019761940
cm 664580
Amerisource
01-Mar-18
$
4,633.42
po 019761940
cm 664992
Amerisource
01-Mar-18
$
1,232.13
po 021683800
cm 665833
Amerisource
16-Feb-18
$
1,286.35
CM# 665537
Amerisource
NA
$
135.57
Sue
Amerisource
07-Mar-18
$
1,891.08
po 063229159
cm 665622
Amerisource
07-Mar-18
$
4,312.20
po 030115748
cm 666290
Amerisource
02-Jul-18
ACH
070218
$ 47,464.92
Amerisource
10-Aug-18
ACH
081018
$ 41,187.36
Amerisource
17-Aug-18
ACH
081718
$
7,134.67
Amerisource
20-Aug-18
ACH
082018
$
3,324.78
Amerisource
07-Sep-18
ACH
090718
$
7,470.19
Amerisource
07-Sep-18
ACH
090718
$
23.60
Amerisource
14-Sep-18
ACH
091418
$
9,127.01
Cardinal
19-Dec-19
$
9,821.05
EXPCH000326442
pd back 12/19/19 but inv is closed - ON ACCT
Cardinal
19-Dec-19
$
7,378.67
EXPCH000427291
Pd Back 12/19/19 but inv is closed - ON ACCT
Cardinal
19-Dec-19
$
281.04
EXPCB000032339
Pd Back 12/19/19 but inv is closed - ON ACCT
Cardinal
17-Oct-18
$
(109.40)
Cardinal
19-Dec-19
$ 10,352.46
CVS100188470136
Pd Back 12/19/19 but inv is closed - ON ACCT
Cardinal
07-Feb-19
$
99.80
DC2730264701
Pd Back 2/7/19 but inv is closed - ON ACCT
Cardinal
07-Feb-19
$
299.40
DC2730301601
Pd Back 2/7/19 but inv is closed - ON ACCT
Illustration 3
5
Customer sheets
The most important part of the workbook, the individual customer sheets are the data
source for consolidation.
Each sheet has the same standard format. Initial 2 rows contain the field legends. Row
3 includes ‘analysis’ - to be elaborated later - and Row 4 includes the field headers.
Field Legends
Field header color
Nature
Invoice No.
Manual data entry
Original invoice amount
Automated through formula
Status of invoice
Dropdown selection
Field Headers
Col#
Field Header
Nature
Notes
name
A
Sr. No.
Automated through
formula
B
Customer name
Automated through
formula
C
Invoice No.
Manual data entry;
Mandatory
D
Original invoice
Automated through
Derived from AR aging sheet. AR aging
amount
formula
from BI needs to be pasted in AR aging
sheet at such intervals as decided by
Management.
E
Status of
Automated through
“Open” if invoice exists in R ging,
invoice
formula
otherwise “Closed”
6
Col#
Field Header
Nature
Notes
name
F
Type of
Dropdown; Mandatory
Options under this section are -
Deduction
1. Expired / shortdated return
2. Damage
3. Recall
4. Shortage
5. Rebate
6. Chargeback
7. Other
8. Unidentified
G
RA# applicable?
Automated through
“Yes” in case of options 1 to 4 above,
formula
“No” in other cases.
H
RA#
Manual data entry;
If column G
= “Yes”, data entry
is
Linked Mandatory
mandatory.
I
Customer's ref
Manual data entry;
no.
Mandatory
J
Customer
Manual data entry;
Comments
Optional
K
Date of
Manual data entry;
Payment date
deduction
Mandatory
L
Outstanding
Manual data entry;
deduction
Mandatory
amount ($)
M
Mode of cash
Dropdown; Mandatory
ACH / Check
receipt
N
Receipt ref. no.
Manual data entry;
In case of ACH - date of deduction
Mandatory
In case of check - Check No.
O
Customer PO
Manual data entry;
Recommended but not mandatory
Optional
7
Col#
Field Header
Nature
Notes
name
P
Status in Oracle
Manual data entry;
Optional
Q
AR Remarks
Manual data entry;
Optional
R
CM status
Dropdown;
Options are -
(dropdown)
Mandatory
1. Issued (when CM issued for
claimed deduction fully with no
dispute / discrepancy)
2. Partially Issued (when CM
issued but not for full amount).
3. Disputed (when
credit/deduction is denied)
4. Pending (when CM is internally
pending to be processed)
S
CM#
Manual data entry;
In case of option 1 & 2 above,
Linked Mandatory
mandatory
T
CM Amount ($)
Manual data entry;
In case of option 1 & 2 above,
Linked Mandatory
mandatory
U
Disputed
Automated through
Results in case CM status is -
amount
formula
1. Issued = $0
2. Partially Issued = Original
outstanding amount (Col L) -
CM Amount (Col T)
3. Disputed = Original outstanding
amount (Col L)
4. Pending = $0
8
Col#
Field Header
Nature
Notes
name
V
Deduction paid
Dropdown; Linked
If CM Status is either “Disputed” or
back?
Mandatory
“Partially issued”, Mandatorily select
“Yes” / “No”
W
Deduction
Manual data entry;
If Yes selected above, mandatory
payback date
Linked Mandatory
X
Amount paid
Manual data entry;
If Yes selected above, mandatory
back
Linked Mandatory
Y
% of open
Automated through
deductions
formula
Z
Amount unpaid
Automated through
Results in case CM status is -
formula
1. Issued = $0
2. Partially Issued = Disputed
Amount (Col U) - Amount paid
back (Col X)
3. Disputed = Disputed Amount (Col
U) - Amount paid back (Col X)
4. Pending = Original outstanding
amount (Col L) - Amount paid
back (Col X)
Analysis (Row 3)
On Row 3 of all customer sheets, an analysis is included to highlight instances of any
mandatory cell which should have included data, is blank. The cells are conditionally
formatted. If any of the analysis cells is in red, the user should use filters to locate blanks
cells and populate the blank cells with appropriate data.
9
How to input data in customer worksheets?
Below are the illustrative steps for data input -
1.
Start with filling in the debit memo no. / customer reference no. against which
deduction is taken, in Column I - when a cheque is being applied.
2.
Input the corresponding details in Columns J to Q at the same time and classify
the deduction in Column F.
3.
Input the invoice number which is left open for these deductions in Column C.
4.
On a weekly basis, check whether RA no. is generated and update Column H when
the RA# is generated. Until the same is generated, user can update cell with “N
”.
5.
On a weekly basis, check whether CM is generated and update Column R, S and T
accordingly.
a. If CM is pending to be generated, mark CM status as “Pending”.
b. If credit
/ deduction is denied to the customer, mark CM status as
“Disputed”.
In above cases, columns S and T shall be blank, as no CM is generated.
c. If CM is issued for the entire deduction amount, mark CM status as “Issued”.
d. If credit / deduction is partially accepted and a CM is generated for the
partial amount, mark CM status as “Partially issued”.
In above cases, columns S and T shall include CM data.
6.
Disputed amount (Col U) is updated automatically. Logic as mentioned above.
7.
When a deduction is paid back by the customer, update Columns V, W and X
accordingly. Please note - when an entire deduction is paid back, the CM status
should be marked as “Disputed”.
10
AR Aging Bucket - Detail
At fortnightly intervals, download AG aging from BI. Copy Aging report as is and paste in
“ R ging Bucket - Detail” sheet. Columns D and E are dependent on this input. Col E
determines whether an invoice is open or close, which is an input for Analysis in Row 3.
Dropdowns
“Dropdowns” is a master sheet. It is the source of options for “Type of deductions” and
“CM Status”. Column C consists of “Sheet Name” which includes a list of all the sheets
that need to be consolidated. At present, 49 sheet names are included in Column C. It
should be noted, Column C is elementary for the Macro to work. Sheet names need to
be updated appropriately in Col C for the Macro to consolidate
the
sheets.
An
illustration of Dropdowns sheet is as below -
Type of deduction
CM status
Sheet Name
(dropdown)
Damage
Issued
Amerisource
Shortage
Pending
Anda
Expired / Shortdated Return
Disputed
Associated Pharm
Recall
Partially issued
Auburn
Chargeback
Bloodworth
Rebate
Blupax
Other
Burlington
Unidentified
Capital Wholesale
Cardinal
CVS
Dakota Drug
Delhaize
Discount Drug Market
DMS Pharma
Drogueria
Drugs Unlimited
Express Scripts
Genetco
HD Smith LLC
Illustration 4
11
Consolidated
Consolidated sheet is the final output of the file. Consolidation exercise needs to be done
at a fixed interval (e.g. - monthly) as decided by the management. Following steps
should be followed for consolidation -
1. Delete all cell values from cells A5 till the bottom.
2. Go to “View” tab in the Excel ribbon on the tab, select Macros > View Macros >
Select Macro1 > Run. The Macro shall commence. Please note - the Macro may
take up to 3 hours depending on the volume of data. It is preferable run the
Macro accordingly, towards the end of the day. This point is illustrated in
snapshots below -
Illustration 5
Illustration 6
12
Illustration 7
Illustration 8
3. Once Macro is completed, a dialog box appears with message “Done!”.
13
Other important areas
Adding new customers to the workbook
1. Add a blank sheet
2. Rename sheet with client name (as per Oracle)
3. Copy cells A1 to Z4 from any customer sheet to the blank sheet.
4. Edit cell B1 and add Client’s name (should be same as sheet name).
5. Copy formulas from Columns A, B, D, E, G, U, Y, Z from any customer sheet to the
new customer’s sheet.
6. Copy dropdowns from Columns F, M, R, V from any customer sheet to the new
customer’s sheet.
7. Add value in cell B1 to Column C in Dropdowns sheet - very important for
consolidation.
Backup and Storage
The DMA should be stored on a shared drive which is accessible to all AR team members.
The AR team members should ensure the integrity of the DMA.
As a matter of practice, weekly backups should be copied and stored on a cloud-based
folder with restricted access to limited people.
Access Controls
Following access controls are enabled in DMA -
All cells with formula are password protected in any sheet.
Columns cannot be added or deleted in any sheet.
Rows can be inserted but not deleted in any sheet.
“Dropdowns” sheet is fully password protected.
Sheets cannot be added, deleted or renamed without a password.
The password shall be available with Management and any person as assigned
by the Management.
14