CREAR ÁRBOL DE DECISIÓN CON SMARTART Y VBA

Hace unos días, un lector me comentaba que el post que había realizado sobre crear organigramas en SmartArt, también se podía adaptar para realizar un árbol de decisión. Y es cierto, de hecho las estructuras jerárquicas que aparecen en SmartArt sí que sugieren su idoneidad para crear árboles de decisión.

Por lo que hoy realizaré un pequeño ejercicio de ejemplo para comprobar cómo se adapta a un caso práctico. Recomiendo complementar la lectura de esta entrada con estos dos post:

• GENERAR ORGANIGRAMA EN SMARTART CON VBA EN EXCEL
• OBTENER NOMBRE E ID DE LOS DISEÑOS, COLORES Y ESTILOS RÁPIDOS DE SMARTART PARA VBA

Investigando por internet he dado con una página muy interesante sobre árboles de decisión, cuyo autor es Federico Garriga Garzón y que se titula Problemas Resueltos de Teoría de Decisión

De todos los ejercicio expuestos, he elegido el que más me ha gustado, pero podéis adaptar el que queráis. Es el ejercicio 19 y su enunciado dice así:

Una empresa está considerando la posibilidad de contratar un experto en ingeniería industrial para la planificación de su estrategia de operaciones. Una adecuada planificación supondría a la empresa unos beneficios de 1.000.000 de euros, mientras que si la planificación no fuera correctamente elaborada, la empresa estima unas pérdidas de 400.000 euros. El director industrial estima que la probabilidad de que el experto realice una adecuada planificación es del 75%. Antes de contratar al experto, la empresa tiene la opción de realizar unas pruebas para determinar la idoneidad del candidato, dichas pruebas tienen una fiabilidad del 80% a la hora de determinar el éxito potencial del candidato en la realización de la planificación de las operaciones de la empresa. Determine la decisión óptima para la empresa, así como el coste que puede asumir la empresa por realizar la prueba de idoneidad.

Pues bien, antes de comenzar y como se indica en la solución, lo primero que debemos hacer es confeccionar el árbol de decisión, solo con las decisiones, sin indicar cálculos. Tomando este ejemplo, los datos que debemos escribir para generar la estructura en gráfico serían estos:

Y ejecutando la macro (que más adelante os mostraré) generamos esta estructura:

Ahora, siguiendo los pasos del ejercicio, debemos realizar los cálculos necesarios que nos ayuden a calcular coste y el criterio para tomar una decisión óptima.

Los cálculos los realizaré en otra hoja (“CÁLCULOS”), para más información y detalle, os remito al ejercicio para consultar fórmulas, conceptos y teorías aplicadas. Estos serían los cálculos (primero mostrando las fórmulas y luego con los resultados):

Una vez que tenemos todos los datos calculados, solo tenemos que trasladarlos a la hoja (“DATOS”) e ir añadiendo información en las columnas de la derecha, así:

En la columna “D” vamos a colocar los cálculos que se refieren a las probabilidades condicionadas, las probabilidades a priori de cada acontecimiento (si es o no idóneo el candidato) y la aplicación del teorema de Bayes en función de contratar al candidato si es o no idóneo y la planificación es o no idónea), el dato irá en rojo.

En la columna “E” indicamos que si se trata de una planificación correcta, el beneficio esperado es de 1.000.000 euros y si es incorrecta las pérdidas serían de  (400.000) euros.

Finalmente en la columna “F” la calculamos directamente en la hoja (“DATOS”) resolviendo de derecha a izquierda (siguiendo el gráfico) y aplicando criterio de esperanza matemática calculando el beneficio esperado de cada decisión. Una vez realizado el cálculo para los tres nudos a la derecha, debemos colocar el resultado (beneficio) mayor en el siguiente nudo. De esta forma determinar mediante criterio de esperanza matemática entre las dos decisiones (Si es o no idóneo el candidato).

Finalmente, y como se comenta en la solución del ejercicio, dado que la última etapa es determinista, se debe elegir la alternativa cuyo beneficio sea mayor. Colocamos los resultados en azul y según cada línea de decisión.

La columna «C» hace referencia a los niveles de decisión, en este caso tenemos 4 niveles con sus nodos correspondientes.

Ahora ejecutamos esta macro para generar el árbol de decisión:

Sub ARBOL_DECISION()
Dim Diseño As SmartArtLayout
Dim Shape As Excel.Shape
Dim oNodos As SmartArtNodes
Dim i, j, Fin As Double
With Sheets("ARBOL")
.Select
'Borramos cualquier forma que se encuentre en la hoja ARBOL
For Each Shape In .Shapes
Shape.Delete
Next
'Insertamos gráfico SmartArt, recomendable utilizar el de nombre y puesto
Set Diseño = Application.SmartArtLayouts("urn:microsoft.com/office/officeart/2008/layout/NameandTitleOrganizationalChart") 'Diseño de la forma Smartart
Set Inserta = .Shapes.AddSmartArt(Diseño)
Set oNodos = Inserta.SmartArt.AllNodes
'Damos formato los colores y estilos rápidos
With Sheets("ARBOL")
For Each Shape In .Shapes
Shape.SmartArt.Color = Application.SmartArtColors("urn:microsoft.com/office/officeart/2005/8/colors/accent0_1") ' Colores disponibles
Shape.SmartArt.QuickStyle = Application.SmartArtQuickStyles("urn:microsoft.com/office/officeart/2005/8/quickstyle/3d5") 'Estilos rápidos
Next
End With
'colocamos la estructura a partir de la fila 3 y columna 1
.Shapes(1).Left = .Cells(3, 1).Left
.Shapes(1).Top = .Cells(3, 1).Top
Fin = Application.CountA(Sheets("DATOS").Range("A:A"))
'Creamos y eliminamos nodos según jerarquía y cantidad de nodos indicados en la DATOS
Do While oNodos.Count < Fin
oNodos.Add.Promote
Loop
For i = 1 To Fin
Do While oNodos(i).Level < Sheets("DATOS").Range("C" & i).Value
oNodos(i).Demote
Loop
With oNodos(i)
' Añadimos los datos de las columnas
v0 = Sheets("DATOS").Range("B" & i)
v1 = Sheets("DATOS").Range("D" & i)
v2 = Sheets("DATOS").Range("E" & i)
v3 = Sheets("DATOS").Range("F" & i)
'Contamos las palabras de cada columna
cv0 = UBound(Split(v0)) + 1
cV1 = UBound(Split(v1)) + 1
cv2 = UBound(Split(v2)) + 1
cv3 = UBound(Split(v3)) + 1
'Escribimos en cada nodo el contenido de las columnas
.TextFrame2.TextRange.Text = v0 & " " & v1 & " " & v2 & " " & v3
'Damos color (rojo y azul) a los valores de la misma forma que en el ejecicio de ejemplo
.TextFrame2.TextRange.Words(cv0 + 1, cV1).Font.Fill.ForeColor.RGB = vbRed
.TextFrame2.TextRange.Words(cv0 + cV1 + cv2 + 1, cv3).Font.Fill.ForeColor.RGB = vbBlue
End With
Next i
End With
'Formateamos el diseño del organigrama, lo presentamos con jerarquía horizontal "hierachy2"
With Sheets("ARBOL")
For Each Shape In .Shapes
Shape.SmartArt.Layout = Application.SmartArtLayouts("urn:microsoft.com/office/officeart/2005/8/layout/hierarchy2")
Next
End With
'Aumentamos zoom para ver correctamente el gráfico
ActiveWindow.Zoom = 180
'Liberamos variables
Set Diseño = Nothing
Set Inserta = Nothing
Set oNodos = Nothing
End Sub

He introducido algunas líneas de código nuevas para visualizar mejor el gráfico, (colocación y zoom) y también he implementado un pequeño sistema para colorear los ítems calculados y que forman parte del esquema.

Y el resultado es el siguiente:

Como podéis observar, el beneficio esperado es de 650.140 euros, fruto de la elección de mayor beneficio, entre “Hacer las pruebas: 650.140 euros” o “Contratar a un experto: 650.000 euros”. Es decir, el coste máximo que puede llegar a asumir la empresa para realizar las pruebas de idoneidad es de 140 euros.

Sin profundizar en los cálculos, el sentido del post es el de demostrar la posibilidad de implementar mediante SmartArt un árbol de decisión.

Por cierto, este post es el último del mes de Agosto, en breve me voy de vacaciones y estaré unos días desconectado 🙂 Nos vemos en Septiembre!!

Descarga el archivo de ejemplo pulsando en: CREAR ÁRBOL DE DECISIÓN CON SMARTART Y VBA