3 Replies Latest reply on Sep 16, 2013 11:48 PM by Vladislav Grigorov

    Forecast For Just A Month

    Neal Smoller

      Today's the 15th.  Sales total to date is $30,000.  That's an average rate of $2,000 a day.  I'd then estimate if I kept this pace, the rest of the month would be an additional 15 days of about $2,000 a day.  Projection/Forecast of $60,000 for the month.

       

      Next week I check the viz and the sales have dropped off to $38,000 total for 22 days or $1727 a day.  8 days left, i'd project $51816.

       

      How can i do this if i have daily sales info?

       

      THANKS!

        • 1. Re: Forecast For Just A Month
          Vladislav Grigorov

          Hi Neal,

          Attached is a workbook that you might find helpful. I basically do what you ask with a calculated field, that evaluates to non-null only for the current month. Then you can use this as you wish, but bear in mind that this approach does no take into account any non-working days, i.e. it is an estimate based only on the days that passed since the beginning of the month.

          Hope this helps.

          • 2. Re: Forecast For Just A Month
            Neal Smoller

            thanks a bunch!

             

            do you mind in english saying what the formula says?

            i wish there was step into step out :/

            • 3. Re: Forecast For Just A Month
              Vladislav Grigorov

              Hi Neal,

              no worries, here's the formula with some comments:

              First of all, I am checking if the record falls into the current month. This is the first row, where I basically take the first day of the month from the [Date] field, and compare it with the first day of the month in the date returned by today() function:

              if datetrunc('month',max([Date]))=datetrunc('month',today()) then

              This evaluates to true only if [Date] belongs to the current month, otherwise it is some date in the past, and we should not estimate the result of a period that has passed already. Therefore I do not have an ELSE statement in the IF clause, i.e. ot returns Null, if the [Date] is outside the scope of the current month.

              The second line basically does the "extrapolation" by taking the sum([Sales]) for the current month, multiplying by the number of days the month has in total, and then dividing by the number of days that have already passed. It is like that:

              sum([Sales])*day(dateadd('month',1,datetrunc('month',max([Date])))-1)/day(max([Date]))

              The second part of the nominator in this row: day(dateadd('month',1,datetrunc('month',max([Date])))-1) works like that:

              1) datetrunc('month',max([Date]) takes the maximum date stored in the [Date] field, and rounds it down to the first date of the month - i.e. instead of 20 August 2013 it will return 01 August 2013.

              2) Then I add 1 month to the result by dateadd('month',1,datetrunc('month',max([Date]))), to get the first date of the following month, and then subtract one day to get to the last day of the month. This is in order to get the last date of the month, which may have 28,29,30 or 31 days.

              3) I return then as an integer the day number in order to know how many days in total are in the month of question.

              In the denominator, what I have is actually the day number of the last day in the [Date] field. As a matter of fact if you do not have a record one day, this will give a false result, so you better substitute that last part with day(today()) instead.

              The third row just closes the IF statement with an END.

               

              Best,

               

              Vlad