Skip to content

Latest commit

 

History

History
108 lines (84 loc) · 3.71 KB

readme.md

File metadata and controls

108 lines (84 loc) · 3.71 KB
Raw

General Ledger Analytics for R

A series of analytics for ERP general ledger data. Purpose is to identify data quality issues and unusual patterns.

Getting Started

In order to run the analysis, you'll need RStudio with the following packages installed.

  • ggplot2
  • data.table
  • readr

If you are new to R, follow the instructions below:

  1. Install RStudio from https://www.rstudio.com/
  2. Type the following into the console for each package listed above: install.packages("[package name]")

Quick start:

  1. Create a working directory (ex. C:\glar)
  2. Open import.r in RStudio
  3. Update setwd() with path
  4. Place gl.csv, tb.csv and coa.csv in root of working directory
  5. Open glar.Rmd in RStudio and click "Knit to HTML"
  6. Enjoy the results!

Input:

You will need to provide three .csv files: gl.csv, tb.csv, and coa.csv. Fields need to be in the order listed below. They are renamed on import, so header names are flexible. Data preparation procedures will likely be required for your specific ERP. Pre-reconciliation of the trial balance to journal line detail is also recommended.

Journal lines (gl.csv) required fields:

Field Type Description
Journal ID chr Unique journal number
Description chr Transaction description
Fiscal Period chr Period of transaction
Fiscal Year chr Year of transaction
Post Date date Date of entry
Effective Date date Date effective on financials
User ID chr Preparer of transaction
Approver ID chr Approver of transaction
System/Manual chr System generated entry flag
Line Number chr Unique number for each Journal ID line
Account chr Ledger account
Amount num Amount of transaction
Dr Cr chr Debit/credit indicator

Trial balance (tb.csv) required fields:

Field Type Description
Account chr Ledger account
Beginning Balance num Balance at start of period
Ending Balance num Balance at end of period

Chart of accounts (coa.csv) required fields:

Field Type Description
Account chr Ledger account

Output:

Output from analytics below will available in the R Markdown file: glar.Rmd

1. Reconciliation
	 a. Journal lines and trial balance net to 0
	 b. Journal lines and trial balance activity reconcile
2. Summary Statistics
	 a. System/manual journal lines
	 b. Counts and amounts by user
	 c. Top amounts
3. Data Quality Indicators
	 a. Journal lines with blank user
	 b. Journal lines with blank account
	 c. Journals that do not net to 0 
	 d. Journal lines with a negative debit or credit amount
	 e. Journal lines with a blank effective date
	 f. Journal lines with a blank posted date
	 g. Journal lines with blank entry description
	 h. Journal lines with an effective date outside test period
	 i. Percent of COA without activity
4. Journal Analytics
	 a. Journal lines posted on weekends or holidays
	 b. Duplicate lines to the same account
	 c. Journal lines with same preparer and approver
	 d. Journal lines to unrelated accounts
	 e. Journal lines with blank or little description
	 f. Journal lines with key words in description
	 g. Journal lines with round dollar amount
	 h. Journal lines with recurring ending digits
	 i. Large credits to revenue at cutoff
	 j. Relationship between reserve and corresponding accounts
	 k. Effective date earlier than posted date (% of lines, >15, >30, >60, >90, >120)
	 l. Benford curve compared leading two digits

Author

Jared Monger