As an Analyst you’ll be spending a good amount of time using and navigating Excel. Nowadays, all financial models, projections, and calculations are done in Excel for the most part. As the A lot of your tasks will be to do the initial legwork to get the model to a point where your superiors can […]
As an Analyst, you’ll be spending a good amount of time using and navigating Excel.
Nowadays, all financial models, projections, and calculations are done in Excel for the most part.
A lot of your tasks will be to do the initial legwork to get the model to a point where your superiors can analyze the assumptions and numbers the model is projecting.
The best people in Excel can complete and navigate a full financial model without using a mouse.
1Yes, if you're that good at Excel, you don't need a mouse.
You can toggle through the program with the ALT Key + a lot of other keyboard strokes to achieve your desired outcome.
Having a strong foundation in Excel will be important to your career, and development as a professional.
Let's start making that happen…
2
Excel is one of the cornerstones of business, the best calculator in the history of calculators.
Excel allows you to predict and project changes to a wide variety of variables of once.
3Most often, its used in conjunction with assumptions about the future, or projections a company hopes to bring to light.
This gives you the opportunity to see how different scenarios will play out over time, except in the present moment, which is incredibly valuable.
4The industry you’re in and the way deals flow through the office, will determine the level you'll be using Excel.
For example, as a Real Estate Analyst, you're going to be heavily involved in Excel and developing financial models.
In Investment Banking, most of the time the Associate will handle the Excel model.
Either way, as an Analyst, you're going to need to get familiar and build a strong Excel skillset either for now, or for the future.
5
The most common use of Excel today in business is through Modelling.
Companies use Excel to try and model the way a certain outcome will look in the future, based on assumptions and predictions they believe in.
This gives companies a way to understand how an investment, deal, or transaction will playout based on projections, assumptions, and analysis they’ve created.
It is used in all types of businesses that require you to try and predict the way a certain decision will play out.
6Analysts are usually tasked with helping or completing a model that involves inputting data, finding information, and making assumptions.
Modeling takes some time and repetitions to get good at, but it’s one of the main ways Excel is used.
7
Another great way to use Excel is to see different scenarios and outcomes using various assumptions and variables.
This one works hand in hand with modeling.
Often times, your boss or superiors will want to see how the model looks under different assumptions and variables.
Once the model is designed and created, it’s easy to see how different scenarios look, and what they’re outcomes would be.
8All you have to do is change the requested metrics, in order to see how it affects the final result.
Often times, you’ll be trying to figure out a solution or the right way to structure a transaction.
Having the opportunity to toggle through different scenarios is incredibly useful rather than have to go back and redo a whole model.
9
Working in Excel requires a level creativity and problem-solving. Often times, you’ll be given data or objectives and will have to figure out a solution.
You’ll be asked to produce a result but might not be explained or walked through on how to achieve it.
This is where you have to be creative and find a solution that produces the desired result.
10Think you’re a handyman, and you have different hammers, nails, and wrenches. These re the different formulas in Excel (VLOOKUP, Index Match’s, If Statements, etc).
Essentially, the different formulas, techniques, and strategies in Excel are going to be your tools in order to solve the problems assigned to you.
The best handymen are also the most knowledgeable about the situations they’re in.
You’ll have to show the formulas you use in the cells, but you’ll have the freedom, for the most part, to figure out how to achieve the desired result.
11
Another great way to use Excel is for designing figures and charts.
It gives you an opportunity to have clean formatting, and a professional feel. As a deal or project moves up the ladder, it gets further and further removed from the Analyst or Associate utilizing Excel.
The higher ups want to see the data and conclusions supporting your hypothesis.
12Often times, there will be a memo or a summary of the conclusions you’ve found in Word.
This is where you’ll summarize the key points and highlights of the model.
The best way to condense the model, while still conveying your point and the conclusions you came to, is by using Excel to design figures.
13By using Excel’s ability to format cells into tables and figures, allows you to summarize the data you analyzed, while referencing the data within each cell.
This is incredibly valuable if there happens to be a mistake in the numbers, and you’re able to look back and see in what cell and which formula is causing the mistake.
Showing you exactly what you need to correct.
14
We wanted to give you a summary of the formulas we primarily used when we were an Analyst.
VLOOKUP: The VLOOKUP function is going to be one of the formula’s you’ll be using to locate data with a specific criteria or keywords. It works in a left to right fashion, meaning you can only find a cell if it is to the right of the variable you’re looking up.
15HLOOKUP: The HLOOKUP function is used when you’re looking to find a variable by using data or criteria from the top of a data table. This function is used in a top to bottom format. It has the same functionality as VLOOKUP, but a different format.
16IF: The IF function is incredibly important for creating and filtering cells based on data. The IF function provides you the opportunity to create rules for certain cells and calculations. You can filter out the cells that don’t meet the criteria you specified in the if statement. Instead of having to search through each cell and determine if it meets your criteria. Excel will do it for you with an IF statement.
SUMIF: The SUMIF function allows you to sum a group of cells based upon your criteria. This formula adds up the different cells which meet the criteria you’re looking for.
17AVERAGEIF: The AVERAGEIF function allows you to average a group of cells based upon certain criteria. This formula averages the different cells which meet the criteria you inputted.
COUNTIF: The COUNTIF function allows you to count how many cells meet the criteria you specified. This formula counts the number of cells that meet your criteria in a given data set.
DATEDIF: This is a secret formula, not many people know about. Use the DATEDIF formula to determine how many days, months, or years there are in between two dates.
18
Text functions help you to format data in a way that allows Excel to produce the results you’re looking for.
Often times you’ll be given data in poor formats, or not in the structure you need them to be in.
You can edit each cell by hand (hard coding), or you can use the text formulae to pull the characters or information you need.
19RIGHT: The RIGHT function allows you to pull a specified number of characters in a cell from the end of a function (the right side).
LEFT: The LEFT function gives you the ability to pull a specified number of characters in a cell from the beginning of the function (the left side).
20MID: The MID function allows you to start a text function in the middle of a cell if you need to. Based on the number of characters you specify, the function will start in the middle, and return the specified number of characters.
CONCATENATE: The CONCATENATE function allows you to put multiple different cells together with different text. It gives you the ability to combine multiple cells into one. This is a great way to create unique criteria for LOOKUP functions.
FIND: The FIND function allows you to find a certain character in a text cell, then start a function from that point in the cell.
21AND: The AND function gives you the ability to search for multiple criteria. If you’re looking for a cell that needs the first AND second criteria to meet it, that’s where the AND function comes in. The cell must have both or all the criteria you inputted into the AND function in order to return true.
OR: The OR function allows you to locate a certain cell-based upon multiple criteria. It gives you the option to find a cell-based on one OR two OR more variables.
SUMPRODUCT: The SUMPRODUCT function allows you to multiply two groups of cells at once, then sums them automatically. Its great when you need to multiply a lot of data in two separate rows at once. The sum of column A times column B.
23
Every task is going to require a different angle or formula to achieve your desired result.
The goal is to produce the result you’re looking for by using the internet, the people around you, or your boss.
There are a lot of small things to remember when using excel. When pasting, consider the format of the original text, and the text or format of the model.
24If what you paste does not conform with what you need, use the Paste Special options and find the option that works best.
If a Formula you’re using isn’t working or coming up as a #REF, try using a Shift + Command + Enter, and see if that works. Sometimes it does, sometimes it doesn’t.
25The ALT key is the most important key on the keyboard. The ALT key gives you the ability to access any button, in any section or area in Excel. If you’ve ever heard of people not needing a mouse to complete a model or excel, it’s because of the ALT key. ALT + (everything) is an option in Excel and expedites the process.
26
Excel Tip: How to use “INDEX MATCH” in Excel What is the “INDEX” Function?
The INDEX function will take a cell range and then return a cell that is featured within that range (the range is determined by a count in which the user provides). The formula would look a little something like this: =INDEX(range, row_or_columns).
28It will count the cells that you've specified within the formula (row_or_colum) and give you total number output. While it isn't the most powerful function when applied on its own, INDEX can be incredibly useful when it is paired alongside other functions (such as MATCH)
The MATCH function will allow you to identify the position of a cell within a given range by pairing it alongside a “criteria string”. The formula is going to look like this when written out: =MATCH(lookup_value, lookup_range, match_type)
29This will let you know where a cell is located, as opposed to gathering numerical information. When you pair both INDEX and MATCH together, you get the best of both worlds. That's what makes the INDEX MATCH function such a reliable formula to use.
30To make use of this function efficiently, you're going to need to understand how intricate the formula is. If you misspell anything or type in the wrong words (in the wrong order), things aren't going to work out. Use the INDEX function to find a range of numbers that you're looking to identify values within.
31Once you've got your range of numbers, you can use MATCH to alert Excel about how many cells you wanted to count into the INDEX range.
This will be based on the value you give. It's a lot of information to take in, especially if you're a novice, but you should open Excel and try this out firsthand.
Personal experience is going to do you much better than any online tutorial would!
32The INDEX MATCH function will give you the ability to: Pull data from columns and rows without having to count Safely insert columns and rows, without harming the rest of the sheet Lookup data that would be considered “going backward”, as in pulling data from columns that are before your starting point
33Make use of a more complex function, as there are many other things that you can accomplish through the use of INDEX MATCH.
INDEX MATCH function is its own thing entirely. This function will allow you to look up specific values within a table based on data from other rows and columns.
VLOOKUP, you'll be able to apply this process to rows and columns at the same time (if you wanted to).
34Some Excel professionals will completely change the way they operated and remove VLOOKUP from their equations entirely, swapping it out for INDEX MATCH.
It's merely a combination of both “INDEX” and “MATCH”, which are individual functions within Excel.
35
Excel is the world’s best calculator.
There is no other program that offers you the same functionality and ability to model better than Excel. The number of variables you can include in a calculation is endless.
All companies use Excel in their financial evaluation and assessments because it presents you the opportunity to forecast the future using endless variables, assumptions, and formulas.
36Everything in Excel works off the formulas, their rules, and their functionalities.
A lot of the analyses and decisions will be based on a model or Excel.
As an Analyst its incredibly important that you can defend, and clearly elaborate on the work you did. All the elements, variables, and assumptions you used to create it.
If a superior asks you about one, which they will, you’re going to have to defend it and defend it well.
37If a superior asks you about one, which they will, you’re going to have to defend it and defend it well.
Excel is going to be around for a long time and for a lot of your business career.
It’s an essential part of becoming a great Analyst.
38