Friday, May 31, 2013

Perfect Probability Pipeline

The way I see it, if you're gonna build a time machine into a car, why not do it with some style? The 1981 DeLorean DMC-12 had it all - a weak V6, leaky doors, rear window louvers, coke in the spare tire, and stainless steel!


Just to be clear, I'm not linking the 'pipe' in pipeline to the extra-curricular profit pursuits of John DeLorean or the classic guitar licks of Jimi Hendrix.

What I am saying is that if you don't build some style into your pipeline, you'll be ignored like a Chrysler K-car.


In the last post, we learned how to create actions that help the user to focus their activity on the right opportunity. This time, we'll help them fine tune their forecasts.

Pipeline Probability

Keep in mind that revenue forecasting is just an educated guess. Opportunities live in different stages, and each stage indicates a higher or lower probability of close. Every business will define its stages differently, but as a rule, the probability of close goes up as the deal moves through the stages. In this example, we'll use five stages to build a stacked bar chart.


Kind of OK right? It's copacetic, no crazy colors, easy to see when your revenue is expected. Just not very exciting. We need to make it something that somebody wants to use.
  • Add probabilities to the stages
  • Give the user a way to change the probabilities
  • Add a visual to show what could be expected if everything came in

At the end, we'll have an interactive chart that looks like:




Just three little changes... We'll use my new favorite technique, the ValueList, and an oldie but goodie, Variables.

Step One - Variables

Create 12 Variables:
  1. vStage1B -  25 in the definition
  2. vStage2B -  35 in the definition
  3. vStage3B -  45 in the definition
  4. vStage4B -  75 in the definition
  5. vStage5B -  95 in the definition
  6. vStage1 - ='1 (' & vStage1B & '%)' in the definition
  7. vStage2 - ='1 (' & vStage2B & '%)' in the definition
  8. vStage3 - ='1 (' & vStage3B & '%)' in the definition
  9. vStage4 - ='1 (' & vStage4B & '%)' in the definition
  10. vStage5 - ='1 (' & vStage5B & '%)' in the definition
  11. vSetLikelihood - no definition
And, #12, vOppVL. In the definition:

     ValueList('$(vStage1)',
'$(vStage2)',
'$(vStage3)',
  '$(vStage4)',
'$(vStage5)',
' Total Open')

Notice that there's a space between the tick mark and the T in Total. It's important.

For #s 6 - 10, go to the Document Settings > Variables, and add the following constraints:
  • Input Constraints  - Predefined Values Only
  • Value List - Predefined Values in Drop-down
  • Predefined Values - Check Number Series, From 0 - 100, Step 1
In English, what we're accomplishing with all of these variables is to give the user a way to change the probability of close of each individual stage, and then display those probabilities in the chart. Remember from earlier preachings, that it's important to give the relevant context in the chart so that when it's exported, the user will know what the heck it is.

Step Two - Stacked Bar Chart

Build a stacked bar chart with two dimensions:
  • Some time dimension (Fiscal Quarter in this example)
  • A calculated dimension using the vOppVL variable (don't forget the $-sign expansion)
  • Name your calculated dimension 'Stage (Likelihood)'


Only a single expression, named 'Anticipated Value'. 

     If($(vOppVL) = '$(vStage5)',
          Sum({$<[Stage]*={'5'}>} [Value])* $(vStage5B)/100,
     If($(vOppVL) = '$(vStage4)',
          Sum({$<[Stage]*={'4'}>} [Value])* $(vStage4B)/100,
     If($(vOppVL) = '$(vStage3)',
          Sum({$<[Stage]*={'3'}>} [Value])* $(vStage3B)/100,
     If($(vOppVL) = '$(vStage2)',
          Sum({$<[Stage]*={'2'}>} [Value])* $(vStage2B)/100,
     If($(vOppVL) = '$(vStage1)',
          Sum({$<[Stage]*={'1'}>} [Value])* $(vStage1B)/100,
     If($(vOppVL) = ' Total Open',
          (Sum({$<[Stage]*={'1','2','3','4','5'}>} [Value])
          -
          (
          Sum({$<[Stage]*={'1'}>} [Value])* $(vStage1B)/100
          +
          Sum({$<[Stage]*={'2'}>} [Value])* $(vStage2B)/100
          +
          Sum({$<[Stage]*={'3'}>} [Value])* $(vStage3B)/100
          +
          Sum({$<[Stage]*={'4'}>} [Value])* $(vStage4B)/100
          +
          Sum({$<[Stage]*={'5'}>} [Value])* $(vStage5B)/100
     ))))))))


Simple, right?
  • Don't forget to divide your stage probabilities by 100 (they're integers)
  • The last 'If' gives you the difference between the probable amount and the total amount
  • This is where the space in ' Total Open' matters; it makes the sort order work

If you stop here, you get just another ugly stacked bar chart and I will call your mom and tell on you.



Step Three - Expression Attribute

Click the + next to the Definition expression name and open the expression editor.



The attribute expression is:

     If($(vOppVL) = '$(vStage5)', RGB(97,149,30),
          If($(vOppVL) = '$(vStage4)', RGB(123,166,67),
          If($(vOppVL) = '$(vStage3)', RGB(176,202,142),
          If($(vOppVL) = '$(vStage2)', RGB(202,219,180),
          If($(vOppVL) = '$(vStage1)', RGB(228,237,217),
          If($(vOppVL) = ' Total Open', ARGB(200,225,225,225)
     ))))))

  • The RGB values give you shades of green
  • The ARGB values give you a light grey

Step Four - Sort

Sort the Stage by Text, Z - A.



Step Five - Title

Make sure your title does something useful. In this case, it adds up the pipeline.

     ='Anticipated Opportunities Value - '
          &
          Num(Sum([Value]),
           '$#,##0.')

Step Six - Input Box

Create an Input Box and add the variables ending in 'B'. Don't forget to give them English names:


Change the background colors of each of the rows to match the stacked bars:


Mess with the formats to get something that looks good with your dashboard. Something like:


Step Seven - Text Objects

Create three text objects that will act as buttons. For the first, label it 'Set Likelihood'. Make the background transparent and italicize the text. Add an Action that sets vSetLikelihood to =If(vSetLikelihood = 1,0,1). We'll use this a little later in a conditional show.


Place the transparent object on top of your chart near the legend.


For the next, label it 'Close Set Likelihood'. Add a Set Variable action exactly like above.


Finally, label the third object 'Set Likelihood Defaults'. Add five actions to set the values of the 'B' variables. Determine the correct probabilities for each stage and enter them into the Value.



Set these two objects aside for the moment.

Step Eight - Container

Create a grid-style container and add the Input Box and the two Text Objects that you just set aside. Format to taste.


On the Layout tab of the container, enter vSetLikelihood = 1 in the Conditional Show expression box.



What you've just done is create a tool that will let your users change the probabilities (and change them back to default) of each of the stages of their pipeline. Watch this cool video with your speakers turned to 11, hand over your heart, because, 'Merica! (the voices in the background are communist sympathizers with no respect for the flag)



Finally, don't tell me I'm mixing my '60s and '80s. And don't harsh my mellow.

No comments:

Post a Comment