Search
  • James Richard

Dear Excel, How can FP&A professionals make the most of their spreadsheets?

After (almost) 40 years, there is a reason why FP&A and accounting professionals continue to use Excel. It works.




But what happens when your company grows and Excel is used for much more than just number crunching? Despite popular thinking that Excel can’t work for enterprises and beyond, you still don’t need to abandon the Excel ship.


Since the creation of Excel, software—particularly FP&A software—has continuously advanced. However, few —if any—have been able to compete with Excel's data storing and mathematical processing capabilities. From financial forecasting and financial modeling to budgeting templates and month-end closing, reporting that would have taken hours can be presented in minutes via Excel spreadsheets — as long as you know what you are doing.


To get the most out of your spreadsheets, your accounting and FP&A teams should take a lesson from this article on Excel for finance to guarantee your staff has all the Excel abilities they need at their fingertips. Then, as your enterprise needs grow, you can integrate Excel with an automated solution.


The bottom line? Excel can still be used for finance. Simply discover how to make it better.


1. Conditional Formatting

The coolest Excel tool, conditional formatting, is located on the home ribbon and offers an almost limitless range of options.



This function allows you to format cells based on the conditions you set.


For instance, a formula can be used to assign that all cells inside a range that include a number greater than 45 should be red, and all cells that contain a value lower than 45 should be green.


It’s as simple as:

  • Choosing the New Rule option from the Conditional Formatting dropdown menu.

  • Then selecting “Use a formula to determine which cells to format.”

  • Then, in the “Format values where this formula is true:” box, enter the condition’s formula, and voila!

And, here’s the beauty of conditional formatting — if you change the variables of any green cells to equal 46, the cell will automatically turn red.



How it works in the world of FP&A

Conditional formatting makes it easier for accounting and finance professionals to identify any warning signs. Recall the simple example of the red and green cells. Overdue bills could be in cells with values greater than 45. So, any cell with a red highlight lets you quickly identify which invoices are past due. You'll also notice that any green cells are less than 45 days after the invoice date. Conditional formatting can be used as a quick glance or a comprehensive deep dive when balancing your books and general ledger.


Conditional formatting allows for a lot of ingenuity on the part of the user and how they choose to implement formatting. So if you need to take a deep dive into your sales data, that’s easy, too!


2. Macros

Now that we have already met the coolest function in Microsoft Excel, we can move on to meet the most powerful function in Excel. With macros, you can automate almost anything in Excel with Visual Basic for Applications (VBA). You can start by taking a quick course to learn VBA, which is a pretty simple programming language to master. In our opinion, VBA is a must-have for finance functionaries.


Don't worry, though; even if you have no experience with programming, you can still record macros to perform some simple tasks, and you can even create buttons to run these macros and place them on your Quick Access Toolbar (QAT).


Macros are a simple way to automate repetitive tasks. You simply record the steps and save. With your predefined macros, you can click a single button to execute those pesky repetitive tasks. Here’s how:



  • Using the Developer Tab: Make the Developer tab and ribbon visible. If you do not see the Developer, right-click any ribbon, and then select Customize the Ribbon. Next, check the Developer item in the right pane of the dialog box. This ribbon has various buttons for working with macros. When you’re ready, click the Record Macro button on the Developer ribbon. This turns the recorder on. It will record everything you do until you turn the recorder off.

  • When you click the Record Macro button, you will see the dialog box — fill it out and click OK. You can give the macro a meaningful name (no spaces), and you can specify where it should be stored. This will make the macro available at all times.



  • Saving Your File: Starting in Excel 2007, macro files must have the .xlsm extension (“m” for macro). You aren’t allowed to save such a file as an .xlsx file.

  • Storing Your Macros: There is a particular file, Personal.xlsb, that’s called your Personal Macro Workbook. This workbook is where you will keep your recorded macros. This file opens automatically as a hidden file whenever you open Excel. Therefore, all of its macros are always available in all your Excel files.

  • Writing and Editing Macros: If you want to write your macros, or if you’re going to look at recorded macros, you need to go to the Visual Basic Editor. You can do this from the Visual Basic button on the Developer tab or, more efficiently, with the Alt+F11 keyboard shortcut.



How it works in the world of FP&A

FP&A professionals use automated macros for financial modeling. Defining your problem is the first step in using VBA to create macros for your model. Once you know the insight you need from the model — for example, if you want to forecast sales for Q4 — you can easily structure your logic, input your variables, define your output, and then run the macro. Model complete.


From mergers and acquisitions to corporate finance, modeling helps to identify the long-term financial health of a company. In addition, it provides forecasts and estimates of the capital outflow, which aids in finance.


The bottom line is that macros give you data visualization and the ability to make the business decisions your enterprise needs.


3. Pivot Tables

We have met the coolest and most powerful Microsoft Excel functions. It's finally time to meet every finance manager's and CFO's dream: the pivot table.


You can slice and dice data in a variety of ways using this function. Pivot tables allow you to break down variables like sales and revenue by categories, such as region, month, and store.


Even though pivot tables simply require a few steps, the payoff is huge.


  • Select a cell in the dataset and choose the Pivot Table button on the Insert ribbon.

  • Excel guesses the data range (which you can override just like you can with the auto sum feature) and then asks where you want to place the pivot table.




How it works in the world of FP&A

Pivot tables are a CFO’s dream as they take a dataset and present it as a table that can be modified or sorted based on numerous variables. Pivot tables provide various perspectives in a single table.


Once you are in the pivot table menu, you can drag categories like gender, region, paid with, and total costs to the rows and filter area. Each number in the pivot table is the sum of the total cost for its category values.




4. Charts & Graphs

Charts are the storytellers of Excel, despite not being the coolest, most powerful, or the favorite of your CFO. A well-designed chart is the best way to tell a narrative, and Excel offers a wide range of charting options.


It takes just two easy steps to create charts that explain the meaning of your data to the decision-makers at your organization:


  • Use the Insert ribbon and find the Charts group with buttons for Column, Line, Pie, Bar, Area, Scatter, and Other Charts. Each choice has a dropdown menu for subtypes. Excel even has a Recommended Charts button for guessing the most appropriate type of chart for your specific data.

  • Probably the most critical step is the Select Data button on the Design ribbon. Here you can edit the data range(s) the chart is based on. You can experiment with the other buttons, and you can also experiment by right-clicking various parts of a chart to see the many possibilities.




How it works in the world of FP&A

Charts provide the visuals FP&A professionals need to communicate their stories to non-FP&A stakeholders and decision-makers in the business, whether it's for financial reporting or financial forecasting.


Say you’re a global manufacturing retailer and you want to see how you did in the month of January? Easy. Make a bar graph, and you can easily see your revenue from each country.



5. Data & What-if Tables

Data or what-if tables provide sensitivity analysis, which is a critical component of most business models and the final ability required to be a great Excel Master. You can find the Data Table option under the What-If Analysis dropdown on the Data ribbon.



How it works in the world of FP&A

Because business modeling is all about what-ifs, that’s exactly what Excel is serving you. You can now query your data, for example, what would occur to revenues if the unit cost increased by x%? Or what if our competitor’s price decreased by y%?


Data tables answer these questions in a systematic way. There are two types of what-if tables: one-way and two-way. A one-way data table has one input and any number of outputs, and a two-way data table has two inputs but only one output.


Let’s say you are a car salesman and need to calculate the interest rates for your client — a one-way data table is perfect for this. The single input is the interest rate, but there are two outputs: the monthly payment and the total interest to be paid.


The Bottom Line

Whether you learned something new about Excel or refreshed your skills; if this felt like a lot of information to take in, here are the highlights:


  • Excel proficiency is essential for FP&A professionals.

  • Financial analysis, financial modeling, and financial reporting are easily done in Excel because of its unmatched capabilities in mathematical calculations, consolidation formatting, and VBA/macro tools.

  • While some organizations have tried to move away from Excel, they almost always come back. Why force your finance team to learn and adapt to new applications?


And you don’t have to force your accounting and FP&A departments to adapt to new applications or software. You can complement Excel with an integrated solution like Datarails.


Datarails transforms Excel into a lean, mean FP&A machine. Here’s the best part: you get to keep using your Excel financial models and spreadsheets while automating repetitive processes. Personally, I think that’s the coolest thing we’ve learned in this article today!


1,430 views0 comments