Here is example #1:

b1 = $300 total project cost
c1 = 3 number of years for this project
d1 = $100 intend to spend in 2005, formula: b1/c1
e1 = $100 intend to spend in 2006, formula: b1/c1
f1 = $100 intend to spend in 2007, formula: b1/c1
g1 = empty cell

What I need to happen when I enter 0 (zero) into d1:
b1 = $300 total project cost
c1 = 3 number of years for this project
d1 = $0 intend to spend in 2005
e1 = $100 intend to spend in 2006, formula: b1/c1 moved from former d1 cell
f1 = $100 intend to spend in 2007, formula: b1/c1 moved from former e1 cell
g1 = $100 intend to spend in 2008, formula: b1/c1 moved from former f1 cell
h1 = empty cell

Here is example #2:

b1 = $300 total project cost
c1 = 3 number of years for this project
d1 = $100 intend to spend in 2005, formula: b1/c1
e1 = $100 intend to spend in 2006, formula: b1/c1
f1 = $100 intend to spend in 2007, formula: b1/c1
g1 = empty cell

what I need to happen when I enter 40 into d1:
b1 = $300 total project cost
c1 = 3 number of years for this project
d1 = $40 intend to spend in 2005
the remaining balance of ($300 - $40 spent in 2005 should be divided by the
remaining 2 years left on this project)
e1 = $130 intend to spend in 2006, formula: (b1-40)/(c1-1)
f1 = $130 intend to spend in 2007, formula: (b1-40)/(c1-1)
g1 = empty cell

Here is example #3:

b1 = $100 total project cost
c1 = 4 number of years for this project
d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25
e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30
f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30
g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15
h1 = empty cell

What I need to happen when I enter 0 (zero) into d1:
b1 = $100 total project cost
c1 = 4 number of years for this project
d1 = $0 intend to spend in 2005
e1 = $30 intend to spend in 2006 (25% of budget), formula: b1*0.25
f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30
g1 = $30 intend to spend in 2008 (30% of budget), formula: b1*0.30
h1 = $15 intend to spend in 2009 (15% of budget), formula: b1*0.15
i1 = empty cell

Here is example #4:

b1 = $100 total project cost
c1 = 4 number of years for this project
d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25
e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30
f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30
g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15
h1 = empty cell

What I need to happen when I enter 11 into d1:
b1 = $100 total project cost
c1 = 4 number of years for this project
d1 = $11 intend to spend in 2005
the remaining balance of ($100 - $11 spent in 2005 should be divided by the
remaining 3 years left on this project as follows: 15% should be paid in the
last year of the project €“ 2008 and the remaining amount should be divided
equally among other years, 2006 & 2007 in this example)
e1 = $37 intend to spend in 2006, formula: (b1-d1-g1)/(c1-2)
f1 = $37 intend to spend in 2007, formula: (b1-d1-g1)/(c1-2)
g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15
h1 = empty cell

I have some other combinations. But these 2 are the most common.

Thank you.

"Vacation's Over" wrote:

OK so specifically what are you dealing with?
if it was simple you could just drag and drop and maintain all formulas.

if it is complicated how would a macro know that you did not spend in this
year and want to move?

please think it through and provide a few use cases


"Vic" wrote:

Thank you for your reply. However, entering the formulas based on percentage
is NOT the issue here. I need to be able to shift my budget forecasts to next
years starting from the first empty cell on the right. For example:
I intended to spend $100 in 2005, $100 in 2006 & $100 in 2007 for a 3-year
$300 project. Now, at the end of 2005, I can't spend the first $100, so I
want to move my project to span from 2006 thru 2008 (2008 would be the empty
cell on the right). All I want is to shift my budget 1 year over to the
right. It is easy to manually re-enter $100, but my budgets are very
complicated and I use many formulas. I can do this manually by moving cells
to the right and enter $0 in 2005. However, it is not practical.
I am looking for a process to automate that.

"Vacation's Over" wrote:

add a column for % for each year and put formulas into your row(1) to use the
% to generate teh cash flows

"Vic" wrote:

I have setup the following 3 year project with total $300 budget:
b1=$300, c1=3years, d1=$100, e1=$100, f1=$100, g1 is empty
if I change the value in d1 to $0 (meaning, I don't intent to spend any
money this year), I need excel to find the next empty cell which is g1 and
move data from f1 to g1, from e1 to f1 and from former d1 to e1.
This is an oversimplified version of what I need to accomplish. I have 50
projects with different year lenghts and money not always distributed as 1/3,
1/3 & 1/3. I could have 25%, 30%, 30% & 15% for a 4-year project and other
combinations.
Does anyone know how to automate this procedure so I could setup 1 formula
for each project and forecast my spendings by changing amount in the current
year (d1)?
Any help would be greatly appreciated.