# Create a timeline in Excel for Natasha’s current situation, as well as the certification program and MBA degree options using the assumptions given the case.

Create a timeline in Excel for Natasha’s current situation, as well as the certification program and MBA degree

options using the assumptions given the case.

Natasha is currently 30 years old, and she plans to work until she retires at age of 65. Thus, Natasha plans to

work for 35 years. She is currently earning $38,000 a year.

Natasha wants to improve her life either by earning a certification in networking or earning MBA degree.

The certification program takes 1 year to complete and costs $5,000 payable at the beginning of the year.

The MBA degree takes 3 years to complete and costs $25,000 per year payable at the beginning of each year.

If Natasha completes the certification program, her income will increase by $10,000 more than what she is

currently earning. The initial base pay differential of $10,000 will increase by 3% (growth rate of salary

differential) each year.

If Natasha decides to complete the MBA degree, her income will increase by $20,000 more than her current

income and the initial base pay differential of $20,000 will increase by 3% (growth rate of salary differential)

each year.

Find the cash flows (salary differential) for the certification option from Year 0 (at age 30) through Year 35 (at

age 65). Note that the cash flow at Year 0 or at age 30 is negative value of the cost paid for the certification. For

Year 1, the cash flow should be 0 since the program takes 1 year to complete, therefore, Natasha’s pay

differential will start in Year 2. Then her pay differential will increase by 3% each year.

Follow the instructions in Required 1 in order to determine the discount rate. You are required to use the

www.treasury.gov to find the March 16, 2016 30-year treasury yield. www.treasury.gov < Data < Daily

Treasury Yield Curve rates < Enter 2016 in date box then look for March 16, 2016 and use 30-Year Treasury

Yield.

Compute the PV of salary differential for completing the certification program. Use only salary differential. Do

not include her current pay of $38,000 in your computations. To compute the PV of salary differential, use the

Excel function of NPV. If the discount rate is in B4, Year 1 cash flow is in D10 and Year 35 cash flow is in S10

for example, then the formula should be as follow:

=NPV(B4,D10:S10)

Note that this is just an example. You should be able to use correct cells based where your data are.

Compute the NPV = PV of salary differential – cost of certification.

Note that cost of certification should be in Year 0. If the cost of certification is entered positive then to find the

NPV, you should do PV of salary differential – cost of certification. If the cost of certification is entered as

negative value in Excel, then NPV = PV + cost of certification. Just make sure that YOU DO NOT ADD the

cost of certification to PV of salary differential.

Find the cash flows (salary differential) for the MBA degree option. Note that the MBA program lasts for 3

years and costs $25,000 a year starting today. Thus, the cash flows in Year 0, Year 1, Year 2 should be the tuition

paid. Since the MBA program lasts 3 years, Natasha’s salary differential of $20,000 will start in Year 4 and will

increase by 3% per year.

Compute the PV of salary differential for the MBA degree option. Insert 0 for salary differential for Year 1, year

2, and Year 3 since the salary differential starts in Year 4 after completing the MBA. Use the NPV function of

Excel in order to compute the PV of salary differential for the MBA degree.

Compute the PV of costs of MBA program.

Compute the NPV = PV of salary differential – PV of cost of MBA degree

Source link