Cash Flow Projection Calculator

[K3KK3R5]

Hi all,

I'm hoping someone will be able to help.

I have a cash flow projection calculator that distributes forecasted construction expenditure over a typical s-curve based on the information that is inputted (programme length, forecasted value).

I haven't changed the any of the formulas, but for some reason the formulas no longer work and the data is presented as "#VALUE!" whenever I try change the programme length etc.

There's no macros or anything and I'm normally quite good at determining how formulas work, but this has puzzled me for a few weeks after some googling.

It was created by my work to use on projects, but the person who created it no longer works for us.

Any help in trying to resolve the issue would be greatly appreciated!

Kind regards,

Craig

[JohnTopley]

Your posted file has no errors: what changed parameters (cells) cause a problem?

[K3KK3R5]

Hi John,

Yes, that is the original file I had when it was working and now no longer works when the data is changed.

If any of the metrics in cells H55, K55, or K56 are changed to a different value, the cash flow values in columns C and D change to #VALUE!.

Unless this is working for you and is purely an issue with my laptop and the calculator works when the data is changed...

Thanks for looking into it for me.

[JohnTopley]

I changed the values in H55,K55 and K56 and experienced no errors. And I would not expect them to produce any UNLESS the format of these cells had changed.

And please update your profile with your Excel version (365,2021 etc)

[K3KK3R5]

Thanks John. Any ideas why it may have stopped working for me at all? I've tried it with colleagues and they're experiencing the same issue.

Is there anything in the formula that relys on linking into anything that I may have deleted?

Thanks

[JohnTopley]

We need a workbook with the error: the only formula impacted by the H/K cells is the Cumulative Gross value which also uses the value in column A
which uses a custom function "Cumulative_spend" (I don't know where this is !!???)

[K3KK3R5]

Thanks John.

The "Cumulative_spend" is in the name manager which is then linked to the other tab. Really not sure how this works and that's where my excel knowledge ends.

Maybe it relies on a macro but has never done in the past and has just stopped working for me.

If I change the Construction Period from 36 months to say 24 months, it then just has #VALUE! in all the cells in column C and D.

It's a 97-2003 document that's been used for years so maybe it's due to an update to MS Excel and a function or feature no longer works or has been updated.

I've updated the excel document with 24 months, and should now display the error message in the values. If you don't see the error messages, then it is likely down to something on my system I would imagine.

Frustrating.

[JohnTopley]

When I open the file, I have the VALUE error. If I re-enter 24 in K56, I still get error: if I enter 30, then no error BUT if I now re-enter 24, no error!

I saved the file as a macro-enabled workbook (.xlsm) and ropened the workbook, there was no error. Trying to save as XLSX file produced "warning" about Excel4.macros and hence needed to save as XLSM file.

So try re-saving as XLSM file.

[K3KK3R5]

Thanks John.

I've tried saving as a macro-enabled workbook before but still the same for me.

Unsure why it's working for you and not me, my colleagues or even on my personal computer.

Thanks for your help anyway, I'll just have to live without it or look at trying to source an alternative calculator.