Your Excel Skills Suck!

Excel is used in every office – healthcare, corporate, government, home, laboratory – not just investment banking. Excel is recognizable to everyone – a bunch of black text and numbers, an underline and a =SUM(A2:A10) function that adds up your expenses for the week.

What people do not know is that Excel is an extremely powerful tool of which people do not know 99.9% of the functionality. Hopping into an investment banking Excel model may not look like Excel at all – it is colorful, formatted, graphical. Excel is the tool whereby all of the quantitative analysis including return sensitivity, accretion math and data mining is conducted for bankers.

Accordingly, Excel is of paramount importance to the aspiring junior analyst or associate. As investment bankers use Excel so much, trial and error – especially to avoid another 4 AM grind – have seen best practices refined over billions of iterations. We will introduce a few here.

This post is not just for investment bankers – the reality is that the average reader’s Excel skills are garbage and whether you are an accountant, investment analyst, and definitely anyone who works in a corporate, you can avoid mistakes and have a much easier life if you follow best practices.

This is also sort of funny because 50% of investment banking job applicants list some sort of proficiency in Excel on their resumes when they are actually terrible.

Experiences with Excel in Investment Banking

To start, I am really good at Excel. Back on track…

Investment bankers, including younger managing directors as most new MDs cut their teeth during a time when Excel best practices were already firmly enshrined, are very serious about Excel skills. If a junior investment banker’s spreadsheets or models are audited without the file being well formatted, readable (easy to follow), flexible and dynamic, their analysis will be gutted and they may be relegated to PowerPoint only slave – a far inferior position in the pit.

Many a new analyst will be yelled at, or in today’s working environment – firmly reminded, over having improper formatting of Excel cells. Inputs need to be blue, formulas need to be black. Cells with unique formulas need to have shading.

At first, it almost seems like associates and VPs are anal over minutia when fretting over cell shading and how you lay out your model. It also gets annoying when they command you to omit using the mouse except when absolutely required. Eventually, operating in Excel becomes clockwork and becomes a tool to affirm your analysis. Next thing you know, you are the managing director. Congratulations!

Receiving Corporate Model from Client

Investment bankers do not fully appreciate how important Excel skills are (and how valuable they are – hence why the bankers get paid) until they are staffed on a sell side mandate and receive the corporate model from the client.

The first thing you will realize is that they are not actual models, just a hodgepodge of random hardcoded (inputs) cells from various corporate divisions. Some cells will link to other worksheets in the corporation’s drives that you do not have access to – tax division, budgeting, whatever.

When the investment bank receives the model, they basically have to rework the company’s model into a functioning, dynamic spreadsheet, which sometimes (all the time) means rebuilding the model from scratch while chasing down several divisions where everyone goes home at 5PM on the dot (and woe to the banker who tries to chase down someone in financial planning and analysis anytime on a Friday).

This is not true for the large caps – as in companies with an enterprise value of over $25 billion, as well as high performing smaller companies that pay well. Generally, a large corporate has ample resources and has their internal divisions well stocked with ex-investment bankers, including the entire corporate development team. Their models may even be better than those of bankers, and they just rely on bankers for execution. You can expect a Shell or Walmart to have a robust corporate model better than you can build it.

Incorporate These Excel Best Practices Now!

Without getting lost in anecdotes, we progress to list things you need to do or need to not do lest you annoy us and undoubtedly annoy your future employer if you do not figure them out quickly.

Format Your Cells

To make the model easy to understand for the outsider, or someone who has to take over your model or audit it.

Input Cells = Blue
Formula Cells/Links to Other Cells = Black
Links to Other Tabs/Worksheets = Green
Links to Other Workbooks = Purple
Needs to be Updated = Red
Assumptions need to be Firmed Up = Pink Shading

The first three are industry standard. The last three are from organizations I have worked in that I find helpful.

The first step is to make input cells/assumptions blue. When you change an input cell, the outputs change. So, if your assumption for inflation is 2%, make sure it is blue – so when people change the 2% to 3%, whatever links to inflation will change.

Formulas that are impacted by input cells or other formulas should be in black. Links to other tabs should be in green. All of these contribute to the spreadsheet being easy to follow pertaining to what drives the model and what constitutes the infrastructure (black cells). Errors are easily traced this way and if you need to identify why something changes, having this formatting in place makes it a lot faster.

Do not Hide Rows or Columns Unless you Group Them

If you press Shift + F10 and then H, you can hide rows or columns depending on what you have highlighted. Do NOT do this without first grouping them with Alt + H + G + G, which will add the collapsible plus or minus sign on the sides of your spreadsheet, so that people know that there are hidden rows.

This is because formulas often include hidden rows, so if someone copies formulas over your whole spreadsheet may be messed up and all of your analysis is subsequently wrong. When someone finds out there was a hidden row or column it is often too late.

As it follows, hiding entire tabs may be even worse if they link anywhere into the main book.

Check for Links from External Workbooks

Alt + E + K will show you links that are dependent on cells in other workbooks. Unless you have a very good reason and have highlighted it, these should not exist.

Usually, you do not have a good reason and the reason why there are external links is because you copied the shell of an old model and have not successfully linked everything back to your new book. If the share price of Alibaba is $199 and you have it linked to another book where the share price of Tencent (Unsponsored ADR) is $55, you are in a world of trouble.

Write Out Data Table Inputs

Data tables are very difficult to understand for people who do not use them often (and are somewhat difficult for people to understand for people who do use them often). However, if you delete a data table’s contents, you may forget what you put in originally to replicate it. If you write down in a comment or in adjacent cells what the row input cell should be and column input cell should be, this makes everyone’s life infinitely better.

Do Not Leave Excel Litter

Every once in a while, you need to calculate something quickly for your own edification. So, you quickly punch in =30/2.5 to the side of your analysis and leave it there. For some very ugly spreadsheets, the file is saturated with random formulas and numbers on the right side and on the bottom.

Don’t litter! When I am auditing your model I have no idea what this information is for and why it is used. After about 5 minutes of trying to track it I realized it has no bearing or context and delete it. Now I am mad and steal your dinner order.

Excel models should be clean and simple – no superfluous work. It either belongs in the model or it does not.

Be Absolutely Sure Before Deleting Rows or Columns

If you delete a row that has inputs to other cells that drive your spreadsheet you may find the whole thing #REFs out.

Make Sure your Balance Sheet Balances

If you are solving a problem for your first year financial accounting class and your balance sheet does not balance, you fail (ok, not really, but you should). However, this has not translated very well into the corporate world.

In many spreadsheets, the balance sheet either does not balance, fraudulently balances or does not balance once an input has changed. These are all investment banking crimes and must stop. When the model flows to projected years into the future, usually this imbalance grows, which should give you a hint as to where to look so that you can fix it (working capital changes do not flow into cash or have a +/- sign swapped).

The first one is inexcusable and grossly unprofessional – although we have seen major companies make this mistake for their subsidiaries. Humorously, none of their relationship banks brought it up because the figure was trivial but you have to laugh – especially when it says that these financial statements were audited by PwC. If the balance sheet does not balance how do we know what account has it wrong, or if anything is right at all?

The second one is when bankers “cheat” and make the check between assets and liabilities + equity round to 0. So, you have assets = 130 and liabilities + equity = 130.4333 which fortunately rounds to 0. By materiality standards, you do not really have a problem – but you do because it means that there is a problem with the plumbing of your model and your firm is being paid millions of dollars to not make mistakes like this.

The problems in case two are usually accentuated when the model is supposed to be dynamic. So in this case, once EBITDA growth is bumped up from 2% to 4%, your =ROUND(A35-A60,0) no longer covers up for you and you have an error.

Also, hint, it usually has to do with not translating foreign exchange properly in terms of remarking your debt.

OK, that’s it for now. But we will have more because you are all very bad at Excel. We will also introduce some quick shortcuts for people who are totally new to Excel or have the skillset of someone who does. Everyone can learn something, even for those who just use it for their university stats course.