Collect Inputs to Use with a QuickBooks Profit-Volume-Cost Analysis Workbook
A Profit-Volume-Cost Analysis workbook enables you to estimate profits at a variety of sales revenue volumes, to estimate break-even points, and to chart break-even and profit-volume-cost data. The figure shows the worksheet range in which you enter the raw data required for the profit-volume-cost analysis. The workbook collects more data points than you may expect.
Most of this information, however, is simply a slightly more granular approach to collecting the three basic inputs that any profit-volume-cost analysis depends on: sales revenue estimates, gross margin percentage, and fixed costs.
You don’t need to be an Excel expert to use this Profit-Volume-Cost Analysis workbook. However, you do need to know how to start and stop Excel and how to enter values into worksheet cells.
To use the Profit-Volume-Cost Analysis workbook, follow these steps:
Open the Profit-Volume-Cost Analysis workbook.
Access this example of a Profit-Volume-Cost Analysis workbook.
When your browser asks whether you want to open the file or save the file, indicate that you want to save the file to your hard drive.
Open the file by starting Microsoft Excel and choosing Office→Open.
When Excel displays the Open dialog box, open the folder with the pvc.xls file and then double-click the file.
Describe the sales revenue that you want to test.
To do this, you must provide three pieces of information: the unit sales price, the low unit sales volume, and the high unit sales volume.
Enter the unit sales price into cell B4. For example, if you sell an item that costs $1,500, you enter $1,500 into cell B4. Use the low unit volume tested and high unit volume tested inputs shown in cells B5 and B6 (respectively) to identify the range of sales volumes that you want to test.
The low unit volume tested value multiplied by the unit sales price equals the lowest revenue volume that the worksheet tests. The high unit volume tested multiplied by the unit sales price amount equals the largest sales revenue volume tested.
The three revenue inputs that you collect and input into cells B4, B5, and B6 tell the workbook which sales revenue volumes you want to analyze.
Describe the variable costs.
When you do real-life profit-volume-cost analysis, you find that your variable costs fall into one of two categories: variable costs that can be expressed as an amount per unit and variable costs that can be expressed as a percentage of the sales price. The worksheet range B10:B17 collects the information needed to describe these sorts of variable costs.
The first set of variable costs, vary-with-unit costs, goes into cells B10, B11, B12, and B13. For example, any direct labor costs associated with the item that you’re selling go into cell B10. Direct material costs go into B11. Factory overhead costs, which are variable and based on units sold, go into cell B12. If you have any other vary-with-unit costs, you enter the amount per unit for these costs into cell B13.
The example vary-with-unit cost values shown mean that for each unit sold, the assumption is that the business pays $75 per unit in direct labor, $40 per unit in direct materials, $400 per unit in factory overhead, and another $40 per unit for other vary-with-unit costs. If you add up these amounts, you see that vary-with-unit costs equal $555 per unit sold.
In addition to vary-with-unit costs, firms often pay variable costs that are best expressed as a percentage of revenue. In the Profit-Volume-Cost Analysis workbook, the worksheet range B15:B17 supplies space to describe and record these variable costs, vary-with-revenue costs.
The workbook, for example, shows a 5 percent sales commission in cell B15. The workbook shows an 8 percent sales tax in cell B16. And just to provide a catch-all category for other vary-with-revenue costs, the workbook includes another vary-with-revenue costs value in cell B17.
In the case of the sample data set shown, for example, vary-with-revenue costs equal 14.5 percent of sales.
Record your fixed costs.
To record or estimate your fixed costs, enter the fixed costs amount into cell B19. Fixed costs show as $150,000.
Estimate any variable costs that vary with profits.
Here’s one other wrinkle that you may often encounter with real-life profit-volume-cost analysis: variable costs that don’t vary from changes in sales revenue but from changes in profits. For example, some businesses have profit-sharing plans. Those profits represent variable costs because they vary with changes in sales revenue — sort of.
In order to estimate the profit-sharing costs, you really need to first calculate profits and then apply the profit-sharing percentage to those profits. Income taxes — federal, state, and local income taxes — also fall into the category of variable costs that vary not with changes in sales revenue, but with changes in profit.
In the workbook shown, three cells — B22, B23, and B24 — let you recognize these variable costs, vary-with-profit costs. You can enter the state income tax percentage into cell B22. You can enter the federal income tax percentage into cell B23. And if you have any other vary-with-profit costs, you can enter the sum of these costs as a percentage into cell B24.
Vary-with-profit costs are tricky to estimate precisely. The trickiness stems from a couple of factors:
Many vary-with-profit costs aren’t simply calculated as percentages of profit. The cost calculations are considerably more complicated. Income tax costs, for example, don’t use a single percentage. They often use a schedule of progressive percentages.
Vary-with-profit costs often interrelate. For example, state income taxes affect federal income taxes. Other vary-with-profit costs may interrelate, too. For example, a profit-sharing percentage may be applied on an after-tax basis. The workbook formulas don’t explicitly recognize any interrelationship between these variables. Therefore, in order to precisely model these vary-with-profit costs, you must enter percentages that have been fiddled with a bit.
After you collect the needed inputs for the Profit-Volume-Cost Analysis workbook, the workbook estimates a break-even point and prepares a profit volume forecast. The workbook also supplies a couple of useful charts that graphically show the break-even and the profit-volume analysis data.