Question

Question 2 (50%) The management of a company is considering replacing a number of old looms in the mill's weave room. The looms to be replaced are two 220-cm President looms, sixteen 135-cm President looms, and twenty-two 185-cm Draper X-P2 looms. The company may either replace the old looms with new ones of the same kind or buy 21 new shutterless Pignone looms. The first alternative requires the purchase of 40 new President and Draper looms and the scrapping of the old looms. The second alternative involves scrapping the 40 old looms, relocating 12 Picanol looms, and constructing a concrete floor, plus purchasing the 21 Pignone looms and various related equipment Following are the revenue and expense predictions: Description Machinery/related equipment Annual revenues due to the new looms Annual labour cost Annual O&M cost CCA Rate Project life Salvage value Alternative 1 $2,000,000 $2,100,000 $190,000 $1,050,000 33% 4 years $110,000 Alternative 2 $1,000,000 $1,630,000 $380,000 $750,000 33% 4 years $65,000

Do part a).

Answer 1

For sensitivity analysis, we first calculate the NPW for the base case, i.e. 0% deviation in each variable. The formulas are entered in such a way that we can alter the values of each input one at a time, keeping other inputs fixed. Please refer to the below screenshot & its formula view given next to understand this:

А в 20,00,000 21,00,000 1,90,000 10,50,000 Deviations from base case 0% 0% 0% Machinery & eqpmnt cost Annual revenues from looms Annual labour cost Annual O&M cost CCA Rate Project life (years) Salvage value Tax Rate (T) MARR 1,10,000 40% 12% Plot Area year Machinery & eqpmnt cost $ -20,00,000.0 Annual revenues from looms Annual labour cost Annual O&M cost Depriciation (=Remaining undepriciated value x 33%) $ $ $ 21,00,000.0 $ 21,00,000.0 $ 1,90,000.0 $ 1,90,000.0 $ 10,50,000.0 $ 10,50,000.0 $ 21,00,000.0 1,90,000.0 10,50,000.0 $ 21,00,000.0 $ 1,90,000.0 $ 10,50,000.0 $ 6,60,000.0 $ 4,42,200.0 $ 2,96,274.0 $ 1,98,503.6 Profit before tax (=Revenue- costs & deprcn.) $ 2,00,000.0 $ 4,17,800.0 $ 5,63,726.0 $ 6,61,496.4 Profit after tax (=PBT x(1-T)) PAT + depriciation $ $ 1,20,000.0 $ 7,80,000.0 $ 2,50,680.0 $ 6,92,880.0 $ 3,38,235.6 $ 6,34,509.6 $ 3,96,897.9 5,95,401.4 Terminal year salvage value After Tax Salvage value (Sx (1-T)) Net free cash flows $ $ $ 1,10,000.0 66,000.0 6,61,401.4 $ -20,00,000.0 $ 7,80,000.0 $ 6,92,880.0 $ 6,34,509.6 NPW $ 1,20,752.2

Formula view:

н Deviations from base case 1 Machinery & eqpmnt cost 2 Annual revenues from looms 3 Annual labour cost 4 Annual O&M cost 5 CCA Rate 6 Project life (years) 7 Salvage value 8 Tax Rate (T) 9 MARR 2000000 2100000 190000 1050000 0.33 110000 0.4 0.12 year 12 13 Machinery & eqpmnt cost -2000000 14 15 Annual revenues from looms 16 Annual labour cost 17 Annual O&M cost Depriciation (=Remaining 18 undepriciated value x 33%) =$F$2*(1+$H$2) =(-$F$3*(1+$H$3))*-1 =(-$F$4*(1+$H$4))*-1 =$F$2*(1+$H$2) =(-$F$3*(1+$H$3))*-1 =(-$F$4*(1+SH$4))*-1 =$F$2*(1+$H$2) =(-$F$3*(1+SH$3))*-1 =(-$F$4*(1+SH$4))*-1 =$F$2*(1+$H$2) =-$F$3*(1+$H$3))*-1 =(-$F$4*(1+SH$4))*-1 =2000000*(1-33%)^E11*33% =2000000*(1-33%)^F11*33% =2000000*(1-33%)^G11*33% =2000000*(1-33%)^H11*33% Profit before tax (=Revenue-costs & deprcn.) 19 20 Profit after tax (=PBT x(1-T)) 21 PAT + depriciation =F15-F16-F17-F18 =F19*(1-40%) =F20+F18 =G15-G16-G17-G18 =G19*(1-40%) =G20+G18 =H15-H16-H17-H18 =H19*(1-40%) =H20+H18 =115-116-117-118 =119*(1-40%) =120+118 22 23 Terminal year salvage value 24 After Tax Salvage value (S X (1-T)) 25 Net free cash flows 110000 =123*(1-40%) =113+121+124 =E13+E21 =F13+F21 =G13+G21 =H13+H21 27 NPW =NPV(12%,F25:125)+E25

Once we have this, we alter the values of each input as per given range & get the NPW values for 3 data points each to plot the graphs (since it is a linear relationship, we don't need more than 2 or 3 points to plot the graphs)

Revenue devitation NPW -25% -836012.8 0% 120752.2 25% 1077517.3 labour cost -30% 224629.6 0% 120752.2 30% 16874.9 NPW O&M cost deviation -10% 0% 10% NPW 312105.2 120752.2 -70600.8

NPW vs Revenue deviation NPW vs Labour cost deviation 1500000.0 250000.0 1077517.3 224629.6 1000000.0 200000.0 150000.0 500000.0 120752.2 120752.2 NPV 100000.0 NPV 0.0 16874.9 -25% 0% 25% 50000.0 -500000.0 -836012.8 0.0 + -30% 0% -1000000.0 30% % deviation in Revenue % deviation in labour cost NPW vs O&M cost deviation 312105.2 350000.0 300000.0 250000.0 200000.0 150000.0 100000.0 50000.0 0.0 -50000.0 -100000.0 120752.2 NPV 0% 10% -70600.8 % deviation in O&M cost