Excel for Finance: Spreadsheet Skills for FP&A and Beyond

This blog was first published in 2020 and updated on December 8, 2021.

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 so you can get the most out of this tool.

The Importance of Excel for Finance

Excel is to finance what the crane is to construction. 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 that complement and leverage the power of Excel. So, let’s learn some tips to unlock Excel’s power for FP&A and beyond.

FP&A software powering your plan to grow

Drive smarter, more data-driven business decisions with numbers you can trust.

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.

Text Colors

Format text colors using the following guidelines:

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

Numbers Formatting

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 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 Financial Analysis

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

1. P&L, balance sheet and cash flow
2. Financial modeling and business valuations (discussed in the next section.)
3. Budgeting and forecasting
4. 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.

Financial Models in Excel

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:

1. Three Statement
2. 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:

1. Assumptions and drivers
2. Income statement
3. Balance sheet
4. Cash flow statement
5. Supporting schedules
6. Valuation
7. Sensitivity analysis
8. Charts and graphs

Financial Reporting in Excel

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.

Let your data become your direction

Vena breaks down planning silos across the organization and empowers you to turn numbers into narratives.

Importing and Manipulating Data

Instead of manually inputting your data into your Excel spreadsheets, importing is the logical next step for efficiency. This process involves extracting data from another program and populating them on predefined areas of the Excel workbook. You can also keep your extracted data in an external database and access them through a data connection.

When you import data into Excel, you save time and avoid the associated headaches of manual inputs. After your direct or indirect connection points are established, refresh your workbook instantly.

Before starting the import process, you must map data fields from the source, e.g., general ledger, to the output file. This initial exercise is crucial and must be tested. Otherwise, as the old saying goes, garbage in, garbage out. After that, the data will transfer correctly. The process can be repeated as many times as needed.

VLOOKUP and HLOOKUP

VLOOKUP and HLOOKUP are two important functions in Excel for looking up and matching a specific text or value in a (V)ertical column or (H)orizontal row to bring back the desired result. You use VLOOKUP if your table has column headers and HLOOKUP if it has row headers.

VLOOKUP

In the example below, the table ranges from A1 to B11. The formula on B13 brings in the value from the table that matches the name on A13.

In the formula, lookup_value is A13, table_array is the table range A1:B11, col_index_num is 2 because the desired value is in the second column and [range_lookup] should be FALSE to avoid an incorrect amount being returned when the lookup value doesn’t exist.

HLOOKUP

This function, shown below, is not as widely used as VLOOKUP. HLOOKUP’s logic is the same as VLOOKUP, except it works horizontally. Notice the row headers must be in the left-most column for this formula to work.

VBA and Macros

VBA stands for Visual Basic for Applications, the programming language used in Excel and all Office programs. FP&A professionals 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.

Index and Match

Index and Match are two separate Excel functions that are often combined in place of VLOOKUP. The key distinctions between them are:

1. VLOOKUP formula has a static reference to a particular column. Adding or deleting a column within the referenced range may break your formula. That same action will not break an Index Match formula as it uses dynamic references.
2. Index Match looks vertically and horizontally.
3. Index Match calculates faster than VLOOKUP, especially when the table range is large.
4. For VLOOKUP to work, the lookup value must match something within a column on the left of the desired results column.
5. Use Index Match when your answer depends on more than one lookup column.

In the following example, a Sales $ of $25,000 is found when the Product is Widget 4. Either Index Match or VLOOKUP will work here. INDEX requires you to define the range where the result will be found, i.e., Column C. 

Then combine that with MATCH, where the lookup value in F2 must match something in column B. Adding zero at the end forces a perfect match.

In the next example, Sales Volume of 8,500 is found when Region is West, AND the Product is Widget 1. This requires matching two values instead of one. You will need an advanced formula to match more than two values.

Automate Tedious Processes with Excel for Finance

While no one questions Excel’s abilities to calculate and produce elegant reports, the effort to maintain them is often manual and tedious. So why not automate these processes wherever possible? Here are some of the top use cases for automation:

1. 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.
2. Automate workflows to route tasks from one person to the next, including the approval process.
3. Combine external data stored in other programs into a database and integrate with Excel. The possibilities for data transformation are endless.
4. Generate high-volume reports automatically on a schedule, or allow a self-serve service to generate reports on demand.

When you automate routine functions in Excel for finance, you save time and money. With modern software like Vena Solutions, automation is a breeze.

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.

(On the subject of analysis, take a look at this post next to learn how Vena’s new Modeler makes what-if analysis even easier)

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.

Unify your departments

Move your team from different spreadsheets to the same page with all revenue data in one place.