# Custom timing of growth factors?

#### Gregg Wolin

##### Member
I apologize for the title but I was having a hard time putting my question into a few words...

I have a model that uses a projection of market share over a 30-year period (monthly).

Sales Start (Mo) 36
Capture: Year 1 1%
Capture: Year 2 2%
Capture: Year 3 4%

Annual Growth 1%
Capture Cap 8%

My existing model works fine IF the analysis starts at time=0. I'd like to ability to delay the start of sales for a period of months (say 36), at which time the Year 1, Year 2 and Year 3 capture rates are used. After the 36 months of sales, months 37-48 will be the Year 3 monthly rate + 1%, Year 5 will be the Year 4 rate +1%. This continues until the period where the annual growth would result in a capture rate > the Capture Cap at which time the capture rate stays flat.

The attached workbook illustrates what i am after.

#### Attachments

• 16.7 KB Views: 5
Last edited:

#### Peter Bartholomew

##### Well-Known Member
How about a look-up table?

The capture years are just the numbers [0-7] with number formatting for the text.

#### Gregg Wolin

##### Member
How about a look-up table?
View attachment 71502

The capture years are just the numbers [0-7] with number formatting for the text.
This requires having the GrowthTable with all years included and doesn't use the Capture Cap as a test. I only want to enter capture rates for the Years 1-3 after which the capture increases annually at the Annual Growth rate until the Capture Cap is hit at which point it stops growing..

#### Peter Bartholomew

##### Well-Known Member
The capture cap is obtained automatically through the use of LOOKUP (though I use XLOOKUP). I had simply made a judgement that the rules to cover the ranges [-∞,0], [1,2], [3,7], [8,∞] would be less transparent than the lookup table.
Code:
``````= IFS(
month<start, 0,
month<start+24, 1+QUOTIENT(1+month-start, 12),
month<start+72, 2+QUOTIENT(1+month-start, 12),
month>72, 8 )
/ 100``````