Data Entry

1. Data Entry

2. Key QBooks Data Entry Processes • Sharing Data between Red House & QB • Job Costing & Labor Burden • % of Completion Adjustment • Reconciling your accounts (we will spend majority of our time on Job Costing and POC)

3. 1. Sharing Data - Red House & QB • Export Red House client list to Qbooks prior to entering bills or payroll • RH sends Customer/Job list to QB • Only Customer Name, Customer #, Job # • QB attaches job costs to the imported RH client list • QB exports only job costs back to RH • Import Job cost in Red House prior to doing profitability analysis

4. Client List Integration File Export / Import Only the Customer Name, ID, Job Number are exported to QuickBooks

5. Export Client List from Red House Select Export from the Tools Menu in Red House Save file to your computer

6. Import Client List into QuickBooks * Detailed Instructions in Handouts

7. Job Cost Integration Red House Job Gross Profit Report QuickBooks only exports Job Costs (COGS) to Red House. All overhead expense data is maintained in QuickBooks QuickBooks

8. How to Export Job Cost data to Red House Run Memorized Report in QuickBooks Save report to excel from QuickBooks. Date range of report will determine which costs are imported to Red House

9. How to Import Job Costs into Red House Select Import from the Tools Menu Attach excel file you previously saved from QuickBooks and click Upload * Detailed Instructions in Handouts

10. 2. Job Costing Key Concepts • Why Job Cost? • Evaluate profitability of each job • Improve Estimating • Calculate Sales commission • Calculate HRS Bonuses • How to Job Cost • Link payroll, material, subcontractors to related jobs • Include Labor Burden

11. Quickbooks Job Cost Process

12. QuickBooks Job Cost Process

13. Record Collections in QuickBooks Select Customer/Job Enter Check Amounts * Detailed Instructions in Handouts

14. Record Job Costs Enter Vendor All COGS must have an ITEM & Customer:Job to integrate with Red House * Detailed Instructions in Handouts

15. Record Job Costs – Payroll All time coded to a job must also be coded to a ‘Service Item’. The ‘Payroll Item’ is used to determine the type of labor (direct, indirect, overtime, etc.) HRS pay is based on the # of hours on the timesheet * employee pay rate.

16. Payroll – Service Items

17. Payroll Items

18. Systems Integration with Job Costs Export Job Costs to Red House Review Job Profitability Reports (to be reviewed later in training)

19. Job Cost Process – Any Questions?

20. Main Points from Job Costing • G&A is only tracked in QuickBooks (not integrated with Red House) • Client Name and Job Number is entered once in Red House and exported to Qbooks • Job Costs are entered in Qbooks and exported to Red House for analysis • Collections are recorded in both Red House and QuickBooks

21. Labor Burden Considerations • Costs directly related to the employment of an HRS are included in labor burden • Payroll Taxes • Heath / Workers Compensation Insurance • Vacation and other benefits • Training and other non-billable time • Using a labor burden we can reflect these other costs of an HRS and apply them as job costs • Qbooks allocates overhead charges of an HRS’s employment to COGS – Labor Burden, and reduces Overhead by an equivalent amount

22. Calculating Labor Burden List HRS Hourly Rate * Labor Burden Worksheet available on Red House Library Amount of Expenses directly related to the HRS employment

23. Set Up of Labor Burden in QBooks • Only HRS employees are set up with a labor burden Make sure Labor Burden is included here

24. Detailed Accounting of Labor Burden • Each Direct Labor has corresponding Labor Burden charge 99.75 Direct Labor x 40% (Labor Burden %) 39.90 Labor Burden

25. P&L– Labor Burden • Labor Burden Labor Burden / Direct Labor = 40.8%

26. Labor Burden is adjusted out of G&A costs

27. Job Costing Summary • Accurate Job Costing takes effort • HRS timesheets must be maintained accurately • Vendor invoices need to be coded timely • Credit Card charges must be reconciled to jobs • Job profitability must be reviewed • Labor burden applied • Learn the Qbooks job costing process • Use the data to improve your business

28. 3. % of Completion (POC) POC adjustment Income Received reflects cash collections (this is the account we used to code cash deposits)

29. Why Adjust for POC • Cash collected prior to starting work and purchasing materials is not income, it is a liability to do work for a client. A month end adjustment is necessary to record customer payments as deposits when the payment are in excess of the % of work completed. • The % of completion adjustment is required to have meaningful monthly financial statements.

30. POC Simple Example • 1/1/04 – $10,000 job sold ($3,333 collected) • 1/30/04 – Job started ($3,333 collected) • 2/15/04 – Job Complete (final $3,333) • Total Job costs $5,000 Financial Impact • January Bank Activity $6,666 (3,333+3,333) • Feb Bank Activity -$1,667 (3,334-5,000) • Advance Payments are not Income, they are liabilities to do work.

31. Red House POC Report • This report should be used to identify adjustments to monthly revenue • % of completion = the ratio of actual cost / estimated costs. • Revenue = sales price * % of completion • Revenue is then compared to the cash collected • If revenue is less than the cash collected the report displays a negative adjustment to revenue and vise versa. • All jobs must have an outstanding balance to be included on this report

32. Percent of Completion Report Gallina Job Over Budget -500 Revised Total -9,224

33. Making the POC Entry • 1) Print the % Completion report from RedHouse • 2) Determine if you have over or under recorded revenue • Positive balances mean you have earned money not yet collected • Negative balances mean you have collected money that has not been earned • 3) Make end of month adjusting journal entry (see screen shots for example) • Date entry the last day of the month • Use the following accounts • 1300 · Costs in Excess of Billings – asset account reflects work completed but not collected • 2480 · Billings in Excess of Costs – liability account reflects cash collections on work which has not been completed • 3200 · % of Complete Earnings – Revenue Account

34. Making the POC Make Journal Entry % Completion Report Analysis If Report Over/Under Total is Positive then The entry is Increase Revenue (credit) Increase Assets for unpaid completed work (debit) Debit Credit 3200 · % of Complete Earnings $ ### 1300 · Costs in Excess of Billings $ ### If Report Over/Under Total is Negative then The entry is reduce Revenue (debit) Increase Liability for uncompleted work (credit) Debit Credit 3200 · % of Complete Earnings $ ### 2480 · Billings in Excess of Costs $ ###

35. Making the POC Entry • 4) Make reversing entry • Reversing entry removes the prior month accrual. Make the reversing journal entry dated the 1st of the next month noting that the account entered previously as a debit now becomes a credit and credits become debits. Reversing Entry dated day 1 of next month If Report Over/Under Total is Positive then Reversing Entry is made with the same amounts used at the end of last month Debit Credit 3200 · % of Complete Earnings $ ### 1300 · Costs in Excess of Billings $ ### If Report Over/Under Total is Negative then Reversing Entry is made with the same amounts used at the end of last month Debit Credit 3200 · % of Complete Earnings $ ### 2480 · Billings in Excess of Costs $ ###

36. POC Considerations • The Red House report Percent of Completion is only a guide to make your adjustment. • The report is only as good as your original estimates. • If jobs are running over budget or there is a high risk that they will not be completed within budget you need to manually adjust the calculations and totals on the report. • Be very careful making adjustments that increase your current month’s revenue (be conservative in your evaluation of job status.)

37. 4. Reconcile your Accounts • Basic Bookkeeping (on your own) • Bank Statements (have all your outflows/inflows been recorded) • Credit Card Statements (have all your expenses been recorded) • Vendor Statements • Red House Integration (Case to help out) • Cash Collections in Red House vs Deposits recorded in QuickBooks • Job Cost in QuickBooks vs Job Cost in Red House

38. Make sure Qbooks is Complete • Bank Reconciliations • Have there been deposits made but not recorded • Payments sent but not recorded • Bank Fees / Processing Charges entered • Accurate Cash Balance maintained • Vendor & Credit Card Statements • Verify that all charges have been recorded • Liability balances should be accurate • Waiting to record charges when paid can create misleading financial statements

39. Collections Reconciliation • Collections are entered in both Red House and QuickBooks • Collections in Red House allow us to maintain accurate Accounts Receivable balances • Collections in QuickBooks are required to accurately manage our cash balances • Monthly collections in Red House can be viewed in the Collections Report (Sales Section)

40. Qbooks / Red House Reconciliation Amounts should agree. If they don’t you need to run the detail of each total and determine what is causing the difference.

41. Job Cost Reconciliation • All Job Costs are entered in Qbooks Only • Job Costs should be identical in both Red House and QuickBooks • Conditions that would prevent job costs from being equal • COGS attached to non- Red House Job Numbers • COGS entered as Expenses, not Items in Qbooks • Edits to Qbooks data never reimported back to Red House • Job Costs Deleted from Red House but never reimported

42. Job Cost Reconciliation Red House Job Gross Profit Report QuickBooksJob Profitability Summary

43. Keeping Accurate Data • Are Job Costing procedures implemented • Reconcile Bank Statements, Credit Cards, Vendor Statements, Loans • Regular evaluation of Actuals • Budget vs Actual Analysis