Information presented below pertains to the cash flows of three mutually exclusive investment proposals and is also included in the file linked here.

Proposal A Proposal B Proposal C

Initial investment $$100,000$ $$100,000$ $$100,000$

Cash flow from operations

Year $160,00025,000110,000$

Year $240,00040,000-$

Year $335,00070,000-$

Disinvestment $---$

Life $($years$)331$

Discount rate $($for all proposals$)20\%$

a$.$ Prepare a table in Excel to compute the net present value for each proposal, A$,$ B and C$.$ In your formulas, reference the discount rate used for all of your calculations to a single cell. Hint: By referencing all of your calculations to a single cell, you can easily develop a sensitivity analysis by changing the discount rate in that cell and the output $($net present value$)$ will update automatically.

Note: Round your answers to the nearest whole dollar. Use a negative sign to indicate a cash outflow.

Proposal A Proposal B Proposal C

Initial investment Answer $1$

$(100,000)$

Answer $2$

$(100,000)$

Answer $3$

$(100,000)$

$1$ Answer $4$

$50,000$

Answer $5$

$25,000$

Answer $6$

$110,000$

$2$ Answer $7$

$75,000$

Answer $8$

$33,333\mathrm{.}33$

Answer $9$

$0$

$3$ Answer $10$

$29,166\mathrm{.}67$

Answer $11$

$58,333\mathrm{.}33$

Answer $12$

$0$

Net present value

$54,167$

$16,667$

$10,000$

b$.$ Prepare a new table listing the discount rate from $5\%$ to $20\%$ along with the net present value for each proposal, A$,$ B$,$ and C$.$ Hint: Using the table from part a$,$ enter each percentage. Take the results and copy and paste values into the new table.

Note: Round your answers to the nearest whole dollar.

Net Present Value

Discount Rate Proposal A Proposal B Proposal C

$5\%$ Answer $13$

$31,851\mathrm{.}85$

Answer $14$

$0$

Answer $15$

$0$

$6\%$ Answer $16$

$0$

Answer $17$

$0$

Answer $18$

$0$

$7\%$ Answer $19$

$0$

Answer $20$

$0$

Answer $21$

$0$

$8\%$ Answer $22$

$0$

Answer $23$

$0$

Answer $24$

$0$

$9\%$ Answer $25$

$0$

Answer $26$

$0$

Answer $27$

$0$

$10\%$ Answer $28$

$0$

Answer $29$

$0$

Answer $30$

$0$

$11\%$ Answer $31$

$0$

Answer $32$

$0$

Answer $33$

$0$

$12\%$ Answer $34$

$0$

Answer $35$

$0$

Answer $36$

$0$

$13\%$ Answer $37$

$0$

Answer $38$

$0$

Answer $39$

$0$

$14\%$ Answer $40$

$0$

Answer $41$

$0$

Answer $42$

$0$

$15\%$ Answer $43$

$0$

Answer $44$

$0$

Answer $45$

$0$

$16\%$ Answer $46$

$0$

Answer $47$

$0$

Answer $48$

$0$

$17\%$ Answer $49$

$0$

Answer $50$

$0$

Answer $51$

$0$

$18\%$ Answer $52$

$0$

Answer $53$

$0$

Answer $54$

$0$

$19\%$ Answer $55$

$0$

Answer $56$

$0$

Answer $57$

$0$

$20\%$ Answer $58$

$0$

Answer $59$

$0$

Answer $60$

$0$

c$.$ Prepare a line chart showing the net present value of proposals A$,$ B$,$ and C with discount rates ranging from $5\%$ to $20\%.$