Monday, August 24, 2015

My Improvised CRM (Customer Relation Management) Database

For years, I have been compiling lists of potential clients from various sources on excel spreadsheets.
Since I focus on contractors and architects, some lists are easy to get. Many small contractors and architects are Certified LBE (Local Business Enterprise) with the City of San Francisco. I can quickly download a long list from the city website. But for the other, I have to think up of the sources, and painstakingly compile one lead at a time. --Talking about time and cost, I wonder for a small local service provider like me, if it is cost effective to just buy lists from lists brokers.

With all these lists floating around, and with many mailers got returned, I was challenged on how to eliminate duplicates, inactivate invalid leads, and have better control over who to touch and when.

For a month I tested out a web based CRM software. Since my marketing department was only I and I did not have to share list or updates with a team, even at $49/month, the CRM was too pricey and had many features that I did not need.

A CRM I was looking for need to do just a few simple tasks:
- allow me to quickly import all the lists I have
- allow me to inactive leads that are no longer valid
- allow me to categorize where the leads come from; say LBE-architect, LBE-contractor, Google, etc.
- allow me to write some notes about this lead
- allow me to find a particular lead quickly and its related info
- allow me to sort the list by source, and show only active leads
- allow me to export the leads to excel and generate mailing labels quickly.

I have been a Quickbooks Proadvisor for years. I revisited the idea of using Quickbooks for lead database. It turned out to fully satisfied all the criteria.

I use "Customer Type" to define sources; i.e. LBE-Architect, LBE-Contractor, Google map, Castro-merchant, etc.

Each customer record allows me to add  up to 7 user defined fields. I use them to filter the lists. I have these fields setup:
- sales (annual sale figure) - show how big the lead is
- year established - show how matured the company is.
-  number of employees - denote size of the company. 2 to 16 is my ideal size.
- legal entity - sole prop tends to be small / loose. Corporate and LLC are more formal/ established.
- worker comp - Yes or No. Any established contractors should have worker comp coverage
- remarks - such as specialty, owner info.

And "note" button is another place where I can date-stamp info, and type very long note. I can keep adding new note above the old notes.

To import the list onto Quickbooks, I go to List | Add/Edit Multiple List.
Select: Customer
Click "Customize Column" button where I can add/remove columns, and move up or down columns to correspond to columns setup on Excel.
There is a link to a video tutorial here. The video is 6 minutes long, which shows how it is done. Very useful.

I even added "Inactive" column to the Add/Edit Mulitple List while importing various lists. Then I  copied and pasted all the info from excel to this window. I manually checked the "inactive" field to put a check mark there.

Next blog, I will write how to filter results and create mailing lists.
Everything takes time. But, with this database, I finally feel like in control.

Monday, August 17, 2015

Quickbooks Keyboard Short-Cuts

I love keyboard short-cuts. They are so useful and are easy on hands.

+ to increase date by 1.
- to reduce date by 1.
t for today.
y for beginning of the year (Normally 01/01/yy, though it doesn't bring up the right date sometimes.)
mmdd followed by tab will put in the date in mm/dd/yy format.

Ctrl+Delete to delete a line (useful on creating invoice or splitting expenses)
Ctrl+Insert to insert a line (ditto)
Ctrl+D to delete the whole entry.

Ctrl+C to copy (good for copying and pasting long text)
Ctrl+V to paste (ditto)

Edit and then select duplicate (useful to duplicate a similar invoice or estimate to bill to various parties or create several versions with mostly same info.)

Ctrl+M to memorize entry I enter regularly.
Ctrl+T to bring up memorized transactions.

I like to memorize reports I pull regularly.
I normally create a group of reports named "01 Monthly Review" where I save Balance Sheet, Profit and Loss, and General Ledger and other useful reports.
Each month when I change the period, I re-memorize them hence overriding the old dates.

Wednesday, April 16, 2014

The Costs and Benefits of Hiring a Bookkeeper

Many small business owners think that it is cheaper to hire part-time clerical staff than a professional-bookkeeper. Is it so? Let's work the math.

To hire and retain a part-time staff, you probably have to pay $20/hour and offer 20 hours/week, which provides the worker some sort of living wage. That is $400/week. Times 4 weeks per month, that is $1600. What kind of quality you get? Clerical? And do you need all those hours? Will there be a lot of idle time?

Compared this to hiring a professional bookkeeper with an accounting degree who has solid knowledge of accounting principles and years of working experience. Add in the skill, hence speed in commanding a software such as Quickbooks which allow them to work efficiently. The bookkeeper works much fewer hours and on as-needed basis. For a small company with up to 10 employees, one day/week is probably suffice. For a smaller company, sometimes twice a month will work. At $45/hour, 8 hours/day, you pay $360/week. Times 2 days/month, that is $720/month. If 4 times a month, that is $1440. Isn't it still cheaper? And you can count on the quality of work.

What you don't know can cost you.

I have seen a high-end real estate broker who managed multi-unit apartment buildings for his mother advanced a lot of money to pay her bills. When she repaid the loan of $100,000, the office manager posted it to income. If this mistake was not caught, what would the taxes be? If Federal and State income taxes combined are 30% -- which is low for a high income earner, that simple mistake will cost him $30,000. If the bookkeeper is paid $1500/month, her whole year of compensation is only $18,000.

I have seen an architecture firm made the project manager do the monthly invoicing. Since the project manager is an architect and his expertise was on architecture/design, he was not accustomed to bookkeeping tasks which are numbers and detail-oriented, he mistyped one of the items lesser by $25,000 and  another lesser by $50,000. There were $75,000 loss of income. Compare this to compensation paid to a bookkeeper who works one day a week at $45/hour. The monthly bookkeeping fee was only $1440.

Professional bookkeepers are more than cost factor. They provide benefits. Hence deserved adequate compensation. They cover one of the three legs of a business; i.e. operation, marketing and finance. They get regular tasks done. And provide timely and accurate reports. Not all reports are made the same. Erroneous reports not only are not useful, they are misleading. Timely and accurate reports show the business owners where they stand, how they performed. Good reports allow them to make informed strategic decisions.

Monday, March 31, 2014

Bookkeeping Presentation at SFPUC Contractor Assistance Center

On 3/20/14, thanks to the sponsorship of Merriwether and Williams Insurance Service, Wayne Lee and I have the opportunity do do a presentation at SFPUC, Contractor Assistance Center on Bookkeeping and Quickbooks. Ben Poole and Price Hallowell of the center have been most helpful in providing technical assistance with the equipment set up. Jennifer Elmore of Merriwether was most kind in chauffeuring us around and provided sandwiches to nourish the mind and bodies of the attendees.

It was a great venue; great conference room with lots of space, light, nice furniture and huge mixing area. We have about 15 attendees. We thought of it a success. The attendees were enthusiastic about the subject matters. We tried hard to share some fundamental principles about money, cash flow vs profit, and financial reports. We hope that these principles will come in handy in the future.

At the first half, we discussed all money related issues; such as why care about financial matters? For it is one of the 3 major areas besides operations and marketing that all business owners need to cover. Why cash flow is different than profit; for you likely have to front all job costs -- labor, material, equipment for 2 months before you get paid. Hence each new job or expansion demands more credits, and you must establish as much credits as you could before you need them. We talked about the 2 ways of financing; i.e. debt vs equity. And how to get a good FICO score; i.e. by demonstrating your willingness to pay and your ability to pay. We covered the 3 major financial reports; Balance Sheet, Profit and Loss and Cash Flow Statement. We dissect components that made up those reports. We tried to explain them in the simplest language.

In the second half, we covered tips and tricks on Quickbooks. Among the attendees, only 1 uses American Contractor which costs $10,000 and 5 uses Quickbooks which costs $250. No wonder majority stays with Quickbooks for the price difference is so big.

Since Quickbooks is form-driven, if you use A/Receivable invoice and A/Payable bill features, Quickbooks will give you many more reports; especially job related ones. We covered items set up. various jobs reports such as Job Profitability Summary, and detailed report on each job, such as Job Profitability Report. Since labor and burden make up a big part of job costs, we emphasized that to effectively allocate labor and burden to jobs, payroll must be integrated within Quickbooks; i.e. by using Intuit Payroll Services. With all payroll burden set up on payroll items list, the allocation will be auto pilot thereafter.

We explained how we work with our clients; mostly on-site as their in-house accountants on a once-a-week or twice-a-month basis. We also support their in-house staff if they already have their accounting personnel.

Friday, March 28, 2014

The Price of Cheap Bookkeeping

I recently worked with a new client who is an interior designer. Of course, the book was not good as expected.

I found that in 2010, there was  $20,000 sitting in a bank account named "uncancellable." Obviously, there is no such bank, hence no such deposit. Her cash balance was inflated by that amount. By tracing the entries, I realized that the deposit was posted and incorrectly applied to various invoices issued in May and September 2010. This was not true --  her client always paid right then and there. Her invoices were due-on-receipt. The deposit entry has been reconciled to the bank statement. The former bookkeeper realized the mistake, and posted another payment to apply to the correct invoice issued in August. And she moved this payment into this "uncancellable" bank account with memo "already reconciled. can not delete."

The result; the income was counted twice, which cost my client $6000+ for Federal and California taxes.

Curious, after quick glance at 2010 tax return, I pulled out 2011 tax returns. And the sales and net income were $50,000 more than book. Minutes later, I realized the tax returns were filed based on accrual instead of cash basis for there was $50,000 sitting in Accounts Receivable. Worse yet, these A/R invoices would never be collected, for there was procedural error. Her clients were presented with invoices showing 100% of material and service costs, and were asked to pay 50% upfront while placing order for the materials. The remaining 50% was billed again on later invoices, and were paid promptly when presented. So the first invoices with remaining balance would never be paid.

We were in time to amend 2010 and 2011 tax returns and reduced her Federal and California taxes by $6000 in year 2010 and $20,000 in year 2011.

That was the price of $25/hour bookkeeping.

To find a good bookkeeper, business owners should definitely ask for a professional with an accounting degree. The knowledge of accounting principles is much more important than Quickbooks, for one can learn software much faster.

Business owners do not have to stand behind their accountants/bookkeepers to micro-manage. But, when each month ended, it is sensible to ask for monthly reports such as Balance Sheet and Profit and Loss. And if the business owners do not know what those numbers mean, have the bookkeeper to review with. Since the business owner runs the business and have "reality check", if the numbers are way off, they should be able to tell.

Wednesday, September 14, 2011

Quickbook - Bank Reconciliation - 4 places to look for errors

It is quite easy to spot where errors come from if you can't reconcile:

Look at these 4 numbers at the bottom left of the reconciliation window and compare to your bank statement to zero-in on where error(s) come from:
- beginning balance
- ending balance
- total deposits
- total withdrawals

If you found that error(s) is on total withdrawals side, you just need to focus on finding the error(s) there.

Tuesday, September 13, 2011

Quickbooks - workflow

Before you can reconcile to bank statement; you should do these first:

First, take care of A/R (accounts receivable).
In this order:
Enter invoice - process payment - move deposit into bank a/c.
(These deposits should agree with amounts as stated on bank statements.)

Second, take care of A/P (accounts payable).
In this order:
Enter bill - pay bill

Third, when reconciling to bank statement.
If you see other deposits and debit charges on bank statement, post the entries to the check register.

If you have problem reconciling; look at these 4 numbers to find where the error(s) are:
beginning balance, total deposit, total withdrawals, and ending balance.