This fall, I am taking a Visual Basic class at UNLV as one of the electives for my Masters Degree in Accounting. Projects for the class require application of the concepts learned, but allow for creativity because each student can create whatever design or function for the program he or she desires.
My latest project in this class is a form that estimates an amount of tax based on income, itemized deductions and dependents. It then returns a message to the user in a text box explaining how much tax, if any, is owed. I know the tax code is much more complicated, but for this project, I used a flat 35% tax rate and $3,000 exemption per dependent. The project required simple calculations, using IF statements, and returning a message to the user, so there was no need to go into unnecessary detail.
The first step I took was to create the form, text boxes, and button to run the code. I then wrote the code so that when the “estimate my tax” button is clicked, it calculates the tax, enters the amounts in the text boxes, and returns a message as shown below:
The code to generate the generate the message in the text box is:
If Tax > 0 Then
Message = Name & “, get out your checkbook. You owe “ & Tax & “. “Else
Message = Name & “, you don’t owe any tax. “End If
The project works, but it’s a little boring, isn’t it? I decided to have a little bit of fun with the project by adding an additional IF statement:
If Income > 250000 Then
Message = Message & “Wow! You’re making more than $250,000. You’d better hope Congress extends all of the Bush tax cuts! “End If
When the amount of income entered is greater than $250,000, the message becomes:
I added another IF statement for the number of dependents:
If Dependents > 10 Then
Message = Message & “By the way, you’re not counting your pets as dependents … are you?”End If
When the number of dependents is more than ten, the message becomes:This isn’t a useful program since the tax calculation is inaccurate, but I demonstrated the ability to use IF statements and gave my professor a few laughs while he was grading it (hopefully the laughs were worth a few points)!
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.
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.
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.
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)
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.
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?
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!
The code to generate the generate the message in the text box is:
I added another IF statement for the number of dependents:
This isn’t a useful program since the tax calculation is inaccurate, but I demonstrated the ability to use IF statements and gave my professor a few laughs while he was grading it (hopefully the laughs were worth a few points)!
