Bell Curve Cash Flow Model

[Anonymous]

I’m having a problem with the bell curve function on the cash flow modeling. Any suggestions? My answers are coming out at 2x the budget value, so I suspect I have a sign reversed somewhere. I can post the formula if that helps.

[Michael Belasco]

Hi Scott. It’s a little hard for me to tell you exactly what’s going on without seeing the formula, but short of posting the formula, one thing you might want to do that I frequently do with long complex formulas that are giving me problems, is I like to first copy the formula from one of the cells and then go to a blank cell and first put an apostrophe (‘) in front and then paste the formula in and hit enter. Putting the apostrophe in front prevents the formula from calculating and allows you to see the formula laid out as text so you can analyze it.

In this instance, what you can do is in the cell right above or below it, put another comma in and paste the formula as displayed in 2.4 and which I copied here:

=(NORM.DIST((AND(J$9>=$D10,J$9<=$E10)*(J$9-$D10+1)),$F10/2,$G10,TRUE)-NORM.DIST((AND(J$9>=$D10,J$9<=$E10)*(I$9-$D10+1)),$F10/2,$G10,TRUE))/(1-2*NORM.DIST(0,$F10/2,$G10,TRUE))*$C10
Look closely at this formula and yours side by side. Pay close attention to dollar signs, commas, etc. and try to identify any differences. Also, as a further hint, in this formula, row 10 is referring to the budget line item, while row 9 is the row that contains the fiscal month.

Why don’t you start there and let me know if you are able to resolve. And if you are continuing to have problems, post your formula and we can take a look.

[Anonymous]

Michael-

That was very helpful – I was missing parens around the first AND. But I’m still getting the same result. Here’s my formula: =(NORM.DIST((AND(L$9>=$E20,L$9<=$F20)*(L$9-$E20+1)),$G20/2,$H20,TRUE)-NORM.DIST((AND(L$9>=$E20,L$9<=$F20)*(L$9-$E20-1)),$G20/2,$H20,TRUE))/(1-2*NORM.DIST(0,$G20/2,$H20,TRUE))*$C20. It’s in cell L20 on the MonthlyCF tab in the template.

The dates are working – e.g I’m only getting design expenses during design months, but I’m getting 2x the budget values.

Thanks again, I appreciate the help.

Scott

[Michael Belasco]

No worries. You are extremely close. Just the difference between a letter and a plus and minus sign, see below:
——————–
=(NORM.DIST((AND(L$9>=$E20,L$9<=$F20)*(L$9-$E20+1)),$G20/2,$H20,TRUE)-NORM.DIST((AND(L$9>=$E20,L$9<=$F20)*(
L

$9-$E20

–

1)),$G20/2,$H20,TRUE))/(1-2*NORM.DIST(0,$G20/2,$H20,TRUE))*$C20

———————
The L that is separated should be K referring to the previous period and the negative (-) sign should be a plus (+) sign.

Let me know if this solves the issue.

[Anonymous]

That was it exactly, thanks!

[Author]

Posts