+1 862 207 3288 This is an assignment dealing with beginning and intermediate level Excel. In this assignment you will develop an investment schedule for
personal savings.
General Problem Description
As a student you probably don’t have a whole lot of money to invest, but once you start your professional career, you will have that
opportunity. This assignment deals with doing calculations with respect investing your money. For this assignment you will do investment
calculations for various types of investments. You will develop a spreadsheet that does these calculations. This spreadsheet must be
parameterized so that when the assumptions to the problem change all of the calculations in arriving at the answer will also change.
Investment Calculations
For this problem you will have an initial investment that can take on any dollar amount and a recurring yearly investment that starts at the
end of the first year which can be changed as you wish, but remains the same throughout each of the 15 years. You will do your investment
calculations for a total of 15 years.
Your spreadsheet must be designed so that if either the initial investment or the yearly investment numbers change, then all of the
calculations in the spreadsheet will change. This is termed parameterizing the spreadsheet.
You are provided with a template of the spreadsheet you are to develop. Be sure to use this template in completing the assignment.
The spreadsheet has three different tabs. The first tab has information on the investment and a summary of the results of your calculations.
The second tab contains the detailed calculations and the third tab contains a table of the average rate of return for each of the
investments.
On the first tab it is already programmed that you may select only one of six different investment choices. They are:
Savings Account
Investment in Bonds
Investment in Stock
Buying a House
Playing the Lottery
Betting on Fantasy Football
The table on the third tab gives estimates of the possible average returns on each of these investments. For each type of investment, the
estimates are in three different ranges: Low rate of return, Middle rate of return and High rate of return. You can think of these estimates
as the lower boundary (you were unlucky) and upper boundary (you were lucky) of the possible rate of return and also the most likely rate of
return (the middle value). While I initially provide values for all of the ranges in all of the investments, you should program your answer
so that any change in these values will be reflected in the results in the rest of the spreadsheet.
Your job is to complete the table of investment calculations on the Calculations tab and then carry the results of the final (15th) year to
the appropriate place on the Assignment 5 tab. In addition, you are to determine if that investment made a profit or not. This is done by
comparing the Total Amount Invested in 15 Years (cell B9 on the Assignment 9 tab) to each of the results in the Investment Value After 15
Years table on the Assignment 5 tab. If the final result is less than the total amount invested then display the following message: “You
lost money!” Right next to the appropriate value (i.e. cells C12 through C14). If you didn’t lose money, then display the following message:
“You made a profit.” in those same cells (i.e. cells C12 through C14).
Once you have developed your spreadsheet, you need to analyze your results. First you will answer a question concerning the results of your
calculations and then you will do some sensitivity analysis. The answer to the question and the sensitivity analysis should be presented in
an MSWord document that presents your results and also provides analysis of the situation.
Question 1: To answer this question use the original assumptions.
Looking at the numbers in the table of the investment results discuss which investments you think would be best for you. So you should rank
order each of the investments from best to worse. That is, you need to think about the probability of each of the three different rates of
return occurring for each of the investments and the amount of risk you would be willing to accept. Since the amount of risk acceptable to
each person may be different, you need to justify why you ranked the investments in the order in which you did.
notjust an ordered list of the investments but also a detailed explanation of your logic in creating that list.
In the following you are asked to do sensitivity analysis using the spreadsheet you developed. This means that you will change some of the
initial parameters and see the affect of these changes. Following are the situations you should analyze:
Question 2:
a. Change the initial amount of the investment to $0. Examine the results of that change. For some of the investments and some of the
rates of return there were major changes (order of magnitude changes), while for others the changes were not as dramatic. Explain what
influenced these changes to be really big or not very big.
b. Change the initial investment back to $10,000. After speaking to an investment advisor, you determine that when you invest in the
bond market, some of the bonds can be “junk bonds” that have a high rate of return but may end up being worthless when they mature. Because
of this you change the low estimate to -.01 and the high estimate to .06. Under what circumstances in your life would this change your
ranking of the investments?
Submit both your Excel workbook and your MSWord document electronically by submitting it in the appropriate assignment in Blackboard. You
must also bring a printed copy of your assignment (only the MSWord document) to class on Tuesday, October 20, 2015. I will be collecting them
at the beginning of the class. Remember! Late submissions are not accepted for grading.
Assignment 5 Investment Calculations
BSIS 105
Fall 2015 Your Name:
Select Type of Investment Savings Account
Initial Investment $10,000
Yearly Additions $1,000
Total Amount Invested in 15 Years $25,000
Investment Value After 15 Years
Low Rate of Return
Middle Rate of Return
High Rate of Return
