• 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.

Super Complex Logic Formula (at least to me)

Y

yhernandez

New Member
I have a worksheet/ table (P&L) that is populated based on the values from an Input worksheet. The P&L is phase based (where phases only calculate costs until you reach revenues), so as the project moves from phase to the next (a phase can take multiple years/columns) the values and value of the project increase/Change. Each Phase has a probability of success to reach the other phase (POS). The cummulative probability is the LOS (likelyhood of sucess or for example at Phase I it would be POS of Phase I * POS of Phase II * POS of Phase III)


Example: (POS of Phase I = 25%, Phase II = 50%, Phase III = 75%.), therefore LOS at Phase I = 9%, LOS@PII= 37.5%, and so on)

---------------------------------------------------------------------------------

Y1 Y2 Y3 Y4 Y5 Y6 Y7 |

Phase I Phase II Phase II Phase III Revenue Revenue Revenue |

|

Sales 0 0 0 0 50 75 125 |

Costs -5 -10 -12 -15 -5 -5 -5 |

risk Adj


Profit ============================================================================

NPV


Currently, I can calculate the NPV as of the phase that begins (lets say Phase I). I would like to also calculate the NPV prospectively (the value NPV is only relevant on the column on a phase change i.e. from phase I to II, even if Phase II has 5 columns, I only want to calculate the NPV as of the first Phase II column, etc.) and what it will be as if I was already in the next phase (II or III, etc.). Having a pickle of time doing this.


Any help would be greatly appreciated.
 
Try this. https://www.dropbox.com/sh/s3b2lf2dyiuvzte/9NgJLwJUkw


See as you cycle through the different sample cases I have built the phase duration can change, and hence so does the NPV, and the subsequent NPV's I want to model.


Yamil
 
Yamil


You could add a couple of helper rows with formulas like:


E59:M59

=(E39:W39)*(E7:W7="Phase II")


E60:M60

=(E39:W39)*(E7:W7="Phase III")


Then use these as the source feeds to your NPV calculations
 
Hi, yhernandez!


Downloaded, opened, and looking at your workbook. Nice construction, well designed, :)

Let's go to the point.

If I don't want to learn how it works, and as I didn't find out at a glance what you were asking for, yes, I'm a little lazy at this time of night (GMT-3), would you please explain what are the cells you're trying to modify/add? Or write down a specs summary of what wanted to achieve? It'd very useful and time saving for those who might help you.


Regards!
 
Hui,


You are 1/2 way there, but by using E39, you are only getting the NPV vs rNPV (row 41). The problem I am having is calculating the rNPV on future phases as if is that future phase has already occurred (i.e. I am already at Phase III or NDA phase). When I go forward in time, my risk adjustment row (row 40), will be different. Maybe we can use helper rows for the POS values as well?


SirJB7,


I am trying to calculate what the rNPV values will be at each of the future Phase Changes that I am modeling. We can do this manually/macro, by changing the input cells to reflect that the phase has been completed. You then repeat the process for each future phase. But this approach is way too time consuming and would not allow to model on the fly changes. I am trying to do it via formulas. I think Hui is on to something w/the helper rows, but I need to figure out a way to project the POS adjustment in the future. Hope this helped.
 
One thing to note Hui,


Helper rows throw off the NPV formula, b/c they insert 0 value periods when instead the NPV being calculated from the helper rows should start the first period where there are values (not 0 period).


Is there a way to suppress 0 value cells for the NPV calculation of the helper row?
 
Hui, I got there w/3 sets of helper rows. Thanks again. Not as pretty as I would have liked, but gets the job done.
 
You must log in or register to reply here.
Back
Top