In a departure for this blog from the technical focus on analytics – gaining insights from (data) history - here we focus on how businesses can efficiently make better decisions when there are multiple ways of achieving the same goal. In any situation, the more variables, or options, one has, the more difficult it can be to decide what to do.

For example, consider a manufacturer of widgets who knows he has an order to fulfill for a customer with multiple retail outlets scattered geographically. He may ask, “Should I hire more people, pay overtime rates, or should I outsource?” Here, he will want to factor in onboarding and training costs as well costs to downsize the workforce if required, at a later time. The best answer may be a combination of all three. Furthermore, he may ask, “Should I make all my widgets in a single production run, and pay inventory carrying costs; or should I produce in multiple runs over time?” Other questions may include, “In which of my plants shall I make my widgets given their different manufacturing overhead costs and the differences in shipping costs to customer locations?"

In each of these cases, one may try to begin to manually calculate the costs for different scenarios. To find the best scenario this way would be impractical. A step in the right direction would be to build a spreadsheet allowing the total cost to be calculated, and then changing inputs to see what the best outcome is. Still, how would you know if you’ve reached the best solution? The answer to this is to employ an optimization technique.

A simple linear solver, which is available as an “add-in” to Microsoft Excel, does this. Within the ‘model’ built using the spreadsheet, the inputs, constraints, and objective function for the problem are defined. The inputs are the values which can be changed, i.e. the variables a manager can decide on; the constraints are limitations on any of the variables; e.g. the total number of widgets made must be greater than or equal to the order amount; and the objective function is the output value to be optimized. By doing this the solver algorithm is allowed to ‘converge’ upon which set of inputs gives the lowest output of all, within a particular degree of confidence. For a problem involving manufacturing overhead calculation, then the solver would be set to seek a minimum.

Clearly, you will need to understand and correctly setup the objective function to achieve the desired results. In case of manufacturing overhead, you will need to get concurrence from your accounting and production teams on how it should be allocated. Should you use revenue based allocation? Resource (head count) based allocation or activity based allocation? It will be helpful to have a schematic of your manufacturing overhead calculation process, similar to the one shown below, before setting up the optimization problem.

Depending upon the complexity of the problem, a person may take minutes or hours to set up the ’model’, and the solver will take seconds or minutes do its part respectively. In addition or in place of excel you can also use "free" or open source tools such as RapidMiner to perform optimization tasks. (see for example, how to use RapidMiner's optimization operator to select the best parameters for a decision tree model). Now you can ask yourself, shall I do my own optimization problems or outsource them to a consultant?

Does your small business need analytics tools or support? Talk to us and participate in our introductory pilot program for custom built analytics.