Select Page

Best of Both Worlds: Using Power BI Visual Planning and Excel to Build Forecast

by | Mar 29, 2020 | Articles

Over the decades, Excel has dominated the world of corporate finance, especially when it comes to working with budgeting and planning. In fact, there is no better tool than Microsoft’s classic spreadsheet software when building financial models.

In a 2018 Robert Half report, over 60 percent of executives in the United States said their companies relied on Excel as the primary tool. In my own experience when speaking with experts in the field, I find that Excel is being used almost universally.

Although many other software came out over the years, including Microsoft’s own Power BI, which some had argued would eventually replace Excel, finance professionals across various organizations still rely on Excel to make calculations and build financial models.

Power BI has proved to be a fantastic tool on its own right for other purposes, such as creating and sharing data visualizations; however, it lacks Excel’s tabular data format, which makes it easier to make different calculations, perform quick manual fixes and fine tune data when necessary. Granted, with the help of DAX formulas and advanced data manipulation techniques, some users manage to reproduce the look of Excel tables in Power BI reports, but the skill level required to pull that off takes time to learn and many choose not to go that way.

In sum, Excel is the best available tool to work with financial statements and forecasting. Having said that, we must admit Excel isn’t perfect and has its own fair share of drawbacks. The main disadvantages of Excel are:

  • Time consuming – depending on the level of details and one’s skillset, building a model can be a long and tedious process.
  • Prone to error – let’s face it, we build models on assumptions, which often leads to wrong forecast figures. This is one of the most serious drawbacks of Excel and financial modeling, in general. Errors are natural part of modeling and when we find them one has to go back and manually fix them, which leads to the next point.
  • Manual – partially related to the first point, there is very little possibility to automate the process of building financial models. One may say Excel macros help with automation; however, industry best practices don’t recommend using them in financial models. Few people know how to use macros, while others may not be able to open files with embedded VBA.
  • Lack of collaboration – Excel is poor at supporting collaboration. Often, there is one master version of a budget kept by a CFO, which he updates upon receiving files from others. In addition, each person working on a project has their own file, which makes it hard to have a single version of the truth. With several people working on their own, a company ends up with a bunch of Excel copies floating around. How do you know which one is the latest one and includes all the updates? How do you know if the company’s CFO didn’t make a mistake and added a wrong copy to his master version? You don’t.

So, you’re probably wondering what is the solution? Well, first, you can try to be very careful and avoid making mistakes when appending two dozen Excel files together. It can be a tall order though and may take days to do it.

Some say they hire interns to do the “dirty work” of housekeeping – storing and cleaning up Excel files, copying and pasting data back and forth in between coffee runs. It might work in some cases, but likely you’ll end up re-doing some of the work after an intern.

Adding write-back features to Power BI

The third and best option is to use both Excel and Power BI when building financial models and forecasts. Use Excel where it excels, as a primary tool to structure your tables, make row-level calculations and add whatever custom numbers you need to add in a model. Once everything is ready, put it into Power BI for further analysis and better visualizations.

Forecasting using Power ON’s Visual Planning

There is one particularly valuable solution that you can add to Power BI – the Visual Planning visual by Power ON. The visual is providing write-back capabilities to a Power BI dashboard and is really helpful for planning and forecast purposes.

Visual Planning is built on top of a tabular model and can update numbers you want via Power BI matrix visual which is similar to Excel Pivot table back to the source and update the model in real-time. In other words, you make changes in real-time, and the changes are saved back to the data repository and everyone in your organization can see them immediately. Most importantly, you’re working in the Power BI environment, meaning that visualizations and other analytics update right away.

When you need to make changes in a budget, you simply make a few manipulations and your data is updated. All users will have a single source of truth since data is stored in a single database and there is no need to manually dig into the numbers, like you would in Excel when forecast scenarios must be updated.

To sum up, using Excel, Power BI and Visual Planning is the best option, especially considering that these products have a lot in common and can be easily integrated. Power BI adds business intelligence capabilities, much better visualizations, automation and sharing options to Excel analysis; meanwhile, Excel is better served as a structural backbone of your report, and Visual Planning if providing valuable write-back capabilities to Power BI matching its functionality with Excel.

Share This