Help with Formula for Budget Spread

Jo-Ann Turning

Can someone help with creating a formula?

We are trying to develop a formula to help create an automatic budget spread/phasing over the year.

Sheet Info:

We have columns named for the twelve months of the year

We have a BUDGET column

We have a START DATE column

We would like to build a formula that would take a $100,000 budget and phase it out over months automatically based on the START DATE.

Example: If START DATE is in [June], BUDGET should be spread

40% in May

40% in June

10% in July

10% in August

Is something like this possible using a formula?

It would be 40 % in month prior, 40% in planned month, 10% during +1 month and 10% in +2 month

Thanks for letting me know if that is possible - and if you have a recommendation for formula to try.

Jo-Ann

Paul Newcome

Will it always be 4 months (1 prior/1during/2after)?

Are you able to provide a screenshot of how your sheet is laid out?

Jo-Ann Turning

Hi Paul - Yes, we would apply the same timing principles. Typically in the past we have requested our team to enter spend by month and it auto totals in the BUDGET field. But we are thinking we can improve the accuracy of our forecasting if we ask them to simply enter the total BUDGET amount and apply that automatic calculation to spread across appropriate months.

Here is screen shot. Thanks for helping.

One note-i'm not sure it will be possible - since technically all meetings held in Q4, should have 100% of budget showing in Oct Nov and Dec (and not pushing over into the next calendar year).

So this may be too complicated to manage in a formula - was just trying to explore options. Thanks again.

Paul Newcome

It is possible with a formula. No worries there. I just wanted to make sure I had the details straight so we could get it put together properly.

And to clarify that last bit about Q4...

October would be 40, 40, 20

November would be 50, 50

And December dates would be 100

Is that correct for going across the months so that it doesn't overlap into the next year?