the following question is to be solved through excel. please show the formulas used to calculate if any

QUESTION FIVE The commercial arm of the Strathmore Centre for Value Investing (SCVI) has developed an investment product Motocas! (Motocas factorial.) The name of the product is not at all creative. However, it describes the purpose of the product: To enable people save up enough money over a period of 36 months to enable them purchase a motor car. Here's how it works. Investors deposit a fixed sum of KES 25,000 at the beginning of each month into the fund. The fund provides a guaranteed return of 1% per month on the fund value at the beginning of each month. Now the actual returns earned by the fund will vary from the guaranteed rate! Where the returns are higher than the guaranteed rate, the excess interest earned is transferred into a reserve account. And where the returns are lower than guaranteed rate, the shortfall is withdrawn from the reserve account and added to the fund value so that it will still have earned the guaranteed rate in the month. The reserve account however earns no interest and is simply an account where excess or shortfall is deposited or withdrawn as needed. Based on complex (very complex) statistical and time series analysis, the actual interest rates over the 36-month period are expected to be as shown on Interestrates data. Required. 1. Calculate the following summary statistics for actual rates of return Minimum, maximum, mean, standard deviation, and coefficient of variation. 2. Plot a histogram of the actual rates of return and discuss your results. 3. Show a schedule of the following for each month over the 36-month period. a. Fund value beginning of month [1 mark] b. Guaranteed interest amount [1 mark] c. Actual interest amount [1 mark] d. Transfer to reserve [2 marks] e. Reserve running balance [2 marks] f. Fund value end of month [1 mark] 4. What is the total profit to SCVI for the product? [2 marks] 5. Plot values of actual fund versus guaranteed fund value at the end of each month for 36 months. [2 marks] [2 marks] [2 marks] 6. Calculate the amount that should be invested each month so that the final fund value after 36 months is KES 1 Million. [2 marks] 7. Do you think the product is worth introducing to the market? Why or why not? [2 marks]

QUESTION FIVE The commercial arm of the Strathmore Centre for Value Investing (SCVI) has developed an investment product Motocas! (Motocas factorial.) The name of the product is not at all creative. However, it describes the purpose of the product: To enable people save up enough money over a period of 36 months to enable them purchase a motor car. Here's how it works. Investors deposit a fixed sum of KES 25,000 at the beginning of each month into the fund. The fund provides a guaranteed return of 1% per month on the fund value at the beginning of each month. Now the actual returns carned by the fund will vary from the guaranteed rate! Where the returns are higher than the guaranteed rate, the excess interest carned is transferred into a reserve account. And where the returns are lower than guaranteed rate, the shortfall is withdrawn from the reserve account and added to the fund value so that it will still have earned the guarantecd rate in the month. The reserve account however earns no interest and is simply an account where excess or shortfall is deposited or withdrawn as needed. Based on complex (very complex) statistical and time series analysis, the actual interest rates over the 36-month period are expected to be as shown on Interestrates data. Required. 1. Calculate the following summary statistics for actual rates of retum Minimum maximum mean standard deviation. and coefficient of variation [2 markel 5. Plot values of actual fund versus guaranteed fund value at the end of each month for 36 months. [2 marks] 6. Calculate the amount that should be invested cach month so that the final fund value after 36 months is KES 1 Million. [2 marks]