Esta semana he estado trabajando en este proyecto en Excel pensado para medir el rendimiento y la producción de un Hotel. Para la elaboración del proyecto he elegido un Hotel ficticio que he llamado «Mountain Retreat», y que cuenta con 50 habitaciones. El objetivo era, a partir de un libro de Excel con distintas hojas de datos (en concreto 9 hojas), elaborar un Dashboard o panel de mando interactivo que permita, a través de una visualización sencilla, medir la producción y el rendimiento del hotel en distintas áreas. El reto del proyecto, además del método de trabajo en sí mismo, era generar un modelo de datos de producción de dos años de hotel de manera aleatoria.
Dashboard de medición de rendimiento de «Mountain Retreat». David Sánchez Vallejo.
El proyecto tiene los datos ficticios de los años 2022 y 2023, los cuales se han generado de forma automática indicando ciertos parámetros que detallaré a continuación. Al ser un modelo aleatorio, no hay que buscar similitud con la realidad en los conceptos de facturación y segmentación de mercado. Por ejemplo, no tiene mucho sentido facturar más en concepto de Late Check Out que en concepto de Restaurante. Lo que se pretende en el proyecto es mostrar la funcionalidad de este método de trabajo, pensado para insertar datos reales del hotel que lo quiera utilizar para medir su rendimiento.
En realidad, si hacemos esta tarea a diario no nos supondría más que unos 15 minutos en ir rellenando la producción del día cerrado, es decir los distintos campos que veremos a continuación, y el resultado a medio-largo plazo es poderoso a la hora de interpretar los datos de una manera visual personalizada y sencilla que nos permitirá tomar las decisiones adecuadas. Todos los datos necesarios se obtienen normalmente de los listados del cierre de turno que genera cualquier software de gestión de hotel: Manager Flash y Trial Balance.
PRIMERA HOJA: Setup.
La primera hoja de Excel se llama «Setup», y es en en la que debemos configurar ciertos parámetros que son la base de los cálculos sobre los que van a actuar las distintas métricas. En nuestro caso, hemos establecido que el hotel cuenta con 50 habitaciones.
Hemos indicado los conceptos de Extra Revenue que se facturan en nuestro hotel, así como los segmentos de mercado, la tipología de las habitaciones y finalmente los hoteles que forman parte de nuestra competencia directa susceptible de analizar con sus respectivas habitaciones.
SEGUNDA HOJA: ManagerFlah.
Una vez tenemos la configuración del libro de trabajo pasamos a la segunda hoja, que he llamado «ManagerFlash«.
Esta hoja está configurada con los siguientes campos: DATE, MONTH, DAY, ROOMS AVAILABLE, ROOM OCC, ROOM OCC LY, TWIN, QUEEN, FAMILIAR, SUITE, ACCESIBLE ROOM, PAX, RMT (Reservations Made Today), RNRT (Room nights reserved today), CXL (Cancellations), ROOM REVENUE, ROOM REVENUE LY (last year), EXTRA REVENUE, EXTRA REVENUE LY, TOTAL REVENUE, TOTAL REVENUE LY.
Una vez hemos configurado las columnas e insertado el rango de fechas en la primera fila , lo ideal es convertir todo el área de trabajo a una Tabla, presionando las teclas CTRL + T, y eligiendo la opción «tabla con encabezados». Renombramos la tabla con el nombre ManagerFlash. Los encabezados de las columnas tienen distintos colores (rojo, azul y negro). Los encabezados en color rojo son los que no debemos rellenar una vez hayamos insertado las correspondientes fórmulas; los de color negro corresponden a los campos que hay que rellenar todos los días; por último, los de color azul tienen truco: debemos rellenar los datos del año anterior hasta el día 31/12/2022. A partir del año 2023 no hacen falta rellenarlos, ya que se nos redirige a los datos de 2022 (que ya serían «last year» en 2023).
– Date, la fecha diaria de resultados. Para hacer esta columna basta con rellenar la primera fecha que queramos, en nuestro caso el 1 de enero de 2022, y después arrastrar hacia abajo todos los campos hasta la fecha final que deseamos, en esta caso el 31 de diciembre de 2023. La idea es tener los registros de dos años de producción del hotel.
– Month, el nombre del mes. Para ello he insertado la siguiente función en la celda B2: =TEXTO(A2;»mmm»). Esta función lo que hace es convertir la fecha de la celda A2 en el nombre del mes con sus tres primeras letras, al haber colocado entre comillas el formato «mmm».
– Day, el nombre del día. Para ello he utilizado la misma función anterior, en esta caso como sigue:
=TEXTO(A2;»dddd»). Ahora nos devuelve el nombre del día en formato texto.
– Rooms Available. Son las habitaciones disponibles, en nuestro caso serían las habitaciones que han quedado por vender el día anterior. Este campo no es necesario rellenarlo, ya que contiene la siguiente fórmula. =Setup!$B$3-[@[Room Occ]] Si recordáis, la primera hoja que hemos creado se llamaba «Setup», y en ella habíamos especificado que nuestro hotel tenía 50 habitaciones; lo que hace la fórmula es indicar que las habitaciones que quedan por vender son SetupB3 (50) menos las habitaciones que se han ocupado. En esta fórmula hay un detalle muy importante a tener en cuenta, me refiero a dar valor absoluto a la celda SetupB3, esto se hace pulsando la tecla F4 sobre la celda la cual queremos que tenga el valor absoluto (lo podéis ver porque B3 está situada entre el símbolo $). El valor absoluto lo que hace es fijar este campo para que al arrastrarlo hacia abajo el resto de las celdas permanezca inamovible; si no le diéramos el valor absoluto, al arrastrar para copiar la fórmula hacia las filas de abajo, el valor cambiaría a SetupB4, SetupB5, y así sucesivamente. Como resultado, nos daría resultados erróneos.
– Rooms Occ. Son las habitaciones ocupadas. Este campo también es una fórmula que no es necesario rellenar, ya que contiene la suma de la ocupación según la tipología de habitación. La fórmula es =SUMA(G2;H2;I2;J2;K2).
Lo que había hecho al generar la tabla y las distintas columnas, fue agrupar los tipos de habitación para ocultarlas a simple vista de la tabla. Si os fijáis, sobre la columna L hay un signo de +; si pulsamos sobre el signo se despliegan las columnas agrupadas.
¿Cómo se agrupan columnas o filas en Excel? Tan sólo hay que seleccionar las filas o columnas que se desean agrupar; después, en la pestaña Datos de la cinta de herramientas de Excel, buscar la opción «Agrupar». Lo podéis ver en la imagen de abajo.
– Rooms Occ LY. En este campo debemos rellenar las habitaciones ocupadas del año anterior, (que serían las de 2021) hasta fecha 31/12/2022. A partir de 2023 están redirigidos a las habitaciones ocupadas en 2022, que pasarían a ser Last Year ahora. Para generar un modelo automático para el proyecto, se ha aplicado una fórmula para cumplimentar los datos del Last Year de 2021. He partido de la premisa de que hay un 10% más de ocupación con respecto al año anterior. La fórmula es =[@[Room Occ]]-([@[Room Occ]]*0,1).
– Distintos tipos de habitación (Twin, Queen, Suite, Familiar, Accesible Room). Aquí hay que insertar el número de habitaciones ocupadas en su correspondiente campo. Estos datos tienen repercusión sobre la columna Rooms Occ, que tal y como expliqué anteriormente tenía la función de suma de los distintos tipos de habitación. Para generar los datos de manera aleatoria, he utilizado las siguientes funciones:
Twin =ALEATORIO.ENTRE(1;22)
Queen =ALEATORIO.ENTRE(1;22)
Familar =ALEATORIO.ENTRE(1;2)
Suite =ALEATORIO.ENTRE(1;2)
Accesible Room =ALEATORIO.ENTRE(1;2)
– Pax. Número de personas que se han alojado el día anterior. En nuestro modelo aleatorio se ha insertado la siguiente fórmula: =[@[Room Occ]]*ALEATORIO.ENTRE(1;2) Se basa en el supuesto de que multiplique el número de habitaciones ocupadas por un número aleatorio de personas entre 1 y 2.
– RMT. Aquí hay que insertar el número de reservas que hemos insertado en el día. Para el proyecto, se ha seleccionado la siguiente opción para generar todos los datos: =ALEATORIO.ENTRE(10;50)
– RNRT. Tenemos que insertar el número de «room nights» que nos han reservado en el día. Para generar todos los datos, he elegido la siguiente fórmula: =[@RMT]*ALEATORIO.ENTRE(1;2). Se trata de multiplicar el número de reservas que hemos tenido en el día por un número aleatorio entre 1 y 2.
– CXL. El número de cancelaciones que hemos recibido. Para el proyecto, he utilizado la fórmula: =ALEATORIO.ENTRE(0;10).
– Room Revenue. Insertamos los ingresos generados por concepto de habitación.
– Room Revenue LY. Esta columna tiene el encabezado de color azul, por lo tanto rellenemos los datos hasta el 31/12/2022. A partir de 2023, se redirige al Room Revenue de 2022, que pasaría a ser Last Year.
– Extra Revenue: Esta columna no la rellenamos. Tiene una fórmula de suma hacia la siguiente hoja de trabajo que veremos después (hoja ‘Trial Balance’) La fórmula es =SUMA(Trial_Balance!B5:J5).
–Extra Revenue LY. Como todas las columnas de Last Year, con encabezado de color azul, rellenamos los datos hasta 31/12/2022 (que corresponderían a 2021), y partir de 2023 se redirige hacia 2022, que pasa a ser Last Year.
– Total Revenue y Total Revenue LY. Estas columnas no hay que rellenarlas, ya que llevan una fórmula que suma el Room Revenue más el Extra Revenue. La fórmula es =SUMA(P5+R5).
TERCERA HOJA: Trial_Balance.
Ahora empezamos con al tercera hoja del libro, que he titulado Trial_Balance. Se trata de ir anotando el Extra Revenue que vamos facturando a diario.
Los conceptos de Extra Revenue se configuraron en la primera hoja, llamada ‘Setup’ y son: Bar, Restaurant, Parking, Pay TV, Telephone, Late Check Out, Shuttle, Tours, Other.
Aquí tenemos que insertar a diario todos los datos de producción. A nivel de configuración para el método de trabajo, no entraña ninguna dificultad, sólo hay que rellenar los datos en todos los campos.
Sin embargo, para el que los que estéis interesados en profundizar un poco en cómo se ha generado este modelo aleatorio, tengo que decir que es la hoja que me ha generado más dificultad. ¿Por qué? Si recordais, la hoja anterior llamada ManagerFlash tiene un campo Extra Revenue que tiene una fórmula que dirige a esta página para hacer la suma de todos los conceptos. Hasta ahí bien. Pero, ¿cómo generar este modelo aleatorio con cierto control teniendo en cuenta que son 9 conceptos distintos? Podemos dividir la cantidad total entre 9, pero esto nos daría el mismo resultado para todos los campos, por lo que no nos sirve.
Para los hoteles que no cuenten con espacio para organizar grandes eventos (bodas, comuniones, fiestas de fin de año, etc…) que les genere un Extra revenue que en determinados días pueda superar al Room Revenue, había que establecer un control para que el Extra Revenue no supere la cantidad del Room Revenue, ya que no suele ser habitual que esto suceda. Si genero números aleatorios sin control se podría haber dado esta circunstancia que trataba de evitar. Por esto lo primero que había que solucionar era generar una cantidad que fuera la suma de todos los conceptos, es decir el total del Extra Revenue por día. Para ello utilicé una columna separada de la tabla principal añadiendo un valor mínimo y uno máximo de la siguiente forma (valor mínimo 83.50 € y valor máximo de 580.56 €).
=ALEATORIO.ENTRE(83,5;580,56)
Copié esta fórmula desde el 01/01/2022 hasta el 31/12/2023.
Después, había que crear nueve números aleatorios distintos, uno por concepto de facturación de extra Revenue:
=ALEATORIO()
Copié esta fórmula desde el 01/1/2022 hasta el 31/12/2023.
La solución era generar números aleatorios que sumaran una cifra específica, cifra que estaba situada en la columna L (los datos generados con valores entre 86.50 € y 580.56 €). Por ejemplo, para el día 1 de enero de 2022 queremos que la cifra de Extra Revenue total sea de 570 €, y queremos que esta cantidad se reparta entre los 9 conceptos posibles que tenemos.
Para ello aplicamos en la primera columna de Extra Revenue, en este caso la que equivale al concepto ‘Bar’ la siguiente fórmula:
=M2/(SUMA($M2:$U2))*$L2
Damos valor absoluto al rango de suma de las columnas M2 a U2, que corresponden a los números generados aleatoriamente en el paso anterior aplicando la fórmula ALEATORIO(). También damos valor absoluto a L2, que es la cantidad específica resultante de la fórmula =ALEATORIO.ENTRE(83,5;580,56). Como resultado tenemos la cantidad facturada en concepto Bar; ahora arrastramos la fórmula hacia las 8 columnas de la derecha y nos dará como resultado la cantidad facturada de cada concepto. El total sumará 570 € para el día 1 de enero. Se repite el proceso para todos los días hasta el 31/12/2023.
CUARTA HOJA: MARKET.
En la cuarta hoja tenemos la segmentación de mercado, diferenciando los distintios canales de venta y la producción en Room Revenue generada a través de cada uno de ellos.
Aquí habría que ir rellenado todas las columnas con la producción diaria según el segmento de mercado, que habíamos configurado en la primera hoja ‘Setup’. Para el proyecto, se generó la producción de manera aletaroria siguiendo el mismo método que en la hoja anterior, la del Extra Revenue. Tan sólo hay una diferencia. En esta ocasión, ya teníamos la cifra objetivo especifíca, en este caso el Room Revenue, que podemos ver en la hoja segunda (ManagerFlash). Por lo tanto, para conseguir la cifra objetivo se utilizó la fórmula siguiente:
=ManagerFlash[@[Room Revenue]]
El resto del proceso para obtener el Room Revenue de cada segmento de mercado es el mismo que el seguido en la hoja anterior.
QUINTA HOJA: COMPETITIVE SET.
En esta hoja anotamos la producción de los hoteles que están en nuestroo competitive set; los datos se recogen de algunas soluciones online como STR, plataformas a las que se sucriben ciertos hoteles y añaden los hoteles que están en su competencia. Si los otros hoteles también están dados de alta en la plataforma, entonces se recogen los datos de todos ellos y se generan estadísticas que ayudan a ver el Benchmarking. Los datos son anónimos, es decir vemos las estadísticas pero no sabemos a qué hotel corresponde cada una de ellas.
En el proyecto se han elegido como competencia otros tres hoteles ficticios que se configuraron en la primera hoja, así como las habitaciones con las que cuentan cada uno de ellos.
Los hoteles de la competencia son `La Posada del Cura’ (70 habitaciones), ‘Mirador del Pueblo’ (60 habitaciones), ‘Cañón del Bandolero’ (40 habitaciones). Se han generado números aleatorios para rellenar el modelo de trabajo.
Para el número de habitaciones ocupadas, se han insertado las siguientes fórmulas:
– Para el Hotel ‘La Posada del Cura’ =ALEATORIO.ENTRE(10;70)
– Para el Hotel ‘Mirador del Pueblo’ =ALEATORIO.ENTRE(10;60)
– Para el Hotel ‘Cañón del Bandolero’ =ALEATORIO.ENTRE(10;40)
Para calcular la ocupación , he aplicado la siguiente fórmula para el Hotel ‘La Posada del Cura’
=[@RoomOcc]*(ALEATORIO.ENTRE(45;130))
La fórmula multiplica el número de habitaciones ocupadas por un precio medio aleatorio entre 45 y 130 €.
Para el Hotel ‘Mirador del Pueblo’ he utilizado:
=[@RoomsOcc]*(ALEATORIO.ENTRE(75;170))
Para el hotel ‘Cañón del Bandolero’:
=[@RoomsOcc]*(ALEATORIO.ENTRE(60;150))
La fórmula de porcentaje de ocupación es nº de habitaciones ocupadas / nº de habitaciones totales del hotel * 100. En Excel he utilizado la siguiente fórmula:
=[@RoomOcc]/Setup!$B$20
=[@RoomOcc]/Setup!$B$21
=[@RoomOcc]/Setup!$B$22
En la hoja Setup habíamos configurado el número de habitaciones de cada hotel.
En la fórmula no es necesario multiplicar por 100 porque damos formato de porcentaje a la celda en la que está insertada la fórmula.
El Average Daily Rate (ADR) es la tarifa media del día, y se calcula dividiendo el Room Revenue por el número de habitaciones que se han ocupado.
=[@[Room Revenue]]/[@RoomOcc]
El Renenue per Available Rate (RevPAR) se calcula dividiendo el Room Revenue por todas las habitaciones del hotel (hayan sido ocupadas o no).
=[@[Room Revenue]]/Setup!$B$20
=[@[Room Revenue]]/Setup!$B$21
=[@[Room Revenue]]/Setup!$B$22
SEXTA HOJA: KPI
La sexta hoja está configurada con fórmulas que generan los Key Performance Indicators (KPI) de nuestro hotel ‘Mountain Retreat’, que son lo indicadores clave de rendimiento, las métricas que nos muestran la evolución del hotel y nos dan las señales de lo que está funcionando bien o mal. No es necesario rellenar nada, ya que las fórmulas hacen referencia a distintas hojas.
Las fórmulas de porcentaje de ocupación, ADR Y RevPAR han sido explicadas en el apartado anterior cuando hablaba sobre los hoteles de la competencia.
ALOS (Average Lenght of Stay), es la duración media de la estancia. Su fórmula es ‘total de noches de ocupación por habitación / cantidad de reservas’.
En nuestro proyecto hemos utilizado la siguiente fórmula, remitiendo a la segunda hoja (ManagerFlash):
=ManagerFlash[@RNRT]/ManagerFlash[@RMT]
El TRevPAR es el Total Revenue dividido por la cantidad de habitaciones que tiene nuestro hotel.
=ManagerFlash[@[Total Revenue]]/Setup!$B$3
Finalmente llegamos a ls métricas que nos comparan con nuestro Competitive Set:
– Índice de ocupación/índice de penetración en el mercado (Market Penetration Index, MPI): Mide el rendimiento de ocupación (Occ.) de un hotel en relación con un grupo acumulado de hoteles (es decir, un grupo competitivo, mercado o submercado). Si los rendimientos son equivalentes, se esperará que el índice de Occ., o MPI, de una propiedad sea de 100 en comparación con el grupo acumulado de hoteles.
Un MPI mayor que 100 representa una participación mayor que la esperada en el rendimiento de la ocupación del grupo acumulado.
Por el contrario, un MPI menor que 100 refleja una participación menor que la esperada en el rendimiento de la ocupación del grupo acumulado.
Para calcular el MPI:
(Occ. del hotel en cuestión/Occ. del grupo agregado de hoteles) x 100
En el proyecto he utilizado la siguiemte fórmula:
=[@[Occ %]]/PROMEDIO(ComptSet1!C3;ComptSet1!J3;ComptSet1!Q3)
El Average Rate Index (ARI) o Índice de tarifa media mide el rendimiento ADR de un hotel en relación con un grupo acumulado de hoteles (es decir, un grupo competitivo, mercado o submercado).
Si los rendimientos son equivalentes, se esperará que el ARI de una propiedad sea de 100 en comparación con el grupo acumulado de hoteles.
Un ARI mayor que 100 representa una participación mayor que la esperada en el rendimiento ADR del grupo acumulado. Por el contrario, un ARI menor que 100 refleja una participación menor que la esperada en el rendimiento ADR del grupo acumulado.
Para calcular el ARI: (ADR del hotel en cuestión/ADR del grupo acumulado de hoteles) x 100
En el proyecto hemos utilizado la fórmula como sigue:
=([@ADR]/PROMEDIO(ComptSet1!E3;ComptSet1!L3;ComptSet1!S3))*100
El Índice ndice de generación de ingresos (Revenue Generating Index, RGI) mide el rendimiento RevPAR de un hotel en relación con un grupo acumulado de hoteles (es decir, un grupo competitivo, mercado o submercado, etc.).
Si los rendimientos son equivalentes, se esperará que el índice RevPAR, o RGI, de una propiedad sea de 100 en comparación con el grupo acumulado de hoteles.
Un RGI mayor que 100 representa una participación mayor que la esperada en el rendimiento RevPAR del grupo acumulado.
Por el contrario, un RGI menor que 100 refleja una participación menor que la esperada en el rendimiento RevPAR del grupo acumulado.
Para calcular el RGI la fórmula es (RevPAR del hotel en cuestión/RevPAR del grupo acumulado de hoteles) x 100
En el proyecto la fórmula es como sigue:
(ManagerFlash[@[RoomRevenue]]/PROMEDIO(ComptSet1!D3;ComptSet1!K3;ComptSet1!R3))*100
SÉPTIMA HOJA: GUEST REVIEWS
En esta hoja del libro se trataba de llevar un cierto control sobre la opinión de los clientes en los principales canales de venta, en una escala de 1 al 10.
La propuesta aquí es un poco simple, lo ideal sería hacer una hoja de Excel separada para cada uno de los canales en las que hay comentarios de clientes, y que esta hoja fuera un resúmen de todas ellas, con la media de puntuación de cada día. Por razones de tiempo para el proyecto no lo he hecho así en esta ocasión, no obstante para el propósito de comprobar la funcionalidad del método de trabajo sirve perfectamente.
Se ha utilizado la misma fórmula para todas las columnas, con el objetivo de que se emita una puntuación entre 0 y 10.
La fórmula es :
=ALEATORIO.ENTRE(0;10)
OCTAVA HOJA: TABLAS DINÁMICAS Y OTROS CAMPOS AÑADIDOS.
Una vez tenemos toda la estructura del libro, tenemos que hacer tablas dinámicas para poder diseñar nuestro Dashboard.
Aquí lo primero a tener en cuenta es que queremos crear gráficas y escala de tiempo que afecte a todas las tablas del libro de trabajo, por lo que debemos crear una relación de tablas.
Para ello, nos vamos a la cinta de opciones, seleccionamos datos y después Relaciones.
Una relación de tablas se crea uniendo dos tablas que tienen un campo en común; el campo principal debe tener valores únicos, niguno que sea repetido. En este caso he elegido el campo fecha para ir jugando en las relaciones de tablas. Este campo único debe estar presente en otra tabla. Es una relación similar a las Primary Key / Foreign Key de SQL.
Una vez creada la relación de tablas podemos insertar tablas dinámicas seleccionando campos de las distintas hojas del libro de trabajo. Con las tablas dinámicas que creemos, generaremos los gráficos y la escala de tiempo que insertaremos en el Dashboard.
Hacemos las tablas dinámicas que nos interesen para generar los gráficos. En este caso, he insertado 13 tablas dinámicas y generado los gráficos que están en el Dashboard.
A tener en cuenta: en todas las tablas dinámicas he escogido el campo Fecha de la tabla ManagerFlash, después he insertado una escala de tiempo.
Una vez creada la escala de tiempo, damos con el click derecho sobre la misma y seleccionamos ‘Conexiones de Informes’.
Aparecerá una ventana emergente en la que podemos seleccionar las tablas que queremos vincular a la escala de tiempo.
Ahora, cada vez que seleccionemos un perídodo de tiempo de la escala temporal, ya sea un día, mes, trimestre o año, afectará a todas las tablas y gráficos que hayamos generado, por lo que el Dashboard cambiará en función de la fecha.
NOVENA HOJA: DASHBOARD
La configuración del Dashboard depende de la idea que tenga cada uno, en este caso elegí un diseño resaltando con un fondo suave la parte central de abajo, es decir la gráfica y métricas relacionadas con el Competitive Set.
La eleboración de la gráfica de anillo que resalta el porcentaje de ocupación la hice de la siguiente forma.
Necesitamos dos valores, uno con el tanto por ciento de ocupación, y el otro con la cantidad que falta hasta llegar al 100%. Para ello, se inserta una tabla dinámica (a la izquierda en la imagen de arriba); al lado insertamos en una celda el valor del total general de la tabla dinámica:
=IMPORTARDATOSDINAMICOS(«[Measures].[Promedio de Occ %]»;$T$3) ——-Esta es la celda W5
En la otra celda insertamos la fórmula
=1-W5
Una vez tengamos estos dos valores a la derecha de la tabla dinámica, insertamos un gráfico de anillo con ellos. En el valor de la ocupación le damos el color que queramos, y en el segundo campo le damos un color claro para que nos de la sensación de que es la parte que falta por completar hasta el 100%.
Pulsando botón derecho sobre el gráfico,damos formato al tamaño del agujero central del anillo, que en nuestro caso ha sido del 65%
Dentro de la parte central del anillo se inserta una forma de elipse, mantenmiendo presionada la techa ‘Shift’ del teclado para que salga en forma de círculo, redonda. Le doy el tamaño adecuado y la sitiuo lo más centrada posible, dándooe el mismo color azul que la gráfica.
Después, hago ‘click’ dentro de la forma y me voy a la barra de fórmulas de excel e inserto lo siguiente:
=’Tablas Dinámicas’!W5
Ahora aparecerá el porcentaje de ocupación. En las opciones de gráfico quito la visibilidad de los datos, para no ser redundante y evitar que aparezca de nuevo el porcentaje de ocupación en la serie de datos.
Otro aspecto a comentar son los ‘trucos’ visuales del Dashboard; en el Dahboard hay tres, me refiero al sistema de calificación de comentarios con estrellas que hay debajo de la escala temporal, a las caras que muestran el estado de satisfacción del cliente y al KPI con al triángulo de color verde hacia arriba y hacia abajo en rojo en las métricas del Competitive Set (MPI, ARI, RGI).
El truco en el siguiente del sistema de estrellas es el siguiente:
Se crea una tabla dinámica de la hoja de Guest Reviews; debajo del promedio de cada segmento, se inserta una celda con la siguiente fórmula:
=REPETIR(«ê»;IMPORTARDATOSDINAMICOS(«[Measures].[Promedio de Booking]»;$BH$6))
Le estamos diciendo que repita ‘x’ veces el valor ‘ê’, que con el tipo de letra Wingdings 2 aparece como una estrella. Ya está: en el caso de Booking que hay 7 de calificación promedio, repetirá siete veces la ê, por lo que veremos siete estrellas con el tipo de letra Wingdings 2.
En el Dashboard insertaremos en ua celda la fórmula =’Tablas Dinámicas’!BH9 y aparecerán las 7 estrellas de Booking. Después cambiamos el color del ‘tipo de letra’ para que las estrellas tengan el aspecto que queramos.
A la derecha de las estrellas en el Dashboard aparece una cara que indica el estado de satisfacción del cliente.
Estos emoticonos de caras se obtienen pulsando la tecla de windows más el punto; entonces se despliega una ventana emergente con distintios emoticonos.
En la tabla dinámica que habíamos creado con los ‘reviews’ he añadido un último campo, el promedio de todas las calificaciones. Pues bien, lo que hay que hacer en el Dashboard es insertar una fórmula condicional como sigue:
=SI(‘Tablas Dinámicas’!BL7<=2;»😣«;SI(‘Tablas Dinámicas’!BL7<5;»☹«;SI(‘Tablas Dinámicas’!BL7<7;»😐»;»😃»)))
Debemos insertar la fórmula condicional como hacemos habitualmente, y cuando vayamos a insertar el valor que queremos que aparezca según la condición tecleamos la tecla de Windows más el punto e insertamos el emoticono que queramos; después seguimos con la fórmula de la manera habitual.
La cara irá cambiando en el Dashboard en función del promedio de las calificaciones de los clientes.
El último ‘truco’ visual lo tenemos en los KPI del Competitive Set; si bien es cierto que los formatos condicionales permiten insertar KPI´s, no obstante a mi personalmente me gusta más la forma en como quedan con este truco.
El truco es parecido al de las estrellas. En este caso la fórmula es la siguiente:
=SI(I28<100%;»q»;SI(I28=100%;»u»;»p»))
El tipo de letra debe ser Wingdings 3, ya que en este tipo de letra, la ‘q’ aparece como un triangulo invertido, la ‘u’ hacia la derecha y la ‘p’ como un triángilo hacia arriba. Después añadimos un formato condicional para resaltar ‘Texto que contiene’, y añadimos las reglas correspondientes con sus respectivos formatos de colores.
Muchas gracias por leer este proyecto sobre el método de trabajo para medir la producción de un hotel. Espero os haya resultado de interés.
Si alguien no disponede tiempo para diseñar su método de trabajo y necesita una persona que le diseñe un método de trabajo personalizado, puede contactar conmigo a través del mail que aparece en el profile de Linkedin.
David Sánchez Vallejo 