Building a Procurement Solution with Power BI

From a standard business point of view, there are many administrative sectors that contribute to the growth of a company, sell its products more competitively, and have larger profits. Some of these areas might seem more important than others, like the commercial or the marketing area. Businesses start small anyway, and owners should not spend time and energy with things that won’t bring greater results, given the limited resources.

However, as businesses grow, some other areas need to develop a certain level of strategic thinking and obtain an important role in the operation itself. In the past years, with technology and the need to deliver even more results, the procurement area has grown to become a strategic sector, instead of just issuing POs (purchase orders).

We can define procurement as every activity that involves obtaining goods and services that support the daily operations of a company. It’s also possible to divide procurement in other subcategories, like direct procurement, indirect procurement, and services procurement. Obtaining savings, developing new suppliers, finding alternatives to supply, and creating contracts are some of the daily activities of a buyer. These activities will contribute directly or indirectly to the company’s margin.

To further develop the area, like any other, it’s very important to define and track KPIs (Key Performance Indicators), as well as business rules and goals. This means that some buyers in companies with more solid procurement sectors must know how to create reports, keep track of their team’s performance, and become more data-driven in their daily decisions of issuing a purchase order. After all, if you’re not doing this, someone is, and they might be well ahead regarding strategy and competitiveness.

Some companies delegate this function to an analyst, a person able to prepare and transform the generated procurement data into reports, dashboards and ultimately insights that will help the area achieve its goals. That’s why, in this article, I want to show you how to transform this data, usually obtained from a database or ERP (Enterprise Resource Planning) system pre-defined reports into an interactive report using Microsoft Power BI.

How’s the end-to-end Procurement Process?

First, to simplify Procurement, let’s explain how the process works:

Figure 1: steps of procurement process

1. Demand or necessity is created: this will usually come from the end-user; but, sometimes, the buyer has authority to perform a sourcing operation without it, anticipating the demand.

2. Purchase Requisition is created: this step is mostly done in the ERP system or in the e-procurement system the company has. Either way, the end-user will specify what needs to be purchased.

3. Buyers receive PR and create RFQ (Request for Quotations): based on current Procurement Guidelines, buyers will request that suppliers send their proposals, so that they’re able to compare them and choose the best one.

4. Negotiation and PO approval: after proposals are received and negotiation is done, buyers submit POs to approval workflow.

5. Send PO to supplier: after approval, POs are sent to suppliers and they’re able to deliver goods or start services as negotiated.

6. Invoice Receiving and Payment: finally, suppliers send invoices that will be paid based on negotiation and created POs, after goods are received or services are finished.

Keep in mind that many other substeps might be added between each step. The important thing is that end-users, most of the time, can’t issue POs; they only create requisitions. Buyers create POs, but they do not pay suppliers; that’s finance’s responsibility. Finally, an invoice shouldn’t be paid if it can’t be related to an issued and approved PO.

These roles and responsibilities must be defined and made clear in Procurement Guidelines, because, in the end, it’s something that will impact KPIs, performance and results of the company.

Beyond that, there should be a great level of control and governance over the area’s activities, mainly because of sensitive information and fraud possibility. That’s why most companies create Guidelines for the relationship between buyers, suppliers and end-users and are very strict with the rules.

Defining Procurement KPIs

There are many KPIs and measures that we can define and calculate when we analyze procurement data. Some of those are:

• Total Spend;
• % of Spend vs Budget;
• % Savings;
• Average payment days;
• % of urgent and maverick buying;
• SLA (Service Level Agreement)

Some of these are rather intuitive, but the definition of these KPIs will be further explained as we build our solution and in the conclusion. Keep in mind that some businesses might define them in a slightly different way, might not have all the data to calculate them or even find them uninteresting — and that’s the beauty of adjusting a solution to the needs of your client.

For example, if someone wants to calculate the percentage of savings or cost avoidance, this information must be generated and stored somewhere, somehow. If we want to compare quantity and amount of urgent POs vs standard buying procedure, there must be a field or any information that allows the analyst to differentiate between these two things. We won’t be dwelling too much in data generation and storage based on requirements, so, I’ll assume that part of the information is readily available somehow.

Creating a sample database

Imagine you have a huge table with all the information regarding your purchase orders. Someone created, many years ago, a report in your ERP system. You extract the table weekly or monthly, creating pivot tables in Excel and updating your KPIs for your manager’s meetings.

Most of the time, it’s even worse than that: it’s necessary to extract not only one report, but many, and you end up performing lots of time-consuming transformations to achieve your result.

To simplify our analysis leading to the application, I created a sample database in Excel containing the needed information to calculate the KPIs, just like the scenario above and as described in the KPIs definition section:

Figure 2: Excel database used in the application

Data was anonymized and supplier names have been replaced with random names of US companies. We have information about the buyer, PR (Purchase Requisition) liberation date and PO issuing date, the product, price and quantity, purchase category and discount information. There is also information on payment terms, PO type, contracts, and PR complexity, ranging from low to high based on its estimated value.

Data Modeling with Power BI

Normally, when we work with Power BI, modeling your data in a star-schema it’s a best practice. In our case, I had a big table to work with, and applied some normalization techniques to create our fact table and our dimension tables. Everything was made using Power BI’s ETL tool called Power Query.

In the end, our data model will look like this:

Figure 3: Power BI data model showing relationship between tables

The best scenario would be to have access to the database itself, using SQL language to generate queries, for example. It’s simpler and far more efficient, but it wasn’t the case. Sometimes, the analyst can only extract reports and tables from the ERP System and must be able to do the work either way.

The procurement application

For a report to be useful and generate value, it must have coherent calculations and analysis and be user-friendly. After creating measures and defining our visualization aspects for good storytelling, we have our final product. Let’s take a look at the overview page:

Figure 4: Overview Page. A quick summary for spending data over time, along with KPIs.

To better analyze the data and generate insights, we can filter it using the slicers above the cards, or we can interact with the dashboard and dynamically analyze it. Either way, using the whole dataset, we see that the company had a spend of about $ 4.33 billions and had over $ 200 million in savings (buyer performance in negotiation, as defined in the database). The category that has the most spend is Raw Material, and the assignment is Cost Center.

We also have the % of SLA, calculated on how many days it took for the PR to become a PO, and another important KPI, the average payment days. It’s a weighted value based on spend and payment days, a nice indicator for cash flow. Finally, we had, in this database, items that were in a contract, so we can calculate the percentage of the total.

On the next page, we focus a little bit more on the yearly spend analysis:

Figure 5: Spend Analysis Page. The focus is to analyze a full year.

As one can see, the most relevant KPIs on this page are the same, but the date filter was changed for a year filter. There is also some new analysis: “% Saving by Buyer” is a way of measuring the performance of buyers from different spending ranges, and the “Spend and Spend YTD” visual lets the user analyze the spend monthly and accumulated. Finally, we have a detailed matrix bringing us information about categories and a trendline field that subtly shows the variation of spend by month.

On the next page, we’re taking a better look at our Suppliers:

Figure 6: Suppliers Page. It’s possible to rank and arrange suppliers based on their spend or any other measure.

We can use some more advanced DAX and data modeling to dynamically categorize suppliers in tier A, B or C. Ranking from highest to lowest, suppliers from tier A contribute to 70% of total spend. B comes next with 20% and tier C suppliers with only 10%. The arrangement is dynamic because it considers the page’s filters. For figure 6, I chose the Raw Material category using the page filters and clicked on category “A” in the pie chart — I wanted to see only the top spending suppliers.

Quickly, we see in our scatter plot that some suppliers have higher spends than others, and some of them have very low payment days. The application shines a light on this, and lets buyers and managers know for sure where they must better negotiate — whenever it’s possible.

Now, let’s look at our yearly budget:

Figure 6: Budget Page. Keeping track of budget is very important for Procurement.

For this one, I had to add another fact table containing information about the budget. Microsoft Power BI’s flexibility lets me connect our previously created dimensions to this new fact table and analyze the data vs the original fact table, containing spending information. We still do not have relationships between two or more dimensions and between our fact tables — we maintain our star-schema model and, consequently, model performance.

To add some more complexity, the budget table was defined by year and by category only. Decomposing the value on a month basis for the current year was made by taking the previous year spending trend and using it in our DAX measures as a denominator for the value. Also, a “Spend Forecasting” measure was created, in which the calculation occurs normally for ranges where we have spending data and follows budget value for future dates — this is a way to initially predict what will happen at the end of the year. For the selection of filters in the image, we predict that spend will be 1.8% above budget by the end of the year (Budget FY).

One last page I wanted to show is the one I named the Buyer Profile, where we can individually analyze buyer performance like its very own file.

Figure 6: Buyer Profile. A quick way to understand your team’s performance and trends.

Using some images from the internet and some tweaks, I was able to bring a picture of the selected buyer to the report. In this page, we have an overview of the buyer, how many purchase orders they have issued, the % SLA, spent by PO type and an auto-generated text from Power BI (on the right) bringing some more details. We can also calculate the moving average of % saving — instead of analyzing it every month, the moving average considers some other time period and is able to show trending. In this application, I used 3 months, and it shows that Aletha started well and got lower on performance over time, but recovered it later.

Conclusion

We successfully transformed a huge table with many columns and information into a good-looking dashboard with relevant analysis and flexibility. There were other pages and analysis to show, like Maverick and Urgent Spending, but the goal was to briefly introduce how we can use Microsoft Power BI to tell a story with some Procurement Data.

The whole transformation, reporting and updating process can be automated, which is a huge gain for the analyst, who will be able to spend more time analyzing its data and creating action plans than handling it. Thank you for reading this article!

KPIs and Measures

This section intends to summarize calculations and KPIs in this article:

• Spend: initial value of PO minus negotiated discount;
• Saving: sum of negotiated discount;
• % Saving: percentage of saving obtained compared to initial price;
• % SLA: percentage of issued POs on time. Calculated using PR liberation date and PO Date vs expected days to create PO (based on PR complexity);
• AVG Payment Days: weighted average of spend and payment days;
• % Contract: percentage of PO items in a contract;
• # Purchase Orders: distinct count of POs;
• # Itens: every row of the fact table represents a different item purchased;
• Budget Spend: defined amount that can be spent in a year;
• % Budget: comparison between budget spend and actual spend;
• % Budget FY: comparison between full year budget spend and actual + year-to-go spend. In this case YTG was defined as the budget itself;
• % Saving Moving Average 3M: moving average of 3 months of the % saving measure. Used to flatten a curve and show trending.