Excel Needed: Two Financing questions. Must A

timer Asked: Jan 28th, 2021

Question Description

Need to good at Excel and business finance.

Only need to work on excel, I Will Provide you excel sample. Must A

Unformatted Attachment Preview

Starting salary $ Annual salary increases - for all years 10-year Treasury Risk premium Discount rate Salary 1 2 3 4 5 $ $ $ $ $ Total Present value 50,000.00 51,500.00 53,045.00 54,636.35 56,275.44 50,000 3.00% you can enter a different rate for e 5.00% 2.00% 7.00% Salary increases 3.00% 3.00% 3.00% 3.00% Discount factor 0.93 0.87 0.82 0.76 0.71 Using Goal Seek (Solver) n enter a different rate for each year Annual salary increases - for year 1 Present value $ $ $ $ $ 46,728.97 44,982.09 43,300.52 41,681.81 40,123.61 $ 216,817.01 1 2 3 4 5 Salary Salary increases Discount factor $ 50,000.00 0.93 $ 61,547.29 23.09% 0.87 $ 63,393.71 3.00% 0.82 $ 65,295.52 3.00% 0.76 $ 67,254.39 3.00% 0.71 Total Present value Scenario: you are wondering what must be the end of Yea increase to make the total present value equal to exactly $250,000.00 Go to Data>>What if analysis>>Goal seek Set cell >> click on L15 To value>> type in 250000 By changing cell>> click on L3 Click OK Note: cell L3 must be a value, not a reference to another a formula; 23.09% Present value $ 46,728.97 $ 53,757.79 $ 51,748.15 $ 49,813.64 $ 47,951.45 $ 250,000.00 must be the end of Year 1 value equal to exactly oal seek a reference to another cell with Section I. 10 points (Excel TAB1) Expected inflation is an important consideration in financial planning and the valuation process. 1. Based on 10-year Treasury nominal and inflation-adjusted (real) rates, calculate expected inflation on the following dates (present the rates and your calculations in a table): a. September 1, 2016 (before election) b. January 12, 2017 (after election) c. September 1, 2020 (before election) d. January 12, 2021 (after election) 2. Generate a graph of expected inflation starting on January 4, 2016 to present (daily). Make sure to adjust the data to decimals before doing any calculations (divide by 100). There are two ways to get the data from FRED: a) Directly from Excel – _download and install Excel Add-in using this link (you need to provide your e-mail and pick appropriate Excel version): https://research.stlouisfed.org/fred-addin/ b) Download data from FRED website: https://fred.stlouisfed.org/ Enter data codes (provided below) into Search Data codes: 10-Year Treasury Constant Maturity Rate: DGS10 10-Year Treasury Inflation-Indexed Security, Constant Maturity: DFII10 MGT160 Section II. 40 points (Excel TAB2) Robert 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 $70,000 per year and increased 3.5% every year. Robert decided that his three-year stint at this employer has prepared him well for an MBA degree. He applied to several schools and was accepted by one of the top-10 programs in the county. Robert notified his manager about his decision yesterday, but today the manager made him the following offer: Robert will be assigned to lead a very important project with immediate salary increase of 10%. The manager assured him that for the following four years his annual raises will be at least 7.0% or double his previous raises. Robert 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 25 years (ignore taxes). He plans to stop working in exactly 25 years regardless of his decision and become a gentleman farmer. 1. Build a spreadsheet with Robert’s_ _c_a_s_h_ _f_l_o_w_s_ _f_o_r_ _t_h_e_ _n_e_x_t_ _2_5 years and calculate the present value of future earnings for two options: (1) stay and lead the project and (2) enroll into MBA program; assume the following: Robert estimates that the two-year MBA program will cost $100,000 per year in tuition and other expenses (assume the payments are made at the end of the year); his starting salary after MBA is expected to be $130,000 and will grow 2.5% per year, the same growth rate that Robert expects his salary will grow at his current employer after the 7.0% raises are over. Robert thinks that the appropriate discount rate is equal to 10-yr Treasury rate (nominal) on January 12, 2021 + 3.50% (from Section I). What option should Robert pick? 2. What should be the immediate adjustment to his current salary to make Robert indifferent between the two options? (Hint: use solver function in Excel) 3. Discuss your findings. What other considerations should Robert take into account in making his decision? Can they be incorporated into the PV (present value) framework? Enter your comments in a text box. ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.
Tags: sfsu

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Questions
Related Tags

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors