• Home page
    • Home page
    • Career
    • Fashion
    • Health & Beauty
    • Personal
    • Skills
    • Inspiration
  • About She Moments
    • About She Moments
    • Privacy Policy
    • Disclaimer
  • Book reviews
  • Contact
She Moments She Moments She Moments She Moments
  • Home page
    • Home page
    • Career
    • Fashion
    • Health & Beauty
    • Personal
    • Skills
    • Inspiration
  • About She Moments
    • About She Moments
    • Privacy Policy
    • Disclaimer
  • Book reviews
  • Contact

How to avoid the unnecessary pitfalls in your ExCel cash flow valuation

Mar 15, 2018 | Skills |

There are a lot of pitfalls when using ExCel’s valuation and data analysis formulas, and here are the easiest ones to avoid.

excel cash flow valuationValuation formulas

XNPV formula: It will return the net present value if you give it a row with cash flows, a discount rate, and a set of dates that match with your cash flows.

The valuation starts in the first cell of dates for your cashflows. So if you want to know what the value is at 31.03.2018, but your cash flows start at 30.06.2018, you need to create a cell for the date with 0 cash flow for it to work.

XIRR formula: This works the same way as XNPV above. You give the same inputs, (except for the discount rate), and it gives you the approximate IRR for the cash flows.

If you have a cash flow that fluctuates between negative and positive numbers, don’t trust the IRR this formula spits out. This has nothing to do with ExCel, but with the concept of IRR itself.

The cash flow errors you want to avoid

For your valuation to be correct, the cash flows need to be correct as well. The most common mistakes for cash flow models include paying out cash that doesn’t exist in the model. Luckily, there are easy ways of fixing this.

Min function: This chooses the minimum of two values. Smart if you want to build options, or if you want to pay back debt. But only as long as there is debt left to pay back, and as long you have cash.

A simple example of the last could be =Min(Cell with debt left, Planned debt payback, Cash available).

Max function: The same use as the Min function, just the opposite. Combinations of the two are quite powerful, and you can create very functional expressions.

In the first example below, the company has cash to pay down its debt each year.
In the second example, there isn’t enough cash to pay back all the debt it’s supposed to. If the model doesn’t stop reducing the debt size, this will result in a wrong valuation.

In the third example, the debt is already paid back, so no cash flow should go to the downpayment of debt.

Data and analysis

This is where you get to use the model you have put hours of work into. And if the model is set up correctly, these results should give some intuitive results.

Data validation: Alt+a+v+v  You often choose to use data validation with an input cell. You want people to be able to change the inputs, but not to something that doesn’t make sense.

If you have three scenarios for Opex, you want the user to be able to choose those three. You don’t want him to put in a negative number that is clearly unrealistic.

You get several “validation” options here. The most useful is to make it choose from a list that you have already created. If you have three opex scenarios somewhere in your model, called HighCase, LowCase and BaseCase, you link to these cells. Then you get a drop-down menu in your input cell where these are the only options to choose from.

Goal seek: Alt+a+w+g This is very useful if you want to look at scenarios. Let’s say you are buying a company that sells one product. You want to see how low the price of this product can move for the value of the company to fall down to $400m. This is where you do it.

Data table: Alt+a+w+t This is useful to check several scenarios. If we continue with the acquisition of the company from above, you want to know what happens with the price if the product price is $4, $5, $6. or $7.

Here is where you go back to the ExCel options and change the formula updates to Automatic, as I showed at the beginning of this guide.

Creating a data table for the first time can be quite tricky, so there’s no shame in following a tutorial from youtube the first times you try.

Macros and circular references

One common problem in a model is to have circular references when calculating cash or debt levels.

Many people will use macros to avoid circular references when calculating optimal debt sizing for a company. If you need this, know that your macro won’t run every time ExCel does an iteration in a data table for example. Which means your numbers will come out wrong.

This doesn’t mean that you should never use macros, but you need to understand how this limits your models later.

Building macros: I’m not going to do a full guide on how to build macros here. It’s pretty easy if you have some basic knowledge of programming. Otherwise, you may struggle.

More articles in the same series:

Over the next week, I will post more articles in the same series, helping you to more easily build a model.

 

  • Basic ExCel shortcuts you need to learn when you start in investment banking
  • How to quickly nail the formatting of your first EcXel model in banking
  • Understanding how to link correctly and roll out big ExCel models quickly
  • How to avoid the unnecessary pitfalls in your ExCel cash flow valuation
  • Error checking in EcXel: Tools to discover your mistakes before it’s too late

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Google+ (Opens in new window)

Related

Tags: cash flow valuation exceldcf excel investment bankingdfcexcel for beginnersexcel for investment bankersexcel she momentsexcel shemomentsexcel shortcuts bankingexcel shortcuts financeexcel shortcuts investment bankingexcel tutorial investment bankingexcel tutorial wall streetexcel wall streetexcel wall street formattingfaster excelformatting shortcuts excelhow to be fast in excelhow to format excel investment bankinginvestment banking summer internship excelmultiasset models excelmultiasset models investment bankingshortcuts for excelwall street internship excelwhat to learn before internship in investment bankingwhat to learn before starting as an analyst in investment banking
Share

You also might be interested in

Basic ExCel shortcuts you need to learn when you start in investment banking

Basic ExCel shortcuts you need to learn when you start in investment banking

Feb 14, 2018

Mastering ExCel is certainly one of the first ways you[...]

Understanding how to link correctly in ExCel for investment banking

Understanding how to link correctly in ExCel for investment banking

Mar 7, 2018

Writing links correctly so you can copy them all over[...]

Error checking in EcXel: Tools to find mistakes in ExCel before it’s too late

Error checking in EcXel: Tools to find mistakes in ExCel before it’s too late

Mar 22, 2018

You will most likely spend almost as much time checking[...]

My Social Accounts

  • Email
  • Facebook
  • Pinterest
  • Twitter

Most Popular Posts

  • The ultimate guide to dress code for Wall Street women The ultimate guide to dress code for Wall Street women 692 views
  • Being treated like the secretary at work Being treated like the secretary at work 488 views
  • How to quickly get over a breakup with NLP How to quickly get over a breakup with NLP 217 views
  • Work appropriate designer bags for fall Work appropriate designer bags for fall 142 views
  • About She Moments About She Moments 94 views

Newsletter

Categories

  • Career
  • Fashion
  • Health & Beauty
  • Inspiration
  • Personal
  • Skills

Contact Me

I am not online at the moment. Please feel free to contact me at any time.

Send Message

She. Moments

Take a moment to improve your career, fashion and lifestyle skills.

Please note that She. Moments has financial relationships with certain merchants mentioned. While all attempts are made to present correct information, it may not be appropriate for your specific circumstances and information may become outdated.

Search the site here

Newsletter

  • Email
  • Facebook
  • Pinterest
  • Twitter

Latest posts

  • Turning down a client who wants to date you
  • Get better at networking in finance
  • Making sense of the investment banking departments
  • The fastest way to learn speed reading
  • How to build an investment banking wardrobe from scratch
  • Error checking in EcXel: Tools to find mistakes in ExCel before it’s too late

Categories

  • Career
  • Fashion
  • Health & Beauty
  • Inspiration
  • Personal
  • Skills

© 2018 — She. Moments Theme by HB-Themes.

  • Home page
  • Privacy Policy
  • Disclaimer
  • Contact
Prev Next