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