Forum Discussion

NoreenJustice's avatar
NoreenJustice
Qrew Trainee
5 years ago

Calculate a Forecast Based on Previous Month Numbers

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
------------------------------
reports and charts

1 Reply

Sort By
  • EricMohlman's avatar
    EricMohlman
    Qrew Cadet
    5 years ago
    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
    ------------------------------