Financial Analysis Exercises $(400$ Points$)$

The Financial Analysis Exercises are intended to allow the student to explore deeper into building

a financial portfolio and to develop skills that will be necessary in the real world. There will be a

total of $4$ financial analysis exercises. The first exercise is an emphasis on sharpening Excel and

time value of money skill set. The other three exercises will be interrelated. Each exercise is worth

$100$ points toward the final grade.

$$ Exercise #$1$ is due Module $2$

$$ Exercise #$2$ is due Module $4$

$$ Exercise #$3$ is due Module $6$

$$ Exercise #$4$ is due Module $8$

Download and view the example of each component for more guidance. Make sure you

review the rubric for specific requirements and criteria.

Financial Analysis Exercise #$1(100$ Points$)$

This exercise will require both a written and Excel spreadsheet component. It is critical to

demonstrate the appropriate Excel skills. There are $10$ mini projects that you are required to

complete. Carefully read additional requirements for each mini project. Using APA format, you

must also complete a written part for each mini$-$project, making a recommendation based on

the results that you obtain in Excel. Be thorough, include an introduction and conclusion to the

whole body of the mini$-$project

$$ Submit a Word file for the written component, addressing all the key points along with

appropriate and relevant examples. APA format is required.

$$ Submit a spreadsheet Excel file for the spreadsheet component, demonstrating the use

of formulas, cell referencing, and calculation with appropriate set up$.$

This exercise #$1$ is due by the end of Module $2.$

Scenario

You are the newest Financial Analyst in Investments, you need to demonstrate your prowess in

Excel, your outstanding written skills and ability to communicate.

Mr$.$ Richards is the Executive Vice President and Chief Investment officer in your new firm. You

are being asked to complete a series of $$pet$$ projects for Mr$.$ Richards. You have been told not

to try to impress him, just do the work and stick to the facts. There are ten mini$-$projects for you to

complete. Each mini$-$project needs to have its own tab in an Excel spreadsheet. It is critical to

use cell referencing and the standard setup for TVM problems provided, he$$s a stickler and

wants to see all of the detail.

Mini$-$Projects

$1.$ A friend of Mr$.$ Richards recently won a law suit for $$30$ million. They have the ability to

either take the payments over $10$ years or settle today for cash of $$25$ million today. Mr$.$

Richard is optimistic that he can earn a $6\%$ return on the money and that they should

settle for $$25$ million today and he will invest it for them.

Excel:

You$$ll need to demonstrate the present value of the $$30$ million today versus the future value of

the $$25$ million in $5$ years to make your argument.

$2$

Written:

Briefly describe which settlement is maximizing the value for the client and explain why?

$2.$ A client of Mr$.$ Richards wants to purchase a large commercial building. The building

costs $$20$ million and he will make a down payment of $15\%$ and finance the rest with a

local bank. The bank terms are $10-$year bullet loan with $30-$year amortization at $4\mathrm{.}5\%$

interest.

The building earns annual rent of $$2,500,000$ and it pays annual taxes of $$1,000,000$ per

year. What will be the annual cash flow from the building? What will be the ROI on the

investment? Given only this information, what are some of the obvious pieces missing

that also need to be considered? What is your recommendation?

Excel:

Using the basic TVM setup, calculate the mortgage for the property and calculate the cash

flow, and ROI for this project.

Written:

Briefly describe the analysis that you have performed, why how you calculated the cash flow,

mortgage payment and ROI. Briefly give your recommendation and the list any missing risks that

should be considered.

$3.$ A client of Mr$.$ Richards wants to purchase one of three bonds:

a$)10-$year corporate bond with a $2\mathrm{.}00\%$ coupon, paying annually, and par value of

$$1,000.$

b$)7-$year corporate bond with a $1\mathrm{.}75\%$ coupon, paying annually and par value of

$$1,000.$

c$)5-$year corporate bond with a $1\mathrm{.}50\%$ coupon, paying annually and par value of

$$1,000.$

What are the current prices for each of these bonds? How will the value of these bonds change

if the respective market rates increase by $50$ basis points? How will the value of these bonds

change if their respective market rates decrease by $50$ basis points? What recommendation

would you make about purchasing one of these three bonds? Would you suggest any further

analysis that might include the use of a relative interest rate risk measure used for bonds?

Excel:

Using the Basic TVM setup, calculate the value of these bonds, where FV is the par value, pmt is

the current coupon $($rate $*$ par $/$ payment frequency$),$ PV is the current price, rate is the current

market rate or in this case the coupon and rate changes, NPR is the years $*$ M$.$ F