Section II. Three questions - 30 points (Excel TAB3) Ian received an undergraduate degree in finance three years ago and has been employed all this time at a mid-size wealth management company. His initial salary was $73,000 per year and increased 3.0% every year. Ian decided that his three-year stint at this employer has prepared him well for an MBA degree. Page 1 of 2 MGT160 Individual Data Case 2: 50 points Complete your work in Excel and submit to Canvas by due date/time. Clearly mark (highlight) your answers He applied to several schools and was accepted by one of the top-10 programs in the county. Ian notified his manager about his decision yesterday, but today the manager made him the following offer: Ian will be assigned to lead a very important project with immediate salary increase of 15%. The manager assured him that for the following three years his annual raises will be at least 7.0% and then will grow at 2.5% per year. Ian is planning to make his decision (quit and go to school or stay and lead the project) based on the present value of future earnings in the next 30 years (ignore taxes). He plans to stop working in exactly 30 years regardless of his decision and become a gentleman farmer. 1. Build a spreadsheet with Ian's cash flows for the next 30 years and calculate present value of future earnings for two options: (1) stay and lead the project and (2) enroll into MBA program; assume the following: Ian estimates that the two-year MBA program will cost $125,000 per year in tuition and other expenses (assume the payments are made at the end of the year and Ian has sufficient savings, which are currently held in a checking account, which pays no interest); his starting salary after MBA is expected to be $145,000 and will grow 2.5% per year, the same growth rate that Ian expects his salary will grow at his current employer after the 7.0% raises are over. Ian thinks that the appropriate discount rate is equal to 10-yr Treasury rate in December 2021 + 4.50% risk premium (use Treasury rate from Section I - do not forget to divide by 100 to turn that number into a fraction). What option should Ian pick? 2. What should the immediate adjustment to his current salary be to make Ian indifferent between the two options? (Hint: use solver function in Excel) 3. Discuss your findings. What other considerations should lan take into account in making his decision? Can they be incorporated into the NPV framework? Section II. Three questions - 30 points (Excel TAB3) Ian received an undergraduate degree in finance three years ago and has been employed all this time at a mid-size wealth management company. His initial salary was $73,000 per year and increased 3.0% every year. Ian decided that his three-year stint at this employer has prepared him well for an MBA degree. Page 1 of 2 MGT160 Individual Data Case 2: 50 points Complete your work in Excel and submit to Canvas by due date/time. Clearly mark (highlight) your answers He applied to several schools and was accepted by one of the top-10 programs in the county. Ian notified his manager about his decision yesterday, but today the manager made him the following offer: Ian will be assigned to lead a very important project with immediate salary increase of 15%. The manager assured him that for the following three years his annual raises will be at least 7.0% and then will grow at 2.5% per year. Ian is planning to make his decision (quit and go to school or stay and lead the project) based on the present value of future earnings in the next 30 years (ignore taxes). He plans to stop working in exactly 30 years regardless of his decision and become a gentleman farmer. 1. Build a spreadsheet with Ian's cash flows for the next 30 years and calculate present value of future earnings for two options: (1) stay and lead the project and (2) enroll into MBA program; assume the following: Ian estimates that the two-year MBA program will cost $125,000 per year in tuition and other expenses (assume the payments are made at the end of the year and Ian has sufficient savings, which are currently held in a checking account, which pays no interest); his starting salary after MBA is expected to be $145,000 and will grow 2.5% per year, the same growth rate that Ian expects his salary will grow at his current employer after the 7.0% raises are over. Ian thinks that the appropriate discount rate is equal to 10-yr Treasury rate in December 2021 + 4.50% risk premium (use Treasury rate from Section I - do not forget to divide by 100 to turn that number into a fraction). What option should Ian pick? 2. What should the immediate adjustment to his current salary be to make Ian indifferent between the two options? (Hint: use solver function in Excel) 3. Discuss your findings. What other considerations should lan take into account in making his decision? Can they be incorporated into the NPV framework