Customizing the Profit Volume Starter Workbook

You can use the profit volume and break-even analysis starter workbook for testing the sensitivity of your costs and profits to changes in revenues and for calculating your break-even point. However, you probably want to change the workbook so that it more closely matches your requirements. For example, if you want to test more than six volumes at one time, you can increase or decrease the number of volumes for which revenue, costs, and profits are calculated. You can change the text describing the revenue, costs, and profits, or you can remove those cost categories unnecessary to your profit volume and break-even analysis.You can define minimums and maximums for specific costs and then include these minimums and maximums in your profit volume analysis.

Changing the Number of Volumes Tested

You can rather easily increase or decrease the number of volumes tested in the profit vol- ume analysis workbook. For example, to increase the number of volumes for which you test revenue, costs, and profits, follow these steps:

• Remove the right border from the last column of the Profit Volume Forecast.
• Copy the current last column to the right into as many additional columns as there are additional volumes for which you want to test revenue, costs, and profits.
• Remove the right border from the last column of the Profit Volume Forecast.

To decrease the number of volumes in the Profit Volume Forecast, follow these steps:

• Clear any unneeded columns from the right side of the forecast.
• Redefine the reference name Increments so that it equals the one number less than the number of different unit volumes you now show in your new Profit Volume Forecast. (For example, with six unit volumes in the schedule, Increments is set to 5.)

Removing Forecasts from the Starter Workbook

You can remove the Break-Even Analysis Forecast, the Common Size Profit Volume Fore- cast, or both the Profit Volume Forecast and the Common Size Profit Volume Forecast. (The Common Size Profit Volume Forecast uses information in the Profit Volume Forecast, so if you remove the Profit Volume Forecast, also remove the Common Size Profit Volume Forecast.) To remove any of these forecasts from the starter workbook, simply clear the forecast you want to remove.

Adding Minimums and Maximums to the Profit Volume Forecast

In your business, you might need to keep certain expenses below or above certain amounts. If so, you can, for example, specify that those costs the starter workbook calculates as a percentage of profits not become positive if the expenses should be expressed as negatives. To set a minimum expense as 0, edit the formula in the first volume column in the Profit Volume Forecast so that it checks for a minimum, as follows:

=MIN(“old formula”, “minimum amount”)

in which the old formula is the formula currently in the cell and the minimum amount is the dollar amount shown as 0, or a negative value, which you don’t want the calculated re- sult to fall below. For example, you could set the State Income Tax formula to never fall below 0 by editing the formula currently in cell B45 to read:

=MIN(-B42*State_Income_Tax,0)

Notice that to keep an expense amount from falling below a certain floor value you use a MIN function because the starter workbook calculated expenses as negative amounts. To set a maximum amount, use a MAX function, with the maximum amount specified as 0, or a negative value in the formula:

=MAX(“old formula”,”maximum amount”)