Question

Citco Company is considering investing up to $590,000 in a sustainability enhancing project. Its managers have narrowed their choices to three potential projects • Project A would redesign the production process to recycle raw materials waste back into the production cycle, saving on direct materials costs and reducing the amount of waste sent to the landfill Project B would remodel an office building, utilizing solar panels and natural materials to create a more energy efficient and healthy work environment. • Project C would build a new training center in an underserved community, providing jobs and economic security for the local community . Required: 1. Assuming the cost of capital is 10%, complete the table below by computing the payback period, NPV, Profitability Index, and Internal Rate of Return. (Future Value of $1. Present Value of $1. Future Value Annuity of $1. Present Value Annuity of $1.) (Use appropriate foctor(s) from the tables provided. Do not round intermediate calculations. Negative amounts should be indicated by a minus sign. Round your "NP" answers to the nearest whole dollar amounts. Round your "pl" and "IRR" answers to 2 decimal places.)

Answer 1

Req1 Year Year Year 0 0 0 1 1 1 2 2 Project A Cumulative Cash Cash Flows Flows $ -5,90,000 $ -5,90,000 $ 1,18,000 $ -4,72,000 $ 1,18,000 $ -3,54,000 $ 1,18,000 $ -2,36,000 $ 1,18,000 $ -1,18,000 $ 1,18,000 $ $ 1,18,000 $ 1,18,000 $ 1,18,000 $ 2,36,000 $ 2,03,000 $ 4,39,000 Project C Cumulative Cash Cash Flows Flows $ -4,28,000 $ -4,28,000 $ 1,07,000 $ -3,21,000 $ 1,07,000 $ -2,14,000 $ 1,07,000 $ -1,07,000 $ 1,07,000 $ $ 1,07,000 $ 1,07,000 $ 1,44,000 $ 2,51,000 2 3 3 Project B Cumulative Cash Cash Flows Flows $ -6,40,000 $ -6,40,000 $ 80,000 $ -5,60,000 $ 80,000 $ -4,80,000 $ 80,000 $ -4,00,000 $ 80,000 $ -3,20,000 $ 80,000 $ -2,40,000 $ 80,000 $ -1,60,000 $ 80,000 $ -80,000 $ 80,000 $ $ 80,000 $ 80,000 $ 1,43,000 $ 2,23,000 3 4 4 4 5 5 5 6 6 6 7 7 8 8 9 10 Payback Period: =Initial Investment/Annual CF's =$ 590,000/$ 118,000 = 5 Years Payback Period: =Initial Investment/Annual CF's =$ 640,000/$ 80,000 = 8 Years Payback Period: =Initial Investment/Annual CF's =$ 428,000/$ 107,000 = 4 Years NPV: =NPV(10%,D5:012)+D4 $ 79,174.42 NPV: =NPV(10%,H5:H14)+ H4 $ -1,24,145.40 NPV: =NPV(10%,L5:L10)+ L4 $ 58,898.43 Profitability Index: =1 + (NPV/Initial Investment) =1 + ($ 79,174.42/$ 590,000) 1.13 Profitability Index: =1 + (NPV/Initial Investment) =1 + (-$ 124,145.40/$ 640,000) 0.81 Profitability Index: =1 + (NPV/Initial Investment) =1 + (-$58,898.43/$ 428,000) 1.14 IRR =IRR(D4:D12) 13.38% IRR =IRR(H4:H14) 5.50% IRR SIRR(H4:H14) 14.41% Req 2 Based strictly on the economic analysis, We should invest in Project A as it has maximum NPV Let me know, if you have any questions over comments section, rate positive if you are satisfied with the solution

XI - Book1 - Microsoft Excel (Product Activation Failed) File Home Insert Page Layout Formulas Data Review View Developer Acrobat Personal Macro O x Auto Sum Calibri 11 A Wrap Text General H Fill - * Cut E Copy Paste Format Painter Clipboard BIU А Merge & Center % +.0 .00 .00.0 Insert Delete Format Clear Conditional Format Cell Formatting as Table Styles Styles Sort & Find & Filter - Select Editing Font Alignment Number Cells T28 B с D E F G H L K N M 0 P - Q R s T T U V w х 1 2 Req1 Year Year Year $ $ $ 0 1 2 3 Project A Cumulative Cash Cash Flows Flows $ -5,90,000 $ -5,90,000 $ 1,18,000 $ -4,72,000 S 1,18,000 $ -3,54,000 S 1,18,000 $ -2,36,000 $ 1,18,000 $ -1,18,000 $ 1,18,000 $ $ 1,18,000 $ 1,18,000 1,18,000 $ 2,36,000 $ 2,03,000 $ 4,39,000 0 1 2 3 4 5 6 7 8 S Project B Cumulative Cash Cash Flows Flows -6,40,000 $ -6,40,000 80,000 $ -5,60,000 80,000 $ -4,80,000 80,000 $ -4,00.000 80,000 $ -3,20,000 80,000 $ -2,40,000 80,000 $ -1,60,000 80,000 $ -80,000 80,000 $ 80,000 $ 80,000 1,43,000 $ 2,23,000 Project C Cumulative Cash Cash Flows Flows -4,28,000 $ -4,28,000 1,07,000 $ 3,21,000 1,07,000 $ -2,14,000 1,07,000 $ -1,07,000 1,07,000 $ 1,07,000 $ 1,07,000 1,44,000 $ 2,51,000 1 2 3 4 5 6 $ S $ $ S $ S 4 $ S s $ 5 6 7 8 9 un S 10 $ 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Payback Period: =Initial Investment/Annual CF's -$ 590,000/$ 118,000 = 5 Years Payback Period: =Initial Investment/Annual CF's -$ 640,000/$ 80,000 - 8 Years Payback Period: =Initial Investment/Annual CF's $ 428,000/$ 107,000 -4 Years NPV: =NPV(10%,D5:012)+D4 $ 79,174.42 NPV: =NPV(10%, H5:H14)+H4 $ -1,24,145.40 NPV: =NPV(1095,L5:L10)+L4 $ 58,898.43 Profitability Index: = 1 + (NPV/Initial Investment) =1 +($ 79,174.42/$ 590,000) 1.13 Profitability Index: =1 + (NPV/Initial Investment) -1 + (-$ 124,145.40/S 640,000) 0.81 Profitability Index: =1 + (NPV/Initial Investment) =1+ (-$ 58,898.43/S 428,000) 1.14 IRR =IRR(D4:012) 13.3896 IRR =IRR(H4:H14) 5.5096 IRR =IRR(H4:H14) 14.4196 31 32 33 Req 2 Based strictly on the economic analysis, We should invest in Project A as it has maximum NPV Sheet1 Ready 77% - 0 е ENG US 22:52 31-07-2020