EXCEL SENSITIVITY ANALYSIS (Any kind of help or guidance is appreciated, you dont have to answer the whole question)

Using these values as an example:

- 2020 house value of 100,000

- House growth of 2%

Alex started to think that the human life is so fragile, and he wanted to enjoy every moment of today. With that thought, he was exploring the possibility that "living without having to work."

Task 1

Currently, Alex is staying with his parents, both of whom are 50-year-old. As a spoiled child, Alex is strongly supported by his parents, so he does not have to work to earn incomes, if that is what he wants. Also, Alex will inherit their investment property as a heritage (priced at 290,000 in 2020), after both his parents die. Once that occurs, Alex will sell the property to gain cash at the market price, which will be saved in a bank to support his daily life. Alex has no other sources of savings and/or incomes. For simplicity, assume that everyone will die at the age of 90.

Suppose that the rental return of the investment property is 5% per year, which is paid once annually. For the received rent, 95% will be used to support the living of Alexs family, and 5% will be re-invested to the investment property to increase its market value. In practice, it can be maintenance work and/or simple add-on of the house. For instance, if the house is worth 1 million at the start of year, $50,000 rent will be earned at the end of year, among which $2,500 is re-invested, such that the house will be worth at its end-of-year market value + $2,500.

Other essential assumptions are listed below:

The market value of a house will increase at the average rate 3%

The cash saving rate is 1.5% per year, paid once at the end of year;

Inflation rate is 2% per year;

Average yearly expense is $80,000 in 2020, which is to be charged once at the end of year; and

The GST of the yearly expense is 10%.

With the above information, build a spreadsheet model to explore the age at which Alexs assets will be depleted. Also, use a data table to demonstrate the sensitivity of the age at which Alexs assets will be depleted to the cash saving rate and inflation rate. Use a scenario manager to consider at least three scenarios. For each one, consider changing at least three factors among constant rental return of the investment property, maximum of human life, average yearly expense, inflation rate, cash saving rate, GST of the yearly expense and yearly percentage increasement in the market value of the house.