Income statement for Open Office Spreadsheets!

I have finally gotten around to publishing my income statement from check register for multiple activities in one account in an Open Document Spreadsheet (.ods).  I have made a few improvements from the version I made in Excel, mostly around making the year of the income statement another factor in the equation so that you can record information for more than one year into the check register and pull each year separately by changing the date on the income statement.  I tried to also integrate the pivot table version of the spreadsheet, but I have found that Open Office does not seem to have an equivalent to the getpivotdata() function.  However, the array function seems to work with less latency in Open Office than it did in Excel, except when changing the year on the income statement.

Let me know if you have any comments, questions or suggestions!

Mutiple Activities in One Account

Another Excel Income Statement

In a previous post I shared an Excel based income statement (profit and loss) that was based on a data register using sumifs and sum arrays to integrate multiple conditions in both Excel 2007 and earlier versions. However, I originally overlooked another very powerful way to summarize data along multiple conditions that are relatively defined. The worksheets here present the same income statement using a “Getpivotdata” function to pull the information based on relative information.

Once again I had to make two versions (one for Excel 2007 and one for earlier versions) as the pivot table functionality is different and the “iferror” function does not exist in the earlier versions. The only caveat to this worksheet that is a downside from using the others is that you must refresh the pivot table after entering the data before the income statement will update, otherwise this seems to be a very powerful way to summarize the data among a multitude of criteria.

Let me know if you have any thoughts, comments or suggestions on these!

Multiple activities in one account using Getpivotdata (for Excel 2007)

Multiple activities in one account using Getpivotdata (ealier versions)

MACRS Half-year convention Depreciation Schedule

This Excel spreadsheet uses lookup functionality to pull in the proper MACRS Half-year convention rate from a table to use in the depreciation schedule.  The lookup that is functionally used is the VLookup as I felt this was the simpler method of accomplishing this functionality, but in column H it has also been demonstrated using the Index function just to drive home the point that there is more than one way to get the job done.

I don’t know if this has a lot of practical application as it will certainly not replace any type of tax depreciation software, but it is a fun exercise to gain additional familiarity with the uses of lookup and index functions.  It may be useful to do some quick entry of a few items to see what the impact may be on some planning issues or for clients to track some of their purchases throughout the year.  This spreadsheet will also function rather well within Google documents for those that do work in the cloud.

MACRS Half-year convention depreciation schedule

Excel based Profit and Loss for multiple activities in one account

Another tool that I have created and would like to share for use, comment, and improvement is a spreadsheet that allows you to track multiple activities running through one bank account for purposes of generating separate profit and loss statements for each activity.  I think it is much quicker and simpler than trying to teach someone to properly use an accounting software and can be a great alternative to the dreaded “green sheets”.

I have noticed others replicate the “green sheet” thinking into Excel spreadsheets used to track profit and loss for light accounting in small business entities.  I think this becomes cumbersome unless you have a monitor 20 miles wide and there is a lot of rework when you want to add an account, thus accounts are often not added and more stuff gets lumped into miscellaneous.

My workbook is set up like a check register where you assign account numbers and activity codes to each transaction.  Then using the power of the “sumifs” function (available in Excel 2007 and newer), Excel can pull the desired information from the activity register into the profit and loss statement.  The version that I made for Excel 2003 and earlier uses the sum function as an array to accomplish the same functionality but runs considerably slower than the version based on the sumifs function.

Also included is a pivot table that summarizes information by account, allowing you to drill down to the detail on an account by account basis.  The 2003 and older version of the PL works with Google Documents rather well, with the exception of the pivot table.

Questions, comments, or suggestions, feel free to let me know what you “really” think.

PL Worksheet with Sumifs, for Excel 2007 or newer

PL Worksheet with Sum array, for Excel 2003 and older

Nebraska Personal Property Tax Template

I have a template that I use for putting together Nebraska Personal Property Tax schedules that I would like to share.  The template is populated with data just as an example of how the template works to pull and update information.

Changing the year in A3 will change the depreciation factor pulled (colum F), making it easy to roll forward from one year to the next.  The depreciation factor pulls based on the input of the current year in A3, acquisition year in column B, and the life in column E.  If you have any questions, comments, improvements, or updates, please let me know!

Personal Property Tax Worksheet

Get paid to interview?

After reading about Notchup on the Neil McIntyre blog, I went to the site to investigate how this project works.  The most interesting part to me was how they make the sale to the companies that will pay you for an interview.

If I were running HR for a company, I would seriously consider using a service like this.  By companies paying for your time (instead of paying a recruiter to find you) they are getting qualified candidates who are potentially more engaged for a fraction of the cost a recruiter would charge.

This truly seems to be a win/win situation for all parties involved, a shining example of people motivated by capitalism.

The key to this process for the individual, is that you need to have experience and qualifications that are in demand (the types who are always getting calls from recruiters).

I think small businesses would do well to look into this arrangement as an alternative to other recruiting methods that are either very costly or rather ineffective.

NolaPro leads the pack on web based accounting software alternatives

The majority of small and medium sized businesses utilize some type of desktop accounting software to handle their book-keeping and accounting chores.  Among the most prolific are Quickbooks and Peachtree.  Quickbooks is now offering its own web-based solution called Quickbooks Online Edition for $24.95 per month ($300 annually).  However, from a review of the comparison between Quickbooks Pro 2008 and Quickbooks Online Edition, it appears that the online edition is missing support for inventory tracking and pricing, as well as additional cost for customized invoicing, delayed billing, class tracking, and time tracking. 

Another plus for the online edition is its support for 3 users plus your accountant at the $24.95 per month base fee compared with $200 per user for QB Pro.  If you’re a small company with no inventory, QB Online may provide a sufficient solution, or you may be stuck on your desktop if you are in love with either the QB or Peachtree software for handling all facets of your business.

There is a very acceptable alternative that I have had a chance to review lately that has flown largely under the radar.  An Ohio based software development firm, Noguska, LLC, has developed a web-based accounting software that appears to be a comprehensive solution that is completely scalable in NolaPro.  NolaPro is free to download and install on your server or desktop, however you can opt for a NolaPro hosted solution for $25 per month, $60 quarterly ($20/month), or $200 annually ($16.67/month).

As an accountant I often have instances where I have one entity with multiple companies and multiple QB files and Quickbooks is slower than dirt at switching between the companies as it unloads one database and loads up another.  In NolaPro, separate companies are on drop down boxes that switch nearly instantly.  More than one user?  NolaPro doesn’t mind as it supports unlimited users with customizable rights.

So QB roles out a new edition, you have to run out and buy the latest and greatest, and your accountant has to do the same to remain compatible.  In the hosted NolaPro solution, upgrades are made automatically and free of charge, and if you choose to host yourself, the upgrade is free for download, just as the original program was.  NolaPro makes its money by the natural consulting business that develops from the utilization of its software.  The software is encrypted so that they have the rights to customization and such.  So if you have a highly specialized invoice for customers, but you want to be cheap, you can download the NolaPro program for free and take the money you saved to hire NolaPro consultants to make your special invoice.

QB Online did not offer an inventory solution with its product, but NolaPro includes an integrated inventory/shopping cart program, where the online shopping cart is tied directly to the inventory system within the accounting package.  I do not know how this works on a desktop installation, but it is pretty neat to see it work on a web-based installation.

There is a demo on the NolaPro site, but it has been messed with quite a bit and doesn’t really give you a feel of what your initial installation will look like, but it is at

I desired to do an evaluation of Netsuite, as I have read about their solutions on, but it has been almost a month since I emailed the company asking for more information and I have received no reply.  However, I am happy that I found NolaPro in my search for solutions to aid the small and medium sized business customers my firm serves.