Calculating pro-rated values for every month

Mohammed Sulaiman

In the below schedule of values, the $ value of each child task is the parent value multiplied by a weight. For forecasting purposes, let us assume that 25th of every month, each line item is billed depending on the whether the start and finish dates fall within the period.

In the below example, activity on Row 20, is to happen between 2nd February, till 6th of April. The total value is $23,437.50. Assuming that the billing date is 25th of each month.

For this activity, if we are to forecast and expect to bill as below :

1. 25th February, for work done between 2nd February and 25th February.
2. 25th March, for work done between 26th February and 25th March.
3. 25th April, for work done between 26th March and 6th April .

And the total of the above three billings will be equal to $23,437.50.

Question :

1. What formula is to be used in the column February 2023, March 2023, April 2023 for the Row 20 ?
2. Let us say, there is another column called May 2023, what formula to use so that for Row 20, the value returned is $0 ?
3. Is it possible combine 1 & 2 ?

The whole idea is able to generate a financial forecast tied with the schedule. As the dates or values are changed, the columns such as February 2023, March 2023, April 2023 should automatically update based on a formula.

Thank you in advance for your help.

Alex.Dixon

I generated this formula (for February):

=IF(OR(Start@row > DATE(2023, 2, 25), Finish@row < DATE(2023, 1, 26)), 0, IF(AND(Start@row >= DATE(2023, 1, 26), Finish@row <= DATE(2023, 2, 25)), Value@row, IF(Start@row >= DATE(2023, 1, 26), NETWORKDAYS(Start@row, DATE(2023, 2, 25)) / Duration@row * Value@row, NETWORKDAYS(DATE(2023, 1, 26), Finish@row) / Duration@row * Value@row)))

That should accomplish what you're aiming for. I'm not sure it's the most elegant solution, but it worked in my tests. You will need to change the relevant dates in the formula in each column for the month of that column. It also divides the Value between billing cycles based on working days (M-F) and not total days, since your durations are in working days.

The Logic Sequence:

The formula first checks if the start date of the activity is after the end of the billing cycle or if the end date is before the start of the billing cycle. If either are true, it outputs "0". Then it checks if the entire activity falls within the billing cycle. If it does, it outputs the full "Value". This still leaves activities that either start OR end in the month. The next check is if an activity starts in the month; if yes then it calculates the number of days between the start of the activity and the end of the billing cycle, over the total duration of the activity, times the value. If the activity doesn't start in the month, the only condition left is that it ends in the month, so it calculates the number of days from the start of the billing cycle to the end of the activity, over the duration of the activity, times the value.

-Alex