Cox Electric makes electronic components and has estimated the
following for a new design of one of its products.
Fixed cost = $19,000
Material cost per unit = $0.16
Labor cost per unit = $0.11
Revenue per unit = $0.67
Note that fixed cost is incurred regardless of the amount
produced. Per-unit material and labor cost together make up the
variable cost per unit. Assuming that Cox Electric sells all that
it produces, profit is calculated by subtracting the fixed cost and
total variable cost from total revenue.
Construct an appropriate spreadsheet model to find the profit
based on a given production level and use the spreadsheet model to
answer these questions.
(a)
Construct a one-way data table with production volume as the
column input and profit as the output. Breakeven occurs when profit
goes from a negative to a positive value; that is, breakeven is
when total revenue = the total cost, yielding a profit of
zero. Vary production volume from 0 to 100,000 in increments of
10,000. In which interval of production volume does breakeven
occur?
to units
(b)
Use Goal Seek to find the exact breakeven point.
Assign Set cell: equal to the location of
profit, To value: 0, and By
changing cell: equal to the location of the production
volume in your model.
units
Please answer questions thoroughly.