Spanky's Place :: Fiscal Year Fun

Perhaps a more appropriate title for this entry would be something like

Lotus Notes, view selections, view column values, programmatic view column names & referencing their values, and dynamically determining fiscal year information.

A buddy of mine called me last week; he needed my help with some Fiscal Year calculations. "No problem", I said, "all you need to do is use the @Adjust formula and....."; at which point I realized that fiscal year calculations aren't that easy.

I've been in this business a long time, and I've developed (in whole or as a contributing team member) lots of Notes / Domino applications for lots of customers. When I say "lots of" customers, I mean hundreds. When I say "lots of" applications, I mean possibly thousands. In all that time, I've done a bunch of FY calculations. (When I say "bunch", I mean I have no idea.) The problem is, I never created a standard Fiscal Year algorithm. I always cobbled the FY stuff together, along with hard coding the dates, in a manner that was unique to the application. I know, that's a bad thing to do, but I'm going to rectify that now.

My friend's specific problem is probably something all of us have dealt with in the past. Given a fiscal year that is different from the calendar year, how do you determine what fiscal year is for any given date? Say for example that your company's fiscal year begins on April 1st of the calendar year. Let's also say that you have some documents regarding financial transactions (with transaction dates, call the field dtTrans) and you want to display them in a view, and one of the view columns will need to display the fiscal year that the transaction date occurred. Quick now, go code the formula for the view column.

If you're like most developers, you immediately started thinking about how to adjust the date to show the fiscal year. The more astute among you may have noticed that I left out a critical bit of information about the FY. I told you it started on April 1st, but I didn't explain the relationship between the FY beginning and the calendar year beginning. And that is something that will hang you up as a developer.

Does FY 2005 begin on April 1st, 2005, or does it begin on April 1st, 2004? You can't simply define a FY begin month and day, you must also define the year. I'm going to make an arbitrary decision here (this is my blog after all) and define that FY 2005 begins on April 1st, 2005. Ok, now that we have that information, go ahead and work out a formula to display the Fiscal Year for any given date.

Ahhh, you realize I was setting you up again. Very good, grasshopper, very good indeed.

I'm not going to go into the details (simply because, as I said earlier, it's my blog), but let me point out something I've noticed: most Fiscal Year formulas need to be adjusted at least once a year. This isn't an attack on lazy developers, it is simply an observation of how code exists in the real world. I'll admit right here that even I am guilty of putting code into production that later needs a "tweak" to make work right. FY formulas quite often are members of The Usual Suspects. (No, that link has nothing to do with this topic, I just think it's a really good movie.)

My point is, in real world environments, Fiscal Year formulas tend to "break" more often than not. I suspect that this is due to the fact that most of them hard code the Fiscal Year begin (or end) date, or possibly the date range. When the calendar year changes, these formulas sometimes need to be adjusted. Sometimes companies (the really masochistic ones) even change when their FYs begin or end. Usually this happens during a merger or buy-out; but it does happen often enough to cause most developers to reach for the Excedrin. (Yes, I'm plugging painkillers now; you have a problem with that?)

So, how do we handle this? Well, I was planning on explaining this all in this posting (with code examples), but I'm afraid I've run out of time this morning. I hate to leave you hanging on like this, but I need to make a living. I'll post the solution, code, & explanation in my next entry.

-Devin