BUSA2020 Excel Sensitivity Analysis Assignment Sample

• Subject Code : BUSA2020
• University : Macquarie University My Assignment Services is not sponsored or endorsed by this college or university.
• Subject Name : Business Analytics

Fundamentals of Business Analytics - Task 1

Sensitivity analysis is a financial tool that helps to identify the interdependency of different variables and determines the change in variable on any changes in other. This financial model is helpful to take many decisions. The major use of the model is within real estate business, share market, banking business, etc. (Source – Sensitivity Analysis, Investopedia,2020)

Looking to the support that Alex receives from parents and available information, following assumptions are considered.

1. The Alex will sell the land property at the age of 65 where his parents will die at 90 as average age limit is of 83 (Source – The health of Australia’s males, AIHW - 2019)
2. Property value is considered as $1 million
3. Growth rate of MOE suburb for vacant land p.a. in 4% and that of the house is 4.5%, higher one, accordingly considered.
4. Any saving that Alex will have, will be reinvested to earn return of 1.5% in case of deficit, it will be adjusted against property value that will be derived at the time of sale and balance amount will be invested.
5. As no information for parents’ income, it is ignored for calculation.
6. After the death of parents, total household expenses will be reduced to half.

Accordingly following results can be derived,

Age of depletion with given assumptions:

• The rent income which Alex will earn is of $50,000/- only as compare to the actual expenses which is $80,000/- which is not sufficient to meet the required expenses.
• In spite of 4.5% growth within the value, Alex will face negative cash flow from the very first year.
• Alex will invest total $45,18,573/- at the end of 65^th year after adjusting negative cashflow of $776,393/-
• With the analysis we can conclude that, the earning rate on the investment is of 1.5% where as inflation rate is 2%. According earning is not sufficient to generate enough cash flow to meet the expenses.
• And gradually assets will start depleting.
• At the age of 25 only, Alex’s assets will be depleted to the cash saving rate and inflation rate

Age of depletion in different scenarios

• If, parent died at 85, growth rate is 3%, inflation rate is 1.5%, property value is 1.9 million
• The household expenses are of $80,000 and GST applicable on the same is of 10%. Thus, total outflow at the end of the year will be of $88000.
• As property generates 5% return, to meet the future cash outflow, minimum rent income should be of $92,700/- i.e. expenditure + reinvestment amount. Thus, value of the property is considered as $1.9 million.
• Although growth rate is decreasing, the investment amount is sufficient to meet the outflow till the survival of him.
• Further, because of the death of parents, Alex will sell the land at the age of 60 and will invest $81,78,841/- to earn the income.
• Alex, will have enough cash, thus no depletion of asset.

• GST rate increased by 20%, growth rate reduced to 2% and rental income increased to 6%, parents died at 75, Investment in property is 1500000

• Reduction in growth will reduce the value of the property and accordingly future investment.
• Increase in GST rate will increase the expenditure.
• In spite of increasing GST, Alex will be able to save while earning renting income.
• Although the investment amount is 1.5 million, Alex will generate positive cash flow from the age of 44 till the sale of asset. Otherwise because of insufficient investment, Alex will have asset depletion from 25^th year only.
• Total investment at the age 51 will be of $26,91,572/-.
• However because of the inflation, even though, overall household expenses are reduced, Alex will have shortfall of cash from the end of the 51^st And its property will start depleting.

• Inflation increase to 2.5%, cash saving increase to 2%, parents died at 80, Alex died at 80, growth rate is 3%, investment in property at $2.5 million.
• The general cash saving rate at present varies between 1.5% to 2% accordingly, increased @2% (Source -Saving account up to 3.5%, by Sally Tindall, 2020)
• No doubt that the inflation rate will affect overall performance, the sufficient investment of 2.5% will help to overcome the situations
• Alex will have positive cash income at all time and has not to worry amount survival as he had dreamed and increased inflation rate and increased inflation will not be question for survival.

Accordingly, following conclusion can be derived,

Fundamentals of Business Analytics - Task 2

Mutual fund investments are an easy, safe and transparent investment option for risk takers. The critical task associated with this investment is to choose the right mutual funds. The risk associated within the investment depends on the type of investment chosen like single asset managed fund or multi-sector assets managed funds (Source- Choosing a managed fund). The growth of the mutual fund depends on its Net Asset Value and reinvestment made (Source - How to Calculate Mutual Fund Returns by Manivel,2018)

Presently Alex wants to sell the house property and invest the same within mutual funds. To calculate the feasibility of the decision following assumptions are considered,

Value of property/ Mutual funds $1000000

Rental Return 5%

Growth rate of property 4.50%

Reinvestment in Mutual funds 10%

Dividend payout of MF Varying from 0% to 10%

Standard Deviation 6%

Mean 8%

Considering above following analysis were conducted.

• Age 85, Dividend payout 5%.
• Considering dividend payout exactly the same as the rent income rate, it can be analyzed that mutual funds are not generating sufficient equal returns as it can be earned with rent income. Every year there will be a deficit which gradually will increase.
• However, the total value of investment at the end of 85^th years in Mutual fund will be of $18,635,985/- which is comparatively higher than the value of property.
• Accordingly, if there is an alternate source of income then only, Alex should go for investment.
• Age 90, Dividend payout 8%
• The mean of the growth shows the average return that property will generate. If we consider the dividend pay out exactly the same as mean of property on a constant basis, it will generate good inflow as well as market value for the investment.
• Thus, if the mutual funds are generating growth exactly to the mean or higher than that, Alex should definitely sell the property and invest in mutual funds

• Age 95, Dividend payout varying 0% to 10%
• Standard deviation shows how much the return on investment can vary from its mean value (source - Measuring Mutual Fund Risk)
• Here considering the same random dividend payout for different years is chosen.
• On analyzing the same, it was found that any return below 5% will definitely generate negative cashflow and accordingly impact the growth rate of the investment. Thus, choosing the mutual fund generating negative outflow will change the results.

Summarizing the analysis, following can be concluded

References for Excel Sensitivity Analysis

References Task-1

Will Kenton, (2020, May 28), “Sensitivity Analysis” published on Investopedia available at (https://www.investopedia.com/terms/s/sensitivityanalysis.asp#:~:text=Sensitivity%20analysis%20is%20a%20financial,a%20certain%20range%20of%20variables.)

The health of Australia’s males – AIHW web report, 2019 available at (https://www.aihw.gov.au/reports/men-women/male-health/contents/how-healthy/life-expectancy-and-mortality)

Savings Accounts up to 3.50% by Sally Tindall (2019, October 19) published on Rate city available at (https://www.ratecity.com.au/savings-accounts)

References Task- 2

Choosing a managed fund published on moneysmart.gov.au available at (https://moneysmart.gov.au/managed-funds-and-etfs/choosing-a-managed-fund)

How to Calculate Mutual Fund Returns by Manivel,2018 published on Holistic investment planner available at (https://www.holisticinvestment.in/how-to-calculate-mutual-fund-returns/#:~:text=i.e.%20%5B(25%2F15)%5E,the%20end%20of%20two%20years.)

Measuring Mutual Fund Risk with topic investments available at HDFC Mutual Fund (https://www.hdfcfund.com/learn/advanced/investments/measuring-mutual-fund-risk)

Remember, at the center of any academic work, lies clarity and evidence. Should you need further assistance, do look up to our Business Analytics Assignment Help