Analyzing the Cost-Volume-Profit Relationship using Excel

Please note that this is not a tutorial on using Excel but only a recommendation on using this feature.

I have worked in many companies who are unsure what a reasonable price for the product is. Some companies just set these rates without doing any research at all. One company who did absolutely no cost study on this. As a result after a short period of time they ended closing up shop because they were losing $20 per item and didn't realize it because they didn't perform What If analyses.

This company should have before they started making the product should have used one of the most powerful tools in Excel. They should have followed the below steps.

1. Comparing Types of Expenses-using the Cost-volume-Profit (CVP) to express the relationship between company's expenses, its volume of business, and resulting profit or net income.

CVP analysis is an important business decision-making tool because it predicts the effect of cutting overhead or raising prices on net income.

2. Another feature is to determine what the break-even point of the product.

3. After completing step 1 and 2, you can start performing What-If-Analysis and goal seek of the product.

An example would be what effect on net income if the sales declined 25k units.

Goal seek takes the opposite approach by specifying a value for the calculated item and then returning the input value needed to reach that goal.

The below completed example is from a recent project to determine the CVP using various production and price schedules.

If only more companies used this tool they can avoid making BAD business decisions. Some companies perform these tasks the old fashion way! One scenario at a time! This is very time consuming.


So I highly recommend that you go to the library or bookstore and get you a copy of advanced excel techniques. After learning this you will be WAY ahead of the average excel user.