How to Use Excel to Calculate Incremental Cost & Incremental Revenue
Incremental analysis is the process of identifying relevant revenue and costs under different assumptions to make the best possible decision on how much to produce and at what price. The decision process involves choosing between alternatives based on the differences. The three major components of incremental analysis are the revenue differences (often called benefits), cost differences and cost savings difference. If one assumption produces higher incremental benefits or revenue than all others, then the right choice is to select that alternative. Using a spreadsheet program such as Microsoft Excel allows you to set up multiple incremental analysis scenarios.
Open a new Excel workbook. Create a name for your file such as "Incremental Analysis." You can perform incremental analysis in one worksheet or spread it across multiple worksheets based on the number of incremental revenue and cost assumptions.
List all relevant revenue assumptions. Relevant revenue refers to those that change under different options. Nonrelevant revenue remains the same no matter what course management decides to take. An example of nonrelevant revenue is a licensing fee.
Type the word "Revenue" in column A, cell A1. Type "Original Revenue" in cell A2, "Adjusted Revenue" in cell A3 and "Incremental Revenue" in cell A4.
Use cell B2 to create a formula for Original Revenue. For instance, last quarter your company produced 10,000 widgets that sold at $10 apiece. In the cell B2, type "= 10,000 * 10," which returns a value of $100,000 in cell B2. Alternatively, you may separate the components of revenue by placing 10,000 in cell B2 and 10 in cell C2. Create a formula in cell D2, B2 * C2 to produce revenue of $100,000. Don't forget to label the extra columns at the top, "Widgets" in cell B1 and "Price" in C1.
Use cell B3 to create a formula for Adjusted Revenue. This is where you make your different production and pricing assumptions. For example, a projection of 12,000 widgets at $9 apiece yields a revenue assumption of $108,000. As in step 4, you may create an Excel formula in cell B3 (= 12,000 * 9) or have separate columns for widgets and price in columns B3 and C3 and performing the multiplication in cell D3 (=B3 * C3).
Create a formula in cell B4 that takes the difference between Original Revenue and Adjusted Revenue to derive your Incremental Revenue. The formula looks like this: =B3-B2. In this case the incremental revenue is $8,000. If you have separate columns for widgets and price, the formula appears in cell D4 (=D3-D2).
Compare costs under both alternatives. Costs not affected by the level of production are irrelevant to incremental analysis. Eliminate these cost amounts from your calculation. You should only list costs that change that are relevant or affected by changes in production.
Separate cost into its fixed and variable components. Focus on your variable costs, because these costs are a direct function of production. Do not include fixed costs, as these are nonrelevant costs. Rent, which is a fixed cost, is nonrelevant because it does not change with the level of production. Assuming variable costs of $4 per unit, the costs under the original revenue is $40,000 (10,000 x 4) and $48,000 under the alternative revenue scenario.
Create a separate line item in your spreadsheet for the calculation of the cost variance under the two alternatives. Label the cell in A6 as "Variable Cost Savings." Type the label (48,000 - 40,000) x $4 in cell A7. Create the formula = (48,000 - 40,000) * 4 in cell B7 (or in cell D7 if you have two separate columns for production and price). The result is $16,000.
Type "Incremental Increase (Decrease) in Profit" in cell A8. Create a formula in cell B8, taking the difference between incremental revenue and incremental cost. The formula looks like this: =B4-B7. The result is a loss of $8,000 ($8,000 - $16,000).
Analyze the results. Based on the incremental analysis, increasing production from 10,000 to 12,000 but dropping the selling price from $10 to $9 produces an $8,000 increase in revenue; however, because the variable costs are high, this actually results in an $8,000 loss. Use steps 1 to 10 to create various revenue and production estimates to derive incremental revenue assumptions. Compare these to variable costs under the different scenarios. Choose the one that produces the highest incremental profit.
Tips & Warnings
- A decrease in costs returns an increase in profit, all else being equal. Add the decrease in costs to reflect an increase in profit. Conversely, an increase in costs leads to a decrease in profits. Deduct the increase from profit to reflect the incremental decrease in profit. Do not include sunk costs in your cost calculations. Sunk costs are not relevant for incremental analysis purposes. These are costs already occurred. In other words, you cannot recover sunk costs.