Archive for November, 2009

One Idea for Learning QuickBooks Better

Sometimes, business owners, accountants, and bookkeepers have asked me the simple question, “How can I learn QuickBooks better?”  A satisfactory answer to their simple question can be complicated, however.  My response is that it depends on how the person best learns.  I suggest that attending training seminars might work best for some.  For others, reading the manual could be helpful.  The follow-up question I am asked is usually, “well, how did you learn it?” My interaction with QuickBooks early in my accounting career (which consists almost entirely of working in accounting firms) was primarily in printing simple reports from client files to use as workpapers for preparing tax returns.  When clients started asking “how do you do this and that” in QuickBooks, I realized that I could use more practical experience myself, so a few years ago I purchased QuickBooks Premier Accountant’s Edition to use at home as my own personal finance software. Some might consider this a crazy idea.  There are certainly less expensive software packages out there that are better designed for personal finance rather than running a business (Quicken, for example).  However, I accomplished my personal objective of learning QuickBooks better.  With my frequent use of QuickBooks at home, entering transactions and reconciling accounts became second nature for me.  Although I don’t regularly use some business functions like inventory and sales tax with my personal file, I had the opportunity to experiment with them at home. So, that is how I learned to use QuickBooks, and I’m passing the advice along to any newly hired bookkeeper or accountant who also wants to become more comfortable with QuickBooks.

Using Excel Macros to Manipulate QuickBooks Reports

Since I began working in accounting, I have observed some fellow accountants exporting data from accounting software programs to Excel and then performing repetitive tasks of reformatting the spreadsheet (moving data to other cells, entering calculated fields, changing fonts, resizing columns, etc).  This has provided me with the opportunity to use and share a valuable skill I learned in one of my classes in college so that these tasks can be completed automatically with the push of a button. One of the powerful features of Excel is Visual Basic for Applications (VBA), a programming language for creating macros.  A macro is a set of instructions for Excel to complete automatically that would otherwise be performed manually by a person. There are countless ways that you could use macros in Excel.  In fact, they can do about anything you would normally do except actually “think” for you.  Completing the tasks I described above of reformatting and manipulating data is just one simple example of what macros are capable of. The video below is a demonstration of a macro I wrote that I call “the trial balance formatter.”  It is designed to take an Excel export of a trial balance report from QuickBooks and automatically make the following changes:
  1. Combine the amounts from the debit and credit columns, show the amounts from the credit column as negative, and rename the resulting column “unadjusted.”
  2. Add four columns to the right of the unadjusted column for adjustments.  Columns C and E are for the amount of the adjustments and have a width of 12.  Columns D and F are for letters identifying the adjustments and have a width of 2.
  3. Add a column to the right of the adjustments titled “adjusted.”  Formulas adding the amounts in the unadjusted and adjustments columns are automatically entered into the cells in this column.
  4. Change the bolded account names and column headings to regular font.
Note: The data contained in this video is completely fictional.  You may want to make the video appear in full-screen mode to better read the text.  If you can’t see the video below, click here. Accountants: If you’re looking to add to your Excel skills, I recommend learning how to write macros.  I don’t write macros very often, but my coworkers and I use macros I’ve written every day. Clients/Potential Clients: If a macro could help you save time by automating a repetitive task in Excel, please let me know!

RSS has Changed How I Use the Internet

Over the past few months, I’ve been much more efficient in reading news, blogs, and other websites that interest me.  I now read twice the content in half the time!  In this blog post, I’m going to give my secret away.RSS

You might have noticed a little orange button on some websites like the one on the right.  I had seen them for a long time, and always wondered what they were.  It is a logo for RSS (“Really Simple Syndication”) which is a web feed format used by many websites that publish updated content (most commonly for news and blogs). Unless you’re already familiar with RSS, that last sentence probably didn’t make any sense.  Keep reading.  It will.  I didn’t really understand it until someone showed me how a “feed reader” works.  That’s where I’ll go next, but first, a little background: Before I figured out how RSS works, I used to go to each of my favorite websites periodically to see what’s new.  This process consumed a lot of time.  First, I had to find the website in my favorites list in my web browser (and if I didn’t save it as a favorite, I had to do try to remember the web address or search for it in Google).  Then, I had to look for the most recent content, read the title (and maybe the first few sentences of the article) and test my memory to see if I had read it before.  This might not sound like too much work, but when you do this with ten to twenty websites, the time really adds up. Some websites offer e-mail updates for new content.  This became annoying as I started getting my e-mail on my phone.  I got tired of being interrupted by the new e-mail notification sound only to find out that the e-mail was reminding me to read a new article at some website. A feed reader (also referred to as a feed aggregator) brings all of the new content from these websites into one place, like your own personal newspaper.  It also lets you know which articles you have or haven’t read.  It frees up your e-mail as strictly a device for communication.  Another benefit of using a feed reader is that you won’t see nearly as many advertisements, if any, compared to visiting each individual website. There are many feed readers out there, and most of them are free services.  My feed reader of choice happens to be Google Reader.  If you’re still a little confused about how feed readers work, watch this video about Google Reader in plain English. With the help of Google Reader, I’m able to keep up with important publications in the profession such as the Journal of Accountancy, read the local newspapers, and enjoy posts from other blogging accountants.  I can also “share” items that I like and want other people to read.  On the right sidebar of my blog, you’ll see a list of items that I have shared from Google Reader. One last thing … and it might be self-promotional, but I wanted to point it out in case you missed it.  On the top right corner of my page, you’ll see a little orange button and the words, “Subscribe to my Blog.”