Using Power ON Visual Planner and Excel to Build Forecast

by | Mar 29, 2020 | Articles

In this article, we look at Visual Planner, a custom Power BI visual from Power ON. The Visual Planner solution provides direct write-back from Power BI to a database. This visual single-handedly turns Power BI into the best planning and forecasting tool in the market.

Excel still going strong

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 companies in the United States relied on Excel as the primary tool.

Many other software came out over the years, including Microsoft’s own Power BI. Some argued Power BI would eventually replace Excel. However, finance professionals still rely on Excel to make planning models.

Disadvantages of Power BI

Power BI has proved to be a fantastic tool on its own right for other purposes, such as reporting and collaboration. However, it lacks Excel’s cell format. This makes it easier to make different calculations, perform quick manual fixes and fine tune data when necessary.

Granted, using DAX formulas and advanced data manipulation techniques, some users manage to reproduce the look of Excel tables in Power BI reports. However, to pull this off, users need a significant skill level. As a result, many business users, who do not have time to learn DAX, may choose to stick to Excel.

Disadvantages of Excel

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.
  • 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. It might work in some cases, but likely you’ll end up re-doing some of the work after an intern.

This is where Power ON Visual Planner comes in

There is one particularly valuable solution that you can add to Power BI – Power ON’s Visual Planner. This solution provides write-back capability from Power BI directly to a database. The ability to change data in real-life is what Power BI is missing. And now finally, Power ON has made it possible.

The Visual Planner write-back engine works on top of a tabular model in Analysis Services or Power BI Premium. Working directly from Power BI, users can add, change or delete data directly at the database level. The changes take place immediately and everyone in an organization can see them right away. Most importantly, since users are working in Power BI, all the advantages of Power BI are available.

With Visual Planner all users have a single source of truth, because all data is in a single database. This means nobody needs to manually dig into the numbers, like we did in Excel when forecast scenario had to be updated.

Share This