Simple CashFlow Analysis

EE Pros,

I have a spreadsheet that is partially completed.  I'm trying to put together a simple "Cashflow" Analysis that includes a calculation for the Break Even Point and the Net Present Value in a 36 month Analysis.  I get the cash "benefits" in a yearly number that is then spread out based on the number/12 and then started in the benefit start month.

Take a look at the model and I think you will see what I'm attempting to accomplish.  Any help would be appreciated.  For the question, let's just do the Break Even Point calculation and then I'll ask another question if I need help with NPV.

Thank you in advance,

B.
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael FowlerSolutions ConsultantCommented:
Bright01 - You did not add the attachment

Michael
0
Bright01Author Commented:
Michael,

Very sorry.  Here you go.

B.
Cashflow-Analysis.xlsm
0
SteveCommented:
you should be able to use the following to return the period number of the break even point in your model:

=MATCH(0,F13:AP13,1)

Is this what you were looking for?

I have also included the discounted cash flow rates for present value calculations.
Cashflow-Analysis.xlsm
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Bright01Author Commented:
The_Barman,

Thank you!  That is part of what I was looking for.  The other part, can you look at line 11 and see how I can make the logic for starting the benefits in the proper month?  My logic doesn't work in the model; although your formula does work for BEP.

Thank  you,

b.
0
SteveCommented:
Please see the attached file.
I have removed the complicated formula and replaced with something a tad simpler.
Hopefully this makes some sense.
Cashflow-Analysis.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
The_Barman,

Is there also a way to get 2 decimal point precision on the BEP instead of a whole number month (e.g. instead of 12, have it be 12.37 Mo.)?

B.
0
Bright01Author Commented:
Wow..... good and fast!  Yes.  That works! I'll author another question for the two decimal precision.

Thank you!

B.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.