Break-even points and data tables


Posted by lauralei on October 28, 2001 2:30 PM

I am studing an intermediate excel course and I have come across a word problem that is confusing. Can anyone figure out how to put this onto paper (worksheet)? Here is the Project:

You can calculate the break-even point (number of units you must sell) if you know the fixed expenses, the price per unit, and the expense (cost) per unit. Create a data table that analyzez the break-even point for prices between $5.00 and $10.00 in increments of $0.20. The following formula determines the break-even point:
Break-Even Point= Fixed Expenes/(Price per unit - Expense per unit)
Assume Fixed Expenses = $10,000,000; Price per unit= $5.69; and Expense per unit $2.45.
Enter the data and formula into a worksheet and then create the data table. Use the Price per Unit as the input cell and the break-even value as the result. For a unit cost of $8.40, the data table should show a break-even point of 1,680,672.



Posted by Juan Pablo on October 29, 2001 6:22 AM

In A1 put your assumed fixed costs: 10000000
In A1 put your expense per unit: 2.45

Now, in A4 put
In A5 put =A4+.2 and drag till A29
In B4 put =$A$1 / (A4-$A$2) and drag till B29

Juan Pablo