Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Programming Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


Paradox® Date, Time, and DateTime Data Types
Odds and Ends - Fiscal Periods
© 2002 Rick Kelly
www.crooit.com

Download the library containing the routines used in this series.

Preface

A library of all OPAL methods presented is available here.

In previous sections, we explored using Paradox Date and Time types and addressed work-arounds in dealing with B.C. dates. This section takes a look at fiscal periods and some basic financial calculations involving dates.


Fiscal Periods

The word "fiscal" means of or relating to financial matters and a fiscal period is simply a period of time for which the financial affairs of an organization or individual are tallied. Typically, fiscal periods do not exceed 52 weeks or 12 months. A common fiscal period is one Gregorian calendar year; January 1st through December 31st and, in the United States, many government organizations use October 1st through September 30th as their fiscal period.

For our purposes, we will assume that fiscal periods are 12 months, beginning on the first day of any Gregorian calendar month.

To accurately associate a calendar month within a fiscal year for general financial reporting, we need to calculate:
  1. The fiscal year
  2. The ordinal month within the fiscal year
  3. The associated fiscal quarter

Fiscal Year

Commonly, the fiscal year is the Gregorian calendar year of the first fiscal month. For fiscal periods starting in January, this is always the Gregorian year. For fiscal periods beginning in the months February - December, the months that wrap after December are adjusted by subtracting one from the year.

For example:

Fiscal Year 2001 beginning January 1, 2001 includes the period January 2001 - December 2001.

Fiscal Year 2001 beginning October 1, 2001 includes the periods October 2001 - December 2001 and January 2002 - September 2002.

In conversations with Tom Krieg regarding fiscal year conventions in Australia (where many things are quite different?), I have learned that the government and many businesses use the year of the last month of the fiscal year which commonly begins with the month of July. For example:

Fiscal Year 2001 beginning July 1, 2000 includes the periods July 2000 - December 2000 and January 2001 - June 2001.

For fiscal periods starting in January, this is still always the Gregorian year. For fiscal periods beginning in the months February - December, the months on or after the starting month to December are adjusted by adding one to the year.

The method below will, given a Paradox® Date Type, return the fiscal year and provides a flag to indicate which year convention to follow.

emFiscalYear
method emFiscalYear(daAny Date, siStartFiscalMonth SmallInt, loUseEndingMonth Logical) SmallInt
;
; Given a date, return the fiscal year
;
; loUseEndingMonth
;
;     True = Use the year of the ending month
;     False = Use the year of the beginning month
;
switch
case siStartFiscalMonth = 1 :
  return year(daAny)
otherwise :
  return iif(month(daAny) < siStartFiscalMonth,
             year(daAny) - iif(loUseEndingMonth = False, 1, 0),
             year(daAny) + iif(loUseEndingMonth = False, 0, 1))
endSwitch
endMethod

Fiscal Month

The fiscal month is the ordinal month number within the fiscal year. That is, the first fiscal month (month 1) is the month the fiscal year starts on and the last fiscal month (month 12) is the month the fiscal year ends on. For a fiscal year beginning October 1st:

Calendar Month Number Fiscal Month Number
1 4
2 5
3 6
4 7
5 8
6 9
7 10
8 11
9 12
10 1
11 2
12 3

To calculate the fiscal month number given a calendar month number, we must know the month the fiscal year begins on and account for any months in the fiscal year following December. Examining the above list for months 10-12 we can see that the following is true.
Calendar Month Number - Starting Month of Fiscal Year + 1

10 - 10 + 1 = 1; 11 - 10 + 1 = 2; 12 - 10 + 1 = 3
If we add 12 to the months 1 - 9, we can continue the same calculation.
1 + 12 - 10 + 1 = 4; 2 + 12 - 10 + 1 = 5; etc.
Now we can accurately calculate a fiscal month with the following method.

emFiscalMonth
method emFiscalMonth(siMonth SmallInt, siStartFiscalMonth SmallInt) SmallInt
;
; Given a calendar month, return the fiscal month
;
return siMonth + iif(siMonth < siStartFiscalMonth,12,0) - siStartFiscalMonth + 1
endMethod

Fiscal Quarter

A fiscal quarter is defined as the four, three consecutive month periods beginning with the first month of the fiscal year. Fiscal months 1-3 are quarter 1, fiscal months 4-6 are quarter 2, fiscal months 7-9 are quarter 3, and fiscal months 10-12 are quarter 4.

This calculation is straight forward. Divide the fiscal month by 3 and round up to the nearest whole number. For this, we will use the OPAL® method ceil(). For clarity of code, I like creating a wrapper for ceil() which we'll call cmCeiling.

emFiscalQuarter
method emFiscalQuarter(siMonth SmallInt, siStartFiscalMonth SmallInt) SmallInt
;
; Given a calendar month, return the fiscal quarter
; it belongs to
;
return smallInt(cmCeiling(emFiscalMonth(siMonth,siStartFiscalMonth) / 3.0))
endMethod

Conclusion

We now have methods that support some common fiscal period calculations that we will be using in the next installment.


Next: Thirteen Month Rolling Histories


References
Common / Shared ObjectPAL® Routines


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 09 Jun 2004
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2004 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.