• 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

  • CaptureGrowth.xlsx
    16.7 KB · Views: 5
Last edited:
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..
 
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
 
Back
Top