MODEL CITIZN

View Original

Excel, Analytics & Power BI - Perfect match for a new King

Lance Rubin

10 February 2023 | by Khaled Chowdhury and Lance Rubin 

Introduction to the co-author

Tools are valueless without people who can leverage them.  

Khaled’s focus is on empowering people to make better-informed decisions. Starting his journey in Excel, he worked his way up in the data life cycle. He went from running FP&A to driving a revolution as Global Director of Data & Analytics.

It all started from his passion for improving our jobs and a fateful encounter with Power BI. To learn more about his Power BI journey, check out the following podcast about his origin story

If you have yet to encounter Power BI, you should, as it has become a necessary tool and skill set for finance professionals.

 

What is Power BI?

Power BI traces its roots to Excel and SQL data warehouses. Even today, Power Query and Power Pivot are still available in Excel but are almost hidden.

Power BI provides a low code/drag-and-drop data solution for data prep, modeling, and visualization.

Why Power BI?

What gets measured gets managed. With the ever-increasing data and the pace of volatility, we are being asked to provide information insight faster, quicker, and more precisely. It’s a tool that eliminates data drudgery, automates manual reporting, and, most importantly, provides one version of the truth to drive collaboration and actions. 

Due to its extensive usage and simplicity, finance and accounting teams are still quite stuck with Excel as their only tool. Not to mention the outdated version of Excel without the knowledge of power tools. However, more is needed to bring value to your team.

CFOs are now hiring people with specific Power BI skills and paying high to compete in hiring good candidates. The talent pool is currently small but will change by adopting and learning the usage better. This is not a choice between Excel or Power BI but the combined skills in both tools to deliver optimum value. Hence, it’s necessary to have both in your tool belt.

Let’s highlight what you are missing out on and why you should be getting started.

Capacity, Capability & Collaboration

If you can relate to any of the following, Power BI will be a massive win for you and your company:

  1. Cannot keep up with the business request or is out of capacity?

  2. Spending more time preparing your data or analyzing it?

  3. Producing critical analysis on a quarterly or monthly basis that should be done frequently on a weekly or daily basis?

  4. Do you need to be alerted when someone goes over budget or is the forecast run rate about to miss the target?

These problems, my friend, need to be fixed. So, OUT with the old and IN with the new!

Khaled has created some simple-to-understand videos and gifs, enjoy!

Native Connectors & Power Query

Out = “Export to Excel” In = “Get Data”                          

With 100+ native connectors, you can get your data from Excel, CSV, SAP, Oracle, Salesforce, etc. The best part is just getting started, as manual cleaning, trimming, copy/pasting, and finding replacements are no longer required. we use click, drag, and drop transformation that will remember how to do it next time when you click “Refresh.” Here is a dirty little secret. It’s also in Excel.

If you are going to take a single suggestion from this article, “Please learn Power Query.” You can thank us later.

It is no longer required to clean up the data and add lookup columns manually.

 

Out = “Manual Repetition” In = “Scheduled Refresh”

You no longer need to be chained to your Excel creation. Power BI delivers updated information at your desired schedule with the scheduled refresh. This resolves your capacity problem. 

Data Modeling & DAX

Out = “1 million rows” In = “Billions and trillions of rows”

Power BI engine does not have limitations on the number of records. Please note: Big Data does not add value; the right data does. It means that Power BI creates value and removes pivot tables on pivot tables.

Out = “xlookup/vlookup/Index/match/pivot table” In = “Relationship & DAX”

Writing 20 VLOOKUP columns to get context for your pivot table is not required anymore.

You can drag the lookup field to your lookup table and be done with it. DAX, the formula language for Power BI, allows you to calculate your KPIs dynamically. For example, time series calculations like MTD, QTD, YTD, or last year can all be dynamically calculated. No need to manually update the spreadsheet to sum one more column.

This takes us to the next step and unleashes our capabilities.

Maybe it's time to learn a little bit about DAX. Click here for getting started.

Dashboards & Power BI Service

Out = “bound by cells” In = “interactive canvas”

Instead of being confined in cells. rows and columns, this allows conveying our analysis on an interactive canvas.

Ever heard of “Pictures speak a thousand words”?

No need to ask for reports for the last month or the region. All of them are presented to them with interactive slicers and drill-through contents.

Your partners can checkout themselves and enter Self-Service.

 Out = “Manually typed email alert (that is late)” In = “Mobile & Automated Alerts”

Say goodbye to print.

You have the information at the tips of your finger on your mobile as soon as you publish it to the service. You may want to be notified when the stock falls below a certain level or when a goal is met.

All of these can be set up with the click of a button. One more notification on your phone is not going to hurt. 

This truly starts opening the paths to collaboration. You always get to communicate the output of your financial model, giving you an excellent avenue.

Our intention was to skim a little to give you an idea of why you should be looking into Power BI to create value by freeing up your capacity, improving your capability, better collaboration with partners, and, as a result, making better decisions.

Where can you learn more?

 We both provide targeted Power BI training relating to accounting, finance, and in particular FP&A.

Lance recently hosted a recorded webinar on behalf of CCH Wolters Kluwer to help accountants and finance professionals get started and go further:

Khaled has driven organizational transformations and now also works with organizations to help achieve culture change with real ROI.

Data Crafters – Let’s Talk

However, you can also get started for free

A.     Microsoft guided Power BI learning path

B.     Getting Started with Power BI: Step-by-Step Guide to Success

C.     Power BI Free Learning Path

If you had to teach this topic to school kids in a class, what key tips would you give them to focus on?

  1. First focus on the concept, then tools

  2. Learn all the basics of Power BI and then focus on specific parts to narrow down

  3. Practice more and more

  4. Work with fellow mates and discuss

  5. Participate in different challenges to showcase your skill

What practical steps can people take to learn more?

 The best way to practice is to attend the Power challenges at least twice a month.

There are several sites where you can find these challenges (Maven Analytics Data Playground, Enterprise DNA Challenges, Inforiver Data Challenge).

Joining different Power BI communities (Microsoft Power BI Community, Inforiver Community) to interact with others’ problems.

This way, you can post your problems to find solutions from others.

It will help you identify issues and get solutions.

 

Where are good places (links) to find out more on a topic?

 Getting started, Microsoft Learn, Power Query is a great resource. Here are some of the best blogs


For specific Finance professionals, we’ll be adding some relevant Finance Transformation Recipes.

If you want to explore some live embedded Power BI dashboards built for accounting and financial data here are a few useful live links on the Model Citizn website:

How do all this disruption, AI, and automation talk impact this topic

You need to crawl before you walk or run.

These tools set you up to harness the power of data.

This process is sequential, as you’ll need these before utilizing AI & ML.

These tools, by default, automate and eliminate your tasks to free up your time and increase your capacity to focus on even further capabilities.

If you want to find out more and follow the rest of the article series, be sure to download the Financial Modelling App.

For more information on financial modeling and content, visit the Model Citizn website.