¿Qué puntuación le otorgas a este Blog?

Estimado amigos,
han pasado ya mis primeros cien días como blogger, y no quería dejar pasar por alto este hecho. Me parece necesario hacer un ejercicio de reflexión sobre las cosas realizadas, y conocer a través de vuestros comentarios, o al menos contestando la encuesta desplegada a tal efecto, cuál es vuestra impresión; me gustaría conocer si las explicaciones son suficientemente claras, si son muy espesas, si no logran explicar el fin de cada ejercicio, si hay pocas observaciones, si hay demasiadas imágenes, etc.
Realmente cualquier puntualización que creáis conveniente hacer para mejorar la línea de trabajo que me he marcado.
Por supuesto, es obvio decirlo, se admiten todo tipo de sugerencias sobre cualquier aspecto relacionado con nuestro blog.
Un saludo y muchas gracias a todos...

Seguir leyendo...

Las restricciones en Solver.

Hemos hablado en alguna ocasión de la herramienta Solver, y quizá hayamos mencionado la enorme potencia que Excel le ha otorgado. Recordemos que su forma de trabajar se basa en un método iterativo, de prueba y error, hasta encontrar una solución que cumpla tanto un problema principal como todas aquellas restricciones dadas.
Como ejemplo propondremos la optimización de sencillo problema matemático. ¿Cuál es el área máxima de un paralelogramo conociendo el perímetro de éste?.
Es decir, debemos maximizar el área sabiendo que tenemos un perímetro de, por ejemplo, 4 unidades. Por facilitar la interpretación supondremos que hablamos de un rectángulo o un cuadrado.
Tenemos la siguiente figura:

Sabemos que el perímetro se define como la suma de los lados, es decir
P = 2·(a + b)
Por tanto nuestra función a maximizar será:
A = b· a
sujeto a
2·(a + b) = 4
debiendo maximizar A (el área de nuestra figura).
Recuerdo, vagamente, de mi tiempos universitarios algunas formas de solucionar y encontrar el resultado óptimo a esta pregunta (mediante el cálculo diferencial, o con la programación lineal - método simplex- etc). Nosotros, sin embargo, aprovecharemos la herramienta de Excel Solver.
Para ello, en primer lugar, nombramos dos celdas una como 'base' y otra como 'altura', son nuestras variables a determinar, y las que nos devolverán una área máxima para nuestro paralelogramo.
Asignamos también el nombre 'area' a la celda C9, formulada como 'base' por 'altura'.
Debemos recordar lo explicado en el post Asignar nombres.
Quizá lo más importante de este planteamiento sea tener formulado el perímetro como
= 2 · (base + altura), lo que hemos hecho en la celda C11.
Tenemos por tanto:

Aplicamos Solver, desde Excel 2007 dirigiéndonos al menú Datos > Análisis > Solver, y en la ventana de la herramienta seleccionamos como celda objetivo el nombre 'area', y como celdas cambiantes las celdas 'base' y 'altura'; sin olvidar lo más importante en este caso, y es agregar la restricción del perímetro celda C11 sea igual a 4.

haz click en la imagen

Podemos observar lo que todos sospechabamos, que el paralelogramo que hace máxima su área para un perímetro de 4 unidades es un cuadrado perfecto de lado 1 unidad. Solver nos devuelve un valor 1 para nuestras variavles -celdas cambiantes- de uno.

Seguir leyendo...

Comparativa entre interés simple y compuesto.

Visualizaremos cuáles son las diferencias entre aplicar un Interés simple y un Interés compuesto a un Monto inicial de 10.000 eur con un tipo de interés inicial del 100% anual (un interés tan alto para que se distinga perfectamente en el gráfico).
Aplicaremos distintos métodos de cálculo, ya sean con funciones financieras de Excel (siempre que sea posible) o con fórmulas financieras.
Recordemos en primer lugar cuáles son las reglas financieras de ambos cálculos:

Utilizaremos las siguientes funciones financieras de Excel:
=PAGO(tasa; periodo; valor)
=VF(tasa; periodo; pago ; valor)
=TASA.NOMINAL(tasa efectiva; número periodos en el año)
Debemos saber que los intereses en capitalización simple son constantes, y que se calculan multiplicando el tipo de interés por el capital inicial. Por otro lado los intereses en capitalización compuesta son crecientes; se calculan multiplicando el tipo de interés por el capital del periodo anterior.
Tras aplicar las distintas fórmulas o funciones a lo largo de un año, obtenemos los siguientes resultados:

El gráfico donde observaremos mejor la diferencia de aplicar un Interés u otro, sale de seleccionar como rango de datos las columnas D7:D19 y G7:G19; vemos como la gráfica para el Interés compuesto tiene forma exponencial creciente, al contrario que la del Interés simple, que es una línea recta. En cualquier caso debe coincidir para cada final de periodo, en este caso, a 31/12.

Adjunto fichero para su correcta interpretación.

Comp Int simple-In...

Hosted by eSnips

Seguir leyendo...

Determinante de una Matriz en Excel.

Una función de uso simple, pero transcendental para el cálculo matricial, es el determinante.
Como en la mayoría de las ocasiones es básico conocer qué tenemos entre manos para poder aplicar correctamente nuestras funciones de Excel. En este caso se hace necesario un conocimiento acertado de lo que significa este 'determinante' de una Matriz, y de los distintos métodos de alcanzar (el más conocido quizá sea la Regla de Sarrus) este valor numérico, también conocido como módulo de la matriz.
La regla de cálculo sería:

Conociendo, entonces, la fórmula matemática para el cálculo de un determinante de una matriz, podremos comprobar como Excel nos devuelve correctamente ese valor.
Calcularemos el determinante de la matriz:

Empleando la regla de Sarrus para una matriz de 3x3 obtendríamos:

y aplicando la función de Excel =MDETERM(matriz) sobre nuestro rango B3:D5 tendríamos:

Seguir leyendo...

Insertar una serie en Excel.

Veremos hoy una forma de completar series de valores de manera automática, mediante el Rellenado de series. Es la misma herramienta tanto para Excel 2003 como para Excel 2007, si bien la navegación por las Barras de menús para acceder a esta utilidad difieren (no mucho, eso si).
En la versión Excel 2003, accedemos desde el Menú Edición > Rellenar > Series

accediendo entonces a la ventana diálogo de la utilidad

Para acceder en la versión Excel 2007 iremos al Menú Inicio > Opción Modificar > Rellenar > Series, desde el que se abrirá la misma ventana diálogo que en la versión Excel 2003.

Desde esta ventana configuraremos las distintas posibilidades de nuestra Serie.
En primer lugar seleccionaremos si deseamos que nuestra serie se proyecte en horizontal (en filas) o en vertical (en columnas). En segundo lugar qué tipo de serie es, esto es, si es de crecimiento lineal (aritmética), geométrica (producto de los elementos), cronológica (temporal) o autorrellenado. En caso de que sea una Serie Temporal o cronológica se habilitará las unidades de tiempo que permitan configurarla.
El elemento a configurar más importante sin duda es el Incremento, ya que nos determinará los distintos valores de nuestra serie. Otro elemento configurable es el límite, o último valor de nuestra serie.
Notar que si el tipo de serie se ha definido como lineal el incremento se agrega al primer valor de inicio y, a continuación, se agrega a cada uno de los valores siguientes; si fuera geométrica el primer valor de inicio se multiplica por el incremento; el resultado y todos los resultados posteriores se multiplican por el incremento.
Propondremos un ejemplo. Y siempre como punto de partida una celda con un valor previamente editado.
Desde la celda B2, al que hemos dado un valor 10, proyectaremos una serie de valores, incrementándolo de 3 en 3 de forma lineal o aritmética, hasta alcanzar un valor máximo de 150 (siempre sin exceder este límite).

tras Aceptar obtendríamos la siguiente serie:

haz click en la imagen

Seguir leyendo...

Creación de una lista personalizada en Excel 2007.

Hemos visto como crear una Lista personalizada en Excel 2003; veremos ahora dónde activar estas Listas personalizadas en Excel 2007.
Desde el Botón de Office, pulsando Opciones de Excel accedemos a:

lo que nos lleva a la ventana diálogo siguiente:

que es la misma ventana, con las mismas posibilidades y manera de trabajar que en la versión Excel 2003.

Seguir leyendo...

Creación de una Lista personalizada en Excel 2003.

Ocurre en cocasiones que necesitamos emplear repetidamente en nuestros trabajos un mismo listado de nombres, por ejemplo, nuestros departamentos, o nuestras delegaciones, o nuestros mismos productos, etc.
El caso es que se trata de una misma lista personalizada a nuestra necesidad. Hablamos normalmente de listados no excesivamente extensos.
En estos casos es cuando se hace útil definir una Lista personalizada en nuestro Excel para disponer de ella cuando lo deseemos.
Desde el menú Herramientas > Opciones activaremos la pestaña Listas personalizadas, donde veremos ya creadas por defecto algunas listas (meses del año, días de la semana, etc), lo importante es que vemos en uno de los cuadros la opción de NUEVA LISTA, que seleccionaremos para generar nuestro listado. En la ventana de Entradas de lista añadiremos en el orden que posteriormente deseemos que aparezca el listado separadas por comas; también podremos agregar este listado para importarlo desde algún rango de nuestro libro o hoja de cálculo.

Tras agregar, ya podremos disponer en ella en nuestras hojas de cálculo. Excel reconocerá automáticamente cualquiera de los elementos que componen la Lista para continuarla.
En nuestro ejemplo hemos determinado la lista: www, excelforo, blogspot, com (es decir, cuatro elementos), escribiendo alguno de ellos en cualquier celda, y arrastrandola con el ratón, Excel completará y continuará con el listado; en cualquier dirección.

Seguir leyendo...

Botón con macro en Excel 2007.

Aprendimos en la entrada Creación de macros cuáles eran los pasos a seguir para crear una macro, usando el 'asistente'. Hoy haremos lo mismo, pero desde Excel 2007, para después asignar la macro creada a un Botón generado por nosotros.
En primer lugar deberemos extraer el Menú Programador, donde se encuentran las herramientas que necesitaremos, ejercitando la opción el Botón de Office en Excel 2007. Buscaremos el botón Opciones de Excel y tras pulsarlo, y dentro de las opciones principales para trabajar con Excel marcaremos Mostrar ficha de programador en la 'Cinta de opciones'.

Hecho esto, podemos generar una macro sencilla empleando el asistente para Grabar macro. Por ejemplo, crearemos una macro que introduzca un texto, y luego de color a la fuente y al fondo de la celda.
Posteriormente, también desde el menú Programador, desde Insertar Controles de formulario, elegiremos el control Botón y automáticamente nos desplegará el listado de macros existentes para asociar uno de ellos a este botón.
Veámoslo en un video.

La utilidad de este uso y otros de los Controles de Formulario lo veremos en una entrada posterior.

Seguir leyendo...

Cálculo matricial en Excel.

Trabajaremos en esta ocasión con Matrices, dándole una visión matemática al asunto. Pretendemos en esta ocasión solucionar un sistema de ecuaciones planteado:
3x + 2y + 1z = 100
1x + 2y + 5z = 150
6x + 0y + 1z = 200
Conocemos distintos métodos para la resolución de estos sistemas de ecuaciones, pero nosotros nos quedaremos con la resolución matricial. Por tanto, en primer lugar convertiremos nuestro problema a forma de matrices:

Seguro que recordamos que para resolver este sistema necesitmaos hallar la matriz inversa de los parámetros de las ecuaciones, para después multiplicarla matricialmente por el de las constantes.
Para ello, en primer lugar calculamos la Matriz inversa, utilizando la función MINVERSA
=MINVERSA(matriz)
es muy importante recordar que al trabajar con matrices hay que ejecutar en forma matricial (pulsando, después de seleccionar el rango esperado, Ctrl+Mayus+Enter).

Una vez obtenida esta matriz inversa, podremos proceder a su multiplicación por nuestra matriz de constantes (recordando las propiedades de las matrices), en este caso, deberemos multiplicar la matriz inversa obtenida por la izquierda a la matriz de constantes.
Emplearemos la función de producto de matrices
=MMULT(matriz1; matriz2)

Lo más importante es ejecutar las funciones en forma matricial!!!.
Podemos comprobar que los resultados de las incognitas obtenidos son correctos, sustituyéndolos en el sistema de ecuaciones.

Seguir leyendo...

Gráficos 3D en Excel.

Expondremos hoy una caso sencillo de gráfico en 3D.
Tenemos un listado de zonas comerciales de una gran empresa con sus datos de facturación del primer trimestre. Esto nos bastará para realizar un sencillo gráfico en 3D, por ejemplo del tipo circular seccionado.
Marcamos el rango con los datos, presionamos insertar gráfico, y ya desde el asistente buscamos el tipo de gráfico adecuado, en concreto uno en 3D.

Podemos dar finalizar y obviar los pasos siguientes del Asistente para gráficos, ya que la herramienta 3D la podemos ejecutar con el gráfico ya creado.
Una vez generado el gráfico, pulsando el botón derecho del ratón sobre el gráfico, extraemos el menú contextual del gráfico

seleccionando la utilidad de Vista en 3D:

ventana de trabajo desde la cual podremos girar sobre los ejes ficticios del gráfico hacia cualquier sentido, con el fin de visualizar lo mejor posible el gráfico, bien manualmente pulsando los botones a tal efecto, o bien de forma numérica si necesitamos mayor precisión.

Seguir leyendo...

Ejemplo de Tabla de datos-Excel 2007.

Desarrollaremos un análisis de sensibilidad sobre dos variables mediante la herramienta, ya conocida, de la Tabla de datos; en esta ocasión emplearemos la versión Excel 2007.
Suponemos una inversión con una tasa de rentabilidad del 2,25% y los siguientes flujos:

en la situación actual el Valor actual de la operación es 1.038,14 eur
=VNA.NO.PER(C5;B3:G3;B2:G2)
con los siguientes argumentos
=VNA.NO.PER(tasa; valores; fechas)
Lo que nos interesa analizar distintos aspectos de la inversión según varíen dos valores, por ejemplo,la tasa de rentabilidad y el importe del último cobro. Como dato especial mencionar que al ser fecha no periódicas hemos empleado la función de VNA.NO.PER que tiene en cuenta esa circunstancia.
Para ejecutar esta herramienta desde Excel 2007 nos deberemos dirigir al Menú Datos > Herramientas de datos > Análisis Y si > Tabla de datos:

Marcaremos por tanto el rango donde pretendemos analizar esta variación de resultados, sabiendo que previamente hemos vinculado la celda superior izquierda del rango a seleccionar con el resultado que pretendemos estudiar, en nuestro caso, con el Valor actualizado (=VNA.NO.PER). Ver entrada con ejemplo de Tabla de datos.
Seleccionaremos, en la ventana diálogo de Tabla de datos, como Celda de entrada(fila) la celda C5, i.e., donde está la rentabilidad sobre la que se calcula el Valor actual; y como Celda de entrada (columna) la celda G3, que refleja el último cobro de la inversión.

El resultado final obtenido será:

La interpretación de los datos era muy obvia desde el planteamiento, a mayor rentabilidad y mayor fuera el último cobro, mayor es el valor actual de la operación.
Podríamos haber cruzado los datos del último cobro con el del primer desembolso... lo haremos en una entrada futura del blog.

Seguir leyendo...

Ejemplo de una provisión en el NPGC 2007.

Explicaremos otra de las Normas de Registro y Valoración del NPGC 2007, la Norma 15ª sobre Provisiones y contingencias.
El NPGC considera provisiones a toda clase de pasivos no financieros con el objetivo de cubrir gastos, pérdidas o deudas que estén claramente especificados en cuanto a su naturaleza, pero que resultan indeterminados en cuanto a su importe o a su fecha de cancelación. Es decir, no son cuentas correctoras, son fondos de ahorro de la empresa.
Pero ¿cómo se valoran?, y sobre todo ¿cómo me ayudará Excel a calcular esta valoración?.
De acuerdo con la información disponible en cada momento, las provisiones se valorarán en la fecha de cierre del ejercicio, por el valor actual de la mejor estimación posible del importe necesario para cancelar o transferir a un tercero la obligación, registrándose los ajustes que surjan por la actualización de la provisión como un gasto financiero conforme se vayan devengando.
Serán las funciones VNA y PAGO de Excel las que me ayuden a calcular o valorar el importe de la Provisión.
Veamos un ejemplo. Una sociedad ha adquirido el 31/12/2008 una Concesión administrativa sobre un terreno para explotar sus recursos madereros, momento en el que se compromete a reparar y replantar las hectáreas arboladas existentes más un 20%. El coste de esa replantación se ha establecido en 1.000.000 euros anuales durante cinco años, a pagar cada 31/12. El tipo de interés aplicable a la operación se ha estimado en el 4% anual.
Para el cálculo del importe de la provisión por rehabilitación de los terrenos planteamos en primer lugar la operación financiera, para después determinar el cuadro de la vida de la explotación.

El cuadro financiero de la operación sería entonces:

La interpretación de esta operación financiera radica en los pagos anuales de 1.000.000,00 eur durante los cinco años pactados, una vez obtenido el valor descontado de esos flujos a un tipo de interés del 4%, mediante la función
=VNA(4%;G3:G7)
tan sólo debemos ir actualizando periodo a periodo, minorándolo cada 31/12 con el pago estipulado; como vemos en el gráfico adjunto. Empleamos para la actualización año a año la función PAGO
=PAGO(tasa 4%; 1 año; valor anterior de la provisión)
La realidad es que el NPGC 2007 trata de igual forma las provisiones que cualquier otra operación con algún instrumento financiero; la base del NPGC es el descuento/actualización de valores.
Adjuntamos el fichero para mejor análisis.

Provisiones.xls

Hosted by eSnips

Los primeros asientos contables que registraríamos serán:

P.D.: Por supuesto, lo expuesto será correcto salvo mejor interpretación de experto en la materia.

Seguir leyendo...

Un formato condicional con Fórmula.

Estamos acostumbrados a emplear la herramienta Formato condicional de una forma directa, es decir, empleando sus condiciones directamente sobre el valor de la celda a 'formatear'; sin embargo las posibilidades que nos ofrece la opción 'Fórmula' están sólo limitadas por nuestra imaginación.
Un ejemplo sencillo de este uso sería dar un formato especial a los días 'Sábado' y 'Domingo' de nuestro mes. Vamos a obviar otras funciones que nos determinen el día de la semana de cada fecha, simplemente escribiremos los días del mes debajo de los de la semana; y sobre estos días de septiembre de 2009 aplicaremos nuestro Formato condicional.
Nuestro mes será:

Seleccionamos el rango determinado por los días del mes B3:H7 y aplicamos el siguiente Formato condicional:

y ya disponemos de nuestro mes con los fines de semana con un formato espefícico.
Podemos realizar combinaciones de distintas fuciones o fórmulas con esta opción del Formato condicional. Todo dependerá de nuestra necesidad de marcar una celda.
Las posibilidades de esta herramienta en Excel 2007 son incluso mayores, ya que nos permitirá aplicar a las celdas con Formato condicional varias utilidades gráficas.

Seguir leyendo...