A TSB (Tax Saver Benefit plan) allows you to put money into an account at the beginning of the calendar year to use for medical expenses. This amount is not subject to federal tax (hence the phrase...

Solution to TSB Planning
A TSB (Tax Saver Benefit) plan allows you to put money into an account at the beginning of the calendar year that can be used for medical expenses. This amount is not subject to federal tax — hence the phrase TSB. As you pay medical expenses during the year, you are reimbursed by the administrator of the TSB until the TSB account is exhausted. From that point on, you must pay your medical expenses out of your own pocket. On the other hand, if you put more money into your TSB than the medical expenses you incur, this extra money is lost to you. Your annual salary is $50,000 and your federal income tax rate is 30%.
a. Assume that your medical expenses in a year are normally distributed with mean $2000 and standard deviation $500. Build a Crystal Ball model in which the output is the amount of money left to you after paying taxes, putting money in a TSB, and paying any extra medical expenses. Experiment with the amount of money put in the TSB, and identify an amount that is approximately optimal.
First, we set up a spreadsheet to organize all of the information. In particular, we want to make sure we’ve identified the decision variable (how much to have taken out of our salary and put into the TSB account — here in cell B1), the objective (Maximize net income — after tax, and after extra medical expenses not covered by the TSB — which we have here in cell B14), and the random variable (in this case the amount of medical expenses — here in cell B9).
Note (this is important): We will never get a simulation model to tell us directly what is the optimal value of the decision variable. We will try different values (here we have arbitrarily started with $2000 in cell B1) and see how the objective changes. Through educated trial-and-error, we will eventually come to some conclusion about what is the best amount of money to put into the TSB account.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
A
B
C
D
TSB Amount (Decision Variable)
2,000.00
$
Annual Salary
50,000.00
$
Tax Rate
30%
After TSB Income
48,000.00
$
Taxes Owed
14,400.00
$
Net Income Before Medical Expenses
33,600.00
$
Total Medical Expenses
2,000.00
$
Amount in TSB
2,000.00
$
Expenses Not Covered (Must Be Paid Out-Of-Pocket)
-
$
Money Left Over in TSB (Lost)
-
$
Net Income After Medical Expenses (Objective)
33,600.00
$
Mean
2,000.00
$
Standard Deviation
500.00
$
=B3-B1
=B5*B4
=B5-B6
This will be a random variable.
=B1
=MAX(B9-B10,0)
=MAX(B10-B9,0)
=B7-B11
Now we add the element of randomness by making B9 into an assumption cell. First, enter the mean and standard deviation for the medical expenses random variable (we put them in cells B16 and B17, respectively).
16
17
A
B
Mean
2,000.00
$
Standard Deviation
500.00
$
Select the assumption cell B9 and click on the assumption button . Select “Normal” and click “OK”.
We are presented with a screen where we can enter the parameters for this normal distribution. We can enter values (2000 and 500) or we can use cell references. Here we enter the cell references. (Unfortunately, you can’t just click on the cells to enter them here; you have to type everything into the boxes.)
Click OK and go back to the spreadsheet, where cell B9 has...