Software

Analyze future profits against variable costs with Excel's data table feature

Extrapolate with Excel! By using Excel, you can calculate current profits as well as potential profits based on the variables of your choice. Mary Ann Richardson shows how you can use Excel to help you make predictive calculations.

You know that if you sell your company's product for $45 you can sell 45,000 units. After some market research, you determine that if you drop the price by 30% to $31.50, you could sell 10 to 50% more units. Before investing in more manufacturing capacity, you want to know if you could make more money by selling more for less. You can use Excel's data table feature to perform this analysis.

First, determine the profit you could earn at the current price of $45 and a variable cost of $5 by following these steps:

  1. Open a blank worksheet.
  2. In cell C1, enter Price.
  3. In cell D1, enter 45.
  4. In cell C2, enter Demand.
  5. In cell D2, enter 45,000.
  6. In cell C3, enter Variable Cost.
  7. In cell D3, enter =D2*5.
  8. In cell C4, enter Revenue.
  9. In cell D4, enter =D1*D2.
  10. In cell C5, enter Profit.
  11. In cell D5, enter =D4-D3.

With a variable cost of $5, the total profit at a price of $45 is $1,800,000. If you enter 31.50 in cell D1, the profit drops to $1,192,500. But, you already know that you can sell up to 50% more units at a 30% discount. Follow these steps to find out how much more profit you will make:

  1. In cell E7, enter Profit.
  2. In cell F7, enter Revenue.
  3. In cell G7, enter Variable Cost.
  4. In cell C8, enter Demand.
  5. In cell E8, enter =D5.
  6. In cell F8, enter =D4.
  7. In cell G8, enter =D3.
  8. Enter 49,500 in D9; 54,000 in D10; 58,500 in D11; 63,000 in D12; and 67,500 in D13.
  9. Select D8:G13.
  10. Go to Data | Table.
  11. Enter D2 in the Column Input Cell box and click OK.

The table shows that even if you sell 50% more units, you make $1,788,750, which is less than you would make for selling 45,000 units at the original price of $45.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

1 comments
deepvibha
deepvibha

I have entered data exactly as instructed. I am not getting the figure of $1,788,750 for the sold quantity of 67500 units Price 45 Demand 45000 Variable Cost 225000 Revenue 2025000 Profit 1800000 Profit Revenue Variable Cost Demand 1800000 2025000 225000 49500 1980000 2227500 247500 54000 2160000 2430000 270000 58500 2340000 2632500 292500 67500 2700000 3037500 337500