Chapter 1 Section 6

Excel Companion Chapter 1 section 6

Open the file NonlinBE.xls.

Finding a break-even quantity is equivalent to finding a quantity for which profit is zero. In standard function notation, P(x) = R(x) - C(x). Whether or not the cost and revenue functions are linear or non-linear, the break-even quantity is the solution of the equation P(x) = 0. Graphically this solution corresponds to one or more points where the profit function crosses the horizontal axis. Enter the formulas = 0.1*x^.5 + 0.5*x + 400 in B11, =2.25*x in C11, and =-0.1*x^.5 + 1.75*x - 400 in D11. Press the macro buttons to obtain updated tables and graphs.

NonlinBE.xls - graphs

Suppose we want to determine what value(s) of the independent variable corresponds to a profit of zero. (If necessary, refer back to the previous section on goal seeking.) Our strategy will be to drive the target cell (profit) to zero by adjusting the cell that contains the initial guess for the break-even point. The target cell must contain an Excel formula as a function of the independent variable. The cell containing the initial guess must contain a numeric value. Verify that break-even occurs at approximately x = 229.

QUESTIONS:

For questions 1 – 6 that follow, assume that: we have cost and revenue functions of x defined as :
	and where cost, revenue, and profit are in thousands of dollars.
1.	Find the profit if 30 units are made and sold.
2.	Find the first break-even quantity (nearest unit).
3.	Find the first break-even dollars of sales.
4.	Determine the profit at a production level of 80 units.
5.	What is the second break-even point (nearest unit)?
6.	To the nearest whole number, for what value of x is profit at a maximum?

Enter your name here:

first name last name

Return to Table of Contents

Return to Chapter 1 Index