1
PRÁCTICA 29
La distribuidora de productos navideños “DE LA PI ERRE” se plantea diseñar un libro de trabajo para el cálculo de las ventas de sus productos. Para ello dispone de las siguientes hojas:
- Tipos de clientes: En esta hoja aparece una relación de los clientes de la empresa, así como el tipo de cliente: P (particular), S (Supermercado) y C (Centro Comercial).
- Tipos de vendedores: En esta hoja se almacenan los datos personales correspondientes a los vendedores de la empresa, la fecha de ingreso en la empresa, así como el código de la categoría a la que pertenecen. Para cada categoría también aparecen una serie de conceptos salariales (B12:G14). El plus de Antigüedad (I5) se paga por año integro de antigüedad en la empresa y es independiente de la categoría.
- Ventas: En esta hoja aparecen los siguientes datos:
o Tipos de lotes de productos que vende la empresa: A, B y C. Cada lote de productos se compone de un número de unidades de 3 artículos diferentes: turrón, trufas y caviar (B2:D4), también disponemos de los precios de ventas por unidad de cada uno de los artículos (B5:D5).
o Descuentos: se aplicarán tres tipos de descuentos:
Cuando el cliente sea un particular y compre más de 4 unidades del producto B, se aplicará el descuento que aparece en la celda F4.
Cuando el cliente sea un supermercado y compre el producto B ó C, se aplicará el descuento que aparece en el rango I2:J5. Por ejemplo, para ventas comprendidas entre 100 y 150 euros, se aplicará un porcentaje de descuento del 5%.
Para centros comerciales y siempre que compren más de 10 unidades de cualquier producto, se aplicará el tipo de descuento que aparece en la tabla (L2:M5). Por ejemplo, para ventas comprendidas entre 200 y 300 euros, se aplicará un porcentaje de descuento del 4%.
o Número de Pedido, nombre del cliente, NIF del vendedor, tipo de lote de producto vendido, unidades y fecha de venta.
A partir de los dichos datos se han de introducir las fórmulas correctas para obtener los resultados correspondientes a tipo de cliente, importe vendido de turrón, trufas y caviar, así como el importe total, descuento y total neto. Tenga en cuenta que las fórmulas se han de poder copiar hacia abajo para el resto de filas.
- Nominas: En esta hoja a partir del NIF del vendedor introducido y de la fecha actual de la nómina se pretende obtener el resto de resultados.
Finalmente, debe obtener en la hoja Informe una tabla en la que se muestre para cada vendedor los conceptos salariales incluidos en la nómina y una fórmula donde se calcule la masa salarial total que tendría que desembolsar la dirección de la empresa para el mes de febrero de 2006.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 A B C D
Tipo cliente Clientes
P Aguilera Ramírez S Apeninos S Atlántico C Carretour S Cheroski P Fernández Pérez P Gavira Florindo
S Las Dos Torres
C Lider P López Vázquez P Martín Vázquez C Mercatoma C Simango S Super Cádiz Clientes 2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 A B C D E F G H I
NIF Nombre Apellidos Fecha de ingreso Cod_cat Plus Antigüedad 12,00 €
32445678F Agustin González Fernández 01/06/1998 A 31433901A Francisca Cifuentes Garrido 01/09/2000 B
32652101G Manuel Macias Redondo 01/11/2002 C
Cod_cat Descripción Sueldo Base Dto IRPF Dto S.S. Comisiones
A Jefe de Ventas 1.803 € 10% 60 € 5% B Vendedor 1.202 € 7% 42 € 4% C Ayudante Vendedor 601 € 5% 30 € 3% Vendedores Categorías Vendedores 3
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 A B C D E F G H I J K L M
Pto Turrón Trufas Caviar Ventas Descuento Ventas Descuento
A 3 3 1 0,00 € 0% 0,00 € 0%
B 2 2 2 DTO. PARTICULARES 100,00 € 5% 200,00 € 4%
C 1 1 3 5% 150,00 € 10% 300,00 € 10%
PVP 1,65 9,85 22,4 200,00 € 15% 400,00 € 15%
Pedido Cliente Tipo Cliente Vendedor Pto. Unid. Fecha Turrón Trufas Caviar Total Descuento Total Neto
1Fernández Pérez P 32445678F B 2 26/02/2006 6,60 € 39,40 € 89,60 € 135,60 € 0,00 € 135,60 € 2Apeninos S 31433901A C 2 26/02/2006 3,30 € 19,70 € 134,40 € 157,40 € 15,74 € 141,66 € 3Carretour C 32652101G B 3 26/02/2006 9,90 € 59,10 € 134,40 € 203,40 € 0,00 € 203,40 € 4Lider C 32652101G A 11 26/02/2006 54,45 € 325,05 € 246,40 € 625,90 € 93,89 € 532,02 € 5Super Cádiz S 32445678F A 4 26/02/2006 19,80 € 118,20 € 89,60 € 227,60 € 0,00 € 227,60 € 6Cheroski S 31433901A B 2 26/02/2006 6,60 € 39,40 € 89,60 € 135,60 € 6,78 € 128,82 € 7Martín Vázquez P 32652101G B 3 26/02/2006 9,90 € 59,10 € 134,40 € 203,40 € 0,00 € 203,40 € 8Gavira Florindo P 32445678F B 5 26/02/2006 16,50 € 98,50 € 224,00 € 339,00 € 16,95 € 322,05 € 9Atlántico S 32652101G C 1 26/02/2006 1,65 € 9,85 € 67,20 € 78,70 € 0,00 € 78,70 € 10Simango C 31433901A B 15 26/02/2006 49,50 € 295,50 € 672,00 € 1.017,00 € 152,55 € 864,45 € 11Mercatoma C 32445678F A 4 26/02/2006 19,80 € 118,20 € 89,60 € 227,60 € 0,00 € 227,60 € 12Las Dos Torres S 31433901A C 2 26/02/2006 3,30 € 19,70 € 134,40 € 157,40 € 15,74 € 141,66 € 13Apeninos S 32652101G B 2 27/02/2006 6,60 € 39,40 € 89,60 € 135,60 € 6,78 € 128,82 € 14López Vázquez P 32445678F C 3 27/02/2006 4,95 € 29,55 € 201,60 € 236,10 € 0,00 € 236,10 € 15Super Cádiz S 31433901A B 3 27/02/2006 9,90 € 59,10 € 134,40 € 203,40 € 30,51 € 172,89 € 16Carretour C 31433901A B 2 27/02/2006 6,60 € 39,40 € 89,60 € 135,60 € 0,00 € 135,60 € 17Lider C 32445678F C 11 27/02/2006 18,15 € 108,35 € 739,20 € 865,70 € 129,86 € 735,85 € 18Aguilera Ramírez P 32652101G B 5 27/02/2006 16,50 € 98,50 € 224,00 € 339,00 € 16,95 € 322,05 € 19Cheroski S 31433901A B 2 27/02/2006 6,60 € 39,40 € 89,60 € 135,60 € 6,78 € 128,82 € 20Atlántico S 32652101G C 3 27/02/2006 4,95 € 29,55 € 201,60 € 236,10 € 35,42 € 200,69 € 21Fernández Pérez P 32445678F C 1 27/02/2006 1,65 € 9,85 € 67,20 € 78,70 € 0,00 € 78,70 € 22Carretour C 31433901A A 5 28/02/2006 24,75 € 147,75 € 112,00 € 284,50 € 0,00 € 284,50 € 23Apeninos S 32652101G B 7 28/02/2006 23,10 € 137,90 € 313,60 € 474,60 € 71,19 € 403,41 € 24López Vázquez P 32445678F C 3 28/02/2006 4,95 € 29,55 € 201,60 € 236,10 € 0,00 € 236,10 € 25Mercatoma C 31433901A C 2 28/02/2006 3,30 € 19,70 € 134,40 € 157,40 € 0,00 € 157,40 € 26Simango C 32652101G C 11 28/02/2006 18,15 € 108,35 € 739,20 € 865,70 € 129,86 € 735,85 € 27Atlántico S 32445678F B 3 28/02/2006 9,90 € 59,10 € 134,40 € 203,40 € 30,51 € 172,89 € 28Martín Vázquez P 31433901A B 5 28/02/2006 16,50 € 98,50 € 224,00 € 339,00 € 16,95 € 322,05 €
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 A B C NIF 32445678F
Apellidos González Fernández
Cod_cat. A Fecha actual 28/02/2006 Conceptos Sueldo Base 1803 Plus antigüedad 84,00 € Comisiones 127,49 € Sueldo Bruto 2.014,49 € Descuentos IRPF 201,45 € Seg. Social 60 Sueldo Líquido 1.753,04 € Nominas 5
Resumen de escenario
Valores actuales: Vendedor 1 Vendedor 2 Vendedor 3 Celdas cambiantes:
NIF 32445678F 32445678F 31433901A 32652101G
Celdas de resultado:
Apellidos González Fernández González Fernández Cifuentes Garrido Macias Redondo
Cod_categoria A A B C Sueldo_Base 1803 1803 1202 601 Plus_antigüedad 84,00 € 84,00 € 60,00 € 36,00 € Comisiones 127,49 € 127,49 € 108,92 € 94,87 € Sueldo_Bruto 2.014,49 € 2.014,49 € 1.370,92 € 731,87 € IRPF 201,45 € 201,45 € 95,96 € 36,59 € Seg._Social 60 60 42 30 Sueldo_Líquido 1.753,04 € 1.753,04 € 1.232,95 € 665,28 € MASA SALARIAL 3.651,27 € 28/02/2006 Informe 6
7
PRÁCTICA 30
La distribuidora de jamones “EL JAMONCITO” se plantea diseñar un libro de trabajo para el cálculo de las nóminas de sus empleados. Para ello dispone de las siguientes hojas:
- Vendedores: En esta hoja aparecen las siguientes tablas:
o Datos personales correspondientes a los vendedores de la empresa, la fecha de ingreso en la empresa, así como el código de la categoría a la que pertenecen. (A6:E8).
o Conceptos salariales correspondientes a cada categoría (B12:G14). El plus de Antigüedad se paga por año integro de antigüedad en la empresa.
o Comisiones mensuales (C19:F24): se pagan en función del importe de ventas de cada producto. Por ejemplo, para ventas mensuales del producto 3J comprendidas entre 1000 y 6000 euros se pagará un tipo de comisión del 2%.
o Premio 1: Para obtenerlo basta con que algunas de las cifras de ventas de los tres productos supere los 20.000 euros (D27), en cuyo caso se pagará un premio de 100 euros (D28).
o Premio 2: Consiste en 50 euros (E28) por cada cifra de ventas mensual que supere los 11000 euros (E27).
o Premio 3: Se obtiene siempre que la suma de las ventas totales mensuales de cada vendedor para los tres productos supere los 30000 euros (F27) y no se haya obtenido el premio 1. El importe del premio es 75 euros (F28).
- Ventas: En esta hoja aparece un extracto de las ventas de cada uno de los productos realizadas por los distintos vendedores durante el mes de febrero de 2006.
- Comisiones y premios: En esta hoja se han de obtener:
o Las ventas totales por cada vendedor y producto para el mes de febrero de 2006 (Ha de crear una única fórmula en C4 y copiarla para el resto de vendedores y productos). o Las comisiones mensuales en euros por cada vendedor y producto, también para el
mes de febrero. (Ha de crear una única fórmula en F4 y copiarla para el resto celdas). o Los premios obtenidos por cada uno de los vendedores.
o El total de comisiones y premios de cada vendedor.
o Una tabla como la que aparece en el rango D9:H13 en la que para el vendedor con el NIF 32445678F se simule el total de comisiones y premios que obtiene según las ventas de los productos 3J y 4J. Por ejemplo si vende de 3J 3000 euros y de 4J 15000 euros el importe por comisiones y premios sería 1.125,50 euros. Para obtener la tabla tenga en cuenta que las ventas de 3J están el rango E9:H9 mientras que las ventas de 4J están en el rango D10:D13.
- Nóminas: En esta hoja a partir del NIF del vendedor introducido y de la fecha actual de la nomina se pretende obtener el resto de resultados.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 A B C D E F G APELLIDOS Y NOMBRE
Cod_cat NIF Nombre Apellidos Fecha de ingreso
B 31433901A Francisca Cifuentes Garrido 01/09/2000
A 32445678F Agustin González Fernández 01/06/1998
C 32652101G Manuel Macias Redondo 01/11/2002
Cod_cat Descripción Sueldo Base Dto IRPF Dto S.S. Plus Antigüedad
A Jefe de Ventas 1.803 € 10% 60 € 12,00 € B Vendedor 1.202 € 7% 42 € 8,00 € C Ayudante Vendedor 601 € 5% 30 € 5,00 € 3J 4J 5J 0 € 1% 1,50% 2% 1.000 € 2% 2,75% 4% 6.000 € 3% 4% 6% 12.000 € 4% 5,25% 8% 24.000 € 5% 6,50% 10% 48.000 € 6% 7,75% 12%
PREMIO 1 PREMIO 2 PREMIO 3
20.000 € >11000 30.000 € 100 € 50 € 75 € Vendedores Categorías COMISIONES Vendedores 8
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 A B C D E Vendedor Fecha A B C 32445678F 26/02/2006 2.200,00 € 3.000,00 € 500,00 € 31433901A 26/02/2006 1.000,00 € 2.000,00 € 500,00 € 32652101G 26/02/2006 2.000,00 € 3.500,00 € 375,00 € 32652101G 26/02/2006 3.000,00 € 16.321,50 € 456,00 € 32445678F 26/02/2006 1.500,00 € 5.000,00 € 1.000,00 € 31433901A 26/02/2006 2.000,00 € 3.000,00 € 700,00 € 32652101G 26/02/2006 1.500,00 € 3.200,00 € 1.100,00 € 32445678F 26/02/2006 3.000,00 € 2.000,00 € 700,00 € 32652101G 26/02/2006 2.500,00 € 2.300,00 € 850,00 € 31433901A 26/02/2006 1.500,00 € 2.500,00 € 150,00 € 32445678F 26/02/2006 2.500,00 € 5.000,00 € 300,00 € 31433901A 26/02/2006 2.500,00 € 1.500,00 € 450,00 € 32652101G 27/02/2006 1.200,00 € 2.100,00 € 125,00 € 32445678F 27/02/2006 3.200,00 € 4.000,00 € 250,00 € 31433901A 27/02/2006 900,00 € 1.500,00 € 325,00 € 31433901A 27/02/2006 1.200,00 € 3.000,00 € 345,00 € 32445678F 27/02/2006 1.678,00 € 2.000,00 € 900,00 € 32652101G 27/02/2006 1.400,00 € 1.375,00 € 723,00 € 31433901A 27/02/2006 1.000,00 € 500,00 € 375,00 € 32652101G 27/02/2006 1.500,00 € 3.100,00 € 210,00 € 32445678F 27/02/2006 2.500,00 € 1.000,00 € 450,00 € 31433901A 28/02/2006 500,00 € 550,00 € 150,00 € 32652101G 28/02/2006 1.000,00 € 2.250,00 € 175,00 € 32445678F 28/02/2006 1.550,00 € 1.500,00 € 1.000,00 € 31433901A 28/02/2006 550,00 € 1.000,00 € 200,00 € 32652101G 28/02/2006 1.100,00 € 4.567,00 € 230,00 € 32445678F 28/02/2006 2.100,00 € 1.000,00 € 600,00 € 31433901A 28/02/2006 750,00 € 990,00 € 150,00 €
VENTAS DEL MES DE FEBRERO DE 2006
1 2 3 4 5 6 7 8 9 10 11 12 13 A B C D E F G H I J K
Vendedor 3J 4J 5J 3J 4J 5J PREMIO 1 PREMIO 2 PREMIO 3
32445678F 20.228,00 € 24.500,00 € 5.700,00 € 809,12 € 1.592,50 € 228,00 € 100,00 € 100,00 € 0 € 31433901A 11.900,00 € 16.540,00 € 3.345,00 € 357,00 € 868,35 € 133,80 € 0,00 € 100,00 € 75 € 32652101G 15.200,00 € 38.713,50 € 4.244,00 € 608,00 € 2.516,38 € 169,76 € 100,00 € 100,00 € 0 € 2.829,62 € 2.000,00 € 2.500,00 € 3.000,00 € 4.000,00 € VENTAS 5.000,00 € 405,50 € 415,50 € 425,50 € 445,50 € DE 10.000,00 € 668,00 € 678,00 € 688,00 € 708,00 € 4J 15.000,00 € 1.105,50 € 1.115,50 € 1.125,50 € 1.145,50 € 20.000,00 € 1.368,00 € 1.378,00 € 1.388,00 € 1.408,00 €
VENTAS TOTALES COMISIONES
VENTAS DE 3 J
1 2 3 4 5 6 7 8 9 10 11 12 13 L TOTAL 2.829,62 € 1.534,15 € 3.494,14 € Comisiones y premios 10
1 2 3 4 5 6 7 A B C D E F G H I Fecha actual 28/02/2006
Vendedores Cod.Cat Fecha ingreso Sueldo Base Plus Antigüedad Sueldo Bruto Descuentos Sueldo Neto
32445678F A 01/06/1998 1.803 € 84,00 € 4.716,62 € 531,66 € 4.184,96 €
31433901A B 01/09/2000 1.202 € 40,00 € 2.776,15 € 236,33 € 2.539,82 €
32652101G C 01/11/2002 601 € 15,00 € 4.110,14 € 235,51 € 3.874,63 €
