Discussions

 View Only
  • 1.  Calculate a Forecast Based on Previous Month Numbers

    Posted 11-05-2019 10:49
    Edited by Noreen Justice 11-05-2019 13:48

    Update: ON HOLD FOR NOW


    I have records that track production volumes. The records include 2 values: an actual amount and a forecasted amount. I would like the forecasted amount to be a formula field but I'm struggling with how to write the formula. The formula should be based on a set growth rate that varies by the Business Dept. Here are the fields: 

    Record ID

    number

    Related Business Dept

    Relationship look up populated from Process ID

    Related Process ID

    Relationship populated ID

    Period (always 1st of the month)

    User entered date

    Actual Units

    User entered number

    Forecasted Units

    number formula to calculate forecasted amount + growth rate. Each Business Department will have their own set growth rate. The example here uses 0.02

     

    If Business Dept = xxx and previous month's (same Process ID record) Actual Units is 0, populate Forecasted Units with (previous month's Forecasted Units + (previous month's Forecasted Units * 0.02)

     

    If Business Dept = xxx and previous month's (same Process ID record) Actual Units is >0, populate Forecasted Units with (previous month's Actual Units + (previous month's Actual Units * 0.02)

     

    Sample

    Record ID – 1

    Business Dept – ABC

    Related Process – 228

    Period – 5/1/19

    Actual Units – 75

    Forecasted Units – 50

     

    Record ID – 2

    Business Dept – ABC

    Related Process – 228

    Period – 6/1/19

    Actual – 0

    Forecasted Units – 76.5      Use previous month's Actual: 75 + (75*0.02)

     

    Record ID – 3

    Business Dept – ABC

    Related Process – 228

    Period – 7/1/19

    Actual Units – 0

    Forecasted Units – 78.03      Use previous month's Forecast: 76.5 + (76.5*0.02)

    Any help would be greatly appreciated!

     



    ------------------------------
    Noreen Justice
    ------------------------------


  • 2.  RE: Calculate a Forecast Based on Previous Month Numbers

    Posted 11-11-2019 12:14
    In general, Quick Base records don't know about data on other records without a relationship in place.

    The simplest setup I can think of to accomplish what you outlined here is to create a relationship from your Units table to itself. You could then have a summary field in the Process to Unit relationship to show what the latest Record ID# is, setup that in a lookup field back to the Units table, a snapshot to capture what that value is as a new Unit is added, and an Automation to populate the reference field in the relationship I proposed to link them together.

    With that relationship in place, you can add the Actual Units and Forecasted Units fields as lookup fields so that you can easily see what the previous month's metrics were. Your current formula could then be based on those details like this:
    If([Previous Unit - Actual Units]=0,[Previous Unit - Forecasted Units],[Previous Unit - Actual Units])*[Related Process - Growth Rate]

    ------------------------------
    Eric Mohlman
    ------------------------------