Applying Break-Even Problems by Using the Goal Seek Feature in Excel
For each application, use the Goal Seek feature in Excel in your solution. Identify the variables
and equations in a text box. Conclude with a summary statement. Your work for each problem
in the tabs should look similar to the following example.
Example: A service company provides employees for hire to companies in need of temporary
secretaries for a period of time. This service company has a fixed weekly cost of $896. This
service company pays the hired secretaries wages and benefits that amount to $7.65 per
hour. The firm that requires and employs a secretary pays the temporary service company
$10.40 per hour, so the temporary service earns a $2.75 per hour profit for placing the
secretary in this temporary position. How many hours per week of secretarial service must
the service company place in order to break even?
For this example,
h = the number of weekly hours worked by the temporary secretaries
R(h) = 10.40h <--Revenue function
C(h) = 7.65h + 896 <--Cost function
P(h) = Revenue - Costs <--Profit function
Break-Even Point is when revenue = costs OR when Profit = 0
Independent variable (h) Revenue, R(h) Cost, C(h) Profit, P(h)
325.8181818 $ 3,388.51 $ 3,388.51 $ -
Therefore, when 326 hours of labor are provided each week, the temporary service will
break even. More than this many hours per week will yield a profit for the temporary service.
, Problem 1: An computer shop sells computers. The shop has fixed costs of $1500 per week.
Its average cost per computer is $649 and the average selling price is $899. How many
computers must the store sell each week in order to break even?
Variable: c = the number of computers sold each week
Revenue Function: R(c) = 899c
Cost Function: C(c) = 649c
Profit Function: P(c) = R(c) - C(c)
Independent variable, c Revenue, R(c) Cost, C(c) Profit, P(c)
6 $ 5,394.00 $ 5,394.00 $ -
Summary: Therefore, the shop must sell 6 computers per week in order to break even. Selling more
than 6 computers per week would yield a profit.
For each application, use the Goal Seek feature in Excel in your solution. Identify the variables
and equations in a text box. Conclude with a summary statement. Your work for each problem
in the tabs should look similar to the following example.
Example: A service company provides employees for hire to companies in need of temporary
secretaries for a period of time. This service company has a fixed weekly cost of $896. This
service company pays the hired secretaries wages and benefits that amount to $7.65 per
hour. The firm that requires and employs a secretary pays the temporary service company
$10.40 per hour, so the temporary service earns a $2.75 per hour profit for placing the
secretary in this temporary position. How many hours per week of secretarial service must
the service company place in order to break even?
For this example,
h = the number of weekly hours worked by the temporary secretaries
R(h) = 10.40h <--Revenue function
C(h) = 7.65h + 896 <--Cost function
P(h) = Revenue - Costs <--Profit function
Break-Even Point is when revenue = costs OR when Profit = 0
Independent variable (h) Revenue, R(h) Cost, C(h) Profit, P(h)
325.8181818 $ 3,388.51 $ 3,388.51 $ -
Therefore, when 326 hours of labor are provided each week, the temporary service will
break even. More than this many hours per week will yield a profit for the temporary service.
, Problem 1: An computer shop sells computers. The shop has fixed costs of $1500 per week.
Its average cost per computer is $649 and the average selling price is $899. How many
computers must the store sell each week in order to break even?
Variable: c = the number of computers sold each week
Revenue Function: R(c) = 899c
Cost Function: C(c) = 649c
Profit Function: P(c) = R(c) - C(c)
Independent variable, c Revenue, R(c) Cost, C(c) Profit, P(c)
6 $ 5,394.00 $ 5,394.00 $ -
Summary: Therefore, the shop must sell 6 computers per week in order to break even. Selling more
than 6 computers per week would yield a profit.
