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

by Mary Ann Richardson | Mar 06, 2007 8:00:00 AM

Tags: Microsoft Excel, Mary Ann Richardson, cell, Microsoft Office Suite Excel Tips Newsletter

Takeaway: 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.

Print/View all Posts Comments on this article

Unable to get the $1,788,750 figuredeepvibha@...  | 10/25/07