Evaluating a string of text inside a formula

[lodidodi]

My end goal is to create a model that predicts revenue from closing fees based on the amount of deals done every month, after applying a failure rate.

For example, in month 4, I have 4 deals that will be started. Based upon a close rate of 70%, I need to predict how many will be closed.

I am doing this using the rand() formula to create a simple monte carlo analysis.

Essentially, if rand()<70%, the deal closes. The 70% is a changeable variable stored in cell K2, so my base formula is “IF(RAND()>K2,1,0)”.

The problem is that this only works when there is one deal started each month. I need to model the ability to have multiple deals started each month. To accommodate this, I have created the formula “=REPT("IF(RAND()>K2,1,0),",D5)”, assuming that D5 is the monthly deals started.

So, if D5 is 4, the result is IF(RAND()>K2,1,0),IF(RAND()>K2,1,0),IF(RAND()>K2,1,0),IF(RAND()>K2,1,0),.

My goal is to then insert this into a sum() formula to calculate the result but this is where I am stuck. I cannot get the sum() formula to evaluate this text. I tried using indirect() in a variety of ways but am having no luck.

Can anyone help?

If you have another way to model this, I would open to that as well.

 

[sericom]

You could add the following module:

Function Eval(str As String)
Application.Volatile
Eval = Evaluate(str)
End Function

Then change your formula to
=REPT("IF(RAND()>K2,1,0)+",D5)

And to evaluate it use the following

=Eval(LEFT(D6,LEN(D6)-1))

That assumes your formula is in D6.

 

[lodidodi]

The problem I found with using the evaluate formula is that it won't update when a user changes the value of an input cell. They have to manually recalculate the formula which isn't ideal for a financial model.