Required:

Based on the above spreadsheet, find or calculate the NPV, IRR, BCR, Payback Period, and Discounted Payback Period where applicable.

B D E F G Year 0 $ $ $ $ $ $ $ A 28 29 PRO1: 30 31 Budgeted sales amount (units) 32 Selling Price ($ per unit) 33 Sales (S) 34 Variable Cost 35 Fixed Cost 36 Automantion Consultancy Fee 37 Depreciation 38 Net Profit 39 Tax paid (30%) 40 Profit after tax 41 (+) Add back depreciation being non-cash flow 42 Other relevant cost: 43 Opportunity cost from existing facility 44 Working Capital Invested (W1) 45 Outlay: 46 Cost of Plant 47 Net Cash Flows 48 Discounting factor (13%) 49 Discounted Cash Flow 50 Cumulative PV cash flow 51 Discounted Net Cash Flow 52 Year 1 300 $ 50,000 $ 15,000,000S 9,000,000 $ 1,800,000 $ 30,000 $ 1,180,000 $ 3,050,000 $ 915,000 $ 2,135,000 $ 1,180,000 $ Year 2 275$ 50,000 $ 13,750,000 $ 8,250,000 $ 1,800,000 $ 30,000 $ 1,180,000 $ 2,550,000 $ 765,000 $ 1,785,000 $ 1,180,000 $ Year 3 250 $ 50,000 $ 12,500,000 S 7,500,000 $ 1,800,000 $ 30,000 $ 1,180,000 $ 2,050,000 $ 615,000 $ 1,435,000 $ 1,180,000 $ Year 4 225$ 50,000 $ 11,250,000 S 6,750,000 $ 1,800,000 $ 30,000 $ 1,180,000 $ 1,550,000 $ 465,000 $ 1,085,000 $ 1,180,000 $ Year 5 200 50,000 10,000,000 6,000,000 1,800,000 30,000 1,180,000 1,050,000 315,000 735,000 1,180,000 S $ $ $ $ 10,000 $ 10,000 $ 10,000 $ 10,000 $ 10,000 $ 400,000 $ S 3,305,000 $ 0.88 6,600,000 7,000,000 $ 1.00 -7,000,000 $ -7,000,000 $ 2,461,364 positive 2,955,000 s 0.78 2,314,198 $ -1,761,023 $ 2,605,000 $ 0.69 1,805,396 $ 44,373 2,255,000S 0.61 1,383,034 $ 1,905,000 0.54 1,033,958 $ 2,924,779 $ -4,075,221 $ $ S