Question

In: Finance

Cox Electric makes electronic components and has estimated the following for a new design of one...

Cox Electric makes electronic components and has estimated the following for a new design of one of its products:

Fixed Cost = $15,000

Material cost per unit = $0.15

Labor cost per unit = $0.10

Revenue per unit = $0.65

Production Volume = 12,000

Per-unit material and labor cost together make up the variable cost per unit. Assuming that Cox Electric sells all it produces, build a spreadsheet model that calculates the profit by subtracting the fixed cost and total variable cost from total revenue, and answer the following 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 = total cost, yielding a profit of zero. Vary production volume from 5,000 to 50,000 in increments of 5,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.

Solutions

Expert Solution

Revenue Volume Fixed Cost VC1 VC2 Revenue per unit
24375 37500 15000 0.15 0.1 0.65
Prod vol Profit
5000 -13000
10000 -11000 Profit 0
15000 -9000
20000 -7000
25000 -5000
30000 -3000
35000 -1000
40000 1000
45000 3000
50000 5000

The solution to the problem is attached above.

In this we first enter all the given data as shown in the top most table. In this table the first item is the revenue which is calculated by multiplying revenue per unit and volume.

The second table contains the volume of units in the first column and the second table contains the profit, which is calculated by subtracting fixed cost and varible cost per unit * volume from the total revenue generated.

we see that 35000 to 40000 is the range of breakeven.

we find this by using goal seek, we set a cell next to profit with the formula for calculating profit and then run the goal seek to change the volume figure in the first table. and we get the answer as 37500.

jeff jeffy answered 1 year ago
Next > < Previous

Related Solutions

Cox Electric makes electronic components and has estimated the following for a new design of one...
Cox Electric makes electronic components and has estimated the following for a new design of one of its products: Fixed Cost = $3,000 Material cost per unit = $0.15 Labor cost per unit = $0.10 Revenue per unit = $0.65 Production Volume = 12,000 Per-unit material and labor cost together make up the variable cost per unit. Assuming that Cox Electric sells all it produces, build a spreadsheet model that calculates the profit by subtracting the fixed cost and total...
General Blast Inc. makes plastic components for toy vehicles and has estimated the following for a...
General Blast Inc. makes plastic components for toy vehicles and has estimated the following for a new design of one of its truck models. Fixed cost = $42,000 Variable cost per unit = $2.20 General Blast Inc. is considering outsourcing its production to one of its subcontractors for $3.73 per unit. The company anticipates that the demand for the new product would be 20,000 units and would like to compare the costs of manufacturing the new plastic component in-house with...
Equipment for immersion cooling of electronic components has an installed value of $182,000 with an estimated...
Equipment for immersion cooling of electronic components has an installed value of $182,000 with an estimated salvage value of $40,000 after 15 years. For years 2 and 10, use DDB book depreciation to determine: (a) the depreciation charge, and (b) the book value. I want an explanation for the steps
The Gatson manufacturing company has estimated the following components for a new product. Fixed cost =...
The Gatson manufacturing company has estimated the following components for a new product. Fixed cost = $50,000 Material cost per unit = $2.15 Labor cost per unit = $2.00 Revenue per unit = $7.50 Round your answer to the nearest whole number. 1. Using a spreadsheet model, what will be the resulting profit if the company decides to make 70,000 units of the new product? 2. Construct a one-way data table with production volume as the column input and profit...
The Gatson manufacturing company has estimated the following components for a new product. Fixed cost =...
The Gatson manufacturing company has estimated the following components for a new product. Fixed cost = $50,000 Material cost per unit = $2.15 Labor cost per unit = $2.00 Revenue per unit = $7.50 Round your answer to the nearest whole number. 1. Using a spreadsheet model, what will be the resulting profit if the company decides to make 70,000 units of the new product? 2. Construct a one-way data table with production volume as the column input and profit...
A manufacturer of electronic components acquires a cutting machine (CM) for $400,000. It is estimated that...
A manufacturer of electronic components acquires a cutting machine (CM) for $400,000. It is estimated that the CM will reduce operating costs by $128,000 annually since it is capable of cutting through high- thickness green ceramic ware. It will be depreciated using the MACRS method, with a recovery period of five years. The project period is six years; it is estimated that the CM will have a market value equal to 10% of its purchase price at the end of...
A manufacturer of electronic components acquires a cutting machine (CM) for $400,000. It is estimated that...
A manufacturer of electronic components acquires a cutting machine (CM) for $400,000. It is estimated that the CM will reduce operating costs by $128,000 annually, since it is capable of cutting through high-thickness green ceramic ware. It will be depreciated using the MACRS method, with a recovery period of five years. The project period is six years; it is estimated that the CM will have a market value equal to 10% of its purchase price at the end of the...
A manufacturer of electronic components acquires a cutting machine (CM) for $400,000. It is estimated that...
A manufacturer of electronic components acquires a cutting machine (CM) for $400,000. It is estimated that the CM will reduce operating costs by $128,000 annually, since it is capable of cutting through high-thickness green ceramic ware. It will be depreciated using the MACRS method, with a recovery period of five years. The project period is six years; it is estimated that the CM will have a market value equal to 10% of its purchase price at the end of the...
A random sample of 300 electronic components manufactured by a new machine is tested and 9...
A random sample of 300 electronic components manufactured by a new machine is tested and 9 are found to be defective. The parts using the old machine have a true defective proportion of four percent. Does this sample provide evidence that the true defective proportion for the new machine is less than the old machine’s at the 5% significance level? Justify using a hypothesis test! Be sure to state you Ho and Ha, your test statistic and p-value, your decision...
Krishna Electronics Pvt. Ltd. is a new venture that manufactures electronic components. The company produced the...
Krishna Electronics Pvt. Ltd. is a new venture that manufactures electronic components. The company produced the following income statement results in its first year of operations: Net Sales Rs. 10,00,000 Cost of Goods Sold - Rs. 6,50,000 Gross Profit Rs. 3,50,000 General and Administrative Expenses - Rs. 2,40,000 Marketing Expenses - Rs. 80,000 Depreciation - Rs. 45,000 EBIT - Rs. 15,000 Interest Expenses - Rs. 35,000 Earnings Before Taxes - Rs. 50,000 Taxes Rs. 0 Net Earnings (Loss) - Rs....
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT