Excel for Finance: Excel Formulas & Skills You Need To Know - Vena (2024)

Finance professionals live and die by Microsoft Excel. Proficiency in Excel is crucial to your success, as it's still the most widely used software for spreadsheets. And yet, most of us only use a fraction of Excel's available functions. But today, we're going to tell you everything you need to know about Excel for finance, and give you 4 functions every FP&A professional should know.

What Is Microsoft Excel?

Microsoft Excel is the industry leading spreadsheet software program. It enables users to develop and manage databases for data analysis, data reporting and data visualization.

What Is Excel Used For and What Can You Do With A Spreadsheet?

Excel has many functions that lets users track their progress and make calculations quickly. Users can easily organize, sort, calculate and visualize data from different sources.

To get the most out of Excel, explore our library of FREE financial Excel templates.

The Importance of Excel for Finance

Excel is to finance what the crane is to construction, what CPE credits are for CPAs. You must have Excel skills to succeed in FP&A. In the United States alone, at least 60% of businesses use spreadsheets.

As a stand-alone software, nothing comes close to Excel's ability to process mathematical equations and manage and store data. Calculations that would normally take hours to complete can be presented in spreadsheets in seconds.

Any number of what-if scenarios can be shown with a few assumption updates. What's more, there are enterprise-class solutions thatcomplement and leverage the power of Excel. So, let's learn some tips to unlock Excel's power for FP&A and beyond.

4 Excel Functions Every FP&A Professional Must Know

Microsoft MVP Liam Bastick talks about the following functions as being great to learn when working with data on a regular basis.

#1 VLookUp

• Open the Excel file that contains the data you want to use for your Vlookup.
• Select the cell in your worksheet where you would like your Vlookup to appear.
• Type in the LOOKUP function, starting with an equal sign followed by "VLOOKUP" and then an open parentheses (e.g., =VLOOKUP().
• Enter the lookup value, or the value you wish to search through to find a result, within quotes inside parentheses, followed by a comma (e.g., =VLOOKUP("value",).
• Choose the table array from which you wish to return data from and enter it directly into your formula, followed by a comma (e.g., =VLOOKUP("value",A1:B10,).
• Pick which column number you would like the Vlookup to return information from and enter it into your formula using a comma (e.g., =VLOOKUP("value",A1:B10,4,).
• Finally, close off your formula with either TRUE or FALSE specifying whether or not you are looking for an exact match of your lookup value within your table array and then press Enter (e.g., =VLOOKUP("value",A1:B10,4,FALSE)).

#2 HLookUp

• Open the Excel file that contains the data you want to use for your Hlookup.
• Select the cell in your worksheet where you would like your Hlookup to appear.
• Type in the LOOKUP function, starting with an equal sign followed by "HLOOKUP" and then an open parentheses (e.g., =HLOOKUP().
• Enter the lookup value, or the value you wish to search through to find a result, within quotes inside parentheses, followed by a comma (e.g., =HLOOKUP("value",).
• Choose the table array from which you wish to return data from and enter it directly into your formula, followed by a comma (e.g., =HLOOKUP("value",A1:B10,).
• Pick which row number you would like the Hlookup to return information from and enter it into your formula using a comma (e.g., =HLOOKUP("value",A1:B10,4,).
• Finally, close off your formula with either TRUE or FALSE specifying whether or not you are looking for an exact match of your lookup value within your table array and then press Enter (e.g., =HLOOKUP("value",A1:B10,4,FALSE)).

#3 Index and Match

Index and Match can be used to look up values in a dataset, similar to the Vlookup function.

• Open the Excel file that contains the data you want to use for your Index/Match lookup.
• Select the cell in your worksheet where you'd like your Index/Match lookup to appear and type in an equal sign followed by "INDEX" and then an open parentheses (e.g., =INDEX()).
• Enter the range of cells from which you wish to extract a value within quotations, followed by a comma (e.g., =INDEX(A1:A10,) ).
• Type in the row number within parentheses (e.g., =INDEX(A1:A10, 4 )).
  After another comma, enter in MATCH followed by another open parenthesis and enter the value you are looking for, surrounded by quotation marks (e.g., =MATCH("value", ).
• Finally, after another comma, type in 0, 1 or -1 specifying whether or not you are looking for an exact match of your lookup value within your table array and then press Enter (e.g.,=MATCH("value", A1:B10 ,0)).

#4 VBA and Macros

VBA stands for Visual Basic for Applications, the programming language used in Excel and all Office programs. use VBA to create macros that execute computer instructions and automatically run routine tasks. If you've worked with a button created in Excel that allows you to click and execute some actions, that's an example of a VBA macro.

For the lay Excel finance user who doesn't know how to program in VBA, you can still record a macro by going to View>Macro>Record Macro. At the next screen, click OK and then perform the actions you want the macro to do.

When finished, go to View>Macro>Stop Recording.

To run the macro, go to View>Macros>View Macros.

Then select the macro you've created and click Run.

Once the macro is recorded, go to the Visual Basic Editor (VBE) if you need to edit it. The quickest way is the shortcut ALT+F11.

In the VBE, to edit the macro that you've just created, click Tools>Macros. Then select your macro from the list and click Edit. The example shown is a simple copy and paste command, but the Macros can be used for more complex tasks.

Why Do Finance Teams Love Excel?

#1 Excel Makes Financial Analysis Easy

Financial analysis includes (but isn't limited to):

• P&L, balance sheet and cash flow
• Financial modeling and business valuations (discussed in the next section.)
• Budgeting and forecasting
• Account reconciliations

Excel is widely used in finance and accounting because it's easy to use and has an unmatched depth of financial functions. On the front end - reports and dashboard summaries; on the back end, data stored and retrieved for calculations.

It's best to know what you want to accomplish before starting your analysis. If possible, use an existing model to copy from. If not, start from scratch.

General best practices include:

• Gather and enter all the data you need first.
• Analyze horizontally and vertically; formulate ratios wherever they add value.
• Add error and balancing checks to catch entry and formula mistakes.
• Keep your spreadsheets simple and auditable.

#2 Excel Makes Financial Modeling Easy

An Excel financial model mathematically represents a company, a financial asset or a portfolio of assets/companies.

Prepare financial models according to a purpose. Two popular models are:

• Three Statement
• Discounted Cash Flow or DCF

Financial modeling simulates the financial performance of an asset over three to ten years. The information enables decision makers to buy, sell, expand, merge assets, or take any other course of action. Often, the end game is to provide a valuation for a financial asset's worth.

As noted above, know and understand the purpose of your financial models and keep them simple, auditable and transparent. If you build them on the fly, you will likely end up in formula hell facing a clunky model.

While it's possible to design them in other ways, use the following sections as a guide from the top down:

• Assumptions and drivers
• Income statement
• Balance sheet
• Cash flow statement
• Supporting schedules
• Valuation
• Sensitivity analysis
• Charts and graphs

#3 Excel Makes Financial Reporting Easy

Many businesses use Excel to report their financials. At a minimum, these should include the P&L, balance sheet and cash flows.

Basic financial statements are needed every month-end and most companies produce the full set quarterly. Financial statements also include numerous supporting schedules.

Gone is the old way of typing statements on word-processing programs. More and more, businesses prepare financials in Excel, often complemented by another data management and reporting software solution.

Once you've built the statements, updating them is easy. You no longer have to manually search all the statements to fix a change in affected areas. In Excel, that same change now cascades and updates automatically.

Use your existing financials to build your templates. Ideally, you should create each statement in its own worksheet within the same Excel file, linking wherever possible. Add supporting schedules to feed into your statements. Enter all the required numbers. If available, use an integrated solution and follow best practices discussed in previous sections.

Explore our range of free financial reporting templates for Excel to help you get started.

#4 Excel For Finance Improves Efficiency By Automating Routine Tasks

We've already covered important Excel functions, but did you know you can do so much more. Here are some of the top use cases for automation:

• Automate data entry. Importing data saves time and reduces or eliminates human error. Sometimes even a minor mistake can have a significant impact on finance.
• Automate workflows to route tasks from one person to the next, including the approval process.
• Combine external data stored in other programs into a database and integrate with Excel. The possibilities for data transformation are endless.
• Generate high-volume reports automatically on a schedule, or allow a self-serve service to generate reports on demand.

#3 Excel Lets You Visualize Data Through Spreadsheet Formatting

Beyond its financial functions, Excel's formatting tools make it highly desirable for report presentations and executive dashboards.

Simple formatting can do wonders to make your spreadsheets stand out. And when you use conditional formatting, brilliance is within reach with reduced errors.

Using the profit and loss (P&L) model below to demonstrate all the tips, let's look at some formatting guidelines. Read our article on how to format your excel spreadsheet for more detailed tips.

Text Colors

Format text colors using the following guidelines:

• Blue for constants and hard-coded numbers such as historical data and assumptions.
• Blackfor formulas linked to other cells within the same worksheet.
• Greenfor formulas with links to other worksheet(s) within the same file, although black is often used here too.
• Redfor formulas with external links to another file.

Formatting Numbers

Format numbers are as follows:

• Currency symbols: Used for monetary values in financial models and reports; the correct format to select is Accounting with the relevant currency symbol. Generally, use the symbol only on the first and last rows to avoid cluttering the spreadsheet. Typically, P&L, balance sheets and cash flows follow this format.
• Decimal places: Are not normally used for financial figures. For monetary values such as cost per unit, share prices, earnings per share and other similar representations, the correct format is Currency, symbol and two decimal places.
• Percentages: Normally shown with the % sign with one decimal place (e.g., 0.1%). To distinguish them further from regular numbers, italicize except when used in assumptions.
• Dates: Use the proper format to avoid errors in calculations. When dates are incorrectly formatted as text, formulas referencing these cells will result in errors.
• Negative numbers: Show them inside brackets, so they are distinctive. A negative amount entered as a positive number can have significant or even devastating effects on financial results.

Excel for Finance: Still The Best

In FP&A, Excel is a must-have skill. Financial analysis, modeling and reporting are done in Excel because of its unmatched capabilities in mathematical calculations, formatting and VBA/macro tools.

Many organizations have tried to move away from Excel, only to find themselves coming back to it. Why force the finance folks to learn new applications when you can complement Excel with an integrated solution?

The result is more efficient teams and more fulfilled finance professionals. And when you use Vena, you overcome Excel's limitations by adding data integrity, up-to-the-minute updates and version controls. Excel is here to stay.