kraus

Document Sample
kraus Powered By Docstoc
					ASSESSING RISK FOR RESEARCH
     AWARDS USING ACL

  Presented by UC Davis Internal Audit Services
Scope of Research Activities
   UC Davis is widely recognized as a world class
    research institution.

   $508 million in research funding was received
    during fiscal year 2006-07.

   We currently have approximately 9,500 contracts
    and grants from federal, state, local and private
    sources.
The Challenge
   IAS included an audit of Research Management and
    Oversight on its fiscal year 2008-09 Audit Plan.

   How do we identify the awards presenting the
    greatest risk in an efficient and effective manner?

   Is it possible to automate the risk assessment
    process?
    The Answer
          Create a Risk Assessment Scorecard using ACL.
                                         COU N T S                                                   R ISK FACT OR S


                                                                                                           AVG ACCT
                           AW AR D                   ST AFF                    AW AR D BASED               BALAN CE          AVG R EC      LE D GER
                                                                                                                                   AVG
                                                                                                                           AVG     POS       AVG
                                                                                       AW AR D AW AR D AVG OD AVG OD       POS     R EC     ACCT
                       AW AR D AW AR D                                 AW AR D AW AR D COST     SU B     BAL    BAL        R EC   D AT E /    LR    T OT AL
AW AR D N U M          ACCT S    PIs       MGR S       SU PPOR T        AMT     EN D   SH AR E FU N D   AMT   APR OP       AMT     AMT      CER T R ISK
0501763                  2       1           0                0    ~~~~~   1     4       5       5          0          0    4        3        5           27
MD00222-01/08 YR5        1       1           0                0    ~~~~~   1     5       5       5          0          0    1        0        5           22
2P30CA093373-06/10       1       1           0                0    ~~~~~   2     0       5       5          1          5    0        0        4           22
5710001905               1       1           0                0    ~~~~~   5     2       5       5          0          0    0        0        5           22
DBI-05-01763             1       1           0                0    ~~~~~   1     0       5       5          1          5    1        0        4           22
5P30CA093373-06S1        2       2           0                0    ~~~~~   2     3       5       5          1          5    0        0        0           21
5UL1RR024146-02          1       1           2                0    ~~~~~   2     3       5       5          0          0    0        0        5           20
DE-FG02-91ER40674        11      10          0                1    ~~~~~   2     5       5       5          0          0    0        0        3           20
2 P01 ES011269-07        1       1           0                0    ~~~~~   1     0       5       5          1          5    0        0        2           19
2P01ES011269-06          1       1           0                0    ~~~~~   1     0       5       5          1          5    0        0        2           19
C2350064026              2       1           0                0    ~~~~~   1     0       5       5          2          3    0        0        3           19
NIH CA103828-04/09       1       1           0                0    ~~~~~   1     3       5       5          0          0    0        0        5           19
P01 AG022500-05/08       1       1           0                0    ~~~~~   1     3       5       5          0          0    0        0        5           19
P60MD00222-04            1       1           0                0    ~~~~~   1     0       5       5          1          5    0        0        2           19
RD-83329201              3       2           1                0    ~~~~~   1     1       5       5          0          2    0        0        5           19
W912EF-08-D-0007         3       1           0                3    ~~~~~   4     4       0       5          1          3    0        0        2           19
972088 STIPEND & FEE     1       1           0                0    ~~~~~   1     0       5       1          1          5    0        0        5           18
DBI-0501763              1       1           0                7    ~~~~~   1     2       5       5          0          0    0        0        5           18
2P01ES011269             5       1           0                0    ~~~~~   1     0       5       5          0          1    0        0        5           17
Risk Assessment Scorecard
   The Risk Assessment Scorecard ranks each award
    based upon a composite risk score.
   Score is derived by using ACL to analyze
    information contained in UC Davis’ key financial
    and decision support systems for predefined risk
    factors.
     Davis Information System (DaFIS)
     General Ledger Review System

     Payroll/Personnel System
Scorecard Risk Factors
   HIGH RISK
     Award  amount – large awards
     Award end date – nearing the end of the award
      period
     Source of funding – federal awards

     School or College – weak internal control environment
      as evidenced by previous audit findings, investigations,
      external audits, etc.
     Cost sharing – awards containing cost sharing
      commitments
Scorecard Risk Factors, Cont’d.
   HIGH RISK
     Overdrafts  – award accounts in overdraft
     Cost transfers – significant dollar amount and near end
      of award period
     Status of general ledger reviews – general ledger
      review is not current
Composite Scores
         A closer look.
                                                    RISK FACTORS


                                                         AVG ACCT
                               AWARD BASED               BALANCE           AVG REC       LEDGER

                                                                              AVG POS
                                      AWARD   AWARD         AVG OD              REC     AVG
                      AWARD   AWARD    COST     SUB  AVG OD  BAL   AVG POS     DATE / ACCT LR TOTAL
 AWARD NUM             AMT     END    SHARE    FUND BAL AMT APROP REC AMT       AMT    CERT RISK
 0501763                1       4       5       5        0         0   4             3     5          27
 MD00222-01/08 YR5      1       5       5       5        0         0   1             0     5          22
 2P30CA093373-06/10     2       0       5       5        1         5   0             0     4          22
 5710001905             5       2       5       5        0         0   0             0     5          22
 DBI-05-01763           1       0       5       5        1         5   1             0     4          22
 5P30CA093373-06S1      2       3       5       5        1         5   0             0     0          21
 5UL1RR024146-02        2       3       5       5        0         0   0             0     5          20
 DE-FG02-91ER40674      2       5       5       5        0         0   0             0     3          20
 2 P01 ES011269-07      1       0       5       5        1         5   0             0     2          19
 2P01ES011269-06        1       0       5       5        1         5   0             0     2          19
 C2350064026            1       0       5       5        2         3   0             0     3          19
 NIH CA103828-04/09     1       3       5       5        0         0   0             0     5          19
 P01 AG022500-05/08     1       3       5       5        0         0   0             0     5          19
 P60MD00222-04          1       0       5       5        1         5   0             0     2          19
 RD-83329201            1       1       5       5        0         2   0             0     5          19
Assignment of Scores to Risk Factors
   For each risk factor, we stratified the range of
    possible values ACL would identify for the awards
    in our population, and assigned a numeric risk score
    to each strata.
     Range   of numeric risk scores is 1 through 5.
      1  = lowest risk
       5 = highest risk
Example of Risk Factor Scoring –
Award Amounts
   Step 1 – Stratify the population of awards.
Produced with ACL by: UC Davis
 <<< STRATIFY over 3,134,968.00-> 59,558,130.00 >>>
 >>> Minimum encountered was 3,134,968.00
 >>> Maximum encountered was 59,558,130.00
AWARD_AMOUNT                     COUNT <-- % % -->      AWARD_AMOUNT


3,134,968.00 -> 14,419,600.39     113 84.96% 54.45%     605,787,882.85
14,419,600.40 -> 25,704,232.79     14 10.53% 23.68%     263,487,210.94
25,704,232.80 -> 36,988,865.20      3 2.26%    8.42%    93,670,076.54
36,988,865.21 -> 48,273,497.59      2 1.50%    8.09%    90,000,000.00
48,273,497.60 -> 59,558,130.00      1 0.75%    5.35%    59,558,130.00
                        133 100.00% 100.00%    1,112,503,300.33
Example of Risk Factor Scoring –
Award Amounts, Continued.
   Step 2 – Assign risk scores to the five strata.
              Award amount

                  DEFINE FIELD Award_Amount_RF COMPUTED

                   1 IF AWARD_AMT < 14419600.39
                   2 IF BETWEEN( AWARD_AMT ,14419600.40 , 25704232.79 )
                   3 IF BETWEEN( AWARD_AMT ,25704232.80 ,36988865.20 )
                   4 IF BETWEEN( AWARD_AMT ,36988865.21 , 48273497.59 )
                   5 IF AWARD_AMT> 48273497.59
Awards’ Risk Ranking
   An ACL script is run for each risk factor to assign a
    score for each award.
   The results of the scripts for each award are
    combined in a table (Excel spreadsheet), and a
    total combined risk score is computed for each
    award.
   The awards are sorted by risk score, with the
    highest risk score preliminarily indicating the highest
    risk award.
Additional Considerations
   We also used ACL to provide us with other
    information pertinent to the awards included in our
    analysis.
   We felt certain factors that when considered in
    totality by the auditor, spoke to the complexity of
    the award.
   Auditor judgment might be needed to adjust the
    ranking provided by ACL.
Additional Considerations, Contd.
   These factors could influence the risk ranking of an
    award:
     Award   accounts – How many general ledger accounts
      are being used to record project financial activities?
     Principal investigators – How many principal
      investigators are working on the project?
     Administrative staff positions – Are administrative staff
      being directly charged to the project? If yes, what are
      their roles?
   Added Information
       A closer look.
                                                                                                   COUNTS



                                                                                      AWARD                  STAFF


                                                                                AWARD    AWARD
ORG NAME LEVEL 1                         AWARD NUM            AWARD AMT         ACCTS     PIs         MGRS     SUPPORT
COLLEGE OF BIOLOGICAL SCIENCES           0501763                    5,902,886    2            1        0             0   ~~~~~
COLLEGE OF AG & ENVIRONMENTAL SCIENCES   MD00222-01/08 YR5          7,595,722    1            1        0             0   ~~~~~
COLLEGE OF ENGINEERING                   2P30CA093373-06/10        16,038,652    1            1        0             0   ~~~~~
COLLEGE OF ENGINEERING                   5710001905                59,558,130    1            1        0             0   ~~~~~
COLLEGE OF AG & ENVIRONMENTAL SCIENCES   DBI-05-01763               5,902,886    1            1        0             0   ~~~~~
COLLEGE OF AG & ENVIRONMENTAL SCIENCES   5P30CA093373-06S1         15,775,217    2            2        0             0   ~~~~~
VICE CHANCELLOR- RESEARCH                5UL1RR024146-02           21,858,677    1            1        2             0   ~~~~~
COLLEGE OF LETTERS AND SCIENCE           DE-FG02-91ER40674         22,725,877    11           10       0             1   ~~~~~
COLLEGE OF ENGINEERING                   2 P01 ES011269-07          3,830,163    1            1        0             0   ~~~~~
COLLEGE OF AG & ENVIRONMENTAL SCIENCES   2P01ES011269-06            5,000,000    1            1        0             0   ~~~~~
VICE CHANCELLOR- RESEARCH                C2350064026                3,728,829    2            1        0             0   ~~~~~
COLLEGE OF ENGINEERING                   NIH CA103828-04/09         6,844,547    1            1        0             0   ~~~~~
COLLEGE OF AG & ENVIRONMENTAL SCIENCES   P01 AG022500-05/08         4,555,137    1            1        0             0   ~~~~~
COLLEGE OF BIOLOGICAL SCIENCES           P60MD00222-04              7,456,196    1            1        0             0   ~~~~~
SCHOOL OF VETERINARY MEDICINE            RD-83329201                7,568,018    3            2        1             0   ~~~~~
COLLEGE OF ENGINEERING                   W912EF-08-D-0007          45,000,000    3            1        0             3   ~~~~~
      Completed Risk Assessment Scorecard

           The final step would be to sort on the total risk
                                    COU N T S                                                 R ISK FACT OR S


                                                                                                    AVG ACCT
                         AW AR D                ST AFF                  AW AR D BASED               BALAN CE          AVG R EC      LED GER
                                                                                                                            AVG
                                                                                                                    AVG     POS       AVG
                                                                                AW AR D AW AR D AVG OD AVG OD       POS     R EC     ACCT
                     AW AR D AW AR D                            AW AR D AW AR D COST     SU B     BAL    BAL        R EC   D AT E /    LR   T OT AL
AW AR D N U M        ACCT S    PIs      MGR S     SU PPOR T      AMT     EN D   SH AR E FU N D   AMT   APR OP       AMT     AMT      CER T R ISK
0501763                2       1           0             0   ~~~~~ 1      4       5       5          0          0    4        3        5          27
MD00222-01/08 YR5      1       1           0             0   ~~~~~ 1      5       5       5          0          0    1        0        5          22
2P30CA093373-06/10     1       1           0             0   ~~~~~ 2      0       5       5          1          5    0        0        4          22
5710001905             1       1           0             0   ~~~~~ 5      2       5       5          0          0    0        0        5          22
DBI-05-01763           1       1           0             0   ~~~~~ 1      0       5       5          1          5    1        0        4          22
5P30CA093373-06S1      2       2           0             0   ~~~~~ 2      3       5       5          1          5    0        0        0          21
5UL1RR024146-02        1       1           2             0   ~~~~~ 2      3       5       5          0          0    0        0        5          20
DE-FG02-91ER40674      11      10          0             1   ~~~~~ 2      5       5       5          0          0    0        0        3          20
2 P01 ES011269-07      1       1           0             0   ~~~~~ 1      0       5       5          1          5    0        0        2          19
2P01ES011269-06        1       1           0             0   ~~~~~ 1      0       5       5          1          5    0        0        2          19
C2350064026            2       1           0             0   ~~~~~ 1      0       5       5          2          3    0        0        3          19
QUESTIONS?
Contacts
Sherrill Jenkins                   Leslyn Kraus
Principal Auditor                  Associate Director
UC Davis Internal Audit Services   UCD Internal Audit Services

ssjenkins@ucdavis.edu              lakraus@ucdavis.edu
530-752-0341                       530-752-9173
ACL Scorecard      Prior to export
Additional Risk Factors

    Award End Date Risk Factor
SET SAFETY OFF

DELETE FIELD Award_End_RF2 OK

DELETE FIELD _Today_Date OK

ACCEPT "Enter Today's Date (YYYYMMDD):" TO _Today_Date

DEFINE FIELD Award_End_RF2 COMPUTED

    5 IF BETWEEN( AWARD_END_DATE , CTOD(_Today_Date),CTOD(_Today_Date) + 90)
    4 IF BETWEEN( AWARD_END_DATE , CTOD(_Today_Date),CTOD(_Today_Date) + 180)
    3 IF BETWEEN( AWARD_END_DATE , CTOD(_Today_Date),CTOD(_Today_Date) + 270)
    2 IF BETWEEN( AWARD_END_DATE , CTOD(_Today_Date),CTOD(_Today_Date) + 360)
    1 IF AWARD_END_DATE> CTOD(_Today_Date)+360
    0

SET SAFETY ON
  Additional Risk Factors

     Sub Fund Risk and Cost Share Risk

DEFINE FIELD Sub_Fund_RF COMPUTED

5 IF MATCH( SUB_FUND_GROUP_TYPE_CODE , 'B', 'C', 'F', 'N', 'V', 'W', 'X' )
3 IF MATCH( SUB_FUND_GROUP_TYPE_CODE , 'G', 'L', 'S')
1 IF MATCH( SUB_FUND_GROUP_TYPE_CODE , 'J', 'P')
        0


 Cost Share Factor
       Cost Share (Y) = 5
       Cost Share (N) = 0
 Additional Risk Factors
    REC Date and Amount Combined factor
Produced with ACL by: UC Davis
 <<< STRATIFY over 0.01-> 117,956.34 >>>
 >>> Minimum encountered was 0.01
 >>> Maximum encountered was 117,956.34

TRANS_LINE_AMT          COUNT <-- % % -->       TRANS_LINE_AMT

   0.01 -> 11,795.64     125 96.15% 27.27%       86163.50
11,795.65 -> 23,591.27      3 2.31% 16.16%        51072.69
23,591.28 -> 35,386.90      0 0.00% 0.00%          0.00
35,386.91 -> 47,182.54      0 0.00% 0.00%          0.00
47,182.55 -> 58,978.17      0 0.00% 0.00%          0.00
58,978.18 -> 70,773.80      1 0.77% 19.24%        60806.32
70,773.81 -> 82,569.44      0 0.00% 0.00%          0.00
82,569.45 -> 94,365.07      0 0.00% 0.00%          0.00
94,365.08 -> 106,160.70     0 0.00% 0.00%          0.00
106,160.71 -> 117,956.34    1 0.77% 37.33%       117956.34

                130 100.00% 100.00%         315998.85
 130 of 496 matched the Filter: TRANS_LINE_AMT > 0

</result>
 Additional Risk Factors
     REC Date and Amount Combined factor
Created a field that identified the days between the transaction and the award end date:

(TRANS_INITIATION_DATE-ORG_ACCT_CG_092608.AWARD_END_DATE)



DEFINE FIELD REC_DateAmt_RF COMPUTED

  1 IF BETWEEN(Date_Diff,-271,-360)AND TRANS_LINE_AMT<= 1000.00
  2 IF BETWEEN(Date_Diff,-181,-270)AND BETWEEN(TRANS_LINE_AMT,1000.01,2500.00)
  3 IF BETWEEN(Date_Diff,-91,-180)AND BETWEEN(TRANS_LINE_AMT,2500.01,5000.00)
  4 IF BETWEEN(Date_Diff , 0 , 89)AND BETWEEN(TRANS_LINE_AMT,5000.01,7500.00)
  5 IF (Date_Diff > 90 OR BETWEEN(Date_Diff,-1,-90))AND TRANS_LINE_AMT >= 7500.01
  0


     This places the highest risk on those transactions 90 days or
     outside of the 90 award closing processes if they are greater
     than $7500; reasoning Extramural is monitoring the transactions
     during the closing process more closely.
    Additional Risk Factors
       GL Online Review of Accounts
LR_Cert_RF COMPUTED
 0 IF CERTIFIED_FLAG = 'Y' AND (FISCAL_YEAR = 2009 AND BETWEEN( FISCAL_PERIOD_START , '01' , '02' ))
 2 IF CERTIFIED_FLAG = 'Y' AND (FISCAL_YEAR = 2008 AND BETWEEN( FISCAL_PERIOD_START , '11' , '13' ))
 4 IF CERTIFIED_FLAG = 'Y' AND (FISCAL_YEAR = 2008 AND BETWEEN( FISCAL_PERIOD_START , '08' , '10' ))
 5 IF CERTIFIED_FLAG = 'N' OR (CERTIFIED_FLAG = 'Y' AND FISCAL_YEAR = 2008 AND
FISCAL_PERIOD_START <= '07') OR (CERTIFIED_FLAG = 'Y' AND FISCAL_YEAR < 2008)
 5
       Additional Risk Factors
             Account Overdrafts
Based on OD balance compared to annual appropriation /             Based on OD amount
various # of months
    Balance (CR) = 0                                                   Balance < 100k = 1
    Balance (OD) and < annual appropriation/12 = 1                     Balance between 101 - 250k = 2
    Balance (OD) and < = annual appropriation/8 = 2                    Balance between 251 - 500k = 3
    Balance (OD) and <= than annual appropriation/4 = 3                Balance between 501 - 750k = 4
    Balance (OD) and <= than annual appropriation/2 = 4
    Balance (OD) and more than 1 annual appropriation/2 = 5            Balance > 751k = 5



                                  ACL Computed fields
Balance_Risk COMPUTED         AS "Balance Risk"                 DEFINE FIELD Balance_Amt_Risk COMPUTED
1 IF Balance_Ind = "OD" AND Balance <= -(YTD_BUDGET_AMT / 12)   1 IF Balance_Ind = 'OD' and Acct_Balance <100000
2 IF Balance_Ind = "OD" AND Balance <= -(YTD_BUDGET_AMT / 8)    2 IF Balance_Ind = 'OD' and BETWEEN( Acct_Balance , 100000 , 250000 )
3 IF Balance_Ind = "OD" AND Balance <= -(YTD_BUDGET_AMT / 4)    3 IF Balance_Ind = 'OD' and BETWEEN( Acct_Balance , 250001, 500000 )
4 IF Balance_Ind="OD" AND Balance <= -(YTD_BUDGET_AMT /2)       4 IF Balance_Ind = 'OD' and BETWEEN( Acct_Balance , 500001 , 750000 )
5 IF Balance_Ind = "OD" AND Balance > - (YTD_BUDGET_AMT /2)     5 IF Balance_Ind = 'OD' and Acct_Balance > 750000
         0                                                               0

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:11
posted:8/13/2012
language:English
pages:25