0. Clarity of layout, logic, following color format, and instructions on page 1 and 2. [10...

Transcribed Image Text:

0. Clarity of layout, logic, following color format, and instructions on page 1 and 2. [10 points] 1. You are a financial advisor. You have a client considering investing $22,000 in a high-growth stock fund. Your client will need their money back at the end of 5 years. This fund has historically returned 12% per year. [20 points] a. How much money can your client expect to have at the end of five years? (Please use consistent formatting throughout the spreadsheet). b. Using a vertical data table, explore how your results vary when the interest rate changes from 4% to 20% (increment by 2%). Then explain why the amount of change increases or decreases as the interest rate increases (e.g. what is the difference between NPV at 4% vs 6% compared to 6% vs 8%..is it smaller or larger. [Note: you can just write your explanation in a cell and highlight the answer). C. Create a two-dimensional table showing how results change when the interest rate changes from 4% to 20% (increment by 2%) and the investment horizon changes from 2 to 8 years (increment by 1 year) d. Using Excel functions across all the data cells in the two-dimensional table, report the: i. The Average savings ii. The Minimum savings iii. The Maximum savings 2. You work in the corporate finance department of Woodford Reserve Distillery. The company has an extra $100,0000, and you are asked to invest this amount. You talk to Truist Bank, and they offer two treasury options: [20 points] Option A -Deposit $100,000, and you get an annual payment of $5,000 for three years at the end of each year and $7,000 for an additional ten years starting at the end of the fourth year. Option B - Deposit $100,000, and you get a lump-sum payment of $120,000 at the end of year 10. For both options you get your original $100K pay at the end of 13 years a. If the company discount rate is 5%, which option would you prefer? b. Using "What if Analysis, at what discount rate would your choice change? C. If the Company discount rate is 12%, what is the most you would pay for Options A & B? Explain why. 0. Clarity of layout, logic, following color format, and instructions on page 1 and 2. [10 points] 1. You are a financial advisor. You have a client considering investing $22,000 in a high-growth stock fund. Your client will need their money back at the end of 5 years. This fund has historically returned 12% per year. [20 points] a. How much money can your client expect to have at the end of five years? (Please use consistent formatting throughout the spreadsheet). b. Using a vertical data table, explore how your results vary when the interest rate changes from 4% to 20% (increment by 2%). Then explain why the amount of change increases or decreases as the interest rate increases (e.g. what is the difference between NPV at 4% vs 6% compared to 6% vs 8%..is it smaller or larger. [Note: you can just write your explanation in a cell and highlight the answer). C. Create a two-dimensional table showing how results change when the interest rate changes from 4% to 20% (increment by 2%) and the investment horizon changes from 2 to 8 years (increment by 1 year) d. Using Excel functions across all the data cells in the two-dimensional table, report the: i. The Average savings ii. The Minimum savings iii. The Maximum savings 2. You work in the corporate finance department of Woodford Reserve Distillery. The company has an extra $100,0000, and you are asked to invest this amount. You talk to Truist Bank, and they offer two treasury options: [20 points] Option A -Deposit $100,000, and you get an annual payment of $5,000 for three years at the end of each year and $7,000 for an additional ten years starting at the end of the fourth year. Option B - Deposit $100,000, and you get a lump-sum payment of $120,000 at the end of year 10. For both options you get your original $100K pay at the end of 13 years a. If the company discount rate is 5%, which option would you prefer? b. Using "What if Analysis, at what discount rate would your choice change? C. If the Company discount rate is 12%, what is the most you would pay for Options A & B? Explain why.