Question
(a) Suppose you are a financial analyst and your company runs many simulation models to estimate the profitability of its projects. If you had to
(a) Suppose you are a financial analyst and your company runs many simulation models to estimate the profitability of its projects. If you had to choose just two measures of the distribution of any important output, such as net profit, to report, which two would you choose? Why? What information would be missing if you reported only these two measures? How could they be misleading? (6 marks)
(b) A country, with no free health service, offers a tax-free savings account for medical expenses. At the start of the year you decide how much to pay into the account. When medical expenses are incurred you may withdraw up to this amount (with no tax paid) exclusively to pay for medical expenses (if the amount invested exceeds your medical expenses, you lose this money). Any medical expenses which exceed the amount you invested must be paid by you using income taxed at 35%. Your annual salary is $85,000, and you assume annual medical expenses are normally distributed with a mean of $1,700 and a standard deviation of $350. The following is the spreadsheet model which shows the results of one simulation in the cell range A16:D16.
i. For the simulation in the cell range A16:D16, explain how the value of $53,861 in cell D16 would have been calculated (with reference to relevant cells in the Excel spreadsheet model above). (6 marks)
ii. Write down an appropriate Excel function for cell C16, i.e. to determine the medical expenses above the amount invested. Explain your choice of function. (5 marks)
iii. One thousand simulations were run and the distribution of outcomes produced the following: Explain why the maximum amount of money left to you is $54,340. (4 marks)
iv. Based on the simulation results in iii., do you think investing $1,400 in the tax-free savings account is appropriate? Or should you invest more? Or less? Explain your answer. (4 marks)
A B D 1 Tax-free medical savings account 2 3 Inputs 4 Annual salary $85,000 5 Tax rate 35% 6 7 Distribution of yearly medical expenses (normal) 8 Mean $1,700 9 Stdev $350 10 11 12 Amount invested $1,400 13 14 Simulation Medical expenses above amount Money left 15 Medical expenses Tax invested 16 $1,879 $29,260 $479 $53,861 to you Money left to you $53,463 $54,340 5.0% 90.0% 0.0050 0.0045 0.0040 0.0035 0.0030 0.0025 0.0020 0.0015 0.0010 0.0005 0.0000 $52,800 $53,000 $53,200 53,400 $53,600 -008'ES$ $54,000 $54,200 $54,400 A B D 1 Tax-free medical savings account 2 3 Inputs 4 Annual salary $85,000 5 Tax rate 35% 6 7 Distribution of yearly medical expenses (normal) 8 Mean $1,700 9 Stdev $350 10 11 12 Amount invested $1,400 13 14 Simulation Medical expenses above amount Money left 15 Medical expenses Tax invested 16 $1,879 $29,260 $479 $53,861 to you Money left to you $53,463 $54,340 5.0% 90.0% 0.0050 0.0045 0.0040 0.0035 0.0030 0.0025 0.0020 0.0015 0.0010 0.0005 0.0000 $52,800 $53,000 $53,200 53,400 $53,600 -008'ES$ $54,000 $54,200 $54,400Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started