Remember Me?

#1
October 12th 17, 04:43 PM
 Junior Member First recorded activity by ExcelBanter: Oct 2017 Posts: 2
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
October 13th 17, 11:20 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2012 Posts: 4
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
October 13th 17, 03:11 PM
 Junior Member First recorded activity by ExcelBanter: Oct 2017 Posts: 2

Why can't I find the attachment anywhere?

Last edited by Wangs930 : October 13th 17 at 08:01 PM
#4
Today, 12:34 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2012 Posts: 42
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\$12-SUM(OFFSET(\$E\$13,0,1,1,DATEDIF(\$F\$9,\$F\$8,"M")+1)))/
(11-DATEDIF(\$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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post ridgerunner Excel Worksheet Functions 6 December 21st 09 01:14 AM Kro Excel Discussion (Misc queries) 2 January 18th 09 11:43 AM Puzzled Investor Excel Discussion (Misc queries) 1 December 29th 07 11:56 AM Crackles McFarly Excel Worksheet Functions 18 August 22nd 07 04:56 AM greg Excel Programming 0 August 9th 04 08:06 PM

All times are GMT +1. The time now is 04:50 AM.