Tal
como señalamos anteriormente, un formulario es utilizado
generalmente para reunir información del usuario que interactúa con él. Pues
bien, a través del siguiente ejemplo veremos como es posible construir
en forma muy sencilla un formulario en Excel, solo que en este caso
específico no lo orientaremos a recolectar información, sino que más
bien para ofrecer información a los clientes de una determinada empresa.
Formulario para realizar cotizaciones
Tomaremos como base la Empresa ABC que comercializa un único
artículo (mgie), cuyo precio varía en razón del color de dicho artículo,
de acuerdo con el siguiente cuadro:
|
Color |
Precio |
|
Amarillo |
9.900 |
|
Azul |
10.000 |
|
Negro |
8.000 |
|
Rojo |
9.600 |
|
Verde |
10.500 |
Este artículo además, por políticas de la empresa, solo es
comercializado en paquetes de 24 unidades cada uno. Con el
objetivo de incentivar las ventas, la empresa ofrece una política de
descuentos, así entonces, cuado el monto total de la venta supera los $
500.000 otorga un 3% de descuento sobre el total, ahora bien si el monto
supera los $ 800.000 el descuento será de un 5% sobre el total.
Debido a lo importante que es el flujo de efectivo para la empresa, ésta
ha decidido aplicar también un descuento especial para fomentar el pago
al contado, siendo este de un 2% (sobre el monto total, una vez
realizados los descuentos por volumen). Las otras opciones de pago
que ofrece es dentro de los 30 días siguientes al de la venta, caso en
el cual esta empresa no ofrece descuento alguno. Además, ahora ha
incorporado también el pago con tarjeta de crédito, pero esta forma de
pago tiene un costo para la empresa debido a las comisiones que debe
cancelar, razón por la cual ha decido sobrecargar el monto total de
venta en un 1,5% cada vez que uno de sus clientes decida pagar su compra
con tarjeta de crédito.
Teniendo en cuenta todas estas cuestiones, ahora deberemos construir un
formulario muy fácil de usar, pero que nos permita considerar todos los
factores que condicionan el precio final de venta de los artículos.
Construcción del Formulario
En primer lugar abriremos un libro en blanco, en el cual llamaremos "formulario"
la primera hoja de dicho libro. Si bien es cierto, que
usted puede construir el formulario de la forma que le sea más cómoda o
agradable, le recomendamos que siga los pasos y posiciones que le
indicamos en este ejemplo, con el objetivo que se más fácil la revisión
en caso de que se comentan errores.
A modo de guía, utilizaremos el resultado final que se desea
obtener con dicho formulario:
Como podemos apreciar, el formulario abarcará desde B4 hasta H19, por lo
que pintaremos el fondo de esta área y además le daremos formato a
los bordes. El área entre B2 y H2 la uniremos para colocarle un título
al formulario, conjuntamente le aplicaremos formato al texto hasta
obtener el efecto deseado.
A continuación lo que haremos será construir la estructura de dicho
formulario, para lo cual le daremos formato a las distintas celdas que
formarán parte de él. Comenzaremos por las celdas donde se
reflejará la cantidad (G7), el precio unitario (G9), agregaremos una
celda para realizar un subtotal (G11). Posteriormente diremos que
en G13 calcularemos el eventual descuento por volumen y en G15
el descuento por pronto pago, para finalmente calcular el total
definitivo en la celda G17.
Luego crearemos una tabla que contenga la relación entre el color y el
precio del artículo, ha dicha área le daremos el nombre de color.
En el caso de nuestro ejemplo lo hemos hecho en el rango B37:C41.
También insertaremos unas etiquetas de texto para hacer referencia a los
controles que posteriormente agregaremos ("Seleccione su Color" y
"Cantidad").
Pues bien, lo que ahora corresponde hacer es agregar los distintos
controles. El primero que agregaremos será el cuadro combinado.
Una vez hecho esto, le vincularemos a la celda G9 (LinkedCell) y
además le asociaremos el área color (ListFillRange).
A la propiedad BoundColumn le asignaremos el valor de "2".
Luego para hacerlo más llamativo, le daremos formato al texto (Font)
y además le daremos color al texto con la propiedad ForeColor.
Posteriormente insertaremos el control de número, lo
dimensionaremos con el mouse hasta que tenga el aspecto deseado.
En cuanto a sus propiedades, lo vincularemos la celda "G7" que es donde
queremos que aparezca la cantidad que esta siendo cotizada. Debido
a que el artículo en cuestión sólo se vende por paquetes de 24 unidades,
asignaremos este valor a la propiedad SmallChange, luego a la
propiedad Min le asignaremos igual cantidad y a la propiedad
Max, le asignaremos un valor lo suficientemente alto, por ejemplo
2000.
Nos queda ahora realizar todo aquello correspondiente a los descuentos,
con el objetivo de evaluar si el monto de venta será objeto o no de
descuento por volumen introduciremos la siguiente fórmula den dicha
celda:
=REDONDEAR(G11*SI(G11<500001;0;SI(G11<800001;-0,03;-0,05));0)
Lo que estamos haciendo en primer término es redondear el resultado
obtenido del cálculo a cero decimales. Luego la fórmula nos
permite evaluar si el monto de ventas (G11) cumple con alguno de los
requisitos para que sean aplicables los descuentos, indicando en cada
caso la cuantía de cada uno de los descuentos en tanto por uno.
Es tiempo ahora de insertar un nuevo control, el cual se trata en
realidad de tres botones de opción que se comportarán como un
único control. Para esto sólo lo seleccionamos de la barra
de herramientas dicho control y lo insertamos, para luego dimensionarlo con el mouse a nuestro
gusto. A través de la propiedad Caption le daremos el
nombre visible a dicho botón, siendo en el primer caso "Efectivo".
A través de la propiedad LinkedCell vincularemos este control con
la celda "C16", para finalmente a través de las propiedades Font,
BackColor y ForeColor configuraremos la apariencia de
dicho botón hasta obtener el resultado deseado.
Luego repetiremos estos mismos pasos con los dos botones siguientes,
solo que en este caso Caption tomará los valores "30 días" y
"Tarjeta de Crédito" en cada caso. En cuanto a las celdas
vinculadas, estás serán "C17" y "C18" respectivamente. Luego de
esto alinee los distintos botones con el mouse hasta que den una imagen
de bloque.
Si realizó correctamente todos los pasos, al probar los controles de
opción podrá comprobar que éstos devuelven el valor de "VERDADERO" o
"FALSO" en las distintas celdas vinculadas, dependiendo si éstos han
sido seleccionados o no. Pues bien, nos basaremos es esto
último para construir la fórmula o algoritmo que nos permita calcular el
eventual descuento o sobrecargo según corresponda.
Así entonces, introduciremos la siguiente fórmula en la celda "G15":
=REDONDEAR((G11+G13)*SI(C16=VERDADERO;-0,02;SI(C17=VERDADERO;0;0,015));0)
Al igual que en el caso anterior, esta fórmula comienza redondeando el
resultado final a cero decimales, luego hace un calculo previo del monto
al cual eventualmente le serán aplicados los descuentos o sobrecargos,
esto es al subtotal menos el descuento por volumen.
Posteriormente la fórmula evalúa la condición de las distintas celdas
vinculadas, para de esta forma establecer si el descuento le será
aplicable o no, o si, muy por el contrario, deberá aplicársele un recargo.
Finalmente, le daremos formato a las celdas vinculadas a los cuadros de
opciones, eligiendo un color igual al del fondo, esto para ocultar el
pequeño "truco" que hemos realizado para relacionar los cuadros de
controles con otras celdas y fórmulas.
|