Accounting for a Detoured Economist

Today I needed to have excel calculate the difference between two dates. There was a compliance issue that was triggered by a 15 year difference and I needed to know which data to concentrate upon. So I wrote a simple formula

=if(date1-date2>15,”Yes”,”No”) - Reads: If date 1 minus date 2 is greater than 15 display Yes, otherwise display No.

I have done some work with SQL databases and a formula such as this would work fine…but this is Microsoft Excel Land and common sense does not always rule. The formula displayed yes for almost any two dates, even when I compared 01/01/2005 and 02/01/2005. Why did this happen?

In order to understand why this formula failed I needed to understand how excel views dates. Excel does not see dates it sees numbers. For example, today’s date (2/19/2007) is seen by excel as 39132. Tomorrow will be 39133, the next day will be 39134, and so on. Day 1 is 1/1/1900. So my formula was comparing days and would only display No when the dates were less then 15 days apart. So in order for my formula to work I needed to make a quick correction.

=if((date1-date2)/365.25>15,”Yes”,”No”)

Problem solved! I divided my comparison by the number of days in a year (365 days a year plus and extra leap-year day every 4 years) to get a comparison in terms of years.

Lesson learned: Approach a problem with a quick and dirty solution, take a moment to figure out how well your solution works and if the solution needs to be fine-tuned, if needed fine-tune the solution with a thorough understanding of why your initial solution did not work.

I love it when a plan comes together.

Next time you need to do a quick and dirty CUSIP lookup, and don’t want to take the time and effort to find your company password to lookup the information for the ratings on an investment, just remember this website.

Fidelity Investments Symbol Lookup

The site requires no password…and the best part is the price…free! If the site is down, you can still use the Standard & Poors website, but it will cost you. FYI, from experience I can tell you that searching for CUSIP’s on yahoo or Google finance is an exercise in futility.

For those of you that don’t know what a CUSIP is…

CUSIP stands for Committee on Uniform Securities Identification Procedures. Formed in 1962, this committee developed a system (implemented in 1967) that identifies most securities, including: stocks of all registered U.S. and Canadian companies, and U.S. government and municipal bonds. The CUSIP system—owned by the American Bankers Association and operated by Standard & Poor’s—facilitates the clearing and settlement process of securities.

The number consists of nine characters (including letters and numbers) that uniquely identify a company or issuer and the type of security. The first six characters identify the issuer and are assigned in an alphabetical fashion; the seventh and eighth characters (which can be alphabetical or numerical) identify the type of issue; and the last digit is used as a check digit. A similar system is used to identify foreign securities (CUSIP International Numbering System).

I am in the middle of an audit in which I accept an excel spreadsheet of time allocations to a choice of two different activities for the employees of the entity being audited. One activity is chargeable to a third party client base…one is not chargeable to that base. How do I know if the spreadsheet is accurate…I am extremely unconvinced that the sheet is accurate, but there is no way to prove the inaccuracy. If an employees says they spent 10% on an activity, when I suspect the number is larger, there is no way I can go back and test my doubts. The client is not motivated to keep more detailed records…they would be at a risk of losing revenue. And the audit requirements are simply that the client self-report these numbers.

The funny thing is that the spreadsheets provided by the client look very similar to the billable hour calculations I do as an auditor. How does a client know that I actually spent 10 hours on investments? They just have to take my word for it…yuck! What a crappy, judgemental, and shaky system to determine revenues and cost on.

The billable hour is an often blogged about topic in the blogosphere. Read more here, here, here, here, and here.