One of my first blog posts was about how I use macros with QuickBooks reports exported from Excel. That post also contains a brief explanation of what a macro is, so please read it first if you are unfamiliar with macros.
In my work, I often find myself combining similar types of expenses provided by a client on a spreadsheet for a more condensed presentation on a financial statement or a tax return. The repetitive task of selecting cells, adding borders, and entering formulas became tiring with one particular financial statement I was working on, so I decided to write a macro that would perform those tasks automatically.
The video below shows me combining expenses from a spreadsheet twice. The first, in which I add borders to the cells and enter the sum formula manually, takes 57 seconds. In the second, I select the range of cells and use a shortcut key on my mouse to run the macro. With the help of the macro, I complete the spreadsheet in only 11 seconds.
There are benefits to using the macro besides just saving time. It reduces eye movement and mouse clicks, both of which seem to wear me out from using the computer over a period of time.
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.
Have you ever read a good article on the Internet, but when you wanted to go back and read it a few days or months later, you weren’t able to find it?
It used to happen to me quite often, but it has become less of a problem since I began using Google Reader to read RSS feeds.
When I like an article I’ve read in Google Reader, I either “share it” or “star it.” I typically share an item that I think others would be interested in. My shared items show up on the left sidebar of my blog, and they also show up in the reading list for those who follow me on Google Reader. Usually, I star an item to which I might need to refer again later but others may not find interesting. Unlike my shared items, my starred items are not publicly visible.
The search function in Google Reader is very helpful. Recently, someone asked me about the differences between the accounting programs QuickBooks and Peachtree. I remembered reading a great blog post comparing them a few months ago that I shared in Google Reader, so I searched my shared items for QuickBooks and Peachtree. The article I was searching for appeared in less than a second, which was a lot faster than things I might have done in the past, such as look through my web browsing history or do a search in Google for terms I may have remembered from the article.
Do you have any other ideas to share about managing the content you read to make it easy to find later? Please share a comment in the box below.
Many years ago, I was involved in an organization in which I and other volunteers received a weekly allowance to cover various expenses such as transportation and meals. In one of our meetings, the president of the organization announced that some of the volunteers had come to him and complained that the allowance wasn’t sufficient to cover the expenses (I was not one of them). He told us that he would consider increasing the amount of the allowance for those who would provide a detailed accounting of their expenses proving a need for a higher allowance.
At our next meeting about a month later, the president said that nobody had presented a list of expenses, so he assumed our allowances must have been sufficient.
I could see the embarrassment in the faces of the others in the room. With the task of keeping track of their expenses, those people probably paid more attention to what they were spending money on and realized that many of those expenses weren’t really necessary.
I’ve been to many seminars and seen TV shows about making a personal budget, and while they may offer some good money saving tips and show how to make a fancy budget spreadsheet, I’ve found many of them lacking in the emphasis of accountability. An ideal spending and saving plan doesn’t provide much of a benefit without the ability to compare actual expenses to budgeted amounts.
Without organized financial records in place, people are left wondering, “where did all the money go?” They might make a guess on how much they spent during the past year on clothing, gasoline, or food; but from personal experience, I know those estimates are usually way off. By the way, I don’t think having a year-end spending statement from a credit card company counts as being financially organized.
The best way to organize personal finances is to use a good computer program (I use QuickBooks, and here’s why) that puts together all of a person’s financial information, including bank, investment, loan, and credit card accounts. I began doing this a few years ago, and since then, I’ve been able to solve the riddle of “where all the money goes.”
Do you use the icon bar at the top of your QuickBooks screen? Do the icons represent the tasks you perform most often in QuickBooks? If you’re using the default icons, the answer is probably no.
I recently began changing the icons to my most frequently used functions. It makes a big difference in efficiency when the shortcuts are just a click away rather than three or four clicks away navigating the menus.
Before
Let’s do a quick analysis of a few icons on the icon bar above (in my case, it was the default, though yours may be different). Let’s say that I don’t use memorized transactions (MemTx) so that icon does me no good. The register icon (Reg) is a little inconvenient because after I click it a box appears asking me to select which account I want to use the register for. I don’t really have any use for the add services or payroll buttons (and if you’re worried that you won’t find those options after removing them, don’t worry – you can find them all in the menus).
Take a look at your icon bar. If you see icons that you have never used or will rarely use, it’s time to give it an extreme makeover.
After
Now here is an icon bar that I can really use! Let’s say that my business has two cash accounts that are used frequently, so I put two icons on the icon bar that link directly to the register for each cash account. As an accountant, I look at balance sheets and profit & loss statements daily, so I have those icons there, as well as the general journal to make adjustments. I think having a backup icon is good just as a reminder because most people don’t backup their QuickBooks files enough.
There are a lot of different ways to customize the icon bar:
Right click anywhere on the icon bar and then click on “customize icon bar.”
In the view menu you’ll also see “customize icon bar.”
Make a window appear that you want to have on the icon bar, then go to the view menu and click on “add [window] to icon bar.”
In the “customize icon bar” screen, you can change the icon’s picture to whatever you like. There’s even a funny picture of a pig (see below) among the icons that I’m trying to figure out a use for … maybe transfers to a savings account?
Do you have any ideas for using the icon bar? Please leave a comment below.
I know several people that make fun of Twitter much like they make fun of a teenager that ends up in the news for sending thousands of text messages per month racking up a huge phone bill. They certainly can make a good argument. A recent study concluded that more than 40 percent of tweets are “pointless babble.” If you look at the public timeline or trending topics on the Twitter webpage, you’ll see spam, self-promotion, and mundane tweets like “I’m having a ham sandwich for lunch.”
I agree that Twitter has its problems, but I believe that it does have a business application as a powerful educational and collaborative tool. As with any technology, any benefit you derive from Twitter depends on your use of it.
If you’re looking for meaningful information on Twitter, you have to follow the right people. If you’re a CPA or work in accounting, you could start with the Journal of Accountancy, the AICPA, and your state CPA society. You can find most accounting software vendors on Twitter sharing good information. There are hundreds of other CPAs and CPA firms that offer insight in the latest news, and many of them are among the brightest in the profession (I won’t list names here, but they’re not hard to find). Hey, even the IRS is on Twitter.
I’ve learned that there are many benefits to Twitter besides using it as a news feed. I have asked questions on Twitter and received helpful responses, sometimes within a few minutes. I’ve been able to get in contact with many of the leaders in the accounting profession who I wouldn’t have been able to meet otherwise. By joining the conversation, a handful of people have been able to get to know me (sort of – well, at least they know that I’m a tech-savvy accountant that shares good information, and that’s a good start).
About a year ago, when I began using Twitter, I asked myself, “why would a CPA want to use Twitter?” The answer, at least for the first few weeks, was unclear while I tried to figure the whole Twitter thing out, but now I understand why.
Today I was experimenting with Google Docs and discovered something really cool that I want to share. I created the chart below titled “Cash Contributed to Partnership” from a simple spreadsheet. The chart is not a static image. The data in the chart is linked to the spreadsheet stored in my Google Docs account. When I change the numbers on the spreadsheet, the chart in this blog post updates automatically.
(disclaimer – the data in the chart is entirely fictional and any similarity to a real-life situtation is purely coincidental – also, this chart may not show in RSS feed readers, so please view this post on my blog website)
This is how I created and published the chart:
1. I created a new spreadsheet in Google Docs, and entered the data shown below.
2. On the top menu, I clicked on insert, then gadget. In the finance category, I selected the “pile” chart that shows stacks of $100 bills of varying sizes based on the values in the spreadsheet.
3. The box you see below appeared. I selected the data range (which must include both the names and amounts) and entered a gadget title and chart header. Then, I clicked on the little arrow in the top right corner and clicked on Publish Gadget.
4. The box below then appeared with a script code that can be inserted into an HTML page. I copied the code below and pasted it into the top of this blog post.
I noticed that when I changed the values in the spreadsheet, it did not change the chart in my blog post immediately. Sometimes, it took a couple of minutes.
Do you have any questions or ideas to share? Please leave a comment below.
First, let me describe my background in regards to web design so you know where I’m coming from.
I built my first web page in the year 2000 using Yahoo Geocities’ WYSIWYG site builder. It was simple. I just typed out some text and dragged it where I wanted it, and put pictures where I wanted them on the page. It was fun and sparked an interest to learn more about web design.
Shortly after that, I took a web page design class in college. The course almost entirely focused on html code. I enjoyed the course and felt proud to finally be a sophisticated “hard coder” creating web pages with Notepad. I created several websites by just writing html code and CSS, including the website for my company, a Las Vegas CPA Firm.
However, I soon discovered that creating a website this way has its problems. For example, changing a navigation bar for all pages in the website required me to cut and paste new code to all of the twenty-or-so html documents that comprised each page of the website.
My eyes were opened to a better way of managing a website at the AICPA Technology Conference in June 2009. One of the speakers demonstrated how he used a content management system (CMS) to pull content from other sources on the Internet and automatically create html code. Another session of the conference was entirely about “blogging, podcasting, and social networking” which covered how a blog can benefit a professional. I left the conference with the resolve that I wanted to learn to use a CMS and become a blogger!
It was a long journey from June to November 2009, when I posted my first article on my blog. Why did it take so long? Consider that I did all of the following:
I carefully observed several other CPA bloggers and picked out things I liked and didn’t like from their blogs.
I researched several CMSs and blogging platforms, including Joomla, Drupal, Blogger, and WordPress. I finally settled on using self-hosted WordPress so I could have total control over my blog and customize it as much as I like.
I purchased my domain and web hosting after a few hours of researching several web hosting services.
I installed WordPress on my web server.
Among more than one thousand themes (or general designs) on the wordpress.org website, I had to choose my favorite. I then had to further customize my design so my blog wouldn’t look exactly like other blogs using the same theme.
I installed several plugins and widgets (programs for backup, security, and sections of my blog, such as the Twitter and Google Reader feeds on the right sidebar).
I opened an account with Google Analytics and configured it to work with my blog, which I have discovered to be a great resource to see statistics of the traffic to my site.
I also opened an account with Google Webmaster Tools to observe how Google indexes my blog.
I created an “about me” page and a disclaimer (every blog should have one!)
At this point, I started writing blog posts. Every blog post has to catch the reader’s attention, be informative, and be entertaining. I wouldn’t write anything I wouldn’t want to read myself! It’s not easy.
This might all sound overwhelming to someone who has never created a website. If you’re in this category, I have some good news. It doesn’t have to take months to start blogging. In fact, I created a simple blog using Blogger and finished it in about an hour. This blog (dustinwheelercpa.com) took me so long because I created it the hard way, and I’ve learned a lot in the process.
I previously wrote a blog post about how I have been using RSS feeds to stay current on the news. The majority of the feeds that I subscribe to are news and blogs, but there are a few that don’t fall under either of these categories that I have found to be useful.
Tweets of a few individuals. I follow about 200 people on Twitter and I can’t log in to Twitter enough times to keep up with every single tweet of the people I follow every day. However, there are those among the Twitter users I follow who share great information and I don’t want to miss any of their tweets. It’s easy to add a Twitter timeline to Google Reader (which I use to read RSS feeds). Copy the Twitter user’s URL (for example, http://twitter.com/whe97007), click on the “add a subscription” button and paste the URL in the box that appears.
Twitter searches for certain words and hashtags. Using Twitter Advanced Search, I created RSS feeds for tweets from Las Vegas, Nevada, that include the words CPA, accountant, tax, or QuickBooks. I have found a few interesting people to follow and some potential business opportunities. For example, a tweet saying “next year I think I’ll have to invest in a good tax accountant” caught my attention and I contacted that Twitter user. The only drawback to subscribing to these feeds is that I have to sift through a lot of unimportant tweets like “I just got my tax refund so now I can buy that HDTV!”
LinkedIn Network Updates. LinkedIn is a great site for professional networking, but I don’t visit it daily. I use RSS as a more efficient way to see what my connections are up to. Occasionally, I notice that one of my connections connects to someone I know, who I then connect to as well. You can get RSS feeds within the LinkedIn account settings.
Notifications for new downloads. For example, I subscribe to the RSS feed of the CPA Technology Advisor to know when a new podcast is available for download.
YouTube channels. In particular, I enjoy the YouTube channel for K2 Enterprises, which provides technology tips for CPAs. Thanks to RSS, I am alerted when a new video is available to view.
If you have any ideas for using RSS feeds, please share them by leaving a comment in the box below.
I have observed people using two methods of handling credit card accounts in QuickBooks:
Recording individual credit card charges in a liability account (set up in the chart of accounts as the Credit Card type). Payments decrease the liability balance.
Recording only the payments for the credit card and allocating the charges among many different expense categories (using splits in the write checks screen as shown below). No liability account exists for the credit card.
On rare occasions, I have seen other techniques, such as running the credit card expenses through accounts payable, but the two listed above are the most common.
I have asked those using method #2 why they chose to record credit card transactions that way, and the answer is usually that they were unaware of any other way of doing it or that they thought it would be easier than method #1. Some say, “Why should I enter every single credit card charge when I can account for all of the charges in one transaction?”
I concede that method #2 works okay when credit cards are paid in full every month, because each payment can be traced to the sum of the transactions on the statement. However, when cash suddenly becomes tight and the company can’t pay the credit card bill in full, you have to allocate, for example, $1,697.45 of credit card charges to a $200 payment. For each partial payment after that, you have to keep track of what transactions you’ve recorded so far and allocate a new set of expenses. This quickly becomes an accounting nightmare that you will experience every time you enter a payment!
There is a second problem with method #2. How is your CPA supposed to know what the year-end balance is on your credit card? It won’t show up in your QuickBooks file. You’ll have to give your CPA the credit card statements and help him or her classify the unrecorded transactions.
I strongly encourage method #1. It handles partial payments of credit card balances painlessly. It makes your QuickBooks file easier for your CPA to work with. Also, if you enter the transactions from your credit card receipts, you will always know how much you owe on your credit card by looking at the account in QuickBooks and you can reconcile the account to make sure all of the charges were processed correctly.
It might seem tedious to enter every credit card charge, but it will probably save you time later!
If you are a client (or a potential client) and want to improve your recording of credit card transactions, do not hesitate to contact me.
(Note: If you still have your heart set on recording the expenses from a credit card statement in one big split transaction in QuickBooks, my recommendation is to set up the credit card account in QuickBooks, as in method #1, and enter the big split transaction from the statement using the “Enter Credit Card Charges” window instead of the “Write Checks” window)
With one click of a mouse button, I’ve been able to help some clients be more efficient in their use of QuickBooks. It’s worth taking the time to look through the preferences in QuickBooks and adjust them to your liking. Below are my top five QuickBooks preferences that you should know about!
Turn off pop-up messages for products and services (under General – My Preferences) – This is new in QuickBooks 2010 R5. If you’re new to QuickBooks and want to know about other products and services you can use with it, you can leave this on. If you’re like me and you know all about the other services QuickBooks offers, check the box to avoid the pop-up windows.
Automatically recall information (under General – My Preferences) – This can be a big time saver for the information you enter regularly to one vendor for the same purpose like utilities. Type in “power company” and the account Utilities:Electricity comes up automatically. Be careful, though! If you enter bills or write checks to the same vendor for different types of expenses and you’re not paying attention when you enter them in, you could have many misclassified expenses.
Default date to use for new transactions (under General – My Preferences) – If you’re using QuickBooks in real-time, especially if you’re writing checks from QuickBooks, use today’s date as the default. If you’re entering many transactions after-the-fact, the last entered date as default is generally better.
Date warnings (under Accounting – Company Preferences) – So, is anybody besides me having trouble writing the year 2010 since we started the new year? Check this box, and QuickBooks will warn you that the transaction you just entered is more than X days in the past. QuickBooks will save you from writing a check dated 1/14/2009 when it should be 1/14/2010. However, if you’re entering several old transactions, such as doing a year’s worth of bookkeeping, you definitely want this turned off.
Desktop (under Desktop View – My Preferences) – Sometimes, when I open QuickBooks files from clients, dozens of windows open up automatically. This happens because you either have the “save when closing company” preference selected, and all of the reports and input windows you had open the last time you used QuickBooks open up again, or you’re using the “save current desktop” preference, and the same windows open up every time from the last time you selected this preference. If you select the “don’t save the desktop” feature, QuickBooks will open faster because it won’t generate any reports or windows when it starts up. (note to accountants – if you get a QuickBooks file with lots of windows open, go to Window on the top menu, and then click on Close All…yeah, I spent a couple of years clicking on dozens of X’s before I figured out that one)
On rare occasions, I have seen other techniques, such as running the credit card expenses through accounts payable, but the two listed above are the most common.
I have asked those using method #2 why they chose to record credit card transactions that way, and the answer is usually that they were unaware of any other way of doing it or that they thought it would be easier than method #1. Some say, “Why should I enter every single credit card charge when I can account for all of the charges in one transaction?”
I concede that method #2 works okay when credit cards are paid in full every month, because each payment can be traced to the sum of the transactions on the statement. However, when cash suddenly becomes tight and the company can’t pay the credit card bill in full, you have to allocate, for example, $1,697.45 of credit card charges to a $200 payment. For each partial payment after that, you have to keep track of what transactions you’ve recorded so far and allocate a new set of expenses. This quickly becomes an accounting nightmare that you will experience every time you enter a payment!
There is a second problem with method #2. How is your CPA supposed to know what the year-end balance is on your credit card? It won’t show up in your QuickBooks file. You’ll have to give your CPA the credit card statements and help him or her classify the unrecorded transactions.
I strongly encourage method #1. It handles partial payments of credit card balances painlessly. It makes your QuickBooks file easier for your CPA to work with. Also, if you enter the transactions from your credit card receipts, you will always know how much you owe on your credit card by looking at the account in QuickBooks and you can reconcile the account to make sure all of the charges were processed correctly.
It might seem tedious to enter every credit card charge, but it will probably save you time later!
If you are a client (or a potential client) and want to improve your recording of credit card transactions, do not hesitate to contact me.
(Note: If you still have your heart set on recording the expenses from a credit card statement in one big split transaction in QuickBooks, my recommendation is to set up the credit card account in QuickBooks, as in method #1, and enter the big split transaction from the statement using the “Enter Credit Card Charges” window instead of the “Write Checks” window)
