Beatriz Lopez Mod2 Anexo

Beatriz López Pinilla
ANEXO TÉCNICO MODELO 2
OBJETIVOS
El objetivo del segundo trabajo es desarrollar un modelo que, en función de las

variables del entorno macroeconómico, pueda hacer una previsión y simular los
resultados fundamentales demuestra empresa SAICA que se recopilan en sus
estados contables.
Para ellos necesitaremos la creación de una ecuación que nos expliquen la

evolución de cada una de las partidas de las cuentas de PPyGG y de esta
manera poder hacer predicciones. Con esta primera ecuación desarrollaremos
una evolución estimada denominado escenario básico con la que
posteriormente jugaremos con ella en diferentes escenarios de probabilidad
tantos óptimos como pésimos.
PROCESO DE CONSTRUCCIÓN DEL MODELO
Lo primero de todo será tener nuestra base de datos con la cual iniciaremos
todo el proceso. Esta base de datos será la misma utilizada en el modelo 1, la
cuenta de pérdidas y ganancias y balance obtenido de la base de datos de
Sabi.
Además de usar una base de datos que denominaremos “macro” en el cual se

recoge información sobre los mercados de todos los sectores, así como de los
precios de compras y ventas y tipos de interés, también recuperaremos la hoja
de “TOTAL” del modelo.
Los datos de Sabi aparecen ordenados del año más reciente al más antiguo
por tanto tendremos que modificarlos con una fórmula de Excel para ordenarlos
cronológicamente. Posteriormente, en una pestaña nueva, crearemos nuestra
propia base de datos personalizada para que se ajuste a nuestro modelo final.
1
En esta nueva base de datos deberemos especificar las siguientes partidas:
Variable Descripción SABI

Cuenta de pérdidas y ganancias
Ingresos Ingresos totales percibidos por la empresa Ingresos de
explotación
Gastos Total gastos de explotación Suma partidas de
explotación gastos de explotación
Compras Adquisición de bienes y servicios para la Materiales
producción
Personal Gastos totales de personal Gastos de personal
Amortizaciones Gastos en dotaciones de amortización Dot. amortiz. de
inmovil.
Otros Gastos Resto de gastos vinculados a la explotación Diferencia entre
ingresos, rdo. de
explotación y las otras
tres partidas de gasto
Rdo. Diferencia entre ingresos y gastos de Resultado Explotación
Explotación explotación
Ingresos Ingresos por rendimientos financieros Ingresos financieros
Financieros
Gastos. Pagos por pasivos financieros. Gastos financieros
Financieros
Rdo. Financiero Saldo neto de operaciones financieras Resultado financiero
Rdo. Resultado neto de operaciones extraordinarias Resultados actividades
Extraordinario extraordinarias
Rdo. Antes de Suma de resultado de explotación, financiero y Suma los resultados
impuestos extraordinario anteriores
Impuesto de Pagos por impuesto de sociedades Diferencia entre el
sociedades Rdo. antes de
impuestos y el Rdo.
del ejercio
Rdo. Ejercicio Beneficio neto a distribuir Resultado del Ejercicio
Balance
ACTIVO Total de activos Activo
Inmovilizado Activos vinculados a la actividad productiva(1) Inmovilizado
Otros Activos Otros activos Activo Circulante
PASIVO Total de pasivos Pasivo
Fondos Capital más reservas Fondos propios
Propios
Fondos Ajenos Otros activos Pas. Fijo más líquido
Todos estos datos aparecen directamente en nuestra base de datos original

excepto por alguno los cuales deberemos calcular como “otros gastos de
explotación que son la diferencia entre el total de gastos de explotación con las
compras, gastos de personal y amortización. El total de gastos de explotación
es la diferencia de los ingresos de explotación con la partida de resultado de
explotación de Sabi. En cuanto al balance de situación, la partida de activos
financieros es la diferencia del activo total con el inmovilizado, por simplificar un
poco la estructura del activo. De la misma manera ocurre con el pasivo y sus
fondos ajenos que son la diferencia del pasivo total con los fondos propios
2
Todo esto será necesario para calcular los siguientes datos necesarios para la
elaboración de nuestro modelo.
numero empleados BBDD sabi

Precio de venta BBDD hoja macro
ingresos en cantidad Ingresos explotación/precio venta*100
tamaño mercado BBDD hoja macro
cuota mercado Ingresos en cantidad/tamaño mercado/1000
Precio compras BBDD hoja macro
Compras en cantidad Compras/precio compras*100
ratio de compras Compras en cantidad/ingresos en cantidad
ratio otros gastos Otros gatos explotación/total ingresos explo.
ratio de inmovilizado Inmovilizado/ingresos totales explotación
salario medio Gastos personal/nº empleados*1000
tasa crecimiento salario (Salario medio/salario medio t-1)-1
salario nacional BBDD hoja macro
tasa salario nacional (salario nacional/el salario nacional t-1)-1
productividad empleados Ingresos en cantidad/nº empleados
tasa cto productividad (Productividad/productividad t-1)-1
tasa de amortización Amortización/inmovilizado t-1
rentabilidad de inversiones Ingresos financieros/activos financieros t-1
tipo interes CP BBDD hoja macro/100
rendimiento de la bolsa 47%
coste medio deuda Gastos financieros/fondos ajenos t-1
Tipos de interés LP BBDD hoja macro/100
Tipo medio IS Impuestos/resultados antes de impuestos
Ratio de capitalización Explicado mas adelante
Ratio de otros activos Activos financieros/inmovilizado
Ratio de apalancamiento Fondos propios/fondos ajenos
Rango/media otros activos DESVEST(ratio otros activos)/
PROMEDIO(ratio otros activos)
Rango/media apalancamiento DESVEST(ratio apalancamiento)
/PROMEDIO(ratio apalancamiento)
tendencia 1 (nº sucesivos según el año hasta 2030)
Con estos datos, podemos calcular todos los resultados hasta el último que
tenemos disponible que es el 2022, pero la finalidad de este modelo es realizar
una estimación para los próximos 8 años hasta llegar al 2030. Una vez
realizado las estimaciones pertinentes volveremos a la hoja “base” a terminar
de rellenar los datos que faltarían.
CÁLCULO ESTIMACIONES
3
Para empezar, abriremos una nueva pestaña a al que llamaremos

“estimaciones”. El propósito de esta es realizar unas regresiones lineales que
sean capaces de predecir econométricamente los datos futuros para las
variables de cuota de mercado, ratio de compras, tasa crecimiento salario,
productividad empleados, rentabilidad inversiones, coste medio de la deuda y
ratio de capitalización.
Cuota de mercado: para esta regresión lineal, cogeremos los valores reales
de 1998 hasta 2028 y el valor aproximado será el resultado de la formula: ß1*x1
+ ß2*x2 + ß3*x3+ ß 0. Siendo ß aquellas betas calculadas con la formula de Excel
(ESTIMACIÓN LINEAL), y las x procedentes de las variables explicativas que
escogeremos para cada ecuación de las estimaciones. En este caso las
variables escogidas son: otros gastos de explotación, el inmovilizado y la
tendencia, todas en logaritmos para las correcciones.
Ratio de compras: para la ratio de compras será el mismo proceso a seguir

donde cogeremos el valor real de la ratio de compras y el valor estimado será
el de las variables explicativas inmovilizado, otros gastos de explotación y
nuevamente tendencia. Nuevamente en base logarítmica y en el caso de la
tendencia se ha corregido poniendo 0 en sus valores.
Salario medio: en este caso las variables explicativas son la productividad y el

salario nacional, y a modo de corrección añadiremos una variable ficticia para
corregir el cambio de estructura que se nos presentó realizando esta
estimación lineal.
Productividad: aquí las variables explicativas son la tendencia, el inmovilizado

y otros gastos. Todas ellas en base logarítmica.
Rentabilidad: para este modelo las variables serán el tipo de interés a CP y el

rendimiento de la bolsa.
Coste medio de la deuda: para este modelo cogeremos la variable de tipo de

interés a LP a la que le añadiremos una ficticia para corregir los cambios de
estructura.
Ratio de capitalización: donde las variables explicativas son el resultado de

explotación y una ficticia para corregir el cambio de estructura.
Una vez obtenidas las betas de cada una de las estimaciones, lo siguiente que
haremos será volcar esta nueva información a la pestaña de base que
4
habíamos dejado aparcada. Una vez ahí, procederemos a aplicar las nuevas
ecuaciones en los años 2023-2030, multiplicando las betas por las variables
explicativas correspondientes de cada estimación y obtendremos las
predicciones en un escenario básico.
Para terminar de rellenar la tabla, deberemos asumir 2 cosas:
- Que los gastos de explotación aumentarán cada año un 20%, por ello
multiplicaremos por 0,2 cada año desde el 2022
- Que el inmovilizado aumentará en un 10% y por ello multiplicaremos por
0,1 cada vez desde el 2022.
El resto de la tabla se completa con la información obtenida a partir de las

nuevas estimaciones y asunciones.
Por último, para el ratio de capitalización, usaremos una fórmula que nos
devolverá un dato en concreto: si el resultado del ejercicio del año anterior es
positivo el ratio de capitalización será el equivalente a la beta 1 de las
estimaciones del modelo de ratio de capitalización, de lo contrario será igual a
1.
CALCULO ESCENARIOS
Esto que acabamos de calcular sería para un escenario básico, pero nuestro
objetivo principal será escenificar los posibles escenarios tanto buenos como
malos para poder hacer un análisis profundo y comparativo de nuestro modelo
de estimación.
Por ello lo primero que haremos será duplicar la pestaña base, y pegarla en
formato valores y la llamaremos BAU. Este será el escenario básico que no
estará a sujeto a modificaciones que haremos posteriormente en la pestaña de
base.
Como queremos hacer el escenario de manera más detallada, además de

duplicar la pestaña base, deberemos duplicar la pestaña macro ya que las
predicciones sobre los datos macroeconómicos también cambiaran en cada
escenario.
5
Para comenzar, abriremos una nueva pestaña a la que llamaremos simulador.

En ella crearemos unos botones de selección de lista para escenificar los
escenarios siendo:
- Escenario 1: BASICO
- Escenario 2: OPTIMO
- Escenario 3: PESIMO
- Escenario 4: PROBABLE
Esto irá encadenado con una salida de barras que estará asociado a la
probabilidad y el ratio de sigma de cada uno.
Para que este simulador funcione, hay que cambiar la formulación de las
estimaciones de la pestaña macro añadiendo unos condicionales. Estos
condicionales serán como el siguiente.
- SI(SIMULADOR!$F$6=4;Macro!AK3*(1+BASICO!CE3+SIMULADOR!
$F$9*BASICO!$EG3);SI(SIMULADOR!$F$6=3;Macro!AK3*(1+BASICO!
$EE3);SI(SIMULADOR!$F$6=2;Macro!AK3*(1+BASICO!$ED3);BASICO!
AL3)))
Esto quiere decir que dependiendo de que escenario estemos (simulador F6=
1, 2, 3, O 4), tomará un valor y otro ya que estos valores serán multiplicados
por la sigma de cada uno de ellos.
Esta fórmula se aplica de esa manera para los valores del mercado, pero para
los precios y los tipos de interés difiere un poco pero la estructura es la misma.
Para visualizarlo, realizaremos una serie de gráficos que serán dinámicos y de

esa manera podremos ver las diferentes fluctuaciones de un escenario a otro.
Nos centraremos en: el resultado del ejercicio, el total de ingresos y el total de
gastos. En el entorno macro, haremos lo mismo para el porcentaje de
crecimiento en el mercado del sector, los precios de venta y los precios de
compra.
Para realizar los gráficos construiremos unas tablas que definan, el escenario,
el básico y la diferencia y traeremos a esas tablas los datos de las pestañas
que hemos ido rellenando de los diferentes años.
6
Este es el aspecto que debería tener nuestro simulador.
CÁLCULO ANÁLISIS DE RESULTADOS
Pero aquí no hemos terminado, además de analizar los diferentes escenarios

que puede tomar nuestra empresa, podemos realizar un análisis detallado de la
cuenta de resultados a su vez.
Por tanto, abriremos una nueva pestaña que llamaremos análisis. En ella
volveremos a crear unos cuadros de lista que contendrán tanto los diferentes
años, como las magnitudes que queremos analizar, y las volcaremos en otro
gráfico dinámico para poder visualizar los datos. Añadiendo unos cuadros de
botón, podemos analizarlo tanto en datos enteros como en tasas y para ellos
crearemos unas tablas dinámicas al lado con las correspondientes tasas de
crecimiento de cada magnitud a analizar
7

Beatriz Lopez Mod2 Anexo

Cargado por

Copyright:

Formatos disponibles

Beatriz Lopez Mod2 Anexo

Cargado por

Información del documento

Título original

Derechos de autor

Formatos disponibles

Compartir este documento

Compartir o incrustar documentos

Opciones para compartir

¿Le pareció útil este documento?

¿Este contenido es inapropiado?

Copyright:

Formatos disponibles

Beatriz Lopez Mod2 Anexo

Cargado por

Copyright:

Formatos disponibles

Beatriz López Pinilla

ANEXO TÉCNICO MODELO 2

El objetivo del segundo trabajo es desarrollar un modelo que, en función de las

Para ellos necesitaremos la creación de una ecuación que nos expliquen la

PROCESO DE CONSTRUCCIÓN DEL MODELO

Además de usar una base de datos que denominaremos “macro” en el cual se

En esta nueva base de datos deberemos especificar las siguientes partidas:

Variable Descripción SABI

Todos estos datos aparecen directamente en nuestra base de datos original

numero empleados BBDD sabi

Para empezar, abriremos una nueva pestaña a al que llamaremos

Ratio de compras: para la ratio de compras será el mismo proceso a seguir

Salario medio: en este caso las variables explicativas son la productividad y el

Productividad: aquí las variables explicativas son la tendencia, el inmovilizado

Rentabilidad: para este modelo las variables serán el tipo de interés a CP y el

Coste medio de la deuda: para este modelo cogeremos la variable de tipo de

Ratio de capitalización: donde las variables explicativas son el resultado de

Para terminar de rellenar la tabla, deberemos asumir 2 cosas:

El resto de la tabla se completa con la información obtenida a partir de las

Como queremos hacer el escenario de manera más detallada, además de

Para comenzar, abriremos una nueva pestaña a la que llamaremos simulador.

Para visualizarlo, realizaremos una serie de gráficos que serán dinámicos y de

Este es el aspecto que debería tener nuestro simulador.

CÁLCULO ANÁLISIS DE RESULTADOS

Pero aquí no hemos terminado, además de analizar los diferentes escenarios

También podría gustarte