Period Project 1 Costs Revenues Net Cum Discount Project 2 Costs Revenues Net Cum Discount 0 $400,000 1 $30,000 $90,000 2 $32,000 $92,000 -$400,000 -$400,000 $60,000 -$340,000 $60,000 -$280,000 3 $34,000 $94,000 $60,000 -$220,000 4 $36,000 $96,000 $60,000 -$160,000 5 $38,000 $98,000 $60,000 -$100,000 6 $40,000 $100,000 7 $42,000 $60,000 $102,000 $60,000 -$40,000 $20,000 8 $44,000 $104,000 $60,000 $80,000 $10,000 9 10 11 $48,000 $100,000 $46,000 $106,000 $108,000 $60,000 $60,000 $60,000 -$40,000 $160,000 $140,000 $10,000 $35,000 $200,000 $200,000 $30,000 $10,000 $75,000 12 3135222222222 14 $30,000 $100,000 $70,000 $230,000 $33,000 $105,000 $72,000 $302,000 $36,000 $110,000 $10,000 $70,000 $10,000 $65,000 -$400,000 $200,000 -$200,000 -$200,000 -$200,000 $10,000 $75,000 $65,000 -$135,000 $10,000 $70,000 $60,000 -$75,000 $10,000 $65,000 $55,000 -$20,000 $10,000 $60,000 $50,000 $30,000 $10,000 $55,000 $45,000 $75,000 $10,000 $50,000 $40,000 $115,000 $10,000 $45,000 $35,000 $150,000 $40,000 $30,000 $180,000 $25,000 $205,000 -$170,000 $65,000 $100,000 $60,000 $160,000 $55,000 $215,000 $35,000 Module 8 Exercises 4 Use the dataset in this workbook Hint: Keep all responses and all work for this question on the same worksheet. a. Compute the net cash and payback period for each investment. Estimate payback within the nearest month b. What is the net present value at interest rates of 2%, 5%, and 10% (Hint - create a dashboard and insert columns and rows as needed and answer in an answer box) c. Use the Excel formula to compute the internal rate of return for each project (Hint - Show the work and insert answers in an answer box) d. Verify the internal rate of return for each project, using Solver $74,000 $376,000 $10,000 $39,000 $115,000 $76,000 $452,000 $10,000 16 $42,000 $120,000 $78,000 $530,000 $10,000 $50,000 18 19 20 24 17 $45,000 $125,000 $80,000 $610,000 $48,000 $130,000 $51,000 $135,000 $54,000 $140,000 $57,000 $145,000 $60,000 $150,000 $63,000 $155,000 $66,000 $160,000 $69,000 $165,000 $1,583,000 $2,905,000 $10,000 $45,000 $82,000 $692,000 $10,000 $40,000 $84,000 $776,000 $86,000 $862,000 $88,000 $950,000 $90,000 $1,040,000 $92,000 $1,132,000 $94,000 $1,226,000 $96,000 $1,322,000 $10,000 $200,000 $10,000 $75,000 $10,000 $60,000 $10,000 $45,000 $10,000 $10,000 $30,000 $15,000 $60,000 $50,000 $265,000 $55,000 $45,000 $310,000 $40,000 $350,000 $35,000 $385,000 $30,000 $415,000 $35,000 $25,000 $440,000 $30,000 -$170,000 $270,000 $65,000 $335,000 $50,000 $385,000 $35,000 $420,000 $20,000 $440,000 $5,000 $445,000 e. For Project 2, by how much must the initial cost increase to make the internal rate of return equal to 5%. Use a scroll bar. f. Repeat "e" using Solver $830,000 $1,275,000 NPV 2% $908,665 NPV 2% $337,458.10 5% $511,175.71 5% $225,869.47 10% $161,189.50 10% $114,627.91 IRR 15% IRR 21%