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:
- vStage1B - 25 in the definition
- vStage2B - 35 in the definition
- vStage3B - 45 in the definition
- vStage4B - 75 in the definition
- vStage5B - 95 in the definition
- vStage1 - ='1 (' & vStage1B & '%)' in the definition
- vStage2 - ='1 (' & vStage2B & '%)' in the definition
- vStage3 - ='1 (' & vStage3B & '%)' in the definition
- vStage4 - ='1 (' & vStage4B & '%)' in the definition
- vStage5 - ='1 (' & vStage5B & '%)' in the definition
- 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
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