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 that complement 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. 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.
Financial analysis includes (but isn't limited to):
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:
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:
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:
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.
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:
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.
Format text colors using the following guidelines:
Format numbers are as follows:
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.
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 MoreEvan Webster is an experienced sales professional and storyteller with a passion for innovative technology. He currently serves as a Senior Area Sales Manager at Vena and previously worked as a Content Specialist. He continually strives to inspire finance professionals to become strategic business partners and is dedicated to helping them automate and streamline their planning processes so they can make better decisions with reliable, data-driven insights—enabling meaningful growth for organizations across the globe.
