Leveraging Tableau in Financial Planning & Analysis (FP&A)

Much has been written about the various digital tools now available to support business analytics. In this article, I examine one tool I find particularly appealing to those of us in #Financial Planning & Analysis (FP&A): #Tableau.? This tool basically allows you to transform a static PowerPoint slide deck or an Excel workbook into an interactive and dynamic dashboard. Rather than flipping through multiple appendices outlining different scenarios or options, for example, the user can instead choose various options from drop-down menus or by applying parameters to view different outcomes.

?In this scenario, therefore, the Tableau dashboard is designed to flow like a typical PowerPoint presentation, the one an FP&A team would typically prepare and present for quarter or month-end close to a business unit team or business unit lead. The dashboard is also designed to facilitate a team member or business unit lead reviewing the dashboard at his own convenience, without the need for a presenter, the user already being familiar with the PowerPoint format. ?What follows is an example.

In this scenario, we look at the financial package for Q1 2023 close for Parks Pharmaceuticals, a fictional biopharma company based in Newark, New Jersey.? The financials to be reviewed are as follows:

1.????? Summary Q1 results

2.????? Q1 Headcount results

3.????? Q1 results for People Driven Costs (T&E, Training, etc.)

4.????? Q1 Project Spend (3rd Party) results

5.????? Q1 Product Details results

6.????? FY2023 Uncommitted Budget

7.????? FY2024 Budget Planning

8.????? 2023 Supplier Distribution

9.????? 2023 Product Prioritization matrix

10.? 2023 Disease Spend distribution

Reviewing the ten financials above will allow us to highlight some key Tableau features that FP&A folks would find useful in presenting a financial package to management.

Background. Parks Pharmaceuticals is comprised of four business units--US Market, Canada, Germany and France—operating in two regions, North America and Europe. Its portfolio of products comprises five disease areas: Diabetes, Dermatology, Oncology, Cardiovascular, and Neuroscience, organized into teams that bear the same names.

Parks Pharmaceuticals uses SAP as its ERP system.? The dashboard is therefore linked to the output from SAP Business Warehouse (BW) via an MS Excel add-in. As the BW report is refreshed with updates from SAP, however frequently, so will the dashboard be updated, eliminating the need to prepare a PowerPoint deck each time a financial package is to be presented. The Tableau dashboard is refreshed by a simple click of the refresh button on the data source page.

As every FP&A professional knows, there are times when adjustments are needed to the ERP output in order to present a more complete and accurate state of affairs; for example, when a Purchase Order (PO) was not executed in SAP in time for the quarter close, but is relevant in obtaining a true picture of spend for the quarter. This adjustment is accomplished by using what I call an intervening datasheet that captures any such high-level adjustments, and integrating this sheet with the SAP output, thus ensuring a more comprehensive and timely set of results in the dashboard.

Another cool outcome of using Tableau is the ability to tailor expense descriptions at the business unit level, without impacting enterprise level reporting. In this dashboard example, we group certain headcount driven costs such as travel, training, dues & subscriptions, etc. into a custom-made expense type called People Driven Costs (PDC). ?This will assist management in making outsourcing decisions, for example, while not interfering with the expense groupings for enterprise level reporting.

This type of expense tailoring also helps with a more efficient allocation of budget resources. For example, travel can be more specifically defined as to its nature to allow for prioritization. Traveling to gather competitive intelligence in support of an upcoming product launch, for example, should not suffer the same fate in budget cuts as travel to a sorority convention, as would be the case with a flat across-the-board cut. Using a reference table to tailor travel expense in a Tableau environment would facilitate a more efficient optimization of the travel budget.

The dashboard is shared with clients or business partners via a Tableau in-house server, or via the Tableau cloud. Access is controlled by the owner of the dashboard using Tableau’s permission features. This allows your client or business partner to access the dashboard wherever internet service is available. ?In our example, this could mean the GM for France easily reviewing the Q1 close package while sipping champagne on the French Riviera. No need to set up a meeting to walk through a set of PowerPoint slides.

?

We will now cycle through the dashboard, which is designed to give the look and feel of a PowerPoint presentation, but with the dynamism and interactivity that Tableau brings to the table. ?The dashboard is as of the end of Q1 2023.

1.Summary Q1 results: As shown in the chart? above, representing the landing page of the dashboard, we start with a high-level summary of spend results for Q1, with expenses grouped into three main buckets (For simplicity, we ignore revenue in this example, but revenue (and net earnings) can easily be added as separate lines). Results show the filtered business unit (BU), France, running 41% favorable in compensation expense. Hovering over the horizontal bars, the tool tip will show you the respective amounts for the budget and actuals that give rise to the favorability of 41 %. The table (or crosstab) below the chart also provides these amounts, i.e. $4.9M budget vs $2.9M actuals.

The table also shows the projected full year (FY) results for France at 18% favorability in compensation. ?For the region, the comparative metric is 16% and for the entire company, 13%. This benchmarking metric comes to the user courtesy of Tableau’s Level of Details (LOD) feature. The takeaway here is that France at 18% is more favorable than its peers in Europe (at 16%) and for the company as a whole (13%). The French team should have some explaining to do for this comparatively high favorability. Maybe the budget for France was padded when it was submitted.

To examine the Q1 variance of 41% favorability further, the user drills down one level to the underlying drivers of the variance by clicking on the line item of interest, i.e. compensation. Based on the organizational hierarchy in this instance, the next level of detail is the team level. Here the BU lead can determine which team is the main contributor to the variance. In this instance, we observed that the Oncology team is the main contributor to the variance in absolute ($1M) terms, while the neuroscience team is the main contributor by rate of variance (62%). Both teams should be further examined to determine the cause and for corrective action to be taken, if any. ?Clicking on any team will revert the chart to the summary spend level. This Tableau drill down feature saves the user having to flip to another page to view the drivers of the variance, as would be the case if using PowerPoint.

2.Q1 Headcount results

Continuing with the review of the compensation costs for the oncology team by drilling into the headcount tab, we see that the Q1 favorability of $1M is driven by failing to hire 4 staff (at various levels) vs budget. This should provide sufficient insights for management to take the next steps to accelerate hiring. It should also alert brand managers to the risk of not achieving their revenue target due to staff shortages.

3.Q1 PDC Results

As with compensation, the same process is followed for reviewing People Driven Costs (PDC). Drilling down further into the oncology team gives us the distribution of spend across the different components of People Driven Costs. As previously mentioned, Tableau facilitates this grouping of costs from which the business lead can draw insights on his people driven costs without interfering with enterprise level reporting, where expenses may be grouped differently to serve external (10K) reporting.

4.Q1 Project Spend (3rd Party) results

With project spend, as with compensation, the same process applies, drilling down from summary page into lower levels to examine the drivers of variances. Here we see the two brands in oncology driving the variance. Ravlene is $60K favorable in promotional spend for Q1. A key metric for managing ?brand spend at Parks is the level of executed POs vs Brand budget. In this case, Ravlene has executed POs for FY 2023 amounting to 80% of its budget, meaning only 20% of budget remains uncommitted. Given the reporting date of Q1, that may be reasonable. However, at the same time, we see the project budget for the Dermatolgy team at 73% uncommitted in comparison. Clicking on the Dermatology line reveals what is under the hood, and what corrective actions may be needed, again courtesy of Tableau’s drill down feature. At face value, it seems that a lag in gathering competitive intelligence (i.e. 13% committed funds) is the main driver of the low commitment level as of Q1, per the chart below.

5.Q1 Product Details results

Drilling down further into the Dermatology brand Bambezi, we see that the item of interest, Competitive Intelligence, comprises the four projects shown. The team can now use the dashboard as a guide to update the project database as needed. Once updated, the new information on projects will flow to update the dashboard, via a simple refresh of the dashboard using the refresh tab in Tableau. This can be done as frequently as the owner of the dashboard chooses. The brand leads and other team members are thus provided with timely information on which to make decisions, giving meaning to the principle of data-driven decisions-making.

6.FY2023 uncommitted budget

This page in the dashboard gives another perspective to the level of POs executed against planned budgets. Here the dashboard essentially aged the level of POs not executed according to planned budget. It basically operates like Accounts Receivable ageing. The more data in Jan, Feb, Mar as of Q1, the more risk to Parks meeting its revenue targets. ?Keeping with Dermatology, we see from the ageing page that the 73% uncommitted funds as of Q1 may not be too bad, since most POs were scheduled to be executed post Q1. However, there is $512K of POs that should have been executed in January that were not. This requires immediate attention, either in executing the PO or in moving the project start date to a later period if warranted. Either way, the dashboard serves its purpose by bringing the status of projects to the attention of management in a timely manner. Again, updating the project database will update the dashboard when the dashboard is refreshed.

7.FY2024 budget planning

Here we showcase Tableau’s parameter feature. ?I find this feature particularly useful in running budget scenarios. Often the BU is given a budget target, as in this example, $70M OPEX target for FY2024. It’s the BU lead’s responsibility, with the aid of his FP&A lead, to optimize that $70M target across its priorities. At budget meetings, scenarios can be examined, leveraging parameters to look at various options that will deliver on the $70M target.

Above, the OPEX budget for France is at $72.7M vs the $70M target with the combination of Compensation, PDC and Project costs selected using the respective parameters. A different set of parameter combinations brings the budget in line with the target as shown below. The target line itself uses Tableau’s reference line feature, capturing the target for FY2024. Reference lines can also be applied at a subcategory level, in this case, at the spend type level (vs. Total OPEX level). At that level, we compare FY 2022 Actuals, FY2023 Projection and FY 2024 budget at each of the spend type levels vs the FY 2023 Budget as reference. This allows the teams to get some context for the FY 2024 budget. This demonstrates the flexibility of the reference line feature.

8.2023 Supplier distribution

A useful supplemental schedule in the dashboard is that of the distribution of suppliers, which will allow us to demonstrate another of Tableau’s key features, Top N. Here we look at the top N suppliers, with N determined by the user of the dashboard. In selecting top 10 as an example, we see that the top 10 suppliers in France amounts to 15% of its 68 suppliers, which account for 71% (i.e. $47M) of spend with suppliers. Reviewing this metric will tell the user if an opportunity exists to consolidate the supply base further, for example, having the top 10 of suppliers accounting for 80% (vs. 71%) of spend with suppliers, bringing more efficiency to OPEX.

?The distribution of suppliers across the brands is also telling. While Omnibus Ltd is focused on promoting two brands, Ravlene and Elextrix, Nam Associates is spread thin across four brands. A discussion is warranted to determine if some suppliers, like Nam Associates, should be more focused on fewer brands, bringing more efficiency to spend and to brand performance. Again, the key feature demonstrated here is the flexible Top N feature.

9.2023 Product prioritization matrix

Still in France, we demonstrate the map feature of Tableau by applying it to a custom display of product ranking. Here we group the products sold in any selected BU into four priority areas, A to D, with size of bubbles representing fully allocated product costs. In France, Bambezi and Altrexi are the highest priority products, accounting for 27% of FTE’s and 38% of project spend (refer to summary financial page of dashboard). At the same time, it is interesting to note that the lowest priority product, Ravlene, which is nearing patent expiration, accounts for 34% of spend, which may warrant a discussion as to whether France is investing in its correct priorities. If warranted, parameters could be brought into the mix to allow the user to move products around the quadrants to see the impact on the distribution of spend by priorities segments.

10. 2023 disease spend distribution

Given that Parks is a biopharma company, a cool way to display its products is by disease areas, positioning the different products in the human body according to the location of the main organ impacted by the disease treated by the products. This could be especially appealing to the R&D folks when giving consideration to where they should be focusing their R&D spend.

Conclusion. The Tableau features demonstrated here today are just the tip of the iceberg when it comes to what Tableau can do in this space. It’s up to your creativity and ingenuity on how to use Tableau to meet that objective. Just like Excel, where many of its powerful features remain underutilized, so it is with Tableau. The platform for example provides you with some standard charts, but with creativity, you can accomplish much more. ?For example, Tableau provides a standard pie chart selection, but with some creativity, you can manipulate this pie chart feature to generate a more attractive donut chart. ?It is all up to your imagination and willingness to experiment.

?At the same time, however, we should resist the temptation to chart a set of data simply because a feature is available. For example, many data analysts will jump to chart geographical data on a map simply because the geo data is available and a geo map can look more attractive than say, a bar chart, which could be more informative. For example, Sweden may be a much more important country to a company than Russia is, in terms of sales volume, but Russia will loom large on a map chart simply because it is 40 times larger geographically than Sweden. In this case, a bar chart, though less attractive than a geo map, will tell a more realistic story about the relative importance of the two countries than the more attractive geographical map.

Feel free to explore the dashboard ?via this link.