Audit Solution in R
Case Study: Analysis of General Ledger
Stewart Li (MCom, Bsc, CA, ACCA)
11 March 2019
Audit data analytics (ADA)
Audit data analytics (ADA) has been gaining momentum for a while. Professional bodies, universities and audit firms certainly realize that ADA can enhance the effectiveness and efficiency of audit tests. For instance;
- Data analytics for external auditors | ICAEW
- Basic audit data analytics with R | CPA Canada
- Rutgers Accounting Research Center and Continuous Auditing & Reporting Lab | Rutgers
- DIGAUDIT | NHH
- KPMG Master of Accounting with Data and Analytics Program | KPMG
Although ADA has been such popular research area in the auditng domain, resources relating to ADA use cases are very limited. This article is meant to shed light on that direction.
Available tools
According to 2015 AuditNet Audit Data Analysis Software Survey, the majority of auditors use Excel to analyze their data. ACL and IDEA were ranked second and third. Their usefulness has been proven. However, their capability in the complete process of data analysis project is very much restricted compared to programming languages such as R or Python.
Many BI tools are available on the market and some of them are widely used by accountants and auditors. However, popular BI tools begain to integrate with R to take advantage of programming languages. For example;
In my opinion, Excel still plays a major role in the business world and won’t go away in the foreseeable future. ACL has been employed by big firms as a significant part of their continious auditing efforts. R is the leading analytics tool in the industry and has a massively supportive community. Personally, I recommend auditors to put Excel, ACL and R to their toolbox.
Case study
INTRODUCTION
The case study is inspired by “Data mining your general ledger with Excel” (J. Carlton Collins, CPA, 1/1/2017).
DATASET CLEANUP
library(tidyxl)
library(unpivotr)
cells <- xlsx_cells("data/gl_stewart.xlsx") %>%
filter(!is_blank) %>%
select(row, col, data_type, numeric, date, character)
gl <- cells %>%
behead("N", "field1") %>%
select(-col) %>%
spatter(field1) %>%
select(-row) %>%
mutate(account = coalesce(account, bf),
subaccount = coalesce(subaccount, account)) %>%
fill(account, .direction = "down") %>%
fill(subaccount, .direction = "down") %>%
select(account, subaccount, Type, Date, Num, Adj, Name, Memo, Split, Debit, Credit, Balance) %>%
mutate(Date = as.Date(Date, "%Y-%m-%d")) %>%
janitor::clean_names() %>%
replace_na(list(debit = 0, credit = 0, balance = 0))
write.csv(gl, "gl.csv")UNDERSTAND GENERAL LEDGER
Trial balance
Completeness test: b/f + current year movement = c/f
| subaccount | total_debit | total_credit |
|---|---|---|
| 401K Payable | 0.00 | 0.00 |
| Accounting Fees | 2544.00 | 0.00 |
| Accounts Payable | 131031.50 | 147728.80 |
| Accounts Receivable | 408309.53 | 375976.45 |
| Accumulated Depreciation | 0.00 | 923.04 |
| Advertising Expense | 2000.00 | 0.00 |
| Auto Loan | 0.00 | 0.00 |
| Automobiles & Trucks | 0.00 | 0.00 |
| Bank Service Charges | 0.00 | 0.00 |
| Business License & Fees | 710.23 | 0.00 |
| Car Lease | 6756.00 | 0.00 |
| Car/Truck Expense | 0.00 | 0.00 |
| Car/Truck Expense - Other | 0.00 | 0.00 |
| Company Checking Account | 480976.45 | 403171.13 |
| Company Savings Account | 0.00 | 0.00 |
| Computer & Office Equipment | 2500.00 | 0.00 |
| Computer Repairs | 390.00 | 0.00 |
| Conferences and Seminars | 4700.00 | 0.00 |
| Contributions | 2500.00 | 0.00 |
| Customer Deposits | 3500.00 | 0.00 |
| Deborah Wood’s Time to Jobs | 114450.00 | 114450.00 |
| Deborah Wood Draws | 135000.00 | 0.00 |
| Deborah Wood Equity | 0.00 | 0.00 |
| Deborah Wood Equity - Other | 0.00 | 0.00 |
| Deborah Wood Investment | 0.00 | 0.00 |
| Depreciation Expense | 923.04 | 0.00 |
| Direct Labor | 0.00 | 0.00 |
| Direct Labor - Other | 0.00 | 0.00 |
| Disability Insurance | 0.00 | 0.00 |
| Dues and Subscriptions | 1900.00 | 0.00 |
| Early Payment Discounts | 0.00 | 0.00 |
| Employee Advances | 0.00 | 0.00 |
| Employee Benefits | 2253.96 | 0.00 |
| Employee Bonus | 0.00 | 0.00 |
| Equipment Repairs | 0.00 | 0.00 |
| Freight Costs | 1810.00 | 0.00 |
| Gas | 907.64 | 0.00 |
| General Liability Insurance | 2760.00 | 0.00 |
| Insurance | 0.00 | 0.00 |
| Insurance-Auto | 1440.00 | 0.00 |
| Insurance - Other | 0.00 | 0.00 |
| Interest Expense | 2296.45 | 0.00 |
| Interest Income | 0.00 | 0.00 |
| Inventory Asset | 131301.50 | 173360.75 |
| Legal Fees | 600.00 | 0.00 |
| Life Insurance | 0.00 | 0.00 |
| Line of Credit | 25000.00 | 106208.85 |
| Machinery & Equipment | 0.00 | 0.00 |
| Maintenance/Janitorial | 2841.95 | 0.00 |
| Marketing Expense | 4982.00 | 0.00 |
| Meals and Entertainment | 1376.35 | 0.00 |
| Mileage | 0.00 | 0.00 |
| Misc. Revenue | 0.00 | 0.00 |
| No accnt | 0.00 | 0.00 |
| Office Equipment | 1100.00 | 0.00 |
| Opening Bal Equity | 0.00 | 375.00 |
| Other Expense | 0.00 | 0.00 |
| Other Expense - Other | 0.00 | 0.00 |
| Other Income | 0.00 | 0.00 |
| Other Income - Other | 0.00 | 0.00 |
| Owner’s Health Insurance | 4272.00 | 0.00 |
| Packaging Materials | 1752.50 | 0.00 |
| Payroll Clearing (owner’s time) | 0.00 | 0.00 |
| Payroll Expenses | 0.00 | 0.00 |
| Payroll Liabilities | 0.00 | 0.00 |
| Payroll Liabilities - Other | 0.00 | 0.00 |
| Payroll Service Fees | 1529.24 | 0.00 |
| Payroll Tax Expenses | 4608.57 | 0.00 |
| Payroll Taxes Payable | 11377.50 | 14251.22 |
| Petty Cash Account | 500.00 | 0.00 |
| Postage and Delivery | 1098.00 | 0.00 |
| Prepaid Insurance | 6875.00 | 6875.00 |
| Prepaid Taxes | 0.00 | 0.00 |
| Prepaids | 0.00 | 0.00 |
| Prepaids - Other | 0.00 | 0.00 |
| Printing and Reproduction | 0.00 | 0.00 |
| Professional Development | 0.00 | 0.00 |
| Professional Fees | 0.00 | 0.00 |
| Professional Fees - Other | 0.00 | 0.00 |
| Professional Liability Insuranc | 6875.00 | 0.00 |
| Promotional Expense | 2021.00 | 0.00 |
| Purchases (Cost of Goods) | 180030.75 | 69.00 |
| QuickBooks Credit Card | 2204.48 | 3453.60 |
| Registration & License | 546.00 | 0.00 |
| Rent | 7005.00 | 0.00 |
| Repairs | 0.00 | 0.00 |
| Repairs - Other | 0.00 | 0.00 |
| Repairs & Maintenance | 1700.23 | 0.00 |
| Retained Earnings | 0.00 | 0.00 |
| Revenue | 0.00 | 411809.53 |
| Sales Commission (outside reps) | 3990.80 | 0.00 |
| SEC125 Payable | 1350.00 | 1400.00 |
| Security Deposits | 0.00 | 0.00 |
| Sick/Holiday & Vacation Pay | 0.00 | 0.00 |
| Supplies | 6199.36 | 0.00 |
| Telephone | 4003.44 | 0.00 |
| Travel | 3452.23 | 0.00 |
| Undeposited Funds | 375976.45 | 375976.45 |
| Utilities | 501.59 | 0.00 |
| Vendor Refunds | 0.00 | 0.00 |
| Wages | 0.00 | 0.00 |
| Wages - Office Staff | 6312.00 | 0.00 |
| Wages - Other | 0.00 | 0.00 |
| Wages - Sales-Inside | 2500.00 | 0.00 |
| Wages - Warehouse | 19705.00 | 0.00 |
| Worker’s Compensation | 2782.08 | 0.00 |
| dr | cr |
|---|---|
| 2136029 | 2136029 |
Missing values
| gl$type | n | percent | valid_percent |
|---|---|---|---|
| Bill | 166 | 0.0304364 | 0.0316733 |
| Bill Pmt -Check | 84 | 0.0154015 | 0.0160275 |
| Check | 559 | 0.1024936 | 0.1066590 |
| Credit Card Charge | 68 | 0.0124679 | 0.0129746 |
| Deposit | 111 | 0.0203520 | 0.0211792 |
| General Journal | 72 | 0.0132013 | 0.0137378 |
| Inventory Adjust | 2 | 0.0003667 | 0.0003816 |
| Invoice | 2632 | 0.4825816 | 0.5021942 |
| Liability Check | 111 | 0.0203520 | 0.0211792 |
| Paycheck | 1246 | 0.2284562 | 0.2377409 |
| Payment | 176 | 0.0322699 | 0.0335814 |
| Transfer | 14 | 0.0025669 | 0.0026712 |
| NA | 213 | 0.0390539 | NA |
| Bill (n=111) |
Bill Pmt -Check (n=48) |
Check (n=550) |
Credit Card Charge (n=36) |
Deposit (n=88) |
Invoice (n=2542) |
Liability Check (n=99) |
Payment (n=176) |
Overall (n=3650) |
|
|---|---|---|---|---|---|---|---|---|---|
| account | |||||||||
| Accounts Payable | 15 (13.5%) | 24 (50.0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 39 (1.1%) |
| Inventory Asset | 73 (65.8%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 845 (33.2%) | 0 (0%) | 0 (0%) | 918 (25.2%) |
| Marketing Expense | 2 (1.8%) | 0 (0%) | 1 (0.2%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 3 (0.1%) |
| Packaging Materials | 3 (2.7%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 3 (0.1%) |
| Promotional Expense | 3 (2.7%) | 0 (0%) | 0 (0%) | 1 (2.8%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 4 (0.1%) |
| Purchases (Cost of Goods) | 12 (10.8%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 845 (33.2%) | 0 (0%) | 0 (0%) | 857 (23.5%) |
| Sales Commission (outside reps) | 1 (0.9%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 1 (0.0%) |
| Supplies | 2 (1.8%) | 0 (0%) | 16 (2.9%) | 6 (16.7%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 24 (0.7%) |
| Company Checking Account | 0 (0%) | 24 (50.0%) | 238 (43.3%) | 0 (0%) | 0 (0%) | 0 (0%) | 22 (22.2%) | 0 (0%) | 284 (7.8%) |
| Advertising Expense | 0 (0%) | 0 (0%) | 4 (0.7%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 4 (0.1%) |
| Business License & Fees | 0 (0%) | 0 (0%) | 2 (0.4%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 2 (0.1%) |
| Car/Truck Expense | 0 (0%) | 0 (0%) | 40 (7.3%) | 1 (2.8%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 41 (1.1%) |
| Computer & Office Equipment | 0 (0%) | 0 (0%) | 1 (0.2%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 1 (0.0%) |
| Conferences and Seminars | 0 (0%) | 0 (0%) | 6 (1.1%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 6 (0.2%) |
| Contributions | 0 (0%) | 0 (0%) | 1 (0.2%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 1 (0.0%) |
| Deborah Wood Equity | 0 (0%) | 0 (0%) | 12 (2.2%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 12 (0.3%) |
| Dues and Subscriptions | 0 (0%) | 0 (0%) | 5 (0.9%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 5 (0.1%) |
| Insurance | 0 (0%) | 0 (0%) | 48 (8.7%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 48 (1.3%) |
| Maintenance/Janitorial | 0 (0%) | 0 (0%) | 15 (2.7%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 15 (0.4%) |
| Meals and Entertainment | 0 (0%) | 0 (0%) | 14 (2.5%) | 7 (19.4%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 21 (0.6%) |
| Office Equipment | 0 (0%) | 0 (0%) | 14 (2.5%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 14 (0.4%) |
| Other Expense | 0 (0%) | 0 (0%) | 11 (2.0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 11 (0.3%) |
| Petty Cash Account | 0 (0%) | 0 (0%) | 1 (0.2%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 1 (0.0%) |
| Postage and Delivery | 0 (0%) | 0 (0%) | 15 (2.7%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 15 (0.4%) |
| Prepaids | 0 (0%) | 0 (0%) | 1 (0.2%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 1 (0.0%) |
| Professional Fees | 0 (0%) | 0 (0%) | 16 (2.9%) | 3 (8.3%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 19 (0.5%) |
| QuickBooks Credit Card | 0 (0%) | 0 (0%) | 12 (2.2%) | 18 (50.0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 30 (0.8%) |
| Rent | 0 (0%) | 0 (0%) | 12 (2.2%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 12 (0.3%) |
| Repairs | 0 (0%) | 0 (0%) | 1 (0.2%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 1 (0.0%) |
| Telephone | 0 (0%) | 0 (0%) | 31 (5.6%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 31 (0.8%) |
| Travel | 0 (0%) | 0 (0%) | 9 (1.6%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 9 (0.2%) |
| Utilities | 0 (0%) | 0 (0%) | 12 (2.2%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 12 (0.3%) |
| Wages | 0 (0%) | 0 (0%) | 12 (2.2%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 12 (0.3%) |
| Undeposited Funds | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 88 (100%) | 0 (0%) | 0 (0%) | 88 (50.0%) | 176 (4.8%) |
| Accounts Receivable | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 4 (0.2%) | 0 (0%) | 88 (50.0%) | 92 (2.5%) |
| Customer Deposits | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 1 (0.0%) | 0 (0%) | 0 (0%) | 1 (0.0%) |
| Revenue | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 847 (33.3%) | 0 (0%) | 0 (0%) | 847 (23.2%) |
| Payroll Liabilities | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 0 (0%) | 77 (77.8%) | 0 (0%) | 77 (2.1%) |
Account relationships
JV on weekends
Many JVs were posted on weekends, which indicates potential frauds.
The heatmap shows many JV were posted on weekends, top 15 of which are presented below. This indicates a potential fraudEach row with column type is assumed to be one JV posting. I guess that is what column num means. Calendar heatmap is presented after summarising total jvpost each day, unless the company did work on weekends.
| type | account | n |
|---|---|---|
| Invoice | Revenue | 165 |
| Invoice | Inventory Asset | 164 |
| Invoice | Purchases (Cost of Goods) | 164 |
| Paycheck | Payroll Liabilities | 153 |
| Paycheck | Wages | 76 |
| Deposit | Undeposited Funds | 52 |
| Liability Check | Payroll Liabilities | 44 |
| Paycheck | Company Checking Account | 19 |
| Invoice | Accounts Receivable | 18 |
| Paycheck | Direct Labor | 17 |
Blance amount
| subaccount | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | total |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| total | 134475.0 | 165419.69 | 246703.1 | 160746.0 | 193359.93 | 185535.82 | 135615.26 | 168312.10 | 195752.1 | 137129.5 | 154666.4 | 258313.84 | 2136028.8 |
| 401K Payable | 0.0 | 0.00 | 0.0 | 0.0 | 0.00 | 0.00 | 0.00 | 0.00 | 0.0 | 0.0 | 0.0 | 0.00 | 0.0 |
| Accounting Fees | 225.0 | 0.00 | 0.0 | 255.0 | 1554.00 | 0.00 | 255.00 | 0.00 | 0.0 | 255.0 | 0.0 | 0.00 | 2544.0 |
| Accounts Payable | 0.0 | 3426.00 | 45508.5 | 22104.0 | 20546.50 | 3988.00 | 7602.00 | 1828.00 | 325.0 | 24633.5 | 568.0 | 502.00 | 131031.5 |
| Accounts Receivable | 25506.8 | 25795.45 | 30483.4 | 32325.3 | 29839.45 | 31882.85 | 39460.25 | 31809.45 | 29191.4 | 28511.6 | 39128.0 | 64375.58 | 408309.5 |
| Accumulated Depreciation | 0.0 | 0.00 | 0.0 | 0.0 | 0.00 | 0.00 | 0.00 | 0.00 | 0.0 | 0.0 | 0.0 | 0.00 | 0.0 |
| Advertising Expense | 500.0 | 0.00 | 0.0 | 500.0 | 0.00 | 0.00 | 500.00 | 0.00 | 0.0 | 500.0 | 0.0 | 0.00 | 2000.0 |
| Auto Loan | 0.0 | 0.00 | 0.0 | 0.0 | 0.00 | 0.00 | 0.00 | 0.00 | 0.0 | 0.0 | 0.0 | 0.00 | 0.0 |
| Automobiles & Trucks | 0.0 | 0.00 | 0.0 | 0.0 | 0.00 | 0.00 | 0.00 | 0.00 | 0.0 | 0.0 | 0.0 | 0.00 | 0.0 |
| Bank Service Charges | 0.0 | 0.00 | 0.0 | 0.0 | 0.00 | 0.00 | 0.00 | 0.00 | 0.0 | 0.0 | 0.0 | 0.00 | 0.0 |
ANALYTICAL PROCEDURES
Property, plant and equipment
| type | date | num | name | memo | split | debit | credit | balance | year | month | weekday |
|---|---|---|---|---|---|---|---|---|---|---|---|
| NA | NA | NA | NA | NA | NA | 0.00 | 0.00 | 45632.00 | NA | NA | NA |
| NA | NA | NA | NA | NA | NA | 0.00 | 0.00 | 16953.00 | NA | NA | NA |
| Check | 2018-06-22 | 5144 | Electronics Manufacturer | Computer | Company Checking Account | 2500.00 | 0.00 | 19453.00 | 2018 | Jun | Fri |
| NA | NA | NA | NA | NA | NA | 0.00 | 0.00 | 25963.00 | NA | NA | NA |
| NA | NA | NA | NA | NA | NA | 0.00 | 0.00 | 0.00 | NA | NA | NA |
| General Journal | 2018-01-31 | DEPR03 | NA | Jan07 Depr Expense | Depreciation Expense | 0.00 | 76.92 | -76.92 | 2018 | Jan | Wed |
| General Journal | 2018-02-28 | DEPR03 | NA | Feb03 Depr Expense | Depreciation Expense | 0.00 | 76.92 | -153.84 | 2018 | Feb | Wed |
| General Journal | 2018-03-31 | DEPR4 | NA | Mar03 Depr Expense | Depreciation Expense | 0.00 | 76.92 | -230.76 | 2018 | Mar | Sat |
| General Journal | 2018-04-30 | DEPR5 | NA | Apr03 Depr Expense | Depreciation Expense | 0.00 | 76.92 | -307.68 | 2018 | Apr | Mon |
| General Journal | 2018-05-31 | DEPR6 | NA | May03 Depr Expense | Depreciation Expense | 0.00 | 76.92 | -384.60 | 2018 | May | Thu |
| General Journal | 2018-06-30 | DEPR7 | NA | June03 Depr Expense | Depreciation Expense | 0.00 | 76.92 | -461.52 | 2018 | Jun | Sat |
| General Journal | 2018-07-31 | DEPR8 | NA | July03 Depr Expense | Depreciation Expense | 0.00 | 76.92 | -538.44 | 2018 | Jul | Tue |
| General Journal | 2018-08-31 | DEPR9 | NA | Aug03 Depr Expense | Depreciation Expense | 0.00 | 76.92 | -615.36 | 2018 | Aug | Fri |
| General Journal | 2018-09-30 | DEPR10 | NA | Sept03 Depr Expense | Depreciation Expense | 0.00 | 76.92 | -692.28 | 2018 | Sep | Sun |
| General Journal | 2018-10-31 | DEPR11 | NA | Oct03 Depr Expense | Depreciation Expense | 0.00 | 76.92 | -769.20 | 2018 | Oct | Wed |
| General Journal | 2018-11-30 | DEPR12 | NA | Nov03 Depr Expense | Depreciation Expense | 0.00 | 76.92 | -846.12 | 2018 | Nov | Fri |
| General Journal | 2018-12-31 | DEPR13 | NA | Dec03 Depr Expense | Depreciation Expense | 0.00 | 76.92 | -923.04 | 2018 | Dec | Mon |
| NA | NA | NA | NA | NA | NA | 0.00 | 0.00 | 0.00 | NA | NA | NA |
| General Journal | 2018-01-31 | DEPR03 | NA | Jan07 Depr Expense | Accumulated Depreciation | 76.92 | 0.00 | 76.92 | 2018 | Jan | Wed |
| General Journal | 2018-02-28 | DEPR03 | NA | Feb03 Depr Expense | Accumulated Depreciation | 76.92 | 0.00 | 153.84 | 2018 | Feb | Wed |
| General Journal | 2018-03-31 | DEPR4 | NA | Mar03 Depr Expense | Accumulated Depreciation | 76.92 | 0.00 | 230.76 | 2018 | Mar | Sat |
| General Journal | 2018-04-30 | DEPR5 | NA | Apr03 Depr Expense | Accumulated Depreciation | 76.92 | 0.00 | 307.68 | 2018 | Apr | Mon |
| General Journal | 2018-05-31 | DEPR6 | NA | May03 Depr Expense | Accumulated Depreciation | 76.92 | 0.00 | 384.60 | 2018 | May | Thu |
| General Journal | 2018-06-30 | DEPR7 | NA | June03 Depr Expense | Accumulated Depreciation | 76.92 | 0.00 | 461.52 | 2018 | Jun | Sat |
| General Journal | 2018-07-31 | DEPR8 | NA | July03 Depr Expense | Accumulated Depreciation | 76.92 | 0.00 | 538.44 | 2018 | Jul | Tue |
| General Journal | 2018-08-31 | DEPR9 | NA | Aug03 Depr Expense | Accumulated Depreciation | 76.92 | 0.00 | 615.36 | 2018 | Aug | Fri |
| General Journal | 2018-09-30 | DEPR10 | NA | Sept03 Depr Expense | Accumulated Depreciation | 76.92 | 0.00 | 692.28 | 2018 | Sep | Sun |
| General Journal | 2018-10-31 | DEPR11 | NA | Oct03 Depr Expense | Accumulated Depreciation | 76.92 | 0.00 | 769.20 | 2018 | Oct | Wed |
| General Journal | 2018-11-30 | DEPR12 | NA | Nov03 Depr Expense | Accumulated Depreciation | 76.92 | 0.00 | 846.12 | 2018 | Nov | Fri |
| General Journal | 2018-12-31 | DEPR13 | NA | Dec03 Depr Expense | Accumulated Depreciation | 76.92 | 0.00 | 923.04 | 2018 | Dec | Mon |
Rent
| type | date | num | name | memo | split | debit | credit | balance | year | month | weekday |
|---|---|---|---|---|---|---|---|---|---|---|---|
| NA | NA | NA | NA | NA | NA | 0.00 | 0 | 0.00 | NA | NA | NA |
| Check | 2018-01-01 | 5001 | Kuver Property | January Rent | Company Checking Account | 583.75 | 0 | 583.75 | 2018 | Jan | Mon |
| Check | 2018-02-01 | 5029 | Kuver Property | Office Rent | Company Checking Account | 583.75 | 0 | 1167.50 | 2018 | Feb | Thu |
| Check | 2018-03-01 | 5059 | Kuver Property | Office Rent | Company Checking Account | 583.75 | 0 | 1751.25 | 2018 | Mar | Thu |
| Check | 2018-04-02 | 5079 | Kuver Property | Office Rent | Company Checking Account | 583.75 | 0 | 2335.00 | 2018 | Apr | Mon |
| Check | 2018-05-01 | 5107 | Kuver Property | Office Rent | Company Checking Account | 583.75 | 0 | 2918.75 | 2018 | May | Tue |
| Check | 2018-06-01 | 5135 | Kuver Property | Office Rent | Company Checking Account | 583.75 | 0 | 3502.50 | 2018 | Jun | Fri |
| Check | 2018-07-02 | 5160 | Kuver Property | Office Rent | Company Checking Account | 583.75 | 0 | 4086.25 | 2018 | Jul | Mon |
| Check | 2018-08-01 | 5190 | Kuver Property | Office Rent | Company Checking Account | 583.75 | 0 | 4670.00 | 2018 | Aug | Wed |
| Check | 2018-09-03 | 5220 | Kuver Property | Office Rent | Company Checking Account | 583.75 | 0 | 5253.75 | 2018 | Sep | Mon |
| Check | 2018-10-01 | 5244 | Kuver Property | Office Rent | Company Checking Account | 583.75 | 0 | 5837.50 | 2018 | Oct | Mon |
| Check | 2018-11-01 | 5270 | Kuver Property | Office Rent | Company Checking Account | 583.75 | 0 | 6421.25 | 2018 | Nov | Thu |
| Check | 2018-12-03 | 5295 | Kuver Property | Office Rent | Company Checking Account | 583.75 | 0 | 7005.00 | 2018 | Dec | Mon |
Payroll
FINANCIAL ANALYSIS
Spread
| subaccount | month | debit_sum | credit_sum | debit_mean | credit_mean | debit_median | credit_median | debit_max | credit_max |
|---|---|---|---|---|---|---|---|---|---|
| Accounts Payable | Jan | 0.0 | 35903.0 | 0.0000 | 5129.0000 | 0 | 4470 | 0.0 | 12812.5 |
| Accounts Payable | Feb | 3426.0 | 12944.0 | 285.5000 | 1078.6667 | 67 | 78 | 2520.0 | 8450.0 |
| Accounts Payable | Mar | 45508.5 | 21819.0 | 5056.5000 | 2424.3333 | 4470 | 0 | 12812.5 | 20950.0 |
| Accounts Payable | Apr | 22104.0 | 23671.5 | 2009.4545 | 2151.9545 | 0 | 164 | 20950.0 | 7925.0 |
| Accounts Payable | May | 20546.5 | 1164.0 | 2935.2143 | 166.2857 | 164 | 0 | 7925.0 | 1000.0 |
| Accounts Payable | Jun | 3988.0 | 6923.0 | 664.6667 | 1153.8333 | 74 | 74 | 2840.0 | 3625.0 |
ARAP trend
Sales breakdown
Comparison P/L
Comparison B/S
REVENUE
Outliers
Top customers
RFM analysis
Revenue vs Cost
Review JV
Review doubel entires of business transactions.
| name | account | debit | credit |
|---|---|---|---|
| Accounting Firm | Company Checking Account | 0.00 | 1554.00 |
| Accounting Firm | Professional Fees | 1554.00 | 0.00 |
| Automobile Insurance Company | Car/Truck Expense | 1440.00 | 0.00 |
| Automobile Insurance Company | Company Checking Account | 0.00 | 1440.00 |
| Automobile Loan Company | Car/Truck Expense | 6756.00 | 0.00 |
| Automobile Loan Company | Company Checking Account | 0.00 | 6756.00 |
| Baker’s Professional Lighting:Store #05 | Accounts Receivable | 2391.00 | 2391.00 |
| Baker’s Professional Lighting:Store #05 | Inventory Asset | 0.00 | 918.52 |
| Baker’s Professional Lighting:Store #05 | Purchases (Cost of Goods) | 918.52 | 0.00 |
| Baker’s Professional Lighting:Store #05 | Revenue | 0.00 | 2391.00 |
| Baker’s Professional Lighting:Store #05 | Undeposited Funds | 2391.00 | 2391.00 |
This table is very useful to check account receivables and revenue over the financial period. Column amount indicate those customers with AR balance in FY2018.
| name | account | debit | credit | amount |
|---|---|---|---|---|
| Baker’s Professional Lighting:Store #05 | Accounts Receivable | 2391.00 | 2391.00 | 0.00 |
| Baker’s Professional Lighting:Store #05 | Revenue | 0.00 | 2391.00 | 2391.00 |
| Baker’s Professional Lighting:Store #10 | Accounts Receivable | 12718.48 | 2038.00 | -10680.48 |
| Baker’s Professional Lighting:Store #10 | Revenue | 0.00 | 12718.48 | 12718.48 |
| Baker’s Professional Lighting:Store #15 | Accounts Receivable | 3307.00 | 1885.00 | -1422.00 |
| Baker’s Professional Lighting:Store #15 | Revenue | 0.00 | 3307.00 | 3307.00 |
| Baker’s Professional Lighting:Store #20 | Accounts Receivable | 1872.00 | 1872.00 | 0.00 |
| Baker’s Professional Lighting:Store #20 | Revenue | 0.00 | 1872.00 | 1872.00 |
| Baker’s Professional Lighting:Store #25 | Accounts Receivable | 2381.00 | 2381.00 | 0.00 |
| Baker’s Professional Lighting:Store #25 | Revenue | 0.00 | 2381.00 | 2381.00 |
MODELING
PCA
Relationships
Time series
Citation
tidyxl,unpivottidyversejanitorskimrcollapsibleTreeggTimeSeriesd3heatmapwidyrigraphggraph,gganimatetsibbleR Markdown,knitr,xaringan
Copyright @ Stewart Li
stewardli8@msn.com
China | Singapore | New Zealand
…