FISCAL AND ACCOUNTING PERIOD CALCULATION LOGIC The 4-4-5 calendar is a method of managing accounting periods. It is a common calendar structure for some industries, such as retail and manufacturing. The 4-4-5 calendar divides a year into 4 quarters. Each quarter has 13 weeks which are grouped into two 4-weeks "months" and one 5-week "month". The grouping of 13 weeks may be set up as 5-4-4 weeks or 4-5-4 weeks, but the 4-4-5 seems to be the most common arrangement. When a 4-4-5 calendar is in use, reports with period by period comparison or trend over periods do not make much sense. You can still do the comparison of a period over the same period in the prior year. You can also have the week by week data comparison. Its major advantage over a regular calendar is that the end date of the period is always the same day of the week which is useful for shift or manufacturing planning. One disadvantage of the 4-4-5 calendar is that it has 364 days (7 days * 52 weeks), so approximately every 5 years there will be a 53 week year, which can make year on year comparison difficult. 52-53 week fiscal year The 52-53 week fiscal year is a variation on the 4-4-5 calendar. It is used by companies that desire that their fiscal year always end on the same day of the week. Any day of the week may be used, and Saturday and Sunday are common because the business may more easily be closed for counting inventory and other end-ofyear accounting activities. There are two methods in use: Last Saturday of the Month at Fiscal Year End Under this method the company's fiscal year is defined as the final Saturday (or other day selected) in the fiscal year end month. For example, if the fiscal year end month is August, the company's year end could fall on any date from August 25 to August 31. Currently it would end on the following days: 2006-08-26 2007-08-25 2008-08-30 2009-08-29 2010-08-28 2011-08-27 2012-08-25 2013-08-31 2006 2007 2008 2009 2010 2011 2012 2013 August August August August August August August August 26 25 30 29 28 27 25 31 (leap year) (leap year) The end of the fiscal year would move one day earlier on the calendar each year (two days in leap years) until it would otherwise reach the date seven days before the end of the month (August 24 in this case). At that point it resets to the end of the month (August 31) and the fiscal year has 53 weeks instead of 52. In this example the fiscal years ending in 2008 and 2013 have 53 weeks. Saturday nearest the end of month Under this method the company's fiscal year is defined as the Saturday (or other day selected) that falls closest to the last day of the fiscal year end month. For example, if the fiscal year end month is August, the company's year end could fall on any date from August 28 to September 3. Currently it would end on the following days: 2006-09-02 2007-09-01 2008-08-30 2009-08-29 2010-08-28 2011-09-03 2012-09-01 2013-08-31 2006 2007 2008 2009 2010 2011 2012 2013 September September August 30 August 29 August 28 September September August 31 2 1 (leap year) 3 1 (leap year) The end of the fiscal year would move one day earlier on the calendar each year (two days in leap years) until it would otherwise reach the date four days before the end of the month (August 27 in this case). At that point the first Saturday in the following month becomes the date closest to the end of August and it resets to that date and the fiscal year has 53 weeks instead of 52. In this example the fiscal year ending in 2011 has 53 weeks. The 52-53 week method is permitted by Generally Accepted Accounting Principles in the United States, as well as by the US Internal Revenue Code (IRS Publication 538). DESIGNING FISCAL AND ACCOUNTING CALENDARS IN DWH AND USING THEM IN MICROSTRATEGY Organizations commonly use fiscal calendars of 13-week quarters, each containing three fiscal periods in which two periods have four weeks and one has five. Each fiscal period starts on the same day of the week, unlike the Gregorian calendar, and has the same number of days. This simplifies period-over-period transformations, among other benefits. In general, the assignment of dates into fiscal periods should be handled in lookup or relationship tables, so that the calendar can be tailored to the organization's requirements. The use of date functions is not recommended. Database-side date functions are usually tied to the Gregorian calendar, which does not map neatly onto any 4-4-5 scheme. (If a given database platform includes functions designed specifically to manipulate dates according to a 4-4-5 arrangement, those functions could be used in ApplySimple expressions.) Following are short fragments of fiscal week and fiscal period lookup tables laying out a 4-4-5 scheme where the first day of the fiscal week is Monday. FISC_WEEK_ID 200601 200602 200603 200604 200605 FISC_WEEK_DESC 2006 Week 1 2006 Week 2 2006 Week 3 2006 Week 4 2006 Week 5 FISC_MONTH_ID 200601 200601 200601 200601 200602 FISC_WEEK_START_DT 1/2/2006 1/9/2006 1/16/2006 1/23/2006 1/30/2006 FISC_WEEK_END_DT 1/8/2006 1/15/2006 1/22/2006 1/29/2006 2/5/2006 FISC_WEEK_ID 200606 200607 200608 200609 200610 200611 200612 200613 FISC_WEEK_DESC 2006 Week 6 2006 Week 7 2006 Week 8 2006 Week 9 2006 Week 10 2006 Week 11 2006 Week 12 2006 Week 13 FISC_MONTH_ID 200602 200602 200602 200603 200603 200603 200603 200603 FISC_WEEK_START_DT 2/6/2006 2/13/2006 2/20/2006 2/27/2006 3/6/2006 3/13/2006 3/20/2006 3/27/2006 FISC_WEEK_END_DT 2/12/2006 2/19/2006 2/26/2006 3/5/2006 3/12/2006 3/19/2006 3/26/2006 4/2/2006 FISC_MONTH_ID 200601 200602 200603 200604 200605 200606 FISC_MONTH_DESC 2006 Period 1 2006 Period 2 2006 Period 3 2006 Period 4 2006 Period 5 2006 Period 6 FISC_MONTH_START_DT 1/2/2006 1/30/2006 2/27/2006 4/3/2006 5/1/2006 5/29/2006 FISC_MONTH_END_DT 1/29/2006 2/26/2006 4/2/2006 4/30/2006 5/28/2006 7/2/2006 With the weeks and months arranged in database tables, a fiscal date table could be created physically in the database, or established with a view based on a calendar date lookup table, as follows: create view LU_FISC_DAY as select a1.DAY_DATE FISC_DAY_DATE, a2.FISC_WEEK_ID, a3.FISC_MONTH_ID from LU_DAY a1 join LU_FISC_WEEK a2 on (a1.DAY_DATE between a2.FISC_WEEK_START_DT and a2.FISC_WEEK_END_DT) join LU_FISC_MONTH a3 on (a2.FISC_MONTH_ID = a3.FISC_MONTH_ID) Attributes may then be created in a simple chain of one-to-many relationships. Note that the start and end date columns are brought into the Fiscal Month and Fiscal Week attributes as attribute forms coming from their respective lookup tables. Fiscal Semester and Fiscal Year attributes may be modeled similarly. Common reporting scenario #1: Fiscal Month to Date With start and end date columns in the lookup tables, it is possible to use database views or MicroStrategy logical views to create week-to-date or period-to-date transformation tables. An example follows of a MicroStrategy logical view defining a fiscal month to date transformation using the above-described tables. It is necessary to join LU_FISC_DAY twice in the from clause because the table must map one date onto multiple other dates. select a1.FISC_DAY_DATE, a3.FISC_DAY_DATE MTD_FISC_DATE from LU_FISC_DAY a1 join LU_FISC_MONTH a2 on (a1.FISC_MONTH_ID = a2.FISC_MONTH_ID) join LU_FISC_DAY a3 on (a3.FISC_DAY_DATE between a2.FISC_MONTH_START_DT and a1.FISC_DAY_DATE) The transformation should then be defined as many-to-many: Results: Common reporting scenario #2: Filtering on days in a fiscal period up to a userselected date This requirement effectively consists of two criteria: 1: Include days before and including the user's selection AND 2: Include only the fiscal period containing the user's chosen date (to eliminate fiscal periods prior to that date) MicroStrategy represents these conditions as, respectively, an attribute form qualification and a relationship filter. In these qualifications, a date value prompt has been saved under the name "Choose Fiscal Date." By using the same prompt in both qualifications, the user will be asked to input only one date. Instead of a prompt, a dynamic date could also be used. Condition 1: Fiscal Day
Comments
Report "4-4-5 Method of Designing Snowflaked Fiscal-Accounting Calendars"