Hi,
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)
Any help would be much appreciated!