Nested formula

[Hanya]

Hi,

I need to create a formula to forecast a specific production profile. the
production profile is as follows; 50 increase for three years after start of
production, then 15 years constant production, then decline thereafter at a
rate of 15% per year.

is it possible?

 

[Pete_UK]

I used A1:B5 to set this little table up:

Start
Increase 50
years 3
const yrs 15
decline 15%

Then in A7:A8 I put "Year1", and "Year2" (without the quotes), and
filled these down. Then in B7 I put this formula:

=IF(COUNTA(A$7:A7)<=B$3,B$1+COUNTA(A$7:A7)*B$2,IF(COUNTA(A$7:A7)<=B$3+B
$4,B6,B6*(1-B$5)))

and copied this down. This is what I got:

Year1 50.0
Year2 100.0
Year3 150.0
Year4 150.0
Year5 150.0
Year6 150.0
Year7 150.0
Year8 150.0
Year9 150.0
Year10 150.0
Year11 150.0
Year12 150.0
Year13 150.0
Year14 150.0
Year15 150.0
Year16 150.0
Year17 150.0
Year18 150.0
Year19 127.5
Year20 108.4
Year21 92.1
Year22 78.3
Year23 66.6
Year24 56.6
Year25 48.1
Year26 40.9
Year27 34.7
Year28 29.5
Year29 25.1
Year30 21.3

Is that what you wanted?

Hope this helps.

Pete

 

[Luke M]

I'll assume your first statement was meant to be 50% increase for three years.
A2 = start year, B2 = initial production (1000)
In a column, copy down years, (2008, 2009, 2010, etc)
In B2,
=IF(A3-A$2<=3,B2+0.5*B2,IF(A3-A$2<=18,B2,B2-0.15*B2))

Then just copy down.

 

[Herbert Seidenberg]

Or if you want smooth S curves...
Excel 2007
http://www.mediafire.com/file/wy25nnnt1wr/12_23_08a.xlsx