Home /Business Improvement and Performance Analysis With Microsoft Excel

Business Improvement and Performance Analysis With Microsoft Excel

“Without continual growth and progress, such words as improvement, achievement, and success have no meaning.” (Benjamin Franklin)  Every business has to grow.  If an organization is not growing it is likely the opposite is true detraction and decline.   One of the first steps in improving a business is to analyze performance.  After the analysis stage, business behaviors can be changed to encourage the actions needed to improve an organization.  The following are a few techniques which can be used with the assistance of Microsoft Excel to measure and improve a business.


 Flux Analysis

A flux analysis is the investigation of a change in a company’s financial statement(s).  The analysis focuses on changes in income and\or expenses from month-to-month or year to year.  A basic flux analysis in can take on the form of a Microsoft Excel table.   For example let’s say a small business owner would like to analyze the change in a company’s profit and loss statement from one year to the next.  In order to do this a spreadsheet could be setup showing the categories in one column (i.e. revenue and expenses) followed by values for year 1 in the first column and year 2 in the second column.  The third column would then take the difference or delta between these two year end numbers.  Any differences from month to month or year to year are than scrutinized.  Investigation into the details would be needed to determine the reasons for large changes.  A few basic questions are to be asked after the analysis is prepared.  What are the reasons for the flux or substantial changes? Did my customer base change? Were there changes in a few vendor contracts? Did I prepay some expenses etc?  In summary why did this change happen?    This analysis gives great clarity into the items that can affect a business’s performance.


Trend analysis

Trend analysis is a technique that visualizes the movement of data points (i.e. profits, expenses, revenue, clients etc.) over the course of time.  A trend analysis differs from a flux analysis in that it measures performance over a longer period of time (years) and displays information in a pictorial way.  Charts such as line graphs or sparklines can be used to depict the various trends which impact a company’s bottom line.


 Ratio analysis

A ratio is one number that analyzes specific areas of a company’s financial health.  Ratios can be reviewed independently, compared to prior years or bench-marked against industry counterparts.  There are many financial ratios from which to choose each of which tells a different story about the institution.  For example there are ratios that measure liquidity, growth, debt vs. income, contribution margin and net margin, break-even point just to name a few.  Of all these measures there are three ratios I think every business should have a good handle. Those measures are contribution margin, net margin and break-even point.        



Net Margin

Net margin is a measure that describes how much of your net revenues are converted into net profit.  This ratio gives you a percentage of how much of your revenues have been converted into profits.  Many institutions have margins at 10% or below.  This means for every hundred dollars they earn they keep ten dollars.  The ratio is computed by dividing a business net profit by net revenues. 


Contribution Margin

Contribution margin is a little different from net margin in that it measures the margin of each product or service delivered.  For example let’s say a small business sells different products.  Each product has a different sale price and production costs.  This means that one product would be more profitable to sell than others.  The contribution margin measures each products marginal contribution to the bottom line.  For example product A is being sold at $100 with a cost of $50, the contribution margin is 50%.  Product B is being sold at $80 with a production cost of $30, the contribution margin is 62%.  What this means is the product B even is actually contributing more the company’s bottom line, even though it is priced lower.  This analysis can help business owners focus on products or services that yield greater return.


Break-Even Point

The break-even point is the point at which revenue meets expenses.  The break-even point tells you how many products need to be sold in order to cover all variable and fixed costs.  A break even point is computed by dividing per unit sales price by the total expenses.  For example, let’s a business has annual costs of $50,000 with only one product for sale at $50.  The business would have to sell 1,000 items to “break-even” and cover the cost of $50,000.  This measure is very helpful in assessing the potential profitability of a start-up or seasoned institution.


In order to improve an organization it must be analyzed and measured.  After this stage strategic goals, peer groups analysis and targets can be set.  In short many of these improvement exercises can be performed in a Do-It-Yourself (DIY) fashion in Microsoft Excel. 


About the author



Othniel Denis is currently Principal of Excellent Ones Consulting LLC. He brings with him a background in finance, training, and information management systems. Prior to opening his own firm, Othniel spent the last 13 years in finance as an Analyst at organizations like New York University, Nassau County Government and Brookhaven National Laboratory to name a few...

Read More

Comments (0)

New Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Latest Tweet