[ad_1]
MBA 2021 Business Analysis Case Study
ASSIGNMENT 2: THE TAX SAVER BENEFIT PLAN
THE PROBLEM
A TSB (Tax Saver Benefit plan) allows you to put money (to a maximum of 10,000 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 3,000 euro and 7,000
euro and all amounts are equally likely. The federal income tax rate is 25%. Assume your annual salary is
50,000 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 (5%) is allocated to presentation and adherence to submission guidelines.
Please make sure to explain and justify all of your answers. Your reasoning and interpretation of the results
are as important for your grade as the technical correctness of your analysis. Please limit the main report to
3 pages and use an appendix with screenshots to clarify your Excel models.
QUESTIONS
(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? [5 points]
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 of 500 euro), given your plan to put the optimal amount from Question (a) in the TSB account. Discuss your results. [15 points]
TIP: Do not start @Risk yet. Use a one-way data table for the sensitivity analysis and interpret your results. MBA 2021 Business Analysis Case Study
(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 32,000 euro? What is the probability that it will be higher than 33,000 euro? [15 points]
(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). [15 points]
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 €0 and €10,000, with increments of €1,000. (do this in one spreadsheet). Assume that your risk measure is the 5% 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)
ROTTERDAM EXECUTIVE MBA 2021
BUSINESS ANALYTICS AND DECISION MAKING
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)? [15 points]
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 €3,000 and €7,000. 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 €0 and €10,000) with this new assumption. Discuss the differences that you observe compared to the results in Question (e). If you put 5,000 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. [15 points]
(g) Go back to your assumption that expenses between €3,000 and €7,000 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. [15 points]
MBA 2021 Business Analysis Case Study
Need Help with a similar Assignment?
What Students Are Saying About Us
.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐"Honestly, I was afraid to send my paper to you, but splendidwritings.com proved they are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"
.......... Customer ID: 14***| Rating: ⭐⭐⭐⭐⭐
"The company has some nice prices and good content. I ordered a term paper here and got a very good one. I'll keep ordering from this website."
