3 Replies Latest reply on Jul 30, 2018 4:31 PM by swaroop.gantela

    Need scenario calculation

    Amee Chapman

      Hello,

      I am looking to do a scenario type parameter over my actual and potential sales view vs my goals. I would like to see Install rates vs actual month sales.

       

      One scenario is that if we have Sales in July, 5% of that will be installed in July, 20% in August (Current month +1), 30% in Sept (Current Month + 2), 25% in month Oct (Current Month +3), 10% in November (Current Month + 4), 10% in December (Current Month + 5)

       

      This will allow us to forecast the pipeline out so we know how much we need to sell to have everything installed by the end of the year.

       

      Can someone walk me through how to set this up? I would like to put different scenarios together in a parameter to have different Install rates for Current month, and the percentages of the months so follow.

       

      Thanks,

      Amee

        • 1. Re: Need scenario calculation
          swaroop.gantela

          Amee,

           

          My apologies at the outset, I didn't quite understand how you wanted your install

          percentages to interact/adjust the sales.

           

          Nonetheless, I made something of a framework for you to adjust for your purposes.

          I made six parameters and then incorporated them (in an arbitrary way) into a calculation:

          IF ATTR([Order Date (Months)])<[Index Month] THEN SUM(Sales)

          ELSEIF ATTR([Order Date (Months)])=[Index Month] THEN SUM(Sales)*(1-[Month 0 Install %]/100)

          ELSEIF ATTR([Order Date (Months)])=DATEADD('month',1,[Index Month]) THEN SUM(Sales)*(1-[Month +1 Install %]/100)

          ELSEIF ATTR([Order Date (Months)])=DATEADD('month',2,[Index Month]) THEN SUM(Sales)*(1-[Month +2 Install %]/100)

          ELSEIF ATTR([Order Date (Months)])=DATEADD('month',3,[Index Month]) THEN SUM(Sales)*(1-[Month +3 Install %]/100)

          ELSEIF ATTR([Order Date (Months)])=DATEADD('month',4,[Index Month]) THEN SUM(Sales)*(1-[Month +4 Install %]/100)

          ELSEIF ATTR([Order Date (Months)])=DATEADD('month',5,[Index Month]) THEN SUM(Sales)*(1-[Month +5 Install %]/100)

          END

           

          If this doesn't suit your purposes, would be grateful for clarification on how you want to use the +month percentage values.

           

          Attached in the Forum thread is a workbook v10.3

           

          277101scenario.png

          • 2. Re: Need scenario calculation
            Amee Chapman

            This is definitely a good start, but what I am looking to do is predict how much Sales we need in order to make goal by tweaking the percentages all at once..So create a parameter that automatically fills in the percentages instead of tweaking each one. And how would I look at this vs a goal?

             

            Thanks so much for your help. If this is as far as I get I will take it!

             

            Amee

            • 3. Re: Need scenario calculation
              swaroop.gantela

              Amee,

               

              My apologies, I'm not familiar with the vocabulary, and so I haven't caught the picture.

              Will definitely defer to others who are in Sales.


              Nonetheless, I will keep trying.

              Would be grateful if you could post a small table of fake data of what your

              actuals and goals would look like, and which column gets multiplied by the percentages.

              Additionally, would be grateful for a drawing of what your expected end graph would look like.

               

              My misunderstandings are that I didn't catch how installs are related to sales.

              Is it that when something is being installed, extra sales are needed

              to make up for the downtime associated with the process of installing?

               

              With regards to the percentages, are you wanting to change the July percentage

              and then have the rest of the months adjust accordingly?

               

              My apologies again for being slow on the uptake.