Computación básica para alumnos secundaria Carlos Fernández Muriano
Search This Blog
Friday, 9 August 2013
OLAP Cube
In database theory, an OLAP cube is an abstract representation of the projection of a relation of an RDBMS (System Administrator relational databases). Given a relation of order N, we consider the possibility of a projection that has the X, Y, Z as the key to the relationship and attribute W as residual. Categorizing this as a function must be:
W: (X, Y, Z) → W
The attributes X, Y, Z axes correspond to the hub, while the W value returned by each triplet (X, Y, Z) corresponds to the data element to be filled in each cell of the cube.
Thursday, 8 August 2013
VBA Macros demystified
Macros demystified: what they are and what they are used
What is a macro?
A macro is a set of commands that can be applied with a single click. They can automate almost any task that can be performed in the program you are using and even let you perform tasks did not think possible.
Are macros a type of programming?
Macros are programming, but need not be a programmer or programming skills to use them. Most macros that can be created in the Office programs are written in a language called Microsoft Visual Basic for Applications, known as VBA. VBA macros are discussed in this article.
What is a macro?
A macro is a set of commands that can be applied with a single click. They can automate almost any task that can be performed in the program you are using and even let you perform tasks did not think possible.
Are macros a type of programming?
Macros are programming, but need not be a programmer or programming skills to use them. Most macros that can be created in the Office programs are written in a language called Microsoft Visual Basic for Applications, known as VBA.
Macros save time and expand the possibilities of the programs you use every day. You can use macros to automate tasks repetitive document production, to streamline cumbersome tasks or to create solutions to automate the creation of documents that you and your colleagues use regularly. Users who are familiar with VBA macros can be used to create custom plug-ins or templates including dialog boxes, or even record information for use in different occasions.
http://office.microsoft.com
Excel: Cash Flow Statements
The cash flow statement (CFS), and the state of source and application of funds (SSA) are separate documents of the annual accounts.
The SSA analyzes the sources of funding and implementation, while the ERA tells us about the origin and use of cash movements through variations in the various activities that affect the company's treasury. Therefore helps to assess the ability of the company to generate cash and its chances of success, survival or failure.
The following Excel application calculates the cash flow for a given year, breaking it down into:
• Operating activities.
• Investing activities.
• Financing activities
see Excel sheet:
Tuesday, 6 August 2013
Excel: Decomposition of financial profitability - ROE (return on equity)
The financial profitability ratio ROE (return on equity) measures the net profit generated by the investments made by the owners of the company. (ROE = Net Profit% B / C.Propios). It is therefore very useful for the partners or shareholders because they can assess and compare their investments with other investment options.
In order to obtain a greater degree of analysis can decompose the financial return on several factors (see formulas in the attached image, click to enlarge), which will be useful for the managers of the company, because they will act on the variables important, or the combination thereof, in order to obtain maximum profitability.
On one side are the ratios that depend on economic management:
• Asset Turnover (Sales / Assets)
• Margin (EBIT / Sales)
And secondly, financial management ratios:
• Financial leverage. (Active / C.Propios x EBT / EBIT)
• Tax effect (Net profit / BAI)
The value of these ratios depend on the financial structure and management of the company itself, but also the sector to which it belongs. For example, industrial companies with large assets, the rotation of the same will be low, so that the origin of your benefit will be the margin and the other variables. In service companies, with small active volume, turnover is high and therefore the profit will come through this route.
The Excel application calculates the four components from the pooled data of balance and income statement.
Monday, 5 August 2013
Excel: Analysis of profitability
The study of the economic productivity of the company is carried out by determining the results achieved in the various activities carried out, but they may have different interpretations, depending on the variable that is related: active, net assets, sales, etc. .
In Balance Analysis application are considered the following:
- Financial return: ie profits plus interest, in relation to the total resources used.
- Return on equity (ROE)
- Overall profitability: the benefit is related to total resources.
- Share Capital.
- Profit on sales.
- Sales margin.
The results are expressed in percentages and are calculated for four periods or different exercises, with the aim of making comparisons and the progress of the company in the short term.
see Excel sheet:
http://empiezoinformatica.files.wordpress.com/2013/08/analisis_de_balances-12.xlsx
Sunday, 4 August 2013
Financial Analysis of balances
The financial analysis of balance sheets by ratios is to determine the ability of the company to meet its expenses and obligations to their respective maturities.
To do this, first, the elements are grouped based on their Active smaller and easier to become money and Liabilities by major and minor degree of enforceability. For example: Non-current assets, inventories, Achievable, Available in the Active tale. And Equity, Non-current liabilities and current liabilities with respect to the liability.
Then relate the elements, resulting in ratios or ratios that evaluate the level of liquidity, solvency, and balance when compared to standard values, or between different periods of the company. The number of ratios to be used may be wider or narrower depending on what you want to deepen the analysis.
In the following Excel application have included five ratios: Cash, Liquidity, Independence, Debt and Stability, aimed at evaluating the ability to pay from lowest to highest term. Also calculated maneuver Fund indispensable in financial analysis, because its positive value is crucial to the stability of the company and as collateral for long-term continued growth.
On the sheet are included guideline values as mean ratios of the data published by central balance sheet and recommendations of several authors, but the analyst must assess also the ratios obtained depending on the particular circumstances of the company and the sector in which it operates.
Finally we performed comparative graphs of the ratios for the four periods analyzed in order to obtain an intuitive and dynamics of the situation.
see sheet:
http://empiezoinformatica.files.wordpress.com/2013/08/analisis_de_balances21.xlsx
Excel: Balance sheet analysis and results
Accounting as a system of information processing reaches its full potential in the analysis of balance sheet and income statement, because it allows a number of techniques used to diagnose the economic situation of the company and from it to make good decisions by both the internal address as by external stakeholders affected by the progress of the company.
Balance analysis can be performed from multiple points of view, for example: Legal, fiscal, labor, property, financial, economic, trade, economy, etc.. And can be used numerous techniques: Comparison, percentages, ratios, index numbers, etc..
The Excel application following is an update and adaptation to Spanish accounting system on one previously published in this blog. Executed application are introduced to the pooled data of the balance sheet and income statement data sheet, resulting in other leaves, a simplified analysis, because it uses a small number of ratios, financial position, profitability, and management of the company. This is done using techniques percentages, ratios and their graphic representation, with the possibility to compare four periods simultánemente.
http://empiezoinformatica.files.wordpress.com/2013/08/analisis_de_balances2.xlsx
Saturday, 3 August 2013
Excel: Operating leverage
Once the company has exceeded breakeven (UR), each increase in sales generates more profit increased until it reaches a point where the increase is similar to beneficial sales. This is because non-current costs, or charges of structure, they lose weight in the income statement. Keep in mind that we are assuming that sales can increase indefinitely with the same costs currents.
This situation is called operating leverage and can be synthesized in the AO ratio = (% increase in profits) / (% sales increase). The AO, taking values greater than one, immediately after passing the UR and approaches one for a high level of sales, so discussed above.
The Excel application calculates the UR from a certain amount of non-current costs and current cost%, and simulates the percentage of increase of benefits from a continuous percentage increase in sales. With these percentages obtained AO ratio (operating leverage), which is also plotted.
see the Excel spreadsheet:
Friday, 2 August 2013
Excel: deadlock or breakeven
The deadlock or performance hurdle is the level of production and income for which the benefit is zero. At this level the company makes losses and, from it, generate profits. Put another way: the point where the level of activity covers all loads, which is also equivalent to the margin covers fixed costs. An estimated UR = CF / (P-VC), where FC = fixed costs P = price per unit, and CV = unit variable cost.
The following Excel application calculates breakeven and simulates the behavior of the following variables for each level of production:
• Units.
• Fixed costs.
• Variable costs.
• Total costs.
• Average costs.
• Income.
• Benefits.
They also do a graphical representation of revenue, fixed costs, variable and total for each level of production, where we can see the UR, when crossing total revenue and costs.
http://empiezoinformatica.files.wordpress.com/2013/08/punto_muerto2.xlsx
Apalancamiento financiero
El
apalancamiento financiero estudia la financiación con recursos propios frente a
con deuda (activo-recursos propios) por una parte y, el efecto del coste de la
deuda en el beneficio ordinario, por otra. El primer ratio se obtiene de
ACTIVO/R.PROPIOS y el segundo de BAI/BAII. Donde BAI=Beneficio antes de
impuestos, y BAII=Beneficio antes de impuestos e intereses. El producto de los
dos ratios indicados es el apalancamiento financiero que puede presentar
los siguientes resultados:
·
mayor que 1, la deuda aumenta la rentabilidad y por
tanto es recomendable.
·
menor que 1, la deuda disminuye la rentabilidad y
no es conveniente.
·
igual a 1, la deuda no tiene efecto sobre la
rentabilidad.
Como puede verse en la descomposición de la
rentabilidad detallada en cuadro superior, el beneficio de la empresa antes de
impuestos es igual al beneficio financiero de todos los activos=recursos de la
empresa, multiplicado por el apalancamiento financiero. Por tanto, éste depende
del beneficio antes de intereses e impuestos (BAII), de coste de la deuda y del
volumen total de la misma.
Conviene aclarar que el apalancamiento financiero solo mide el efecto sobre la rentabilidad financiera, sin tener en cuenta el volumen adecuado de deuda, en cuanto a la capacidad para su devolución.
La aplicación Excel siguiente obtiene la descomposición de la rentabilidad del BAI/ACTIVO en rentabilidad (BAII/ACTIVO por APALANCAMIENTO FINANCIERO), para tres fechas de balance determinadas.
Conviene aclarar que el apalancamiento financiero solo mide el efecto sobre la rentabilidad financiera, sin tener en cuenta el volumen adecuado de deuda, en cuanto a la capacidad para su devolución.
La aplicación Excel siguiente obtiene la descomposición de la rentabilidad del BAI/ACTIVO en rentabilidad (BAII/ACTIVO por APALANCAMIENTO FINANCIERO), para tres fechas de balance determinadas.
Thursday, 1 August 2013
Minimum required working capital
The working capital is the current assets financed by long-term. This item, also called working capital, working capital or revolving fund that determines the financial capacity of the company to operate in the long term. It can be calculated by subtracting basic form long resources (own and others) the value of fixed assets, or by the difference between current assets and current liabilities. His overall value must be positive.
But there are other calculation methods that provide more information about their formation and composition. This is the analytical method of aggregating the needs of average investments funds for storage, manufacture, sales, billing and finance treasury least half of suppliers; useful to know the minimum amount of working capital necessary for the proper functioning of the company and proper management of it acting on its components. The details of implementation can be seen in the following Excel application:
Statement of source and application of funds
The State of Origin and Uses of Funds, explains how they have changed the accounts that form the assets and liabilities, for a period of time determined by two balance sheet. Try to show what have been the causes which have led to an increase or decrease in working capital, indicating its variations, origins and applications.
The state also reveal the quantitative variations, provides the information necessary to investigate the causes that influenced the economic and financial structure of the company.
The following Excel application allows groups to develop a EOAF account after
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.
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.
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.
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.
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
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
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
¿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 (campo: en un informe de
tabla dinámica o de gráfico dinámico, categoría de datos que se deriva de un
campo de los datos de origen. Los informes de tabla dinámica tienen campos de
fila, columna, página y datos. Los informes de gráfico dinámico tienen campos
de serie, categoría, página y datos.) 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 (elemento: subcategoría de un campo en informes de tabla
dinámica y de gráfico dinámico. Por ejemplo, el campo "Mes" podría
tener los elementos "Enero", "Febrero", etc.)
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.
|
Subscribe to:
Posts (Atom)



















