I work for a consultancy company that has consultants starting and ending their contracts for our clients on certain dates (Timeline Column).
There is a total revenue value for each of those contract (Forecast Revenue).
How would I go about making sure that for any Q1/2/3/4 of the current FY I get a view of how much revenue of that contract falls into which quarter?
E.g.: Our FY starts on the 1st of July. Consultant starts on the 1st of August (i.e. in Q1) and finishes on the 5th of February. If I add a column for Q1, Q2, Q3 and Q4 each, can the revenue that is earned in Q1 be calcuated based on the Forecasted Revenue column for that quarter specifically?
And then for each subsequent quarter?
In this scenario Q2 would be the biggest in terms of revenue, Q1 and Q3 lower and Q4 zero.
It doesn’t need to be exact as I’m aware that the timeline counts working days without taking into account holiday periods or leave, just something that is a good approximation.
I currently have a formula that calculates the quarterly revenue but distributes it evenly across quarters which isn’t helpful:
ROUND({Forecast Value (ex GST)}/((ROUND((DAYS({Delivery Timeline#End},{Delivery Timeline#Start})/30.417),0)/3)),0)
In this example, the chart is grouped by week, and the bar segment is Salesperson. The chart displays the estimated weekly revenue for each salesperson. The leftmost bar is the current week. The bars to the right are the future weeks. The grouping options are day/week/month/quarter, and you can use any of your Number columns as the unit. You can place the chart in a Monday dashboard or schedule to be sent via email.
What you want can be accomplished with a marketplace app called the Advanced Formula Booster. It is the app this gives you the most options and control over your formulas. It does not use formula columns, rather directly writes to any column in your board.
