There are two books on my office shelf that mean a great deal to me, but I never read them. Sure, I might flip through the pages once in a while, but I don’t use them for reference. They are horribly outdated.
One is titled Accountants’ Handbook, published in 1956. The other is Principles of Accounting – Advanced, published in July 1955. My Grandpa signed the inside cover of the latter with the date 8/21/1957.
Inside the books are several bookmarks. Grandpa used none other than ten-key printouts, something that has gone the way of the dinosaurs these days. I recently gave away my ten-key to another employee since I never used it. Whenever I have to add up numbers, I use Excel.
The books also contain a number of examples of how to complete preprinted forms such as a materials requisition and a cash disbursement journal, which in my world have become antiques. The last time I had to deal with these paper forms was in my Accounting class in high school, since it’s all done through computer software these days.
The books’ antiquity is interesting, but their sentimental value is that they were Grandpa’s. He never taught me anything about Accounting that I can recall, but he defined what an accountant was in my eyes at a young age. When I was in elementary school barely learning basic math, Grandpa would challenge me with something more difficult. His favorite trick would be to ask me an addition or subtraction problem that would go beyond the century mark, for example, 96 plus 7.
As I got older, the questions got tougher. I remember one in particular:
Three friends eat in a restaurant, and the bill is $25. They pay $10 each, and get $5 back in change. Since they can’t split the $5 three ways, each of them keep $1 and give the waiter a $2 tip. So, each of the men paid $9 (10-1), a total of $27. The waiter kept $2. 27 + 2 = 29. From the $30 they paid, where did the other dollar go?
Grandpa laughed as I thought this one out loud, as if he knew the answer and wasn’t going to give it to me.
Impressed with my Grandpa’s number-crunching abilities, I thought that accountants must be really smart. I also admired his kindness, patience, and constant encouragement. There are many reasons why I chose Accounting for my career, and my Grandpa’s influence is one of them.
Grandpa passed away many years ago, but I am reminded of him every day when I pass the two old books as I make my way to my desk.
Need a loan amortization schedule? There’s a template for that.
Creating a personal monthly budget? There’s a template for that.
Is one of your kids getting married and you’re planning out the expenses? There’s a template for that.
Okay, you get the point and you’re not amused with my not-funny “there’s an app for that” parody, so I’ll stop there.
Friends have sometimes asked me to help them create spreadsheets for all of the above purposes, perhaps unaware of the many templates that exist for Microsoft Excel. Sure, there are some complex customized spreadsheets you should ask a CPA for help with, but for common and simple stuff, you could use a template for free. There are tons of them.
To view a list of templates available in Excel, click on the Office Button on the upper-left corner of the Excel window, then click on New. You might only see a few under Installed Templates, but there are many more you can download from Microsoft Office Online (you’ll see a disclaimer from Microsoft saying that these templates were supplied by members of the Microsoft Office Online community, and Microsoft does not promise that the templates will work for your purposes or be free from viruses and defects).
The most common spreadsheet I am asked to produce is a loan amortization schedule. Excel has a template for this that I am particularly impressed with. All you have to do is enter values such as the loan amount and interest rate in the orange colored cells and the table magically appears. Change one of the values, and the table revises itself immediately. The template is also capable of handling extra principal payments, either regular or occasional, and adjusting for the total number of payments automatically.
If you only want to only use Excel and not any other accounting software, you can download templates for check registers, general ledgers, invoices, sales receipts, balance sheets, and income statements. Doing bookkeeping in Excel doesn’t sound like a lot of fun to me and I wouldn’t recommend this to anyone, but using these templates is certainly easier than trying to do it from scratch.
You’ll also find several templates that are helpful for tax purposes, such as a mileage log and business travel expense log. Use these templates to organize your information, and your tax preparer will be impressed.
What if you can’t find the template you want on Microsoft Office Online? You might be able to find it among the thousands of templates from Google Docs (Google’s free suite of online productivity software which includes a spreadsheet program similar to Excel). If you’d rather use Excel, you can create the spreadsheet in Google Docs with the template and then save it as an Excel file. I have done this with a few Google Docs templates, and they work perfectly in Excel.
The next time you need to create a spreadsheet, just remember, there might be a template for that.
You’re near the end of a long and busy day, reconciling your bank account in QuickBooks. You’ve checked off every deposit and check from the bank statement and thought you were done, but the bottom right corner of the reconciliation screen says “Difference: $20.00.”
You need to hurry to make it to a dinner appointment, so you click on the “reconcile now” button anyway, and the following box appears:
QuickBooks Reconciliation Discrepancy
You’ve cleared dozens of transactions totaling several thousand dollars, so a discrepancy of $20 is too small to be worth correcting, right? So you click on the “enter adjustment” button, QuickBooks automatically creates a $20 transaction to Reconciliation Discrepancies expense, and congratulations, you’re done reconciling!
Maybe you just missed recording a $20 bank charge in the books. In this case, the adjustment for the discrepancy is inconsequential. However, just because a discrepancy is small, it doesn’t mean it is not worth investigating. Below are a few examples of what could have caused the discrepancy that might make you glad you looked into it further:
You deposited $20 from a customer in your bank account, but forgot to record it in your books and missed it while reconciling. The customer gets upset when he/she is invoiced again and claims that the bill was paid, but you have no record of receiving the payment.
A bank error takes $80 out of your account, but the original check was for $60. In your haste of reconciling, you didn’t catch the difference in the amounts. If you discover the error, you’ll probably get $20 added back to your account (plus, you’ll score some points with the boss).
You missed an unrecorded $1,000 deposit from a customer and an unrecorded $980 fixed asset purchase. You might think that missing two large transactions that somehow happen to net out to a small amount would be very unlikely, but I have seen it happen many times!
You never know why reconciliation discrepancies exists. That’s why I cringe when I see them.
When you have a reconciliation discrepancy, you can’t blame the bank or QuickBooks. You can only blame yourself for making a mistake in the reconciliation process. You can blame your perfectionist accountant for telling you to start over and do it again, but you might thank your accountant for it later.
Do you feel in control of your e-mail, or do you feel your e-mail controls you?
This question was posed by the instructor in a session about Outlook that I attended at the 2009 AICPA Information Technology Conference. The large volume of e-mail that I have to process every day certainly makes it hard to control, but I learned a few good e-mail management strategies at the conference that have helped.
First, I am starting to use my inbox as a staging area rather than a storage container. I found that I was not the only one who had this problem. In the Outlook session, the instructor asked the attendees of the conference how many messages they had in their inboxes. The answers began low starting at ten, but grew quickly to 2,200! The inbox should be used much like how you use your mailbox at home where the post office delivers your mail. It would be silly for me to open my mailbox, read my letters, and then stuff them all back into my mailbox. So, why do I do this with my e-mail?
When I bring my mail in the house, I generally sort it before opening anything. The ads usually go straight to the trash, the bills accumulate on my desk until I’m ready to pay them all at once, and the magazines go on a table near the sofa. The same process of sorting and organizing should also be used for e-mail.
This process can be automated (to a certain extent) with what are called e-mail rules in Outlook. I have begun using rules to automatically move e-mail messages to specific folders based on criteria such as the sender’s name and subject. For example, I receive a large amount of e-mail from the AICPA like the CPA Insider newsletter and e-mails announcing what’s new for CPE. None of these are what I consider to be junk mail because I want to read them, but they are not time-sensitive and have less importance for getting my attention than say, an e-mail from a client. I established e-mail rules making anything from the AICPA automatically appear in a subfolder I created called “AICPA” rather than my e-mail inbox.
To create a rule in Outlook 2007, click on Mail in the navigation pane, then on the tools menu, click on Rules and Alerts. A box will appear similar to the one below. The box below shows my AICPA rule.
Outlook E-mail Rules
E-mail rules have especially helped me with my e-mail on my cell phone. My inbox on my cell phone syncs only with the inbox in Outlook and not the subfolders, so I am no longer interrupted by the new e-mail notification sound on my phone only to find that the AICPA is offering a new CPE course.
I am committing myself to having a clean e-mail inbox for 2010. How’s that for a new year’s resolution?
When I was fifteen, I played a computer game that simulates building and operating an amusement park. The object of the game is to make money by thrilling patrons on the rides, taking care of their basic needs (extra sugar in the sodas), and making them happy enough to empty their pockets on souvenirs.
The game begins with enough cash to build a park, but it is all debt financed.
The first time I played the game, I paid little attention to the financial side of it. I just wanted a really cool looking amusement park. I blew almost all of the cash building a Ferris wheel, an inner tube water ride, and a roller coaster, all of them customized to be as tall, long, and fast as the game permitted.
Then, expenses popped up that I wasn’t planning for. I quickly figured out that my patrons couldn’t find any restrooms. When I saw smoke coming out from some rides and kids were falling out of them, I had to hire mechanics. Oh, and I had to hire janitors to clean up after a few kids that must have ridden my extreme roller coaster one too many times.
Just when I thought I was making some money, my bank balance was mysteriously drained at the end of every month (a month in the game is about 20 minutes in real life). After this happened for a few months, I decided to investigate my cash flow problem by looking at my financial statements (imagine that, financial statements in a video game – sounds like real fun) and found that the disappearing cash had gone to pay interest on the debt.
Disappointed that my amusement park was headed to bankruptcy, I gave up on it and started a new one. This time, I immediately paid the debt down to a minimal amount that I needed to frugally build my park with cheap rides like bumper cars and a merry-go-round. I paid off the debt as soon as I was able to, and bought bigger rides when I had the cash to pay for them. Without the burden of interest, I was able to slowly build a bigger and more profitable park than I had the first time.
I’m grateful that I learned the importance of avoiding excessive debt and spending through a video game, rather than a real-life scenario.
Now, I could talk about when I played SimCity and hiked the tax rates, but I’ll save that for another blog post.
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.
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:
Combine the amounts from the debit and credit columns, show the amounts from the credit column as negative, and rename the resulting column “unadjusted.”
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.
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.
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!
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.
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.”
There are two books on my office shelf that mean a great deal to me, but I never read them. Sure, I might flip through the pages once in a while, but I don’t use them for reference. They are horribly outdated.
One is titled Accountants’ Handbook, published in 1956. The other is Principles of Accounting – Advanced, published in July 1955. My Grandpa signed the inside cover of the latter with the date 8/21/1957.
Inside the books are several bookmarks. Grandpa used none other than ten-key printouts, something that has gone the way of the dinosaurs these days. I recently gave away my ten-key to another employee since I never used it. Whenever I have to add up numbers, I use Excel.
The books also contain a number of examples of how to complete preprinted forms such as a materials requisition and a cash disbursement journal, which in my world have become antiques. The last time I had to deal with these paper forms was in my Accounting class in high school, since it’s all done through computer software these days.
The books’ antiquity is interesting, but their sentimental value is that they were Grandpa’s. He never taught me anything about Accounting that I can recall, but he defined what an accountant was in my eyes at a young age. When I was in elementary school barely learning basic math, Grandpa would challenge me with something more difficult. His favorite trick would be to ask me an addition or subtraction problem that would go beyond the century mark, for example, 96 plus 7.
As I got older, the questions got tougher. I remember one in particular:
Three friends eat in a restaurant, and the bill is $25. They pay $10 each, and get $5 back in change. Since they can’t split the $5 three ways, each of them keep $1 and give the waiter a $2 tip. So, each of the men paid $9 (10-1), a total of $27. The waiter kept $2. 27 + 2 = 29. From the $30 they paid, where did the other dollar go?
Grandpa laughed as I thought this one out loud, as if he knew the answer and wasn’t going to give it to me.
Impressed with my Grandpa’s number-crunching abilities, I thought that accountants must be really smart. I also admired his kindness, patience, and constant encouragement. There are many reasons why I chose Accounting for my career, and my Grandpa’s influence is one of them.
Grandpa passed away many years ago, but I am reminded of him every day when I pass the two old books as I make my way to my desk.
