CHARGEBACKS & REBATES PROCESS REVIEW
October 7th, 2019
TABLE OF CONTENTS
Executive Summary
3
Masters Updating Process
4-7
As-Is & To-Be Process Maps
5
Gaps & Suggestions
6
Gap Analysis
7
Chargebacks
8-15
As-Is & To-Be Process Maps
9
Gaps & Suggestions
10-13
Analysis of Error Instances
14
Gap Analysis
15
Rebates
16-20
As-Is & To-Be Process Maps
17
Processes Observed
18
Gaps & Suggestions
19-20
Gap Analysis
21
To-be process analysis
22
Next Steps
23-24
2
EXECUTIVE SUMMARY
SCOPE
DESIRED OUTPUT
OUR APPROACH
The project is intended to identify key
1.
Conducting workshops across various
risk areas and process control lapses in
The project would examine the business
divisions to get an insight on the ‘As-Is’
the chargeback, rebate process and
processes in Client under chargebacks and
processes. The following persons were
evaluation of system masters valid for
rebate functions, the existing policies and
interviewed:
both. The gaps would be oriented
procedures that govern the functions, all
Chargebacks Associate
around areas of possible
revenue
roles and responsibilities of staff who
Senior Chargebacks Analyst
leakage/excessive costs which would in-
engage in the process, and automated and
2.
Designing the ‘As-Is’ process flows and
turn aid Client Management to make
manual tools that facilitate the cycle to:
getting validation from the respective
process decisions with flexibility agility
divisions.
while scaling operations quickly.
Identify gaps and opportunities for
3.
Preparation of the preliminary gaps and
streamlining.
high level suggested actions based on
The objective of recommendations to
the analysis of the current processes.
the gaps derived from the analysis will
Deliver appropriate recommendations
4.
Submission of the final report - ‘As-Is’
focus mitigating risks and increasing
to meet the project goals.
process flows, gap analysis, ‘To-Be’
process controls through automation
process flows & recommendations.
(system-driven or external) actions. This
will yield transparency around amounts
deducted
(CB & Rebates) and an
efficient mechanism to full proof them.
3
Masters Updating
Process
related to Chargeback Contracts, Rebates and Prices
4
MASTERS - TEAM INTERVIEWS & PROCESS MAPS
As-Is Process
To-be Process
People
Susan Hreno
Senior Chargeback Analyst
(SCBA)
Janet Gutierrez-Helders
Manager, Sales Operations,
Sales & Marketing (CSM)
Amitha Gentela
Associate Director - Finance
(enclosed separately as annexure)
(enclosed separately as annexure)
(AD-F)
5
MASTERS - GAPS & SUGGESTED ACTIONS
#
Gap
Type
Suggested Actions
1
No Document Management System (DMS) within the system which yields to
Generic
a) Explore the option of creating DMS within Oracle, which would feed valid
saving the same document on multiple media (Email, E-Drive, Paper) by multiple
information into the masters via OCR mechanism.
people (DNA, CST, SCBA). A roster should be maintained for managing the
b) Alternatively, if above is not possible, explore an external DMS tool which
folders.
could interface with Oracle and provide the same functionality as stated in (a).
2
The process of manually updating the contract price list by looking at
Generic
If suggested action under gap #1 is undertaken, this gap ceases to exist.
documents is prone to errors.
3
There is no approval mechanism once a price list is updated. The maker-
Generic
Create an approval mechanism in Oracle to validate every material update in the
checker principle is absent which is an audit control gap and causes a high-risk
masters.
possibility of notifying a wholesaler an erroneous price for updating in his
system.
4
a) Excessive paperwork observed due to repeated and unnecessary printing
Generic
If suggested action under gap #1 is undertaken, this gap ceases to exist.
and filing of documents.
b) Same documents being saved repeatedly on K-Drive.
5
No reference number of the addendum letter effecting change to a contract
Generic
a) Create a reference number for each addendum document to a contract
leads to manual search for documents in archives. There is a risk of losing a
effecting a material change in the original contract.
document.
b) The referencing of addendum documents should be added in Oracle to update
any changes in the contract.
c) The wholesaler should be requested to provide the reference number each
time they give the specific reference to an addendum.
6
Deletion of line item is possible and does not go under any approval
Generic
a) Access to delete any line item from a master should be with IT team only.
mechanism.
b) Log of any deletion of line item or header should be maintained by Oracle.
c) If
(a) is not possible, alternatively, create an approval mechanism for any
deletion AND maintain log as per (b).
7
It is observed when there is an update in the offers, SCBA needs to manually
Specific
Add a search field in the masters where the user can enter a product code and
look through an entire list and find products to make any changes which makes
find the products.
it time consuming and provides possibility of error.
SBCA - Senior Chargeback Analyst | CST - Customer Service Team | DNA - Director, National Accounts
6
MASTERS - GAP ANALYSIS
Gap
Brief Description of the gaps
Impact
Risk
Effort to
3
#
remediate
1
1
Absence of DMS with dynamic
High
Medium
Medium
2
Storage Options.
(such as email,
paper, e-drive etc.)
2
Manual updating of the contract
Medium
Medium
Low
2
price list is prone to errors
3
No Secondary check / approval while
High
High
Low
5
updating List price.
4
Duplication of efforts / redundancies
Low
Low
Low
in saving and printing documents.
4
5
No unique identifier allocated to
Medium
Medium
High
addendums for future tracking
7
purposes.
6
Changes / deletion of masters has no
High
High
Low
Low
Medium
High
approval mechanism
Risk
7
Masters not easily searchable.
Low
Low
Low
Changes
/ updates to it is done
*Size of the bubble represents the
< Keys >
Impact level of gap
Risk due to gap
manually, hence time consuming
Effort to Remediate
High
Impact on IC#
Financial risk
#IC - Internal Controls
Medium
Financial Impact
Reputational Risk
DMS - Document Management System
Low
Operational Impact
Operational risk
7
Chargebacks
8
CHARGEBACKS - TEAM INTERVIEWS & PROCESS MAPS
As-Is Process
To-be Process
People
Mackenzie Kiernan
Chargeback Associate (CBA)
(Process Champion)
Susan Hreno
Senior Chargeback Analyst
Amitha Gentela
Associate Director - Finance
(enclosed separately as annexure)
(enclosed separately as annexure)
9
CHARGEBACKS - GAPS & SUGGESTED ACTIONS
#
Gap
Type
Validation
Suggested Actions
Probable impact of Suggested Actions
8
Significant
manual intervention is
Strategic
Of 66 total process steps
a)
Close to 50% of the manual processes are
Increased efficiency and more productive
observed in the overall chargeback process
in a single cycle 46 are
either automatable through Oracle
/
use of time making the process scalable.
thereby making it error prone.
observed to be having
external intervention or are avoidable.
Possibility of erroneous processing is
manual intervention
b)
Manual intervention to be subjected only
reduced.
in cases where system work-flow and
controls need to be overridden.
9
The systemwide correlation of Client's
Operational
Daily average of DMs
a)
Processing of DM should be verified at the
Eliminates the chances of missing out on
invoice to DM to CM to payment voucher is
submitted in April &
level of individual line items.
processing of DM and elimination of
missing, as the wholesalers do not provide
May 2019 observed to
b)
Each product SKU should be identified
manual verification by count.
necessary data, yielding to -
be approximately 200
with a Unique Reference Number (URN)
Ensures that stronger Internal Controls for
a) Processing of DMs into CMs verified by
which groups into a Unique Lot Number
tracking validity, accrual and realization of
matching at the total count levels
(ULN) to identify inventory roll-forward
a chargeback and a reverse chargeback are
which is inadequate.
and request wholesaler to provide data
in place.
b) Logical source for any CB amount
with reference to ULN and eventually
Better visibility and transparency yielding
should be Client's invoice to
URN.
to reduction in manual process steps.
wholesaler, which is missing across the
c)
Eventually in our system we should be
Transparency in understanding collection
process workflow.
able to track Client's Invoice (which has
of reverse chargebacks and easier
c) Direct impact on validation of reverse
URN and ULN) to DM to CM to payment
reconciliation of AR.
chargebacks
receipt.
10
No distinct mechanism for verifying
Operational
RCB amounting to
reverse chargebacks vis-à-vis actual
$1.63M in April & May
returns.
2019
DM - Debit Memo | CM - Credit Memo | CB - Chargeback | RCB - Reverse Chargeback
10
CHARGEBACKS - GAPS & SUGGESTED ACTIONS
#
Gap
Type
Validation
Suggested Actions
Probable impact of Suggested Actions
11
Product-wise pending quantity for sales by
Operational
Instances of
“Error
Request for intimation from wholesaler
Provides Client data to forecast sales,
wholesaler is not computed or analyzed by
getting product price”
via EDI (through 844) with reference to
accrued chargebacks and the extent of
Client. This leads to -
for April & May
2019
ULN and URN.
discontinued products that continue to
a) For
contractually
discontinued
total up to 35-line items
Once the inventory roll-forward of
exist in the wholesaler's inventory.
products, add backs into chargeback
which averages to
discontinued product is known held with
Automation of process of verifying
reports done by CBA
/ SCBA is
approx.. 1 instance per
the wholesaler based on ULN/URN,
whether a product is discontinued and
observed. This is justified assuming
working
day.
Net
chargeback against this quantity needs to
checking masters.
pending shelf stock with the
quantity and amount of
be tracked in our system.
wholesaler.
chargebacks are 46
and
System should identify discontinued
b) Limited possibilities for Client of
$1,205
respectively.
products from masters and check if price
knowing accrued chargebacks and
submitted by wholesaler matches old
rebates.
contract price as per masters, in case of
both,
chargebacks
and
reverse
chargebacks.
12
Overall process observed to have printed
Operational
NA
Data available daily can be saved, for
Cost and time efficiency in terms of
copies of data / reports, which cause -
reference on K-Drive.
avoidance of paper wastage and filing.
a) Manual filing and maintenance of
The process of manual reconciliation of
Automation (through robotics or excel) to
documentation.
one file to another can be automated
significantly improve time efficiency for
b) Manual reconciliation / verification of
using RPA.
repetitive tasks thereby creating a
information undertaken on line item
Alternatively,
excel
formula-based
preparedness for scalability.
basis using "old-school" methods.
reconciliation
should be followed
c) Reduced efficiency and increased time
mandatorily in the interim.
consumption.
d) Excess paperwork leading to ignorance
of green-initiative.
CBA - Chargeback Associate | SCBA - Senior Chargeback Associate
11
CHARGEBACKS - GAPS & SUGGESTED ACTIONS
#
Gap
Type
Validation
Suggested Actions
Probable impact of Suggested Actions
13
We have observed that both members to
Operational
Total 7,060 lines out of
CBA should undergo detailed training to
Increased time for SCBA to undertake other
Rebate & Chargeback process are fairly
757,152
(1%) lines in
cover all possible scenarios of the process.
productive tasks.
new, and a thorough handover is not
April and May were not
done. For example, Process roadblocks
processed on the same
faced by CBA are channeled to SCBA where
day amounting to CB of
duplication of process is observed in
$431,773
addition to a drag into SCBA's time.
14
Escalation to AR and IT for CMs not
Operational
NA
A clear log of all issues escalated to IT needs
Helps in improving the systems and process
reflecting on TSR despite processing DMs.
to be maintained by the system and tracked
from a time-to-time basis.
Resolution of for such cases in not known
on a monthly basis to avoid
and is a concern.
15
Same data for DMs saved 4 times - despite
Transactional
IT team’s mailbox
CBA to be given access to EDI system for
No intervention and time consumption of
having 3 soft copies, data is manually
CBA’s mailbox
direct daily data on DMs
IT team
printed and filed.
K-Drive
Data available daily can be saved, for
Savings in terms of cloud space.
Hard copies
reference on K-Drive.
Cost and time efficiency in terms of
avoidance of paper wastage and filing.
16
Verification of processing of DMs at total
Transactional
NA
The reconciliation should be done within
Eliminates approximation-based checks
count level is not foolproof
the system itself.
and enables easy identification of problem
Alternatively, this is possible to be done at
areas.
individual DM level with excel formula-
Generates audit trail to help in
based automation.
implementation of further course-
A log of total DM lines received, lines
correction to system and manual
processed by system, lines remaining
processes.
unprocessed, and time lag to process
should be maintained for validation and
course correction in the process.
CBA - Chargeback Associate | SCBA - Senior Chargeback Associate
12
CHARGEBACKS - GAPS & SUGGESTED ACTIONS
#
Gap
Type
Validation
Suggested Actions
Probable impact of Suggested Actions
17
Incorrect contract no. submitted by wholesaler
Transactional
Instances of “Agreement is null” for April &
General tendency has been
Significant savings in time to
rectified internally by CBA / SCBA in disputed view of
May 2019 total up to 173-line items which
observed where errors by
complete the process
Chargebacks in system, causing increased time and
averages to approx.
4
instances per
wholesalers are course corrected
Onus of submitting correct
effort for resolution
working day. Net quantity and amount are
internally.
information asserted on wholesaler
683 and $4,169, respectively.
Ideally, deny the claim and ask
Correct information is processed
wholesaler to resubmit.
into Oracle.
Complexity of error resolution
process is significantly reduced.
18
Line is duplicated error due to returns requires manual
Transactional
Instances of “Line is duplicated” for April &
This error could be avoided by
Elimination of a manual resolution
effort despite a simple system logic which is easily
May 2019, with negative quantity total up
building a logic in the system -
process which is time consuming.
configurable
to
1237-line items which averages to
where the system also considers
approx. 38 instances per working day. Net
quantity of product line items.
quantity and amount are
1,778
and
$48,440, respectively.
19
Line is duplicated error due to rebilling requires
Transactional
Instances of “Line is duplicated” for May
Request needs to be made to the
Elimination of a manual resolution
excessive manual effort as archives of chargebacks need
2019*, with positive quantities total up to
wholesaler to submit new
process which is time consuming.
to be referenced for identification and resolution.
587-line items which averages to approx.
invoice in cases of rebilling
27 instances per working day. Net quantity
Alternatively, system should be
and amount are
1,365
and
$13,235,
programmed to identify
respectively.
instances of rebilling.
20
a. In case of mismatch in calculated CB amount by
Transactional
Instances of
“calculated amount is not
In case of mismatch, once it is
Eliminates the process of accepting
Oracle and claimed amount by wholesaler, contract
matching with claimed amount” for April &
internally verified that prices are
wrong claims primarily caused as
revisions are checked manually. If a revision is made
May 2019, total up to 2638-line items
updated in the system, any
correct contract prices are not updated
effective within 10 days prior to the claim being
which averages to approx. 60 instances per
mismatch should be denied.
in wholesaler's systems.
submitted, wholesaler's claim at old prices is
working day. Net quantity and amount are
Manual corrections should be
accepted.
3,302 and $107,091, respectively.
avoided.
b. The policy of 10 days is subjective and not followed
to the T. There is a lack of clarity of whether it is 10
working days or calendar days.
13
CHARGEBACKS - DENIAL INSTANCES
April & May 2019
Analysis on Errors
Description of error
Sum of
Count of Errors
Sum of Qty
Chargebacks ($)
This error is tagged when the system is unable to identify contract no.
Agreement is Null
submitted by the wholesaler with contract master data.
173
683
4,169
This error is tagged when the amount claimed by wholesaler is not in line
Calculated amount does not match
with the amount calculated by the system based on the WAC and Contract
claimed amount
2,638
3,302
107,091
price entered in the Contract Master data.
Code Conversion Mapping is not
This error is tagged when system is unable to identify a product as code
defined for this product
mapping process is not completed.
36
475
960
This error is tagged when product is not on the contract or is discontinued
TO BE UPDATED
r in Getting Product Price n
Erro
from a contract.
35
46
1,205
Chargeback Associate (CBA)
(Process Champion)
This error is tagged when system identifies a combination of fields to be
Line is Duplicated
same and assumes it to be a duplicate chargeback submission.
20,511
37,223
1,099,452
Susan Hreno
This error is tagged when WAC price submitted by wholesaler is invalid
or
Purchase Price is missing
Senior Chargeback Analyst
missing
285
814
2,193
Total Discrepancies
23,678
42,543
1,215,070
Total Line items in Historical for
757,150
1,376,500
41,169,311
April & May
% of Discrepancies to Historical
3%
3%
3%
Denials as per CB historical
797
1,902
46,562
% of Denial to instances of error
3%
4%
4%
14
CHARGEBACKS - GAP ANALYSIS
Gap
Brief Description of the gaps
Impact
Risk
Effort to
12
#
remediate
8
8
Manual process prone to errors
High
High
Medium
9
9
Track of Chargebacks through Invoice to DM to CM
High
High
Medium
to Payment workflow is missing.
DMs to CMs verification done only at total count
level
17
10
No process to track reverse chargeback vs actual
Medium
High
Medium
20
10
return
11
No track of unsold inventory held by Wholesaler
Medium
Medium
Medium
19
13
16
11
12
Process is inefficient & error prone as based on
High
Medium
Low
manual verification of printed documents.
13
Inadequate handover and training
Medium
High
Low
15
14
Escalation to IT for unknown issues
Medium
Medium
Not
known
18
15
Redundancy in DM storage. 3 soft copies and a print.
Low
Low
Low
16
Verification at total count level not foolproof
Medium
Medium
Low
Low
Medium
High
17
Internal rectification of wrong submissions of
Medium
Low
Low
Risk
contract is time consuming
18
Line duplication errors due to returns / rebilling
Low
Low
Low
*Size of the bubble represents the
< Keys >
Impact level of gap
Risk due to gap
Effort to Remediate
done on manual basis
High
Impact on IC
Financial risk
19
Medium
Low
Medium
IC - Internal Control
Medium
Financial Impact
Reputational Risk
20
Mismatch in calculated amount and claimed amount
Medium
Medium
Medium
accepted within 10 days prior to revision of contract
Low
Operational Impact
Operational risk
DM - Debit Memo | CM - Credit Memo |
15
Rebates
16
REBATES - TEAM INTERVIEWS & PROCESS MAPS
As-Is Process
To-be Process
People
SCBA Hreno
Senior Chargeback Analyst
(Process Champion)
Amitha Gentela
Associate Director - Finance
(enclosed separately as annexure)
(enclosed separately as annexure)
17
REBATES - PROCESSES OBSERVED
% of $ value volume from Jan-Aug 2019
Offer name
Customer's
Offers observed to total
Customer's volume to
name
offers
total offers
Indirect Purchase Rebate (Differential)
Walgreen's
48.44%
57.54%
6% Non PRxO MSA Fees -- 3rd Party
8.77%
ABC
19.57%
3% Order Analytics Service Fee - PRxO Sales
0.39%
One Stop Maintenance PUR
McKesson
8.82%
12.92%
Sales Based Rebates - 25% Preferred Sales
3.97%
Cardinal
5.02%
Sales Based Rebates - 15% 3rd Party & 10% Non-Contract (Red Oak)
0.43%
Explicit processes observed and mapped
70.82%
95.06%
Indirect Purchase Rebate - Differential
Kaiser
3.35%
3.35%
Implicit processes observed and mapped
74.17%
98.41%
18
REBATES - GAPS & SUGGESTED ACTIONS
#
Gap
Type
Validation
Suggested Actions
Probable impact of Suggested Actions
21
A timing difference is observed across the
Strategic
Total rebates (excluding
Undertake ABC analysis of Wholesalers and
Elimination of gaps in reconciliation by
process of recording and reporting of
differentials and non-
for the top 5, understand dates and fields
getting transparency in Wholesaler's
indirect sales between the wholesalers and
contract rebates), where
considered for monthly rebate reports from
timings.
Client internally. This is causing:
5%
threshold-based
wholesaler, make commercial team aware
Avoidance of risk of overcompensating
a) Threshold based approval on total
acceptance is used is
of the need to do so and request their
wholesalers and thus increasing cost of
quantity and value amounts by Client
$21.69M for the period
help to get answers/responses in a timely
sales.
across all schemes with all wholesalers.
Jan to Sep 2019.
manner
Reduction in time and efforts required to
b) Wholesaler backups being considered
Match internal report timings to dates
reconcile wholesaler's and Client's rebate
first and valid source of information,
considered by wholesaler to match line
backups.
compared to Client's internal data.
item reconciliation.
Automation (through robotics or excel) to
c) Unreconcilable line item variances
Checks could be automated through
significantly improve time efficiency for
between wholesaler's backups and
robotics or excel based formulas and done
repetitive tasks thereby creating a
internal Client reports on a monthly
at SKU level.
preparedness for scalability.
basis.
d) Threshold based approvals has the
innate risk of approving products that
are not a part of the offer/contract.
22
Excessive manual intervention is observed
Strategic
Of 93 total process steps
Close to 50% of the manual processes are
Increased efficiency and more productive
in the overall rebate process thereby
in a single cycle 88 are
either automatable through Oracle
/
use of time making the process scalable.
making it error prone and person
observed to be having
external intervention or are avoidable.
Possibility of erroneous processing is
dependent without any backup.
manual intervention
Manual intervention to be subjected only
reduced.
in cases where system work-flow and
controls need to be overridden.
19
REBATES - GAPS & SUGGESTED ACTIONS
#
Gap
Type
Validation
Suggested Actions
Probable impact of Suggested Actions
23
In case of Rebates, it is observed that the process is
Operational
NA
A team member should be adequately
Process becomes less person
heavily dependent on SCBA and is person oriented.
cross-trained to act as a fallback for
dependent and does not pause in case
There is no backup for SCBA which creates a Key-man
SCBA.
of SCBA's absence.
risk.
24
No defined process to analyze non-contract sales to
Operational
Total
non-contract
A thought-out process needs to be
Clear definition of benchmarks to
turn it into contract-based sales
rebates up to Sep
defined, mapped and communicated to
identify cases of conversion.
2019
is
$432,111
all concerned.
which is 1% of total
rebates.
25
PUR rate in the system not used by SCBA to check
Operational
NA
Provide access to a system generated
2 level checks at SCBA and AD-F's levels,
value of rebates at line item level, which ideally needs
report with PUR rates and quantities
making process foolproof.
to be done by her. Currently total quantity level check
included (could be Accrual's report).
done by SCBA and total value level check done by AD-F.
26
Maintaining WDR-manual excel file, which includes -
Transactional
For
Walgreen's
The output of WDR-manual can be
Significant saving of SCBA's time
a) Undertaking excessive manual effort to maintain /
Differential process,
achieved by automating WDR-BI
Eliminates manual process steps,
update the file
on a monthly basis, it
through Oracle by incorporating
thus making it shorter and efficient.
b) Using filters for excluding Pharmerica and entering
is
observed that
necessary logics within it.
Quicker turn-around time to
manually in WDR-manual file.
average time taken
Alternatively, excel formula-based
prepare WSR.
It is observed that the exercise of maintaining the
to complete the
automation should be followed
WDR-manual is not necessary and is not scalable.
process is 2 working
mandatorily in the interim for
weeks.
excluding Pharmerica transactions
27
In case of denial of claim, SCBA follows up with
Transactional
NA
As a rule, all follow-ups for rebates
Better and more productive use of
wholesaler
should be done by AR
SCBA's time
SCBA - Senior Chargeback Associate | AD-F - Associate Director of Finance | WDR-manual - Walgreen’s Differential Manual excel report | WDR-BI - Walgreen’s Differential
20
Report exported from BI
REBATES - GAP ANALYSIS
Gap
Brief Description of the gaps
Impact
Risk
Effort to
#
remediate
22
21
21
Timing difference in accounting indirect sales
High
High
High
leading to -
Threshold based approval leading to risk of
23
product approval not in offer /contract
Non availability of Internal data for decision
making
25
Unreconciled balance between vendor and
Cossette books.
26
25
22
Excessive Manual Intervention
High
High
High
23
Procedure of Rebates is person centric and
High
High
Medium
prone to key-man risk
24
Lack of standard process to deal with non-
Medium
Medium
Medium
27
contract sales
25
PUR rebates not checked for values, line item
Medium
Medium
Medium
Low
Medium
High
level. Only done at total level.
Risk
26
Manual excel for WDR is redundant and not
Medium
Low
Medium
scalable
*Size of the bubble represents the
< Keys >
Impact level of gap
Risk due to gap
Effort to Remediate
27
Follow-up for payment done by SCBA
Low
Low
Low
High
Impact on IC
Financial risk
IC - Internal Controls
Medium
Financial Impact
Reputational Risk
PUR - Per Unit Rebate | WDR - Walgreen’s Differential Report | SCBA - Senior Chargeback Analyst
Low
Operational Impact
Operational risk
21
To-Be Process Analysis
Particulars
Chargebacks
Rebates
Total
As-Is process steps
66
94
160
Manual
46
79
125
System-driven
20
15
35
To-Be process steps
51
67
118
Manual
27
45
72
System driven
21
15
36
Excel automated with eventual RPA implementation possibilities
3
-
3
Excel automated processes without eventual RPA implementation possibilities
-
7
7
Process steps eliminated (net) on account of automation
15
27
42
Process steps variance
Total
-23%
-29%
-26%
Manual
-41%
-43%
-42%
System driven
5%
0%
3%
Excel - Automated
100%
100%
100%
22
NEXT STEPS
Outputs of Current Pilot Engagement With Client:
Detailed Documentation of the ‘As-Is’ Process in 2 Finance Department areas of Chargebacks and Rebates
Internal Control Lapses as a result of the detailed ‘As-Is’ Process Study
Internal Cases of Process Inefficiencies making it onerous and time-consuming
Simple Quick-Fix Solutions for the observed control lapses and inefficiencies
Detailed Documentation of ‘To-Be’ Process for Chargebacks and Rebates assuming the implementation of suggested solutions
Thoughts on Future Engagement with Client:
1.
Internal Audit focussed Internal Process Review with In-Depth Documentation of the ‘As-Is’ Processes to identify control lapses and inefficiencies in the below stated
areas catering to the Finance & Accounts departments::
Month-End Closing Process
Procure to Pay Process Work-flow
Order to Cash Process Work-Flow
Inventory to Sale Process Work-Flow
Process Work-Flow Related to Capital Expenditure Projects
Delegation of Authority Across all processes
2.
Internal Audit focussed Review of Existing Process Documentation for the purposes of the Process Optimization and if needed Process Re-engineering in the below
stated areas catering to various department in Operations:
Production Planning
Material Management
Product Costing and Pricing
Supply-Chain Management
IT Infrastructure
3.
Evaluation of the quality of Financial Planning & Analysis reports for the level of adequacy, accuracy and automation
23
NEXT STEPS: EXCEL-BASED AUTOMATION OF PROCESSES
Sr.
Excel purpose
Area
Gap
Mode
# of
Duration
Week 1
Week 2
Week 3
Week 4
Week 5
Week 6
Week 7
Week 8
#
reference
resources
(days)
1
Reconciliation of no. of Debit Memos received
Chargebacks
Gap #12;
Onsite
1
3
on a day with no. of Credit Memos generated,
Gap #16
maintaining
a log for
roll-forward
Offsite
1
2
reconciliations.
2
Analysis
of
past
discrepancies
and
Chargebacks
Additional
Onsite
1
7
chargebacks historical reports
analytics
Offsite
1
10
3
Automation of Walgreen's Differential Report
Rebates
Gap #26
Onsite
1
5
using BI report as a source
Offsite
0
0
4
Reconciliation of Rebates at line item level as
Rebates
Gap #21
Onsite
1
10
per wholesaler and as per Oracle BI (% based
rebates), identification of discrepancy types,
Offsite
1
10
point of accrual and logics to resolve
discrepancies.
5
Reconciliation of Rebates at line item level as
Rebates
Gap #25
Onsite
1
10
per wholesaler and as per Oracle BI
(PUR
rebates), identification of discrepancy types,
Offsite
1
10
point of accrual and logics to resolve
discrepancies.
6
Pilot testing of #1, 2, 3
Onsite
1
10
7
Pilot testing of #4, 5
Onsite
1
5
8
Training and handover of all Excel-based
Onsite
1
8
models
Blue bar - Onsite resource time | Green bar - Offsite resource time
24
Thank You
CONTACT US
USA
214 West 29th Street, 2nd Floor
New York City,
NY 10001
UK
2, Wollaton Vale,
Nottingham,
NG8 2NR
UAE
# 112, Level 1, C1 Building,
Dubai World Trade Centre District,
Sheikh Zayed Road,
Dubai
India
18th Floor, The Ruby,
Senapati Bapat Marg, Dadar (West),
Mumbai
Contact
Ateet Mathur
[email protected]