5 Replies Latest reply on Dec 13, 2012 1:36 PM by comment

    Dealing with Fiscal Year data

    shane0801

      Good Morning everyone,

       

      This is my first time posting to the forum so I apologize if this is in the wrong place, not enough data or if this has already been answered. I have been searching the web for the last few days trying to find a best option for my problem but have had no luck. So here it goes.

       

      I am building a weekly Sales database for my current company. They have one in Access that is currently being used but I feel like it does not follow any of the best practices for a database. So I am rebuilding it in FM Pro 12. Within the new database the weekly sales totals for each department are imported from an xml file from our POS system. Everything is working just fine so far. I have the sales data relating to their proper departments and locations. The problem I am having is how to handle the date aspect of this database. Each sale entry has the week-ending date but the accounting department here uses 13 periods instead of 12 in a fiscal year. This causes the fiscal year to start on a different date from year to year. But this can change the period that each week will fall under.

       

      Now within this database they want to be able to run reports by week, period, quarter and year. So my question is how should I handle the date information? What I was going to do is have a seperate table that handles the week-ending date and what period and quarter it should fall under using calculations. The calculations are being figured from the start date of the fiscal year that they enter into a resources table. This way accounting can change the fiscal year start date at the start of each year and the calculations for that year will be calculated using that field. I then relate the week info to the sales by a weekID that is auto-generated. Is this the proper way to handle it or am I over thinking the problem and should just keep the period and quarterly data within the sales database? Any help is appreciated.

        • 1. Re: Dealing with Fiscal Year data
          taylorsharpe

          So for each transaction date, you have to determine the end of that week date and the end of week date would be used to determine what week/quarter/month the transaction is counted in.  I would start with a calculation that takes the transaction date and converts it to a end of week date and use the end of week date for all your reporting. 

           

          Let ( [

           

          F1 = Transaction Date ;

          F2 = DayOfWeek ( F1 ) ;

          F3 = 7 - F2 ;

          F4 = F1 + F3

           

          ] ;  F4  )

           

           

          * This assumes you are converting your day of the week to the 7th Day of the week which is Saturday. 

          • 2. Re: Dealing with Fiscal Year data
            shane0801

            Taylorsharpe,

             

            Thanks for the quick reply.  I was able to have all the dates converted to the end of week date and that seems to be working pretty good for me.  What I am struggling with is figuring out how to keep track of the period and quarter that each week falls into because of the start of each fiscal year always being a different date.  I am not sure if I should just add a period and quarter field to the sales table and have these handle my calculations.   But then I was not sure if I should keep all the date data and calculations in a new table and then use a relationship back to the sales table. I guess I am not sure what is the better practice to use here.

            • 3. Re: Dealing with Fiscal Year data
              comment

              shane0801 wrote:

               

              the accounting department here uses 13 periods instead of 12 in a fiscal year.  This causes the fiscal year to start on a different date from year to year.  But this can change the period that each week will fall under. 

               

              Obviously, you need to start by calculating the "period" into which the sale entry falls. Unfortunately, you haven't provided any details about how this works. Are they perhaps using something like this:

              http://en.wikipedia.org/wiki/4-4-5_Calendar

              • 4. Re: Dealing with Fiscal Year data
                shane0801

                Michael,

                 

                Thank you for that link.  After reading that and some other pages it dawned on me that my approach was way to complicated.  We are using a 52/53 week calendar structure.  The year is broken into 13 periods with 4 weeks in each period.  Then the quarterly structure is  5 periods in the first quarter then only three periods in the other 3 quarters.  I am now just taking the week ending date and calculationg the week number by using the weekofyearfiscal() funciton.  That week number is now used in a relationship to a table that holds the weekNo, periodNo and quarterly NO in it.  Before I was trying to figure out the period, and quarterly info by using calculations from the day of the year instead of just using the weekNO to figure out how the period and quarter are assigned.

                • 5. Re: Dealing with Fiscal Year data
                  comment

                  Well, I hope you have worked it out all right, because for me the numbers do not add up.