The Weighted Forecast Report Example

Select ‘[Tenant Name] Sandbox' and pick ‘Budget’ as your starting data. This means the report is based on budgets recorded in the system.

Next, join project data to the Budget so that we can easily see which project the budget belongs to - make sure to set the join on projectID on both data tables. This tells the system how the data is related across the tables

Next create a custom column. Custom columns allow users to do math with the data available in the system without adding new fields on to Projectworks. Enter the following formula in the modal that opens and name the column ‘Budget Weighting’. This formula states that:

1. if a Project status is ‘Signed’ we expect to get 90% of the total budget,

2. if the project status is Committed, we expect to get 75% of the total value, and

3. if the project status is Opportunity, we expect to get 50% of the total value'

   case([Project - ProjectID → ProjectStatus] = "Signed", 0.9, [Project - ProjectID → ProjectStatus] = "Unsigned (committed)", 0.75, [Project - ProjectID → ProjectStatus] = "Unsigned (opportunity)", 0.5)
    

Create a second custom column that uses the column we just created and multiplies it with the amount. Name it ‘Weighted Budget Amount’ . This calculates the budget value based on the weightings we’ve given. IF a project moves from one status to another, the weighting calculation will change: [Budget Weighting] * [Amount]

 Add a filter to only look at projects that are Active. This means the report only looks at current and potential work.

Summarize the Sum of ‘Weighted Budget Amount’ by Project Status

Select Visualize. You will see a new chart that shows the total sum of the weighted budgets as well as the weighted values of budget amounts per each project status.