FUNCIONES LOGICAS Y ESCENARIOS

OBJETIVOS

Con este caso crearemos una hoja de cálculo que nos permita obtener las comisiones
derivadas de las ventas de un grupo de vendedores. Nos centraremos en los siguientes
aspectos de Excel:

• Herramienta administrador de escenarios

• Funciones lógicas

ENUNCIADO

La empresa VéndeloTODO se dedica a la distribución de cuatro productos de limpieza:

LímpialoTODO, LávaloTODO, DesengrásaloTODO, y todoTODO ( este último limpia, lava,
desengrasa al mismo tiempo). Para realizar la distribución tiene contratados a cinco
vendedores a los que se les realiza una liquidación semanal para el cálculo de sus
remuneraciones.

Se contemplan dos categorías de vendedores (1 y 2). Cada vendedor ha contratado con la

empresa sus propias comisiones por cada producto.

COMISION DE VENTAS
NOMBRE CATE limpialoTOD lavaloTODO desengrasaloTODO todoTODO
GORÍA O
Adriana Fernández 1 5% 10% 3% 15%
Valles
Pilar García 2 4% 10% 5% 10%
Extremera
Pilar Gamero 2 10% 5% 7% 5%
Adame
Ana Gonzalez 1 15% 6% 2% 13%
Montesinos
Javier Vázquez 1 6% 15% 10% 7%
Ramos
Tabla 1

La empresa en su política retributiva, contempla ciertos premios y sanciones con base en las
ventas conseguidas. Esta política es distinta para cada categoría y se resumen en los párrafos
siguientes:

CATEGORÍA 1:

Si las ventas semanales de LávaloTODO superan las $ 500.000 se obtiene un premio de $

50.000
Si las ventas de LímpialoTODO o de DesengrásaloTODO son inferiores a $100.000 se les
aplica una sanción de $ 25.000 .
La remuneración bruta(que incluye lógicamente, la remuneración por ventas, los premios y las
sanciones), no pueden ser inferior a $ 20.000. Semanales
Esta información está recogida en el rango A16:B21
CATEGORÍA 2:

Si las ventas totales(suma de la conseguidas en los cuatro productos) superan los

$ 5.000.000 se obtiene un premio de $ 250.000
Si las ventas de cada uno de los cuatro productos supera $ 1.000.000. El premio es de $
200.000 (este premio no es compatible con el anterior).
No existen sanciones.
La remuneración bruta mínima semanal es de $ 40.000
Esta información está recogida en el rango C16:D21.
Para mecanizar la liquidación semanal de cada vendedor se ha diseñado un libro de trabajo
denominado [Link] que contiene una única hoja de cálculo denominada
VéndeloTODO S.A.; Figura 1.

figura 1.

Se debe diseñar la hoja que realice el cálculo semanal de la remuneración para cada vendedor.
En concreto:

1. Se usarán escenarios que simplifiquen la entrada en la hoja de los datos

individualizados (nombre, categoría y comisiones) de esta forma, el usuario
sólo tendrá que introducir los datos relativos a las ventas semanales de cada
vendedor (rango B5:B8).
2. Indicar el contenido de las celdas con fórmulas del caso propuesto
(B9:B12:B14:D5:D11).

SOLUCION.

Como puede deducirse de la lectura del enunciado, cada vendedor ha contratado unas
comisiones particulares para sus ventas. Nosotros debemos calcular la remuneración de cada
vendedor, par lo que haremos uso de los escenarios en Excel, aunque no es ésta la única
solución, ya que podríamos haber optado por crear una hoja para cada vendedor o una zona
dentro de la misma hoja, entre otras posibilidades.

Volviendo al caso de la empresa VéndeloTODO, una sola hoja contendrá la estructura de

cálculo de la remuneración para todos los vendedores y crearemos un escenario distinto para
cada vendedor, que contendrá sus datos particulares, es decir, su nombre, comisiones
contratadas de ventas y su categoría. De esta forma, una celda ( o un conjunto de celdas) de
una hoja de cálculo puede tener asociados simultáneamente varios valores, uno por cada
escenario que hayamos creado en la hoja en cuestión. Dicho con otras palabras, una
estructura o modelo único puede servirnos para resolver diversos casos.

Observando la hoja tal como aparece en el enunciado, deducimos que existen tres tipos de
datos en el caso: los que más varían, que son las ventas semanales de cada vendedor, que se
introducirán al finalizar cada semana con objeto de calcular la remuneración; estas ventas se
teclearán, tal como dice el enunciado, en el rango B5:B8. Un segundo grupo de datos son los
relativos a las condiciones generales de venta, distintos para cada vendedor pero que no
varían de semana en semana. Nos referimos el nombre, categoría y comisiones de ventas por
producto . Estos datos, recogidos en los rangos B2:B3 y C5:C8, serán incluidos en escenarios
(uno por cada vendedor). Por último, el tercer grupo de datos no depende de ningún vendedor
en concreto, sino que afectan a las dos categorías, constituyendo el resto de celdas de la hoja.

A continuación comenzaremos por crear los escenarios correspondientes a cada vendedor.

Se despliega el menú HERRAMIENTAS y le selecciona ESCENARIOS; aparecerá un cuadro

de diálogo denominado Administrador de escenarios, tal como aparece en la figura. Como
Excel indica, no existe creado ningún escenario, por lo que procederemos a crear el primero,
que contendrá los datos pertenecientes al primer vendedor. Comenzamos pulsando sobre el
botón Agregar.

Figura 2.
En primer lugar, debemos introducir el nombre que identificará al escenario en cuestión; por
ejemplo, podemos usar el primer apellido del vendedor. En segundo término, hemos de indicar
a Excel cuáles serán las celdas cuyos datos variarán en función del escenario; en nuestro
caso, los nombre, categorías y comisiones por producto son datos que varían en función del
vendedor: el rango, por tanto, de celdas cambiantes es el formado por B2:B3;C5;C8 al ser en
estas celdas en donde se encuentra la información citada. Por último, es posible escribir algún
comentario u observación que sea útil para identificar el escenario en cuestión, así como
activar las opciones Evitar cambios y ocultar.
 Figura 3.
Una vez hemos identificado el escenario mediante un nombre e indicadas cuáles son las
celdas cambiantes, nos queda , como tercer paso, dar valores a dichas celdas. Al pulsar sobre
el botón Aceptar del cuadro de diálogo Agregar escenario, un nuevo cuadro de diálogo nos
permitirá asignar el valor que en este escenario tendrán cada una de las celdas cambiantes.
La figuran anterior muestra los datos para el primer escenario del enunciado. Una vez
introducidos, sólo queda pulsar sobre el botón Agregar para crear un nuevo escenario o
Aceptar para volver al cuadro de diálogo del Administrador de escenarios.
Un proceso similar al descrito habremos de realizar para asignar los valores correspondientes
al resto de escenarios, hasta completar los cinco (uno por cada vendedor)

Figura 4.

Si observamos la figura 3, vemos que cada celda del escenario se identifica mediante su
dirección (B2,B3,C5,C6,C7, y C8). Si queremos diseñar una interfaz más amigable, podríamos
asignar un nombre a cada celda, lo que facilita la entrada de datos. Para ello, seleccionamos
INSERTAR/NOMBRE/DEFINIR y a cada celda le damos un nombre, cuando volvamos a los
escenarios, el cuadro del diálogo Valores del escenario aparecerá como la figura 5

Figura 5
Creados los cinco escenarios, al seleccionar uno de ellos en el cuadro diálogo Administrador
de escenarios, los valores de las celdas cambiantes del mismo pasarán a mostrarse en la hoja
de cálculo. En nuestro caso la celda B2 sólo puede contener un valor: el nombre de uno de los
vendedores; pero gracias al uso de los escenarios, la citada celda tienen asociados cinco
posibles datos: cada uno de los nombre de los vendedores en función del escenario que esté
seleccionado. Para comprobarlo, sólo tiene que desplegar el menú HERRAMIENTAS y
seleccionar ESCENARIOS; escoja cualquiera de ellos y compruebe sobre la hoja cómo los
datos introducidos en las celdas cambiantes pasan a estar vigentes en la hoja de cálculo.

A continuación pasaremos a analizar el segundo punto del enunciado: diseño de las fórmulas
para resolver el caso.

Celda B9. Se trata de sumar el contenido del rango B5:B8; la solución más simple es usar la
función SUMA ( incluso puede pulsarse sobre el icono de autosuma de la barra de
herramientas estándar), de manera que el contenido de la celda será:

=SUMA(B5:B8)

Celda D5. En esta celda debemos calcular la comisión paracial derivada de las ventas de
LímpialoTODO; el procedimiento es muy simple, ya que basta con multiplicar el porcentaje de
la comisión (celda C5) por las ventas de la semana (celda B5), La fórmula quedaría:

=C5*B5
Celda D6:D8. El cálculo que se realiza en estas celdas es equivalente al de la celda D5 pero
para el resto de productos; podemos, por ello, copiar la celda C5 sobre las celdas D6:D8. La
copia no plantea problema alguna tal como está escrita la fórmula; tampoco habría problemas
si la fórmula de D5 fuera $C5*$B5, pero nunca podríamos fijar las filas.

Celda D9. El contenido de esta celda es similar al de la celda B9. Por tanto:

=SUMA(D5:D8)

Celda D10. En esta celda, Excel debe indicar la sanción que, en su caso, haya incurrido el
vendedor. Recordemos que el enunciado señala que el vendedor será sancionado con 25.000
Pta. En el caso de que, siendo de la categoría 1, las ventas de LímpialoTODO o de
DesengrásaloTODO hayan sido inferiores a $ 100.000. Para resolver esta problemática es
preciso que nos adentremos en el ámbito de las funciones lógicas.

Celda D10. Si la categoría del vendedor es 1 y las ventas de los productos LimpialoTODO o
DesengrásaloTODO son inferiores a 100.000 Pta., se aplicará una sanción de 25.000 Pta. No
existe una única forma de formulario: las que siguen ( que no son todas las posibles), son
válidas:

=SI(B3=1;SI(O(B5<B18;B7<B18);B19;0);0)
=SI(B3=1;SI(Y(B5>=B18;B7>=B18);0;B19);0)
=SI(B3=2;0;SI(O(B5<B18;B7<B18);B19;0)
=SI(O(B5<B18;B7<B18);SI(B3=1;B19;0);0)
=SI(Y(B3=1;O(B5<B18;B7<B18));B19;0)

Celda D11. En esta celda hemos de calcular el posible premio que el vendedor en cuestión
haya obtenido; como indica el enunciado, en caso de que la categoría sea 1 se obtendrá un
premio de $ 50.000. Para la categoría 2 se podrán obtener dos premios; uno de $ 250.000.
Cuando las ventas totales superen los cinco millones; otro en el supuesto de que las ventas de
cada uno de los cuatro productos sean mayores que el millón de pesos. Ambos premios no
son incompatibles.
Hemos de considerar que debemos realizar una única fórmula que, situada en la celda D11 sea
capaz de resolver todas las posibilidades comentadas. Una solución sería:

=SI(B3=1;SI(B6>B20;B21;0);SI(B9>D18;D19;0)+SI(Y(B5>D20;B6>D20;B7>D20;
B8>D20) ;D21 ;0))

Celda B12; esta celda es la encargada del cálculo de la remuneración bruta, s decir, la suma
de las comisiones obtenidas aumentadas con los posibles premios y detraídas, en su caso, las
sanciones; además, existe una remuneración bruta mínima de $ 20.000. Para las categorías 1
y 2 respectivamente. Mostramos tres de las posibles soluciones:

=SI(B3=1;SI((D9-D10+D11)<B17;B17;D9-D10+D11);SI((D9-D10+D11)<D17;D17;D9-
D10+D11))
=SI((D9-D10+D11)<SI(B3=1 ;B17 ;D17) ;SI(B3=1 ;;B17 ;D17) ;D9-D10+D11)
=MAX((D9-D10+D11) ;SI(B3=1 ;B17 ;D17))

Celda [Link] la retención de impuesto sobre la renta, por lo que será:

=B12*C13

Celda B14. Calcula el líquido a percibir, es decir, detrás de la remuneración bruta la retención
de impuestos:

=B12-B13