Project 1 Project 2 Period Costs Revenues Net Cum Discount Costs Revenues Net Cum Discount 0...

Transcribed Image Text:

Project 1 Project 2 Period Costs Revenues Net Cum Discount Costs Revenues Net Cum Discount 0 $400,000 1 $30,000 $90,000 2 3 $32,000 $34,000 5 $38,000 6 -$400,000 -$400,000 $60,000 -$340,000 $92,000 $60,000 -$280,000 $94,000 $60,000 4 $36,000 $96,000 $60,000 $98,000 $60,000 $40,000 $100,000 $60,000 -$220,000 -$160,000 -$100,000 -$40,000 7 $42,000 $102,000 $60,000 $20,000 8 $44,000 $104,000 9 $46,000 $106,000 10 $48,000 $108,000 11 $100,000 $60,000 12 $30,000 $100,000 $60,000 $80,000 $60,000 $140,000 $60,000 $200,000 -$40,000 $160,000 $70,000 $230,000 13 $33,000 $105,000 $72,000 $302,000 14 $36,000 $110,000 $74,000 $376,000 $10,000 $200,000 $10,000 $10,000 $10,000 $65,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 $10,000 $40,000 $30,000 $180,000 $10,000 $35,000 $25,000 $205,000 $200,000 $30,000 -$170,000 $10,000 $75,000 $65,000 $100,000 $10,000 $70,000 $60,000 $160,000 $10,000 $65,000 $55,000 $215,000 $60,000 -$200,000 -$200,000 $75,000 $65,000 -$135,000 $70,000 $60,000 -$75,000 $55,000 -$20,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) $35,000 d. Verify the internal rate of return for each project, using Solver $50,000 $265,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. 15 $39,000 $115,000 $76,000 $452,000 $10,000 $55,000 $45,000 $310,000 16 $42,000 $120,000 $78,000 $530,000 $10,000 $50,000 $40,000 $350,000 f. Repeat "e" using Solver 17 $45,000 $125,000 $80,000 $610,000 $10,000 $45,000 $35,000 $385,000 18 $48,000 $130,000 $82,000 $692,000 $10,000 $40,000 19 $51,000 $135,000 $84,000 20 $54,000 $140,000 $86,000 $776,000 $862,000 $10,000 $35,000 $30,000 $415,000 $25,000 $440,000 $200,000 $30,000 -$170,000 $270,000 21 $57,000 $145,000 $88,000 $950,000 $10,000 $75,000 $65,000 $335,000 22 $60,000 $150,000 $90,000 $1,040,000 $10,000 $60,000 $50,000 $385,000 23 $63,000 $155,000 $92,000 $1,132,000 $10,000 $45,000 $35,000 $420,000 24 $66,000 $160,000 $94,000 $1,226,000 $10,000 25 $69,000 $165,000 $1,583,000 $2,905,000 $96,000 $1,322,000 $30,000 $20,000 $440,000 $10,000 $15,000 $5,000 $445,000 $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% Project 1 Project 2 Period Costs Revenues Net Cum Discount Costs Revenues Net Cum Discount 0 $400,000 1 $30,000 $90,000 2 3 $32,000 $34,000 5 $38,000 6 -$400,000 -$400,000 $60,000 -$340,000 $92,000 $60,000 -$280,000 $94,000 $60,000 4 $36,000 $96,000 $60,000 $98,000 $60,000 $40,000 $100,000 $60,000 -$220,000 -$160,000 -$100,000 -$40,000 7 $42,000 $102,000 $60,000 $20,000 8 $44,000 $104,000 9 $46,000 $106,000 10 $48,000 $108,000 11 $100,000 $60,000 12 $30,000 $100,000 $60,000 $80,000 $60,000 $140,000 $60,000 $200,000 -$40,000 $160,000 $70,000 $230,000 13 $33,000 $105,000 $72,000 $302,000 14 $36,000 $110,000 $74,000 $376,000 $10,000 $200,000 $10,000 $10,000 $10,000 $65,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 $10,000 $40,000 $30,000 $180,000 $10,000 $35,000 $25,000 $205,000 $200,000 $30,000 -$170,000 $10,000 $75,000 $65,000 $100,000 $10,000 $70,000 $60,000 $160,000 $10,000 $65,000 $55,000 $215,000 $60,000 -$200,000 -$200,000 $75,000 $65,000 -$135,000 $70,000 $60,000 -$75,000 $55,000 -$20,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) $35,000 d. Verify the internal rate of return for each project, using Solver $50,000 $265,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. 15 $39,000 $115,000 $76,000 $452,000 $10,000 $55,000 $45,000 $310,000 16 $42,000 $120,000 $78,000 $530,000 $10,000 $50,000 $40,000 $350,000 f. Repeat "e" using Solver 17 $45,000 $125,000 $80,000 $610,000 $10,000 $45,000 $35,000 $385,000 18 $48,000 $130,000 $82,000 $692,000 $10,000 $40,000 19 $51,000 $135,000 $84,000 20 $54,000 $140,000 $86,000 $776,000 $862,000 $10,000 $35,000 $30,000 $415,000 $25,000 $440,000 $200,000 $30,000 -$170,000 $270,000 21 $57,000 $145,000 $88,000 $950,000 $10,000 $75,000 $65,000 $335,000 22 $60,000 $150,000 $90,000 $1,040,000 $10,000 $60,000 $50,000 $385,000 23 $63,000 $155,000 $92,000 $1,132,000 $10,000 $45,000 $35,000 $420,000 24 $66,000 $160,000 $94,000 $1,226,000 $10,000 25 $69,000 $165,000 $1,583,000 $2,905,000 $96,000 $1,322,000 $30,000 $20,000 $440,000 $10,000 $15,000 $5,000 $445,000 $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%