Benefits of Using Microsoft Excel for Financial Analysis

Microsoft Excel does the algebra for you, and the charting, and the graphing and the statistical analysis. All that is required for analyzing financial data using excel is a few minutes to enter the data into s spreadsheet and a little knowledge about which excel tools can be used for financial analysis. This article will discuss some of those software tools and illustrate how they work.

Excel regression and statistical analysis

Regression analysis takes a set of dependent and independent variables such as sales figures and adjustments in marketing budget over 12 months and then calculates a deviation for each of those variables around the mean i.e. average dependent variable. As the independent variable changes i.e. marketing budget, the sales values may fluctuate up or down around the mean which may also rise or fall.

From the above information, excel can then calculate P-Values, betas and alphas can be found using excel which help determine the strength of the correlation between variables in addition to the magnitude changes in the independent variable are matched by the dependent variable. All these results can be charted, plotted and graphed using the regression function in Microsoft excel with just a few minutes of data inputs.

Other statistical analysis can be performed by adding in the Excel data analysis feature and some of the analysis this particular excel toolpack can achieve are the following:

• Covariance: Measures average deviation from the mean
• Correlation: The relationship between two variables
• Descriptive statistics: Includes a wide range of calculation that describe data
• ANOVA analysis: Analyzes and contrasts two information categories
• Ranks and Percentiles: Descriptive statistics that categorize and organize variables

Histograms, scattergrams, pie graphs and bar charts

By entering data into a spreadsheet in excel one is making possible the conversion of that data into graphical display which can lead to a potential host of other benefits. There are several types of graphical and pictorial display of data that can be accomplished using Microsoft excel including the aforementioned charts, graphs and plots. Each graphical display allows the information to be displayed in a different way such that relationships and changes to the variables over time can be observed. A few of the benefits of these excel tools are the following:

• Improves presentation
• Aids analysis of information
• Helps financial decision making
• Communicates financial ideas

Descriptive statistics

Summary statistics take numerical data and reconfigure it into analytical metrics. For example, if one has a list of weekly customer counts for an annual or 52 week time period, that data can be summarized using a number of excel tools. For example the weekly client count can be organized by rank from highest to lowest or lowest to highest. Some of the descriptive statistics made possible through Microsoft excel include the following:

• Moving average: Changes in the mean over time
• Standard error or t-test: Summary of standard deviation using square root
• Standard deviation: Used to determine range of movement of variables
• Skewness: A non-normal distribution of data after being plotted
• Range: The low and high ends of a data distribution
• Mean, median and mode: average, middle and most frequent numbers
• Count: Total value of data

The above descriptive statistics can assist in determining potential future client counts, averages, fluctuations over time and fluctuation levels. While alone descriptive statistics might not be as useful in making financial decisions as with the aid of other excel tools, they do summarize data in a form that can be analyzed more easily.

Microsoft excels data analysis tools can be applied to financial data of many types. For example, product sales, costs and expenses, price movements, taxes etc. can all be measured, plotted and analyzed using excels analysis tools. What's more, the financial data of more than one business can be compared for assistance in determining market conditions and other in house vs competitor financial data.

In summary, Microsoft excel organizes, analyzes and calculates using financial data using the software's built in metrics and features. These tools can benefit a business in a number of ways. For example, the resulting statistics may assist a business manager determine how to adjust a budget, or which clients to spend more money advertising to. Also, the financial analysis may demonstrate how much a companies worth changes over time thereby informing the manager and/or owner as to whether valuation is to volatile or stable for the business climate.

Excel also analyzes data using pictorial displays that can assist a financial analyst determine percentage distributions, proportions and relations between financial products and variables, changes in financial data over time, accuracy of the software analysis outputs and possible causes for changes in data values. In other words, Microsoft excel organizes, present and analyzes financial data in such a way that further analysis, insight may be gleaned, and decision may be made from the resulting analysis.

Source:
1. Albright, Winston and Zappe. 'Data Analysis and Decision Making with Microsoft Excel' Albany, NY. Duxberry Press.