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. 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. 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. Excel powers efficiency by streamlining and automating complex or manual and tedious tasks. It's used at businesses in which understanding large amounts of data and the relationships between those data sets is essential to remaining competitive in the industry. In finance, users make spreadsheets commonly to create budgets and forecasts, model scenarios, analyze variances and more. To get the most out of Excel, explore our library of FREE financial Excel templates. 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. Microsoft MVP Liam Bastick talks about the following functions as being great to learn when working with data on a regular basis. Index and Match can be used to look up values in a dataset, similar to the Vlookup function. 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.What Is Microsoft Excel?
What Is Excel Used For and What Can You Do With A Spreadsheet?
The Importance of Excel for Finance
4 Excel Functions Every FP&A Professional Must Know
#1 VLookUp
#2 HLookUp
#3 Index and Match
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", ).#4 VBA and Macros
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.
Looking to brush up on your Excel skills? Check out our list of top Excel courses for finance professionals.
Unify your departments
Move your team from different spreadsheets to the same page with all revenue data in one place.
Explore our library of free financial Excel templates for for operational, strategic and financial planning!
Learn More
