Help with - Combining S Curve modeling market penetration & IRR data table on: 1) timing of peak & 2) % Market penetration.

[ExcellingOcfUpsides]

Hi guys.

Would really appreciate some help with following.

Creating 72 month valuation model on a "dummy" investment amount using existing data.

ive created several ways of modelling penetration and will probably take a line of best fit from them to then feed my IRR calculation.

The task I want to do is then be able to tell:

what if maximum penetration is reached earlier (currently all models trend to max at end of 72m) & what if maximum penetration is different.

essentially this would involved shifting the penetration graph vertically / horizontally and then taking the resulting data points that would have fed such a graph and then feeding it into my IRR model.

now statically that can probably be done..but using a data table? im not sure how to do it.

im not sure if my issue with IRR data table sensitivities is due to me being beginner with them or something else but i have only been able to feed the sensitivities by doing a % of flex into the model. e.g. previously id have £100 cost per customer but to make IRR data table work to show results for e.g. 105, 110, 115 etc i had to change the formula to 100+ [cell ref] and use that as input to feed through the % diff that the IRR would run (e.g £100x(1+5%), x1.10%, 1+15% etc) ---> because of this i have to also link the results of my data table into a seperate table --> anyone know reason why?

in any case, whilst i can do it for simple variables which are generally flat but for penetration its at a curve. so i somehow need to get that to be taken into effect.

im not sure if i have explained this properly sadly.

ill try post some backup to help visualize if it helps.

alternatively if someone can suggest me a different market penetration modelling tool that would be great. as i said have 28m actual data and 8m forecast so i can model a proxy for what penetration would be but i need to dynamically be able to change the peak and timing whilst retaing elements of the curve.

 

[ExcellingOcfUpsides]

Imgur: The most awesome images on the Internet

essentially these are the penetration models. the smooth non curvy ones are the actual data (whether it be cumulative , or weighted % customers from each month per investment etc) whilst more curvy are system generated penetrations.


Essentially the data table should simulate the penetration peak being earlier and being higher / lower and feeding that through IRR.

open to any ideas.