Informática Educativa Carlosfmur@gmail.com: July 2013

Wednesday, 31 July 2013

Profitability analysis with Du Pont system

This method was developed by Du Pont de Nemours for a good number of years and tries to explain the production of the profitability of an investment based on two factors:1. - Rotation of assets.Two. - Percentage of overall profit or sales margin.The first might be the number of times that sales cover net assets and in the second the percentage of overall profit (profit after taxes + financial expenses) on sales. The concepts of investment and performance may vary from one author to another depending on the accounting items that include. Some version includes a third factor: "leveraging", especially when you want to determine the return on equity. In short, the equation that applies here as follows:•% Return on assets = Rotation X% of profit on sales•% Return = Sales / Assets X (100 x Global Profit / Sales)Many analysts believe that these two parameters (asset turnover and sales margin) are very representative in the management and control of a company or business. The variation of asset returns can be obtained by varying the overall profit margin and asset turnover varying, or by combining both. The performance must be conducted on the variables behind each parameter.The system is usually presented in a graph, which is decomposed, in sequence, the profitability of the company in the two ratios discussed, detailing the accounting variables that contribute to them. The following Excel template makes this chart from basic data balance sheet and income statement.Its implementation provides an understanding and explanation of how they get business profitability, which can be compared with others in the same industry or the same in different periods. It is interesting to see how certain activities obtained via rotations profitability compared with other base their strategy on high margins. For example, industrial companies with large assets, the asset turnover is low and therefore the yield is to be obtained with high margins, compared to Supply trade and services companies with high asset turnover and therefore, sales or profit margins lower.
http://empiezoinformatica.files.wordpress.com/2013/08/sistema_du_pont2.xlsx

Excel: restatement of financial statements

The restatement of financial statements is a very common practice in Latin American countries in order to update the accounting information incorrect due to inflation. In any accounting statement there are monetary items that are in physical monetary units so in an inflationary environment modifies its purchasing and there are other non-monetary items, its value varies more or less similar to inflation. The restatement aims to bring the states to know the actual values in the decision making process.
In Mexico, for example, there are legal rules, Bulletin B-10, which serves as the basis for its practical application in that country.
For application with Excel presents the next book, sent by LG Garcia Castro, which can be accessed from the following link.

http://empiezoinformatica.files.wordpress.com/2013/07/reexpresion_contable2.xlsx

Average period of maturation of the company

The length of the operating cycle of the company, or cyclo-goods-money money is called economic maturation period of the company. At this time raw materials are acquired, transformed, stored, sold and charged to customers. Therefore, the average period of economic maturation of the company is composed of the average period of storage of raw materials, the manufacturing, sales and customer collection.
The knowledge of this ratio is useful for the financial management of the company, especially to determine the financial ripening period, subtracting the days that finance providers and working capital calculated by the analytical method.

http://empiezoinformatica.files.wordpress.com/2013/07/periodo_medio_maduracion_empresa2.xlsx

Tuesday, 30 July 2013

Analysis of balance sheets and income statements

With this application, an analysis of balance sheets and income statements using ratios. After entering the data, up to four periods on the same sheet, are automatically calculated financial ratios, profitability and management major, and its graphical representation.
The financial analysis is to determine whether or not liquidity problems, the study of the profitability determines the evolution of the company and the return on capital invested, and management analysis related variables involved in short-term financing, obtaining rotations and collection and payment deadlines means.
The Excel workbook is intended to accountants, entrepreneurs, freelancers, etc.., Who want to get a quick and simplified its business in terms of the aspects discussed: finance, economics and management.

http://empiezoinformatica.files.wordpress.com/2013/07/analisis_debalances2.xlsx

Treasury Budget Analysis Company

Although a company is profitable liquidity can present problems because it depends on the development of collections and payments in lieu of income and expenses. Then, against the static analysis of liquidity through ratios, there is another option which is the study of dynamic collections and payments, and from them, funding requirements and anticipated cash balances.
The cash budget determines in advance the liquidity situation of the company and therefore able to anticipate possible problems and if necessary seek appropriate forms of funding.
The book Excel Input the summary budget, giving as output the excess or deficit, the financial needs and anticipated cash balances. Also analyzed the data graphically and calculated trends.

http://empiezoinformatica.files.wordpress.com/2013/07/presupuesto_tesoreria.xlsx

Power Pivot Microsoft Business Intelligence (en español)

Herramientas familiares. Capacidades mejoradas.

Poder Pivot es un mashup (un mashup es una página web o aplicación que usa y combina datos, presentaciones y funcionalidad procedentes de una o más fuentes para crear nuevos servicios) de datos de gran alcance y una herramienta de exploración de datos basado en xVelocity tecnologías en memoria que proporciona un rendimiento sin igual analítica para procesar miles de millones de filas a la velocidad del pensamiento.

(xVelocity es la familia de Microsoft de tecnologías de administración de datos optimizadas para memoria y en memoria de SQL Server 2012. El motor de análisis de memoria xVelocity y la característica de índice de almacén de columnas optimizado para memoria xVelocity son los dos primeros miembros de esta familia.) http://msdn.microsoft.com/es-es/library/hh922900.aspx

Traiga la inteligencia de negocios de autoservicio para todo el mundo:
Trabaja con más de un millón de filas de datos en cuestión de segundos
Integra los datos reutilizables de diferentes fuentes

Conecta todos con las ricas características integradas:

Compartir y colaborar con seguridad
Administrar los datos centralizados

http://www.microsoft.com/en-us/bi/powerpivot.aspx

Monday, 29 July 2013

Business Intelligence: Microsoft Power Pivot

Familiar Tools. Enhanced Capabilities.

Power Pivot is a powerful data mashup and data exploration tool based on xVelocity in-memory technologies providing unmatched analytical performance to process billions of rows at the speed of thought.

Bring self-service business intelligence to everyone:

Work with over a million rows of data in seconds

Integrate reusable data from different sources

Connect everyone with rich integrated features:

Share and collaborate securely

Manage centralized data

http://www.microsoft.com/en-us/bi/powerpivot.aspx

¿Qué es un informe de tabla dinámica?

Un informe de tabla dinámica es una forma interactiva de resumir rápidamente grandes volúmenes de datos. Use un informe de tabla dinámica para analizar detenidamente datos numéricos y responder a preguntas no esperadas sobre los datos. Un informe de tabla dinámica está especialmente diseñado para:

· Consultar grandes cantidades de datos de muchas maneras diferentes y cómodas para el usuario.

· Calcular el subtotal y agregar datos numéricos, resumir datos por categorías y subcategorías, y crear cálculos y fórmulas personalizados.

· Expandir y contraer los niveles de datos para destacar los resultados y ver los detalles de los datos de resumen de las áreas de interés.

· Mover filas a columnas y columnas a filas para ver diferentes resúmenes de los datos de origen.

· Filtrar, ordenar, agrupar y dar formato condicional a los subconjuntos de datos más útiles e interesantes para poder concentrarse en la información que le interesa.

· Presentar informes electrónicos o impresos concisos, atractivos y con comentarios.

Los informes de tabla dinámica suelen usarse cuando se desea analizar totales relacionados, especialmente cuando se tiene una larga lista de cifras para sumar ya que los datos o subtotales agregados le permiten examinar los datos desde perspectivas diferentes y comparar las cifras de datos similares. En el siguiente ejemplo de informe de tabla dinámica, puede ver fácilmente cómo se comparan las ventas totales del departamento de golf del tercer trimestre en la celda F3 con las ventas de otro deporte, o trimestre, o con las ventas totales de todos los departamentos.

1. Datos de origen; en este caso, de una hoja de cálculo

2. Valores de origen del resumen del Trim3 de Golf en el informe de tabla dinámica

3. Informe de tabla dinámica completo

4. Resumen de los valores de origen en C2 y C8 desde los datos de origen

Ejemplo de datos de origen y el informe de tabla dinámica resultante

En un informe de tabla dinámica, cada columna o campo de los datos de origen se convierte en un campo de tabla dinámica que resume varias filas de información. En el ejemplo anterior, la columna Deporte se convierte en el campo Deporte y cada registro (una colección de información sobre un campo) de Golf se resume en un solo elemento Golf.

Un campo de valor, como Suma de ventas, contiene los valores que van a resumirse. En el informe anterior, el resumen Golf Trim3 contiene la suma del valor de ventas de cada fila de los datos de origen para la que la columna Deporte contiene Golf y la columna Trimestre contiene Trim3. De forma predeterminada, los datos en el área de valores resumen los datos de origen subyacentes en el informe de gráfico dinámico de la forma siguiente: los valores numéricos usan la función SUMA para sumar valores y los valores de texto usan la función CONTAR para contar el número de valores.

Para crear un informe de tabla dinámica, debe definir el origen de datos, especificar una ubicación en el libro y organizar los campos.

http://office.microsoft.com/es-ar/excel-help/crear-o-eliminar-un-informe-de-tabla-dinamica-o-de-grafico-dinamico-HP010342375.aspx

VBA Excel un ejemplo real

Supongamos que tiene un libro que contiene listas en una gran cantidad de hojas de cálculo y que desea cambiar el nombre de cada hoja de cálculo para que coincida con el encabezado de la lista de esa hoja de cálculo. No todas las hojas de cálculo contienen una lista. Si una hoja de cálculo tiene una lista, el encabezado está en la celda B1, de lo contrario, si no tiene una lista, la celda B1 está en blanco. Los nombres de hojas de cálculo sin listas no deben modificarse.

Normalmente, esto podría tratarse de una tarea compleja que implica ver cada hoja de cálculo para saber si tiene una lista, copiar el nombre de la lista en caso de que exista, hacer clic en la ficha de hoja de cálculo y luego pegar el nuevo nombre. En lugar de realizar todos estos pasos manualmente, use VBA de Excel para cambiar el nombre de las hojas de forma automática.

Aprendizaje sobre objetos

Para resolver un problema de programación de VBA, primero debe saber qué objetos manipulará el código. Para investigar y conocer esa información, una herramienta esencial es la referencia del modelo de objetos de Excel, que forma parte de la referencia del programador de Excel 2007 en Microsoft Developer Network (MSDN).

Estos materiales de referencia se actualizarán para Excel 2010 cuando la herramienta esté disponible al público; sin embargo, la referencia del programador de Excel 2007 se adecua a la mayoría de los casos de Excel 2010.

http://msdn.microsoft.com/es-es/library/office/ee814737(v=office.14).aspx#odc_Office14_ta_GettingStartedWithVBAInExcel2010_ARealWorldExample

VBA Excel - Conceptos básicos de código

VBA Excel - Entering a Formula in a Cell

http://www.java2s.com/Code/VBA-Excel-Access-Word/Excel/EnteringaFormulainaCell.htm

'Enters the formula =SUM($G$12:$G$22) in the active cell:
Sub fomula()
ActiveCell.Formula = "=SUM($G$12:$G$22)"
End Sub

Macros 2013 - Propiedades, métodos, eventos

Sunday, 28 July 2013

Excel Formula

Formula Basics in Excel

For people just getting started with Excel, the layout can be a bit intimidating because it is essentially just a blank grid. But once you learn how to navigate the program and enter data, you'll want to start manipulating that data. Excel's numerous functions range from the basics like summing a group of cells or finding a maximum value to complex functions such as standard deviation or Bessel functions. To practice working will formulas, start with just a few cells of data and formulas that represent basic concepts with which you are already familiar.

Formula Syntax

To designate a cell as a formula, you must first type an equals sign. Each formula has its own specific syntax. For example, the "SUM" formula has a very simple syntax because the only parameter it takes is the cells you want to add. By comparison, the "ROUND" formula takes two parameters: the cell you want to round and the number of digits to which you want to round the number. When you enter a formula, Excel reminds you of the syntax as you type.

Referencing Cells

While it is possible to compute formulas using only numbers, the real power of Excel is that you can compute formulas with data from other cells. To reference a single cell, simply type the name of the cell. For example, B4 or D15. To reference a range of cells, use a colon between the first and last cell. For example, B4:D15. You can also list multiple distinct cells by separating them with commas. For example, B4,B15,E16.

Mathematical Symbols

When writing formulas, you may find it useful or necessary to include mathematical symbols. For example, if you want to round the difference of two cells, you can use the formula, "=ROUND(A4-A5,2)." The use of the subtraction sign first finds the difference of the two cells before the formula rounds the difference. Parentheses are beneficial if you need to group certain pieces of data or if you want to nest formulas.

Using Formulas

To enter a formula, you can simply start typing the name and then double-click on the match you want to use. However, if you cannot remember the formula or you aren't sure if the formula you want exists, click the "Insert Function" button on the "Formulas" tab. You can either search for a description of the function or select the category of the formula. When you find the formula you want to use, select it and click "OK." A dialog box will prompt you to enter each necessary piece of data. When you have filled out all of the fields, click "OK" and the result of your formula will

http://smallbusiness.chron.com/formula-basics-excel-26810.html