if Analysis using Excel I. Cox Electric makes electronic components and has estimated the following for a new design of one of its products: Fixed Cost = $10,000; Material cost per unit = $0.15; Labor cost per unit = $0.10; Revenue per unit = $0.65 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 Cox Electric sells all that it produces, profit is calculated by subtracting the fixed cost and total variable cost from total revenue. 1. Implement the mathematical model for calculating profit in Excel. If Cox Electric makes 12,000 units of the new product, what is the resulting profit? 2. Construct a one-way data table with production volume as the colum
What if Analysis using Excel
I. Cox Electric makes electronic components and has estimated the following for a new design of one of
its products:
Fixed Cost = $10,000; Material cost per unit = $0.15; Labor cost per unit = $0.10; Revenue per unit =
$0.65
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 Cox Electric sells all that it produces, profit is
calculated by subtracting the fixed cost and total variable cost from total revenue.
1. Implement the mathematical model for calculating profit in Excel. If Cox Electric makes 12,000 units of
the new product, what is the resulting profit?
2. 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
revenue = total cost, yielding a profit of zero. Vary production volume from 0 to 100,000 in increments
of 10,000 units. In which interval of production volume does breakeven occur?
3. Construct a one-way data table the same with no 2, but production volume from 5,000 to 50,000 in
increments of 5,000 units. Is the data table show the exact breakeven point? What is the breakeven
quantity?
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
