Cost-Plus-Pricing with Tableau & Tableau Prep

Oct 17, 2021

Klaus Schulte

I’ve just finished a project where I had a very interesting and fun challenge: Create a Cost-Plus-Pricing Tool in Tableau.

“No, you can’t do pricing in a BI Tool”, or “We need flexibility for our pricing tool, that only Excel gives us” is something I’ve already heart many times, when discussing about pricing. But of course you can! Tableau Prep and Tableau Desktop are powerful tools, and if you are able to use them both, you can in deed create a very handy pricing tool. In this post, I’d like to share the basic idea and my main calculations, which will then allow you go from here and to create your own pricing tool in Tableau.

(And please forgive me for not wanting to discuss the advantages and disadvantages of the cost-plus pricing method here, there are many resources about this topic out there.)

Data Model in Tableau Prep

Prep Flow to set up the data model.
Data Model in Tableau Prep

For my data model, I imagine a two-stage production process. All products must first pass through a capacity point of the first production level in order to be finally processed at the second production level.

Sample machinery data for stage A machines.
Stage 1 capacities

In my example, we have cost rates per unit for my first and my second production stage. Imagine these are costs for labor, indirect material, energy, or whatever.

Sample machinery data for stage B machines.
Stage 2 capacities

Obviously, you’ll find different and komplexer set ups in the wild. All of these informations you’ll get directly from your ERP and/or your cost accounting system.

For both production stages, I’ve also added a row for my default filter setting I will use at a later stage.

In the next step, I’ll join these two tables on the stage variable with the “!=” operator. This get’s me all possible combinations between stage 1 and state 2 aggregates.

Step 1 join of the two tables on stage variable where stage != stage
Join

For my pricing scheme, I will then need the different positions. Although I will only calculate everything later in Tableau Desktop, I already need the placeholders in my data model. Therefore I create the fields (Direct Material, Direct Labor, Indirect Material, Overhead, Scrap, Discount, Markup) and then just add NULL in the formula editor.

Set up of positions with NULL variables
Positions

A pivot step will then transform these dummy columns to rows and create a position and a value variable.

Pivot step to get a position and a value variable
Pivot

Last step in Prep is to create a sub-total variable and group the positions according to the pricing model, e. g. Direct Material, Machine Costs and Scrap add up to the Manufacturing Costs. I’m also changing the value data type from boolean to Number (decimal).

Grouping of positions to sub-totals
Sub-Total

That’s it!

Building the App in Tableau Desktop

First step in Desktop is to parametrize the inputs according to the pricing model:

Input parameters in Tableau Desktop
Parameter

Then, I’m creating the measures for my pricing model. This could be one big calculation, to keep it clean and structured, I’ve separated the calculations here into three fields. I’m integrating the parameters from step one and also the machine costs already included in my data model.

Calculated field for manufacturing costs
1 Manufacturing Costs
Calculated field for overhead costs
2 Overhead Costs
Calculated field for selling price
3 Selling Price

Next step is to put the view together:

Creating the view
Putting together the view

In this step, I’m…

  • filtering down the stage A and stage B variable to my default setting,
  • creating a comment field to display the selected values to the respective positions,
  • and using my Selling Price measure as a Running Total compute using my Sub-Total variable. (See also this post for context)

Finally, I put everything on a dashboard and show the parameters and filters used in the view:

Putting everything on a dashboard
Dashboard

Voilá!

Wrap Up

I hope you’ve found this piece useful and interesting to read. If you have feedback or questions on this, feel free to post them in the comments below or reach out to me on Twitter.

In case you are having own use case for this general set up, I’m very much looking forward to have a discussion with you.