Please answer these questions related to the case, which is due September 29th. Please upload to Canvas the excel spreadsheet showing your work. Be sure to use formulas and cell references as much as possible in your spreadsheet. Note that there is not necessarily one right answer, so it is important to provide your logic. Please turn in one case per group, with the names of the members of the group on the case.

1. Determine Sophia's approximate biweekly take-home pay assuming her effective state income tax rate is 4%.

Hints:

Assume 1 allowance in the take-home pay calculation. Also, initially assume no other additional monthly deductions. Take-home pay can be determined using IRS Circular E information. However an easier approach for estimation is to use of one of several websites such as

www.calculator.net/take-home-pay-calculator.html

(Note that this calculator takes into account how many pay periods there are in a year, so just use the bi-weekly pay frequency).

2. do a monthly budget for Sophia using Excel. That is, determine her after-tax take home pay each month (cash inflow), and estimate her expenses each month (cash outflows). While you may not agree with her planned expenses (e.g., does she really need to spend that much on clothes?), simply treat these planned expenses as a given. Also, assume that the monthly income/expenses are the same each month (e.g., you don't need to take into account any special expenditures that occur upfront with regards to rent).

3. Calculate how much Sophia will have for discretionary spending each month considering her estimated "budget" presented above. In other words, after taking into account her anticipated cash inflow and cash outflows, how much is left over each month?

4. Determine how much Sophia should have in an emergency cash fund for unexpected expenditures. Please be sure to provide your logic.

5. How should the discretionary amount calculated above be spent? Assume she wants to be an aggressive saver. How much is added to her 401K annually when matched by her employer?

(Hint: Assume she wants to max out her 401 contributions. Why? Two big benefits are that these contributions are not taxed until withdrawn after retirement, and the employer will match

the contribution. So, it makes a lot of sense to take advantage of this. Start with the excess of monthly net pay over budgeted expenditures (calculated in #3) for 401K contributions and enter this as the monthly deduction in the take-home pay calculator above.)

6. How should Sophia allocate her investment assets (e.g., the $50,000 from her uncle) given the four fund options presented? Remember that Sophia is young, so time is on her side. Justify your answers.

Hint: You can use the AAII web site asset allocations for someone her age.

http://www.aaii.com/asset-allocation#aggressive?adv=yes

7. If she invests aggressively with her $50,000, her contributions to her 401K, and the hospital's 15% match, estimate how much will be in her investment account and her 401K in ten years. (Hint: Use the AAII 10 year average return to compute her 401K balance in 10 years.) Don't forget about the time value of money and the beauty of compounding.

8. If Sophia keeps allocating her investments in the same manner and gets the same anticipated returns for her entire career, what will be the balance of her investment account and 401K when she retires after working forty years? Disregard any tax implications on the investment account earnings.