As a financial analyst, your goal is to help businesses make better investment decisions by evaluating the financial status of the company, current market trends, and the projected outcome of such investments.
Literally, your advice could make or break a business. That’s why companies, during hiring interviews, make sure that their financial analyst is competitive, efficient, and accurate.
If you’re a financial analyst and you’re hoping to ace that interview, better suit up and check your Excel skills. It makes sense that your interviewer will ask you questions about Excel as it will be one of your highly-used tools.
In this article, we have prepared interview questions about Excel every financial analyst should know. Let’s get into it!
1. What are array formulas?
It’s expected that you’re already familiar with the basics of Excel. That’s why we’re kicking this off with an intermediate topic — array formulas.
First off, an array is a range of values. In Excel, you’ll be able to easily identify an array since it’s wrapped inside curly brackets {}. An array could represent vertical ranges, horizontal ranges, or both.
An array formula is a formula that performs calculations on different values instead of just a single value. These special formulas are used in conditional sums, lookups, data analysis, and a lot more.
Here’s an example of what an array looks like in Excel:
array-example
Array formulas are useful in Excel as they allow you to perform actions that can’t be done with regular formulas. These formulas allow you to do multiple calculations and return either a single result or multiple ones.
2. What are the lookup functions in Excel?
Some people say that the moment you learn how to use lookup functions is the time you stop being an amateur in Excel.
Basically, lookup functions are functions that let you search for an approximate match in a column or row and return the corresponding value from another row or column.
Previously, there were only 2 types of lookup functions — VLOOKUP and HLOOKUP. Last August 2019, Microsoft announced a new lookup function: XLOOKUP.
Let’s dive deeper into these functions:
VLOOKUP, short for vertical lookup, looks for a specified value in a column and returns another value from the same row with that of the specified value.
vlookup-sample
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Parameters:
- ‘lookup_value’ – what you’re looking for
- ‘table_array’ – where to look
- ‘col_index_num’ – what you want to know
- ‘range_lookup’ – optional; setting for returning an exact match or approximate match; if omitted, the default is to return the approximate match
HLOOKUP, or horizontal lookup, is the horizontal version of the VLOOKUP. It lets you look for a value in a row and returns a value from the same column of that specified value.
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Parameters:
- ‘lookup_value’ – what you’re looking for
- ‘table_array’ – where to look
- ‘row_index_num’ – what you want to know
- ‘range_lookup’ – optional; setting for returning an exact match or approximate match; if omitted, the default is to return the approximate match
XLOOKUP, which is also dubbed as the successor to the old functions mentioned above, can go vertical or horizontal. Learning XLOOKUP shows the interviewer how updated you are with the current trends and updates in Excel.
xlookup-sample
Syntax:
=XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])
Parameters:
- ‘lookup_value’ – the value to look for
- ‘lookup_array’ – the range or array to search for the value
- ‘return_array’ – the range or array to return
- ‘match_mode’ – optional; represents the match type; default is an exact match
- ‘search_mode’ – optional; represents the search behavior; default is “search from first value”
The point in knowing the different lookup functions and how to use them is that they’re useful in solving certain problems in Excel faster and easier. With the lookup functions, you can easily find a specific value and use it in a formula.
3. What is a pivot table? How do you use one?
A pivot table helps you handle and visualize data easily. There’s more than an 85% chance that you’ll be asked about pivot tables since it’s one of the most highly-sought Excel skills.
In simple terms, a pivot table is an Excel feature that lets you take data and rearrange it. You can make a table and choose what and how you want to view your data.
With a pivot table, you’ll be able to draw conclusions from your data more confidently. With a pivot table, it’s also easier to handle and summarize large quantities of data.
pivot-table
In a way, a pivot table is like a report generator where you can add and remove fields as you like.
Creating and using a pivot table is a broad topic. But you should know that there are different sections in a pivot table:
- Report filter
- Column labels
- Row labels
- Values
Here’s a screenshot of how it looks like to configure a pivot table:
pivot-table-fields
In addition, you can use pivot charts to visualize your pivot table. A pivot chart takes the data in the pivot table and shows a graphical representation of it. It’s similar to how you can create charts in Excel using source data.
4. What data formats are usually used in Excel?
Knowing the different data formats in Excel is sometimes taken for granted. But you never know when the interviewer will ask you about basic formatting.
Data formats shine the most especially during conversions like changing time to integer and also in conditional formatting.
In Excel, you can use data formats like:
- Strings – Text-type format which can contain letters, numbers, and punctuations.
- Numbers – Numerical values that can be formatted in decimal places and separating commas.
- Dates – Technically stored as numbers. Different date formats are available.
- Percentages – Also stored as numbers but can be formatted as percentages or in decimal form.
- Currencies – Monetary-format in different currencies.
Of course, there are more formats available in Excel. This GIF summarizes them all:
formats-excel
You’ll be amazed at how useful data formats are when building a financial model. Aside from making it easy to understand what the numbers are, you’ll also be able to take away a few steps in calculations like converting percentages to integers.
5. How do you forecast in Excel to see the possible outcomes of some business deals?
This one is a given seeing that forecasting is one of your tasks as a financial analyst. If you’re not that familiar with it, better understand this one to the heart.
What is forecasting? Forecasting is a tool you can use to make educated guesses about future investments or deals. It relies on past and current financial data as well as analysis of trends.
In Excel, there are 3 commonly used methods in forecasting:
- Moving averages
- Exponential smoothing
- Linear regression
A moving average is the average of a subset of numbers commonly used in time-series data. By understanding the average of the subsets, you’ll understand better how the trend plays in the long-term.
Here’s an example of a moving average with a chart:
moving-average
There are 3 possible ways to get the moving average:
- Manual average function
- Moving average trendline option
- Moving average tool in the Analysis ToolPak
Whichever you choose produces the same result. All of them are easy to do.
Next is exponential smoothing. It has ‘exponential’ on its name since it assigns exponentially decreasing weights on older observations. New or recent data are given more weight.
Exponential smoothing can easily be done using the ‘Forecast’ tool in Excel which uses the ‘FORECAST.ETS’ function under the hood.
Here’s what it looks like in Excel:
exponential-smoothing-sample
Lastly, we have the linear regression which uses a past variable to predict its future equivalent. In simple terms, linear regression calculates the linear relation between the timeline series and the value series similar to a causal model.
To use linear regression, you have to be familiar with the ‘FORECAST.LINEAR’ function.
Syntax:
=FORECAST.LINEAR(x, known_y’s, known_x’s)
Parameters:
- ‘x’ – the target date for which you would like to predict a value
- ‘known_y’s’ – the timeline series range
- ‘known_x’s’ – the value series range
Here’s how it looks in Excel:
linear-regression-sample
Knowing how to forecast in Excel will surely impress your interviewer. It’s a valued skill that can potentially save a drowning business.
What matters most
There are still a lot of possible questions the interviewer might throw your away. However, what matters most is that you know how to apply the functions and features of Excel in real-life business problems and scenarios.
Sometimes, you’ll be asked more open-ended questions and scenarios. For example, the interviewer might present to you a spreadsheet full of raw data. Then, he might ask you to summarize and report specific information about it.
This is where your practical knowledge of functions and features come into play. It’s important to not just understand the ‘what’, focus also on the ‘why’ and ‘how’. Once you’ve done that, you’ll surely be able to ace that financial analyst interview.
This guest post was written by Kasper Langmann, Microsoft Office Specialist and co-founder of Spreadsheeto.
