Forum Discussion

CoreyJohnson's avatar
CoreyJohnson
Qrew Member
6 years ago

Creasting a forecast Pipeline model for sales team

Been racking my brain with the problem for a few days now and im at a dead end. Im trying to create a table that will build a forecast for a new sales Opportunity.  The Executives in the compnay would like to see it a certain way and im trying to nail this in order to promote Quick Base across the company. Anyways in an opportunity there is a " Service Start Date", "Contract End Date" and " Total Dollar Amount". So lets say i have an Opportunity thats Total Dollar Amount is $1000000. The Service Start Date is 02/01/2020 and the contract end date is 06/1/2020 thats $250,000 a month... but i need to be able to show that in a fiscal year Report broken down by month if that makes any sense. 

So it would look something like this 
Months                  Jan.      Feb.       Mar.         Apr.       May       June     July     Aug.    Sept.    Oct.     Nov.    Dec.
(Opportunity)        $0        250K     250K       250K      250K     ​ $0        $0        $0        $0         $0        $0       $0


Any Help would be amazing! For some reason i keep drawing a blank on how best to handle this

------------------------------
Corey Johnson
------------------------------
App builders
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    My suggestion is to make a child table called Sales Opportunity Months.  One Sales Opportunity will have many Sales Opportunity Months.
    When a Sales Opportunity is added or edited, you will trigger an Automation to delete all children as step 1.
    Then the Next Step will be to create say 12 children, or more if you think that the longest span of the opportunity would be more than 12 months.  To do that you will need to calculate 12 fields representing the first of the month for each month from the start date to the end date.  Some of them will be blank as say if its a 3 month project, then months 4 through 12 will have blank dates.  The dates should be in a date format set to the 1st of the month.  The value per month will be calculated by taking the sales opportunity and dividing it by the number of montha to be spread.

    So the Automation would have 14 steps

    Step 1 . Delete Existing children
    Steps 2 thru 13  - Create 12 children whether you need them or not
    Step 14 delete an children with no dates.


    3.

    Then the last step of the automation will be to delete any children with blank dates.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------