Hillsburg Hardware 12/31/2018 Prepared by: IMGoodAuditor 3/14/2019 Workpaper Purpose: Document the Analytical Procedures Performed on the Sales and Collection Cycle The following Analytical Procedures were performed for Hillsburg Hardware for the year ended December 31, 2018: 1. Horizontal Analysis of balances, including Sales, Sales Returns & Allowances, Gross Margin, Gross AR, AR Allowance, Bad Debt Expense, Total Current Assets, Total Assets, Net Earnings. 2. Vertical Analysis of Sales Returns & Allowances as a percentage of Sales 3. Vertical Analysis of Gross Margin 4. Vertical Analysis of net AR as a percent of total current assets 5. Number of days AR is outstanding 6. Analysis of changes in number of AR balances 7. Analysis of changes in number of AR balances > $100K 8. Vertical analysis of AR Allowance as a percent of Gross AR 9. Vertical Analysis of bad debt expense as a percent of net sales 10. Agree AR Aging Report total with the AR general ledger control account 11. Scan of AR detail to identify related parties and/or significant credit balances, if any. 12. Aging Analysis 13. Monthly Sales Analysis During testing of controls and testing of transactions no significant deviations in controls or transactions were detected. The following analytical procedures are performed in order to determine the extent of sampling needed (sample size) during tests of details of balances performed on the balance of Accounts Receivable In thousands (5000) 12/31/2018 Percentage 12/31/2017 Percentage 12/31/2016 Combined Horizontal & Vertical Analysis Change Change Analytical Procedure(s) (AP): Sales** * * 144,328 9.0% 132,421 7.0%% 123,737 AP 1 Sales returns & allowances* * ** 1,242 1,195 1,052 AP 1 Sales returns & allowances/Gross sales 0.99% 0.9% 0.9%% AP 2 Gross margin 39,845 9.6% 36,350 7.0% 33,961 AP 1 Gross margin percentage 27.696 27.5% 27.4% AP 3 Gross Accounts receivable**** 20,197 15.39% 17,521 3.3% 16,961 AP 1 Net AR / Total current assets 37.2% 32.5% 32.3%% AP 4 Number of days receivables outstanding 18.05 0.3%% 17.96 -2.8% 19.32 AP 5 Number of accounts receivable 258 16.7%% 221 5.7% 209 AP 6 Number of accounts receivable over $100,000 37 15.69 32 6.796 30 AP 7 Allowance for uncollectible accounts**** 1,240 -5.49% 1,311 21.5% 1,079 AP 1 Allowance for uncollectible accounts / Gross AR 6.19% 7.59% 6.496 AP B Bad debt expense* * * * 3,323 -2.19 3,394 7.3% 3,162 AP 1 Bad debt as a percent of net sales 2.3% 2.6% 2.6% AP 9 Total current assets 51,027 2.3% 49,895 1.5% 49,157 AP 1 Total assets 61,367 0.936 60,791 1.896 59,696 AP 1 Net earnings 5,681 21.9% 4,659 39.0% 3,351 AP 1 "Amounts agree to the General Ledger Trial Balance without exception.Aging Analysis (AP 12]: AP 12 - Aging Analysis The amounts listed below in the aging analysis agree with the Accounts Receivable Aging Report generated from the Accounts Receivable Master File (AR Subsidiary Ledger). The total of Accounts Receivable Aging Report was agreed without exception to the balance of Accounts Receivable in the General Ledger Trial Balance (AP 10). Additionally the detail of the individual customer accounts included in the Aging Report were scanned. Per scan of the account balances, no related party balances were identified and no significant credit balances were identifed for purposes of reclassification (AP 11) Total 0-30 days 31-60 days 61-30 days 91-120 days over 120 days Sum for 61 and older Accounts receivable aging 12/31/2018 20,196,800 14,217.156 2.863,366 1,408,642 1,038,926 662,710 3,110,278 70.39% 14.21% 6.97% 5.14% 3.28% 15.40% Accounts receivable aging 12/31/2017 17,521,155 12.404,978 3,644.400 788.452 490,592 192.733 1,471,777 70.80% 20.80% 4.50% 2.80% 1. 100% 8.40% Monthly Sales Analysis (AP 13): The following amounts were obtained from the auditor extracting sales transactions from the Sales Transaction File and summarizing them in ACL by month. The totals for each fiscal year have been agreed without exception to the amount of sales reported in the General Ledger Trial Balance for each respective year. In thousands ($000) Sales by month 12/31/2019 Percentage 12/31/2018 Percentage 12/31/2017 Percentage 12/31/2016 Percentage January 3.750 2.6% 12.027 8.3% 11,800 8.9% 10,600 8.6% February 11,750 8.1% 10,600 7.3% 9,980 7.5% 10,530 8.5% March 11,502 8.0% 11,100 8.4% 10,950 8.8% April 11,600 8.0% 10,860 8.2% 11,201 9.1% May 12.300 8.5% 11,250 8.5% 11,690 9.4% June 11,400 7.9% 10,900 8.2% 11,150 9.0% July 5,699 3.9% 5,800 4.4% 5,790 4.7% August 12,500 8.7% 11,400 8.6% 12.125 9.8% September 10,700 7.4% 11,550 8.7% 10,760 8.7% October 10,300 7.1% 12.210 9.2% 8,790 7.1% November 10,034 7.0% 13,011 9.8% 8,001 6.5% December 25,666 17.8% 12.560 9.5% 12.150 9.8% Total 15,500 144.328 132.421 123,737Requirements: You, the forensic accountant, had pre-defined that a change greater than plus or minus 5% in a measure would be an unexpected result (anomaly). You also include with the definition of anomalies an unusual directional change in a measure regardless of the percentage change. Review the 13 Analytical Procedures performed above and list below Analytical Procedure Anomalies (unexpected/unfavorable results) that may indicate a revenue or receivable related financial statement fraud: Indicate whether the List the accounts are Accounts potentially List the Analytical Procedure # and a brief Potentially overstated or description of the anomaly Misstated understated