Blog

Best In Edmonton

May 5, 2020/in Accounting /by David Lord

It’s not every day you get some recognition for being good at what you do. I’m please to say, I was deemed a Best in Edmonton candidate this year!

Conversion from Abacus to Quickbooks Desktop

October 12, 2017/in Accounting /by David Lord

I worked recently with a client, Smith Electric, to help them upgrade their accounting system from a legacy system called Abacus to Quickbooks Desktop. We chose to go with QB Desktop because they managed a significant number of inventory parts, and Quickbooks Online just isn’t ready to manage large item lists yet.

As part of the engagement, I agreed to implement Quickbooks Desktop, set up their chart of accounts, input opening balances and import their client, supplier and item lists. Simple enough, right…? The project was more time consuming than I anticipated.

Their legacy system, Abacus, uses flat files. For those that are unfamiliar with accounting systems, older legacy systems use essentially a series of independent files that are tables containing information as opposed to a relational database. Think of them as a series of excel files that the software knows how to find and update. These files can be opened in Notepad or Excel and the data can be extracted. Unfortunately however, there is no roadmap and so you have to open a LOT of files to look at the data they contain, identify if it’s the current data (as opposed to a stale dated backup) and then figure out how each file meshes with the others.

For example, in this case there was a file that contained their part numbers, and matched each internal part number with several different supplier numbers for the same part. There was a different file for the part descriptions, which in turn was in a different file from pricing data. And since there were multiple part numbers for the same part, the many to one and many to many relationships made matching the data a bit more complicated.

Once I was able to find the different data sets, I was able to merge them together using Microsoft Access into working tables that could be imported into Quickbooks. Initially I used the multiple item editing feature built into QB, but realized quickly that this tool doesn’t handle large imports well. The hangups and crashes forced me to shift gears. After some frustration, I remembered that you can import the lists as .IIF files and was able to do batch imports of the customer, supplier and item list.

I set up the chart of accounts by hand as it wasn’t that complicated and handbombed their last year-end closing balances as the new year opening balances. Since this project got underway about two months into their fiscal year, I was able to pull a P&L and Balance sheet from their legacy accounting system and, with one big journal entry, was able to enter their operating activity for the first two months in one shot. This allowed the client to avoid having to manually enter everything. The downside of course, is a complete lack of detail for the first two months of this fiscal year.

Working with Smith Electric did highlight a few things that QB doesn’t do well.

Customizing dropdown menus in forms – In this case, the client has all their customers coded using a 4 letter code that is loosely modeled after the customer name. In invoices, you can only have the customer ID visible in the customer selection dropdown. So if you don’t know the customer ID off by heart, you have to actually search for it by opening up the customer center. It would be a pretty obvious upgrade to allow the end user to choose which columns to display in these drop down menus. The fix in this case is to update the customer ID field to include also the name of the customer following the 4 letter code. But this then pollutes the data field with extra characters making future imports/exports for things like mail merges complicated.
Multiple part numbers for the same part – In this case, Smith Electric has their own part number for each part they have in inventory, and then they might order a part from up to 5 different suppliers. Each supplier has their own part number also. In Abacus, they were able to input multiple part-numbers for the same item. It worked similar to an alias for your email. Quickbooks doesn’t have a feature like this however. And so the fix was to simply enter subparts to their internal part number whose ID’s matched the supplier part number. It made it convenient to be able to type in a part number on a sales order or invoice, but it really balloons the item list. It also complicates inventory tracking as you end up with subitems with negative inventory counts and parent items with positive ones or vice versa. Not an ideal situation. Allowing some sort of part # aliasing would help solve this.
Bill numbers – It never occurred to me that you don’t actually have to enter a bill number when posting payables, and you can’t make this a mandatory field in the preferences. In the case of Smith Electric, they ended up entering a number of bills without the bill reference number and it made the first month of payables difficult. They now have to remember to always enter this information. Since some fields are mandatory fields, it stands to reason this is something that can be turned on or off with relative ease. Why not give the end user the ability to customize additional mandatory fields?
Supplier credits – Smith Electric buys very specialized parts and often returns them for credit. This is a routine thing and they end up getting lots of supplier credits. In Abacus, a supplier credit shows up in the payables listing as simply a negative value bill. And so the payables process enabled the end user to simply click of the bills to be paid alongside the credits to apply and generate a cheque for the net amount owing. In Quickbooks however, you have to manually click into the discounts/credits area, check of the credit to use, apply it to an invoice, and go through these steps one invoice and one credit at a time. It’s very cumbersome when you’re working with any volume of supplier credits. I’ve never had a reason to question this workflow before, but moving these credit transactions directly into the payables list as negative values and allowing the end user to check them off as they do the bills seems like an obvious way for this workflow to function.

Following the implementation, there were a few issues that Smith Electric needed assistance with.

Bank Recs – Without only a lump sum entry for operating activity into the bank, the oustanding cheques from prior months weren’t broken out as separate items. This made the bank rec difficult so we set up a ‘suspense’ bank to post any reconciliation discrepancies to. Ideally, this balance will trend to zero over the next couple months. Not ideal from an internal control perspective, but since Smith Electric is a privately owned business, the risk and immateriality of the dollar value of the discrepancy made this the simple, efficient way to handle this.
GST – The GST return was polluted by the customer opening balances. In order to give the client some level of detail for their AR, these balances were entered as simply one invoice per customer for the total outstanding as at month-end. Even though these invoices were entered with an exempt GST code, they were picked up in the GST sales reporting numbers. We had to manually remove these from the sales number to be reported on the first GST filing after implementation.

Overall, the implementation of Quickbooks Desktop was a success. Smith Electric now has an accounting system it can count on going forward. Ideally, they will move into QB Online once it is robust enough to accommodate their needs. As with any system upgrade, there will be a learning curve. Things work differently, and some features were lost in exchange for some features gained. But the stability of Quickbooks in comparison to Abacus, along with the plethora of training resources and the widespread familiarity among bookkeepers and accountants will make the accounting process for Smith Electric much better going forward.

Have You Ever Heard of Jobber?

January 30, 2016/in Accounting /by David Lord

I’m pleased to announce I’m now a Certified Jobber Advisor! What is Jobber, you ask?

David Lord, a Jobber Certified Advisor

Jobber is an online, cloud-based software tool that makes life better for field services businesses. If you’re the kind of business that sends people out into the field to do work on a customer job-site, and your jobs range from a couple hours to a week in length, Jobber is for you.

Jobber is very well suited for residential cleaning, snow removal, landscaping/lawncare, and trades like plumbers or electricians, just to name a few. If you want a tool that helps you with preparing estimates, workorders, scheduling and invoicing then you have to check out Jobber. It’s a complete replacement for the old-school carbon copy LEM’s that have been a staple in field services businesses for the past 30 years.

The feature-set is huge and way too much to cover here. Check out the Jobber Website website for more info. If you want a hands on demo or some assistance in implementing Jobber, contact me and I’d be happy to assist.

Launched!

December 10, 2015/in Announcements /by David Lord

After years of using www.davidlord.ca for email, I’ve finally launched a site at this domain. I hope to use this blog as a platform for publishing tips & tricks about the things that have helped me in my day to day roles.

It will likely include a lot about the software and tools that I’ve used to create efficiencies, or that have helped me solve problems in business. From time to time I may wax philosophical on life.

Regardless, I hope you get some value from what I write.

Tips & Tricks

Best In Edmonton

Conversion from Abacus to Quickbooks Desktop

Have You Ever Heard of Jobber?

Launched!

Contact Details

Office Address

Mailing Address