ACCT3610 Business Analysis and Valuation
Question:
The Problem
A TSB (Tax Saver Benefit plan) allows you to put money (to a maximum of euro) into an account at the beginning of the calendar year. The money in this account may only be used for paying medical expenses. The amount that you put into the account is tax deductible. As you pay medical expenses during the year, you are reimbursed by the administrator of the TSB, until the TSB account is exhausted. The catch is, however, that any amount left in the TSB at the end of the year is lost to you and will be transferred to the government.
You estimate that your medical expenses for the next year will be any amount between euro and euro and all amounts are equally likely. The federal income tax rate is. Assume your annual salary is euro before tax. You would like to decide how much money to put into your TSB account at the start of the next year. You want to look at your disposable income (after paying taxes and medical expenses).
The Task
The assignment requires you to model the situation regarding a tax saver benefit plan in Excel and implement the risk analysis methods such as sensitivity analysis and simulation. Please hand in a report that answers questions (a) through (g) below. You can discuss the task with members of your team, and even help each other with the technical analysis, but you should write the individual report on your own. A small part of the overall grade is allocated to presentation and adherence to submission guidelines.
(a) You start your analysis by assuming that the average case for your medical expenses will happen. In this case, what is the optimal amount to put into the TBS? Explain why this is optimal. What is your disposable income?
Tip: Do not take uncertainty into account as of yet (do not start @Risk). Just assume medical expenses = average medical expenses and assume that this is known to you when you decide how much to put into the TSB. Note that the medical expenses to pay after TSB exist only when the total medical expenses are more than what you have put into the TSB.
(b) You recognize, however, that there is some uncertainty involved in the problem. Perform a scenario analysis (i.e., finding the best- and worst-case disposable incomes) and sensitivity analysis (with steps euro), given your plan to put the optimal amount from Question (a) in the TSB account. Discuss your results.
Tip: Do not start @Risk yet. Use a one-way data table for the sensitivity analysis and interpret your results.
(c) Set up a simulation model and analyze the results (given your plan to put the optimal amount from Question (a) in the account). Analyze the results and compare them to the outcome from Questions (a) and (b). What is the probability that your disposable income will be lower than euro? What is the probability that it will be higher than euro?
(d) Given your decision to put the optimal amount from Question (a) in the account, analyse what the average cost or benefit is for the government of introducing this TSB account (compared to not having the account).
Tip: The government’s cost or benefit is your benefit or cost, respectively.
(e) You wonder whether it would be better to put in a different amount. You want to do the risk analysis for amounts between, with increments of. (do this in one spreadsheet). Assume that your risk measure is the percentile. Analyse and discuss the results (Hint: (i) prepare a mean-risk graph in which the expected disposable income is presented on the X-axis and the risk measure on the Y-axis, (ii) consider the distribution of the disposable income for each setting and explain the form). Is the optimal TSB amount different from what you originally thought (see Question (a)) and why (not)?
Tip: You can use the RiskMean() and RiskPercentile() functions.
(f) You are wondering about your assumption that all values for the medical costs are equally likely between. You now think that a better assumption is to say that the most likely value is €5,000, and the further you go to the extremes, the less likely these values are. Are the results from the previous scenario and sensitivity analysis still valid? Discuss why (not). Repeat your simulation analysis (for TSB levels between and with this new assumption. Discuss the differences that you observe compared to the results in Question (e). If you put euro in your TSB account, is the average disposable income in this case higher or lower than under the previous assumption (all outcomes equally likely) and explain why.
(g) Go back to your assumption that expenses between and are equally likely. If the tax rate will go up, will your optimum TSB level go up or down? Analyze the situation with a tax rate of 65% and explain the influence of the tax rate.
