Spreading a budget over a number of months depending on activity date

BBFL

Hi All,

I have a sheet which tracks marketing activity and the cost of it. Our activity can sometimes be one day, and other times it can run over several months.

I have added helper columns for each month and named "January 2024", "February 2024" and so on where the purpose is to assign an appropriate amount of budget in each of those months depending on when the activity runs . I have created a couple of helper columns - The first calculates the number of months that the activity is running (see below) and the second then splits the budget by dividing the budget by number of months to give a monthly spread.

Formula to calculate months (Named 'Helpermonths1' - =IFERROR((NETDAYS([Activity Start Date]@row, [Activity End Date]@row) / 365) * 12, "")

Formula to calculate monthly budget (Named 'Helpermonths3' - =[Actual Cost (Media & Production Comb.)]@row / ROUND([Helpermonths2]@row, 0)

Formula for each month to allocate (the date changes for each monthly column) - =IF(AND(VALUE(YEAR([Activity Start Date]@row) + IF(MONTH([Activity Start Date]@row) < 10, "0", "") + MONTH([Activity Start Date]@row)) <= 202401, VALUE(YEAR([Activity End Date]@row) + IF(MONTH([Activity End Date]@row) < 10, "0", "") + MONTH([Activity End Date]@row)) >= 202401), [Helpermonths3]@row)

My issue seems to be that where I have activity that runs across multiple months it seems to add the monthly budget more than it should. So, if we have activity running from middle of January to middle of February, 'Helpermonths1' is calculated as one month but that monthly amount is then added twice (in January and February) meaning that it is double counted. That can also happen where it runs across multiple months at times too.

Would appreciate any suggestions for fixing this. Not sure if there is a better way of calculating the months rather than updating the monthly columns themselves.

Thanks in advance.

Chris Mondeau

@BBFL you may look at using the prorate function. This takes a value and distributes the amount over periods of time within defined ranges (in your case the marketing costs for the entire project but only for the active dates within a specific month).

Essentially prorate takes a budget, distributes it over the total range of the activity, sums the distributed amount for a defined range (in this example by month).

Here's one of the formulas: =PRORATE(Budget@row, Start@row, End@row, DATE(2024, 1, 1), DATE(2024, 1, 31))

You can modify this formula for each month, where you would change the DATE() function to match each month accordingly. One thing to note is that I've hard-coded the month durations, but there are ways to make them more dynamic so that it accounts for Leap Years or can span multiple years.

Hopefully that helps!