Question

Profitability Analysis
(Reference: Peters, Timmerhaus, West in Plant Design and Economics for Chemical Engineers 5^th Ed.)

A process, projected to have a total depreciable fixed capital investment of $100 million, with no allocated cost for off-site utilities, is to be installed over a 3-yr period (2018-2020). Just prior to start-up, $30 million of working capital is required. At 100% production capacity (projected for the third and subsequent operating years), sales revenues are projected to be $130 million/ yr. and the total annual production cost, excluding depreciation, is to be $ 90 million/yr. Also, the plant is subjected to operate at 50% and 75% of full annual capacity during the first and second operating years. Thus, during those years, revenues are anticipated to be 50% and 75% of the sales revenues projected in the third and subsequent years, respectively. Operating expenses in Y1 and Y2 are 75% of that in Y3.

Using the straight-line method to calculate for depreciation.
Perform a 10 year-period analysis and compute for the ROI and PBP.

Dear Prof., if you are going to use a spreadsheet, please provide the necessary formula and complete solution.

Answer 1

Revised
Year,Y	2020/Y=0	1	2	3	4	5	6	7	8	9	10
1.Initial Investment	-100
2.Working capital	-30										30
3.Production capacity		0.5	0.75	1	1	1	1	1	1	1	1
4.Revenues		=D38*F39	=E38*F39	130	130	130	130	130	130	130	130
5.Production cost		=75%*F40	=D40	-90	-90	-90	-90	-90	-90	-90	-90
6.St.line depn.		-10	-10	-10	-10	-10	-10	-10	-10	-10	-10
7.EBIT( sum 4 to 7)		=SUM(D39:D41)	=SUM(E39:E41)	=SUM(F39:F41)	=SUM(G39:G41)	=SUM(H39:H41)	=SUM(I39:I41)	=SUM(J39:J41)	=SUM(K39:K41)	=SUM(L39:L41)	=SUM(M39:M41)
8.Total CFs(1+2+7)	=C36+C37+C42	=D36+D37+D42	=E36+E37+E42	=F36+F37+F42	=G36+G37+G42	=H36+H37+H42	=I36+I37+I42	=J36+J37+J42	=K36+K37+K42	=L36+L37+L42	=M36+M37+M42
ROI=EBIT/Initial Inv.
ie. EBIT/130		=D42/130	=E42/130	=F42/130	=G42/130	=H42/130	=I42/130	=J42/130	=K42/130	=L42/130	=M42/130
Av.ROI=Sum ROI/10	=SUM(D45:M45)/10
Pay-Back Period
9. Cum.CFs(of row 8)	=C43	=C48+D43	=D48+E43	=E48+F43	=F48+G43	=G48+H43	=H48+I43	=I48+J43	=J48+K43	=K48+L43	=L48+M43
10.PBP=6+(2.5/30)=
=6+(2.5/30)
Yrs.

Revised
Year,Y	2020/Y=0	1	2	3	4	5	6	7	8	9	10
1.Initial Investment	-100
2.Working capital	-30										30
3.Production capacity		50%	75%	100%	100%	100%	100%	100%	100%	100%	100%
4.Revenues		65	97.5	130	130	130	130	130	130	130	130
5.Production cost		-67.5	-67.5	-90	-90	-90	-90	-90	-90	-90	-90
6.St.line depn.		-10	-10	-10	-10	-10	-10	-10	-10	-10	-10
7.EBIT( sum 4 to 7)		-12.5	20	30	30	30	30	30	30	30	30
8.Total CFs(1+2+7)	-130	-12.5	20	30	30	30	30	30	30	30	60
ROI=EBIT/Initial Inv.
ie. EBIT/130		-9.62%	15.38%	23.08%	23.08%	23.08%	23.08%	23.08%	23.08%	23.08%	23.08%
Av.ROI=Sum ROI/10	19.04%
Pay-Back Period
9. Cum.CFs(of row 8)	-130	-142.5	-122.5	-92.5	-62.5	-32.5	-2.5	27.5	57.5	87.5	147.5
10.PBP=6+(2.5/30)=
6.08
Yrs.