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