Month End Reconciliation in StarBuilder Session - 211

1. Month End Reconciliationin StarBuilder Session - 211 Presented by Adam Hutchins

2. Objectives Discuss reconciliation methods for the following topics: • Trial Balance • Financial Statements • Bank Reconciliation • Job Cost to GL • AP to GL • AR to GL • Payroll • Contracts • Equipment Costing • Progress Billings

3. Trial Balance What to do if the trial balance is not in balance: • Run the trial balance report in order to isolate the period that is out of balance. The period and YTD amounts should be out by the same amount.

4. Trial Balance 2) Run the Transaction Detail Report by Journal – one journal at a time.

5. Trial Balance Three things to look for: • Does page one of the report show a transaction with a missing account number? • Does the last page show a balance other than zero? • Does the report run on the GL journal show postings made with JC only post code? i.e. 9171, 9172

6. Trial Balance • The report will help identify which journal is out of balance. • Run the report on select Transaction Date ranges to find the specific date. • Refer to other reporting; i.e. proof report, system update, in order to determine what account to effect. • Post a one-sided journal entry to correct.

7. Financial Statements What to do if the Financials are out of balance: Ask and answer three questions • Is the Trial Balance in balance? • Do the Assets = Liabilities + Equities? • Does the current year’s earnings on the balance sheet equal the net profit on the YTD income statement?

8. Financial Statements If answering “yes” to question 1 and “no” to questions 2 or 3, then there is a problem with the setup of the financial statement parameters. Run four reports to compare. • Trial Balance report • Income Statement • Balance Sheet • Parameter Review report

9. Financial Statements Three possibilities of an incorrect format: • An account is missing from the setup. • An account is listed more than once Every account should be included on the balance sheet and every income and expense account on the P&L. c) There is an incorrect sign (+/-) designation.

10. Financial Statements If Desperate: • Auto Generate another set of financial reports with a different starting line number to get in-balance reports and work with the new reports until the setup issues can be corrected on the original set.

11. Bank Reconciliation How to balance the bank statement with the StarBuilder Bank Reconciliation:

12. Bank Reconciliation • The statement date is the date assigned to reconciled records. The beginning balance is from the bank statement.

13. Bank Reconciliation • Mark the records to be reconciled. When done the Ending Balance should match the ending balance on the Bank statement.

14. Bank Reconciliation Reconcile to the current cash balance. Run the report on outstanding transactions.

15. Bank Reconciliation • Note the total of the outstanding transactions on the Reconciliation report. • Confirm the cash balance on the trial balance report. • The ending bank balance from the bank statement + total outstanding transactions = current cash balance.

16. Job Cost to GL How to reconcile the job cost expense and revenue to the GL account balances: • Run the Work In Progress-Full Questions report from the Job Cost Report Wizard. Choose “Job Totals Only” for the printing format and select YTD costs when given the option.

17. Job Cost to GL 2) Compare the totals at the end of the WIP report to job cost expense and revenue account balances on the trial balance report. Note: The YTD balances on the WIP report are balances through the current GL accounting period as represented in the GL Calendar Setup.

18. Job Cost to GL Sample WIP report

19. Job Cost to GL Sample trial balance report

20. Job Cost to GL What to do if the balances do not agree: • Run the GL Transaction Details Report (Reporting on Account No.) on the accounts in question and ensure every transaction lists a job number. If every transaction lists a job number then proceed to step 2.

21. Job Cost to GL 2) Run the GL Transaction Details Report (Reporting on Job), click on NEXT and uncheck the box, “Print Cost Code & Billing details.” This report is in summary by job. Compare this report to the WIP report for every job to isolate the ‘problem job.’ The transaction detail on the job can then be compared to the Job Cost Detail report.

22. AP to GL How to reconcile the AP ledger to the AP account balance in the GL: Review the Reconcile AP Balances screen.

23. AP to GL Print reports to confirm the balances on the Reconcile AP Balances screen. • Print a current trial balance to confirm the GL Accounts Payable Trade balance. • Print an AP open item or aging report with no restrictions on the date range to confirm the AP Current Balance.

24. AP to GL Scenario 1: If there is no variance in the Reconcile A/P screen: • Print the AP open item report (run by Format – Invoice Reconciliation) from 1/1901 through the current period. • Print the GL trial balance for the current period and compare the AP account balance to the ending balance on the AP report.

25. AP to GL • If the reports do not agree then there may be postings in future periods. Print the GL Transactions details report on all future periods for review. • If an invoice was paid in a period prior to it being issued, then a variance will show between the two reports. • Print the AP check register on the current period and look for check dates from another period.

26. AP to GL Scenario 2: If there is a variance in the Reconcile A/P screen: • Print the AP open item report (run by Format – Invoice Reconciliation) from 1/1901 through a prior period. • Print the GL trial balance for the same prior period and compare the AP account balance to the ending balance on the AP report until the out of balance period is discovered.

27. AP to GL Once the out-of-balance period is discovered: • GL Transaction Details Report (Reporting on Journal Print the ) for the one period, selecting Report on Specific Journals and check mark Payables. • Compare this to the AP open item report (run by Format – Invoice Reconciliation) from the period through the same period, checking Include Zero-Balance Invoices.

28. AP to GL

29. AP to GL

30. AP to GL In addition: • Print the GL Transaction Details Report (Reporting on Journal) for the one period, selecting Report on Specific Journals and check mark Cash Disbursements. • Compare this to the AP Check Register Report run on the one period.

31. AP to GL

32. AP to GL

33. AP to GL • Compare report totals and if the totals do not agree, reconcile the detail. Note: In AP, an invoice is recorded as a journal number 3 type transaction and cash disbursements as journal number 4.

34. AR to GL • AR to GL follows the same logic as AP to GL. The reports and table structure are essentially identical and the variances can be isolated and discovered in the same manner. Note: In AR, a sales invoice is recorded as a journal number 1 type transaction and cash receipts as journal number 2.

35. Payroll What reports can be used for reconciliation within the payroll module: • Process Payroll reports • Employee Tax Register • Labor History Detail Reporting • GL Transaction Details • GEMM Reporting

36. Payroll The Employee Tax Register is a summary of the balances for each employee.

37. Payroll The Pay Check report in the Labor History Detail Report is the detail per employee.

38. Payroll • Print the Job Labor report in the Labor History Detail Report and compare to the labor expense on any of the Job Cost reports. i.e. Job Cost Status, Job Cost Detail, or the Job Detail Transaction report. Note: The Job Detail Transaction report and the GL Transaction Details report pull from the same table source – GLDetail.

39. Contracts How to reconcile in the contracts modules: • Subcontracts • Prime Contracts • Purchase Orders

40. Contracts - Subcontracts Print the Balance Report, checking Print Supporting Detail and Include zero-balance invoices.

41. Contracts - Subcontracts Sample of the Balance Report

42. Contracts - Subcontracts • The detail of the invoices should agree with the balances on the Balance Report. • Print the AP Retainage report and compare to the Balance Report. • Print the Balance Report or Status Report by job and compare the total to the committed amount on the JC Wizard Buy Out report. If there are POs, that total would need to be accounted for as well.

43. Contracts – Prime Contracts Print the Balance Report, checking Print Supporting Detail and Include zero-balance invoices.

44. Contracts – Prime Contracts • The detail of the invoices should agree with the balances on the Balance Report. • Print the AR Retainage report and compare to the Balance Report. • Print the Balance Report or Status Report by job and compare to the contract and change order amount on the JC Status report.

45. Contracts – Prime Contracts • Print the Change Order Estimate Report and compare to the change orders within the Progress Billings module and the approved and pending change order amounts on the JC Wizard Project Estimate Review Report.

46. Equipment Costing • Print the Gross Profit Report from within the Equipment Costing Reports. • This report will list the total revenue and costs in summary that have been posted against a piece of equipment.

47. Equipment Costing Sample report

48. Equipment Costing • Print the GL Transaction Details Report (Reporting on Equipment) in order to see the cost detail that has been posted through GL, AP and Payroll against a piece of equipment. Note: The Labor Cost on the equipment report is operator time (Pay Types 50-52) as posted through the payroll module.

49. Equipment Costing • Print the GL Transaction Details Report (Reporting on Transaction), click on the NEXT button that is to the right and choose Equipment number for the Transaction ID Range. This report will show the detail that has been posted through the equipment module. Compare this to the Total Revenue on the Equipment reports.

50. Progress Billings • For reconciliation purposes, the Job Status Report from the Progress Billings module can be compared to the Job Cost Status in JC Report Wizard, the Prime Contract Status Report, the AR Retainage report and the AR Open Item report run by job; including zero balances.