Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 12th 17, 04:43 PM
Junior Member
 
First recorded activity by ExcelBanter: Oct 2017
Posts: 2
Post 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   Report Post  
Old October 13th 17, 11:20 AM posted to microsoft.public.excel.worksheet.functions
mag mag is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2012
Posts: 4
Default 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   Report Post  
Old October 13th 17, 03:11 PM
Junior Member
 
First recorded activity by ExcelBanter: Oct 2017
Posts: 2
Default

Why can't I find the attachment anywhere?

Last edited by Wangs930 : October 13th 17 at 08:01 PM
  #4   Report Post  
Old Today, 12:34 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2012
Posts: 42
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate How Long Funds Will Last ridgerunner Excel Worksheet Functions 6 December 21st 09 01:14 AM
Code to Allocate Funds Kro Excel Discussion (Misc queries) 2 January 18th 09 11:43 AM
Future Value of Mutual Funds Puzzled Investor Excel Discussion (Misc queries) 1 December 29th 07 11:56 AM
Investing,Mutual Funds Formula? Crackles McFarly Excel Worksheet Functions 18 August 22nd 07 04:56 AM
stocks/funds greg Excel Programming 0 August 9th 04 08:06 PM


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

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017