Help with Formula for Budget Spread
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
Answers
-
Will it always be 4 months (1 prior/1during/2after)?
Are you able to provide a screenshot of how your sheet is laid out?
-
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.
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!