Home 
Search 
Today's Posts 
#1




Need help creating a formula that automatically reallocates unspent funds
So lets say I have $10,000 to spend in travel from Jul to Jun. For budgeting purposes I have spread this evenly throughout the whole year ($833.33 per month). However, I am not spending it this consistently. In fact, I haven't spent any of it for the first three months of the year. I want to spend it all or I will lose it.
My spreadsheet is dynamic so when I update the month it looks for values in a database and reflects actual costs. Since I did not have any travel expenses in the first three months it assumes that money will not be spent and the new projected spend is $7,500 because the first three months are now empty. I need a formula that will take the $10,000 and evenly allocate it across the remaining 9 months, so each month would show a budget of $1,111.11 now. I need the formula to update every time I change the month and to basically do this calculation and spread it evenly across the remaining months: (Original budgeted amount ($10,000)  money spent through current month($0)) / remaining months in the year(9 months). My current formula after the Vlookup to pull from the database is as follows: IF(Firstmonth (greater than) currentmonth, 10,000/12,0) Last edited by Wangs930 : October 13th 17 at 03:13 PM 
#2




Need help creating a formula that automatically reallocatesunspent funds
Hope this can help. (attachment) Best regards DM 12.10.2017. u 17:43, Wangs930 je napisao/la: So lets say I have $10,000 to spend in travel from Jul to Jun. For budgeting purposes I have spread this evenly throughout the whole year ($833.33 per month). However, I am not spending it this consistently. In fact, I haven't spent any of it for the first three months of the year. I want to spend it all or I will lose it. My spreadsheet is dynamic so when I update the month it looks for values in a database and reflects actual costs. Since I did not have any travel expenses in the first three months it assumes that money will not be spent and the new projected spend is $7,500 because the first three months are now empty. I need a formula that will take the $10,000 and evenly allocate it across the remaining 9 months, so each month would show a budget of $1,111.11 now. I need the formula to update every time I change the month and to basically do this calculation and spread it evenly across the remaining months: (Original budgeted amount ($10,000)  money spent through current month($0)) / remaining months in the year(9 months). My current formula after the Vlookup to pull from the database is as follows: IF(Firstmonthcurrentmonth, 10,000/12,0) 
#3




Why can't I find the attachment anywhere?
Last edited by Wangs930 : October 13th 17 at 08:01 PM 
#4




Need help creating a formula that automatically reallocatesunspent funds
I need the formula to update every time I change the month and to
basically do this calculation and spread it evenly across the remaining months .. . . . . Here is a test spreadsheet that shows my formula if it helps: https://www.dropbox.com/s/qotn6licdg...heet.xlsx?dl=0 I'm hoping the following might help getting started. Change the formula in cell F12 of Test_Sheet tab '12345678', by replacing "$E12/12" by the following fragment: ($E$12SUM(OFFSET($E$13,0,1,1,DATEDIF($F$9,$F$8,"M")+1)))/ (11DATEDIF($F$9,$F$8,"M")) Then copy F12 across to Q12. In this fraction, the numerator is the budget remaining (the original minus the historical months' consumption); the denominator is the number of months remaining. Test_sheet has 4 months of historical data (rather than 3), leaving $9,500 to be spread across the 8 remaining months, giving $1,187.50 per month. 
#5




That was enough to get me rolling so I could adapt it to my sheet, thanks!!

Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Calculate How Long Funds Will Last  Excel Worksheet Functions  
Code to Allocate Funds  Excel Discussion (Misc queries)  
Future Value of Mutual Funds  Excel Discussion (Misc queries)  
Investing,Mutual Funds Formula?  Excel Worksheet Functions  
stocks/funds  Excel Programming 