Question about analyzing data (costs and profits) from business or accounting

I’m working on a Excel question and need guidance to help me study.

I have a chemical engineering question who is related to business or economics and I need some to do calculation on excel.

You can find the question attached below:

4. A proposal is set out below for a continuous process for manufacture of a fine chemical intermediate. At present this is made in a seven year old plant at the rate of 500 tons per year by an expensive batch process. Wider usage is precluded because of the high cost of the chemical which is now $ 1600/ton broken down as follows:

Raw materials………………………………….. 10 % of selling price

Energy…………………………………………….. 10 % ” ” ”

Labor and supervision…………………….. 30 % ” ” ”

Overhead…………………………………………. 10 % ” ” ”

Depreciation…………………………………….. 30 % ” ” ”

Profit………………………………………………… 10 % ” ” ”

The new process will enable the product to be made at a much lower cost which will open up a considerably larger market. For each ton made on the new plant the feedstock costs are the same; energy costs are reduced by 60 %; labor and supervision costs are reduced by 80 %; overheads stay at the same level as the old plant regardless of the capacity. The fixed capital cost is given by the relationship below:

$ Fixed Capital cost = 14300*(capacity) .5

and the market size-price relationship is given as

500 t/y ……………………….$ 1600/t

1000 t/y ……………………….$ 1540/t

1500 t/y ……………………….$ 1280/t

2000 t/y ……………………….$ 1000/t

2500 t/y ……………………….$ 720/t

3000 t/y ……………………….$ 560/t

3500 t/y ……………………….$ 440/t

4000 t/y ……………………….$ 380/t

a) Comment on viability of installing the new process.

b) What capacity do you recommend?

c) Discuss your conclusions.