Making the Profitable Choice Tool: Part I

The Profit

Close your eyes and imagine you’re a tire dealer. You have customers who drive a wide range of vehicles with various size tires. Some drive SUVs, some drive sports cars and others have small sedans. These customers are looking for your expertise to provide them with the right brand and the right size tire as quickly as possible, all while you’re looking to make a profit. You may ask yourself, where does this profit come from? Just like any other product, tires are no different. You take your sell price minus your purchase price. This is your profit margin. However, you can also profit from another source: directly from the tire manufacturer.

It comes from what we’ll call “A Manufacturer Tire Program.” Think of this as a type of bonus incentive for the tire dealer. For example, a tire manufacturer would pay a dealer $3 a tire if they are part of their program and meet a quota of 1000 tires. Other tire manufacturers may pay $3 a tire for only a certain number of a specific type of tire. Every manufacturer has a program and every program has different rules with different levels.

If you’re a sales associate, it’s a lot of information to keep up with. Cue the “Profitable Choice Tool” — a love story.

The Crisis

With all the disruption in the tire industry, the ATD Advanced Analytics Center of Excellence (AACoE) was tasked with one of its first major projects: to help build a tool that would demonstrate ATD’s commitment to keeping our dealers profitable.

The tool we decided to build was something that would help our sales associates navigate these program rules more effectively. For any one of our 80,000 customers, what would happen to their bonus incentives if they moved volume away from one brand to another? Our sales associates already had keen intuition into this, but often had to do this with pen and paper calculations or spend time combing through pages of program rules to calculate a number.

Our sales associates needed a tool where you could enter a customer number, a number of units from brand X to brand Y and see what would happen to the customer’s bonus incentive. This would be enormously helpful in the immediate term. The AACoE was asked to build the tool fast. In one or two week’s kind of fast.

The Tool

Like many enterprises, Excel is heavily embedded in our day-to-day operations. It’s an extraordinarily powerful application. At ATD, our sales associates are intimately familiar with Excel and it appeared to be the best medium for delivering a program rules tool. We had already deployed an application a few months earlier that had encoded a portion of program rules into an amalgam of Python if … then statements and for loops. For example, if a program had four tiers, our snippet of program rules code might look like:

Where total_payout is the total bonus incentive this customer can expect to receive from Brand A given customer_units_brandA. These units can be actual numbers imported from BI, or a user-entered number in the event we want to simulate growth or movement of units from another distributor over to us. For example, if we wanted to see what happens to our bonus incentive from Brand A if we move from 150 units to 1500 units, we would run:

There’s nothing particularly fancy about this. Nevertheless, the benefits are huge — what used to be back of the napkin calculations can be done in an instant. However, we aren’t having our sales associates load a Python module and run these calculations from a command-line interface. We had to determine a proper front-end for this, and we went with Excel.

Python and Excel: A Love Story

When the news broke that Microsoft was considering replacing Visual Basic with Python as the Excel scripting language, we were ecstatic. Python doesn’t plug-and-play well with Excel without third party tools, and it didn’t help that we had to deploy this tool fast.

Why did we choose Excel? At the time, we had a very small team and had a handful of applications already deployed with RShiny. However, we weren’t familiar with user inputted table manipulation in RShiny and we decided that coding the tool in RShiny (or Dask or flask/java) would take more time than dealing with the finagling of getting Excel and Python to work together.

Our first inkling on how to do this, which turned out to be wrong, was that the easiest way to build this tool with an Excel front-end was to call Python directly from Visual Basic. That is, you can do something like:

which will return whatever the Python output script is to variable RetVal. We decided to run with this idea, coding all the program rules into our master_rules.py file, calling each individual function from Excel, and exporting the returned values back to the Excel cell. Our initial interface looked something like:

Example Interface

pct_example2

At the top, you see what the customer’s current bonus incentive payout would be given their current situation (Current payout). Then, if you make any volume moves, you’ll see what the customer would make moving forward (Go-forward payout) and the benefit or downside of making those moves (Benefit).

Below the payout boxes, you see what we term the “insanity matrix.” Insanity because we’re the largest tire distributor in North America and carry a lot of tires whose manufacturers have a lot of programs, so the matrix actually becomes quite large. This may have been the first indication that perhaps a matrix-based user interface wasn’t the most optimal method of product delivery. The idea is that you move units from where the customer is currently at (the rows) to brands you want to simulate moving to (columns). In the particular example above, we’re moving 30 units from Brand A to Brand D and we want to know what the bonus incentive change would be from that move.

calculate

A big red “calculate” button sat on the right, making it unmistakable that you have to click something for numbers to pop up. When clicked, it pushes the existing units through our manufacturer_A_calc() and manufacturer_B_calc(). For example, a click in this scenario would call manufacturer_A_calc(50, 100, 0) and manufacturer_B_calc(0) and sum the outputs to get Current Payout. And sum manufacturer_A_calc(20, 100, 0) and manufacturer_B_calc(30) to get the Go-forward Payout.

This worked well on our local machines. However, that’s because Python was already installed with our laptops. All other associates at ATD would need to have Python installed on their machines, and that’s no easy task to complete in a few days for a salesforce of 500+ associates. So we decided to try something more creative — what about dropping the python.exe file directly into the folder with our Excel tool and calling it that way? Like so:

windows_python_setup

Then we could just zip the folder up and distribute it via email.

Stay tuned for Part II of Making the Profitable Choice Tool to see if we were able to create this tool in the short amount of time and if our creative fix helped speed up the process.