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

Conviscating professional services

I’ve been watching a lot of the health care debate and town-hall meetings lately, as I believe this is a seminal issue that will define our view of rights vs. privileges in this country for a long time to come.  Now the analogy that I am going to draw is a stretch, but it really is the general direction that we are currently headed.

What if some legislators decided that everyone in America had a right to ‘affordable’ professional tax preparation?  How would this potentially affect the market for tax preparation services?  Furthermore, these same legislators want to tell you how to provide this service and help you define the extent of service each client may need.  At the same time, they will tell you how much this service is worth under the auspice of ‘negotiating the best deal for the customer.’

Would you be excited to be providing services in this type of marketplace?  Would the customers be excited to receive the services that are grudgingly provided by professionals who used to work in a free enterprise?  Would there still be the same incentives to become more effective and efficient and utilize technology or would these improvements be mandated with more legislation and grudgingly implemented?

How do you think that doctors and patients will fare any better than other professionals under the same circumstance?  Are they so much more altruistic that it will work?

Age of Indulgence or Flawed System?

A reader of the blog encouraged me to read Age of Indulgence by Ron Thomas and Chad Munson, both pen names for sake of anonymity, and offer my thoughts on the book.  The book is a compilation of horror stories in which the client generally gets screwed by firms and firm employees that view the client as a piggy bank.  After each set of stories, the authors interject their ideas for ways to mitigate these abuses.

While I agree that these are issues that need to be brought to light and that these types of abuses should not exist, I disagree with almost every recommendation that the authors make about controlling these abuses.  The authors generally take the position that the client needs to be a more vigilant baby-sitter for the firm as a way to control their own audit costs or the firm needs to be a more vigilant baby-sitter of its employees.

In my opinion, the client would be better to get the firm to control its own costs by demanding an arrangement where the fees for the engagement are fixed, so long as the scope of the engagement does not change significantly from beginning to end of the project.  If the contractor remodelling your kitchen finds asbestos, I doubt that you will expect the asbestos remediation to be within the scope of his initial price quote.  If the auditor finds financial cancer, they should be able to amend the arrangement letter and ask for a higher price.

If your accounting firm can’t figure their numbers well enough to set a price on an engagement, how well do you think they understand your numbers?

The client and the audit firm also view the billable hour/cost plus expenses pricing model in an entirely different light.  The client sees the costs figured from the budgeted hours and expected expenses as either a close proxy or upper limit to the actual expenses to be incurred.  While the audit firm may see this as the baseline if everything goes very smoothly with nearly any inconvenience justifying an opportunity to bill more time.  Thus the problem of selling time rather than solutions.

With regard to expenses, being able to pass these right along in the client billings gives serious problem about playing with OPM (other people’s money).  If you want to stay at a $200/night hotel while you’re on an engagement, you should be able to do so, but it should be part of your cost structure that limits YOUR profit, not increased expenses for the client.

Overall, the book is good and brings up very relevant issues, but the authors miss the root of the problem and give you solutions for the symptoms without addressing the illness.

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


Will IFRS really help make financial statements more comparable by having all publicly traded company reports based on the same set of standards?  In my opinion, they will look comparable and that may be the end of it.  The issue I see is having many accountants, both auditors and internal accountants, working from a rules based mindset in a principles based environment.

In the rules based environment there is a line to push up to and to make a clear call when crossed.  In the principles based environment there will be many lines depending on personal tastes, preferences, and risk aversions.  Some will be overly conservative with the real fear being that some will be overly aggressive.  Not all professional judgement is crafted equally.  Some CFO’s of small organizations are not that thoroughly experienced and seasoned and rely heavily on the work of external auditors to guide their application of accounting principles or rules.

Not all auditors are imbued with the same degree of professional skepticism and ethical adherence.  Some will encourage clients to push the lines and see themselves in a similar light as the corporate counsel that informs of where the lines are clear and where they are not.  Cluing the client in to the areas of ambiguity so that lines can be pushed without being technically crossed.

Others will be so scared of being seen in the former light that they will take the most conservative stances thinking that it makes them more ethical than most.  Either way too conservative or too agressive does not paint a proper picture of economic standing and well-being.

However, this will probably exhibit a type of normal distribution with most professionals trending towards some median that is seen as an industry or professional norm with outliers that make the news reports on the aggressive side and those that rant against the aggressiveness of the median on the conservative side.  Hopefully these norms will be sufficient to produce a resonable degree of comparability for most financial statements, but finding the likely places for abuse to lurk may become more difficult as abuse becomes a more vague concept.

However, more concrete standards may evolve in the way that legal principles evolve into legal precedents with the results of disputes aiding to draw lines in the gray areas of the principles.  If this is the route that follows, we essentially get rules anyway even though we started out with principles.  However, the rules have been arbitrated and may still be disputable rather than set by the standard setter.

Rental real estate investing

Here are some best practices and tax considerations that will make managing rental real estate easier on you and  your accountant:

  1. Keep a separate bank account for business. Even if you are just going to report the activity on Schedule E of your 1040, having the activity of the business separate will make book keeping and question answering much easier. If the property will be held by an LLC, this is necessary to help maintain the liability shield to show the distinction between the business entity and your personal assets.
  2. Only the interest on mortgage loans is deductible, not the whole payment. Additionally points paid on a mortgage are deductible ratably over the life of the loan.
  3. Establish a capital expenses account separate from the repairs and maintenance account. In the capital expenses account keep track of larger improvement or repair projects, remodeling, or asset purchases (computers, telephones, HVAC units, etc). At the end of the year, the capital expenses account can be easily adjusted to the balance sheet to capitalize and depreciate these assets.
  4. Depreciation considerations. Make certain to allocate part of the property purchase price to land. Look to guidance from the valuation or tax valuation as to a reasonable proration of these amounts.
  5. Deduct insurance premiums, as well as other business expenses (the ordinary and necessary expenses of carrying on a trade or business that are paid or incurred in the tax year). These are not deductible as a homeowner, but they are as a real estate business owner.
  6. Miscellaneous closing costs are deductible in the year of purchase. For example, title and escrow fees, title insurance, appraisal fees, loan application fees, and recording fees are all deductible in the year of purchase.
  7. Real estate income is passive income. Losses reflected on Schedule E will be carried forward to offset other passive income in future years. The exception to this rule is if you are a materially participating real estate professional. To meet this exception more than half of all working hours performed during the year must involve real property trades or businesses in which the taxpayer materially participates and these working hours need to be more than 750 hours.

Business Valuation Training

I have been at AICPA business valuation training this week with Robin Taylor and Ron DiMattia as our instructors and it has been a great learning experience for me.  I am just getting started in business valuation, and this class is full of professionals from business and industry that have way more experience than I do, so I have been able to learn a lot in that respect as well.

From what I have learned this week, I really anticipate growing my career in this direction as the growth and formation of the profession is really taking shape and new business opportunities are growing.  The proposition is not compliance driven and the value to the client is often easy to lay out.  It’s also a practice area where a professional can really differentiate himself/herself as an expert.

Due to the degree of professional judgement used to inform the conclusion of value, the choice of professional can make a huge difference in the quality of the conclusion reached.  Especially in the growing litigious environment it is important that a professional be well skilled and produce a thorough product.  Lack of licensing requirements for valuation services also make associations with reputable organizations and proper professional certifications highly desirable in the market for these services.

Furthermore, the degree of consideration given to the financial statements makes a CPA valuation expert especially qualified to analyze and reach conclusions about the financial health and prospects of the subject being valued.  This will be a fun and exciting area to work in for years to come.