• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Last edited:

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
 
Top