Company Fiscal Year and Calendar Year
The use of a Company’s fiscal year is an obvious requirement. But like many obvious criteria a failure to consider how to handle it can create work if not handled correctly up front. A year starts on January 1 and ends on December 31. At least for people. But corporations have years that can begin on any date, end on any date, and have any number of weeks, months, or days in each year. Some companies operate on standard calendar months with each month starting on the first of the month and ending on the last day of the month with their year starting on January 1 and ending on December 31. Other companies operate on a 4-4-5 calendar where the first two months of a quarter have 4 weeks and the last month in the quarter has 5 weeks. If all the weeks have 7 days then the first day and last day of the company’s year will not be either January 1 and December 31 except by coincidence. Other companies operate on 13 4-week months. Many countries outside the US operate on a June 30 fiscal year as required by the local tax authorities and some companies in the US end on different months such as April. This can be complicated by the fact that when 52 7-day weeks are used for a fiscal year, the extra days accumulate and some companies add a 53rd week to bring their fiscal calendar back into an approximate alignment with the Gregorian calendar.
Companies who operate on calendar months need to have the month spilt into weeks for analyzing trade promotions as monthly data is not useable. In those cases, the 4 weeks in a month will have an odd number of days and not start on the same day each week. The point is that a simple perspective when looked at in detail becomes more complex. When building a model and database for only one company, only their particular calendar needs to be considered.
All of this can be handled in many ways. Some factors to consider:
- Length of the database. A week number starting with week one at the beginning of the file and continuing to week 693 is what SPAR uses. There is no set number of weeks but should be long enough to handle 6 years of history (to allow for building the necessary sales and promotion databases for analysis, and long enough going forward so that the files don’t have to be updated every few years. If the model is used for forecasting (as it should be) then 18 months are required going forward. With 6 years of history (312 weeks) and 18 months of forecasts (78 weeks) that is 390 weeks. Adding 312 weeks to the database allows for 6 complete years prior to the need to modify the file. The reason SPAR uses 693 weeks is based on an historical set-up and how the SPAR programs are written and there is nothing magical about that number (or any other number of weeks).
- Ability to tie into Company data. The dates of the weeks need to tie into company fiscal periods. If a company is on a calendar month then the weekly data should be based on splitting the month into 4 weeks. Other company calendars are easier to work with but need to tie into a company fiscal periods. This is needed because management will want summary information based on a company’s month, quarter, or year and the numbers will need to tie in.
- Ability to tie into promotion periods. The dates must tie into how promotions are run. Companies on a fiscal calendar generally run their promotions for full weeks so tying into these dates is easy. Companies on calendar months usually run their promotions from the 1st or 15th of the month until the last day of a month or the 15th of a month. It is therefore necessary to spilt the sales data into weeks so the promotions cover full weeks and not a few days in the first and last day of the calendar. When splitting the month into weeks it is necessary to use the starting and ending dates of promotions as the starting/ending dates for individual weeks.
The database SPAR uses follows:
- Week number (1-693).
- Actual calendar dates for the start of the week and the end of the week (mm/dd/yy).
- Fiscal dates as ww/mm/yy where ww is the week number in the month which can be from 1 to 6, mm is the month which can be from 1 to 13 and the last two digits of the year that week falls into. As an aside this works until 2099 when the last two digits of 2100 are less than the last two digits of 2099. When SPAR built the databases in 1977 they didn’t consider they would still be operating in 2000 and all the subroutines had to be modified to handle the fact the increasing years no longer had a higher number.
- The number of shipping (or selling days if using scanner data) days in the week. This allows for an easy translation between fiscal dates and calendar dates.
Using this type of dates file structure which ties into the sales data, promotion file, and company fiscal periods will save a lot of aggravation and issues. This structure is only one of many that can be used and alternatives are perfectly acceptable.
In summary, thought needs to be given to how the weeks in a year are to be set up. Doing it in advance so that the sales, promotion, and other data files can be set up properly will prevent a lot of wasted time and work if it is not determined up front.