Question Details

(solution) Can someone please help me with this. I need to use cell


Can someone please help me with this. I need to use cell referencing in the excel sheet in conjunction with  word


Case 1 Discounted Cash Flow Analysis Discounted Cash Flow Analysis Davis, Michaels, and

 

Company 0

 

Copyright © 1994 The Dryden Press. 1 rights reserved.

 

All

 

1 Part I Fundamental Concepts Tom Davis was born and raised in San Francisco and served as a

 

Navy lieutenant in Vietnam. After his discharge he used the GI Bill to

 

attend NYU, where he received his degree in finance and held a

 

part-time job at Steel, Robbins, Hernandez, and Associates, a

 

regional brokerage firm headquartered in New York City. After

 

graduation he was offered a permanent position with Steel, which he

 

gladly accepted. While at the firm, Tom became friends with Gene

 

Michaels, a Stanford MBA who had been working as a financial

 

analyst with the company for just over a year. Although Tom enjoyed

 

his work, his ultimate goal was to open a financial consulting firm in

 

his hometown. After five years, Tom managed to save enough

 

commissions to realize his goal. He convinced Gene to become his

 

partner and to move to San Francisco to open their own financial

 

consulting firm, Davis, Michaels, and Company.

 

Davis, Michaels, and Company provides financial planning

 

services to upper-middle-class professionals. Basically, the firm

 

provides consulting services in the areas of income tax planning,

 

investment planning, insurance planning, estate planning, and

 

employee benefits planning for small, family-owned businesses. The

 

firm is heavily involved in the Chinese community where Tom has

 

close ties. Also, both he and Gene speak fluent Chinese. The firm

 

does not have a tax lawyer or CPA on its staff, so Tom and Gene hire

 

outside experts when a problem arises which they cannot handle,

 

but this is rare.

 

Business has been good, perhaps too good. Tom and Gene have

 

been working overtime to handle the load, and no end is in sight. In

 

fact, Tom recently turned away several potential customers because

 

he didn?t think that the firm could offer them the high degree of

 

personal service that it usually gives its customers. As a permanent

 

solution, he is talking to career resource center personnel at several

 

universities.

 

He hopes to hire a finance major who can start work

 

immediately after graduation, but that is still several months away.

 

In the meantime, Tom believes that Janet Ho, the firm?s top

 

secretary, can handle various financial analysis duties after turning

 

over some of her clerical duties to someone else. Janet has been

 

taking night courses in business at a community college, and she is

 

convinced that she can handle increased responsibilities. Tom has a

 

great deal of faith in Janet?she has been with the firm from the very

 

beginning, and her great personality and sound work ethic have

 

contributed substantially to the firm?s success. Still, Tom knows that

 

there is little room for error in this business. Customers must be

 

confident that their financial plans are soundly conceived and 2 Case 1 Discounted Cash Flow Analysis properly implemented. Any mistakes create instant mistrust, and the

 

word spreads quickly.

 

To make sure that Janet has the skills to do the job, Tom plans to

 

give her a short test. As far as Tom is concerned, the single most

 

important concept in financial planning, whether it be personal or

 

corporate, is discounted cash flow (DCF) analysis. He believes that if

 

Janet has solid skills in this area, then she will be able to succeed in

 

her expanded role with minimal supervision. The basis for the test is

 

an actual analysis that Tom is currently working on for one of his

 

clients. The client has $10,000 to invest with a goal of accumulating

 

enough money in 5 years to pay for his daughter?s first year of

 

college at a prestigious Ivy League school. He has directed Tom to

 

evaluate only fixed interest securities (bonds, bank certificates of

 

deposit, and the like) since he does not want to put his daughter?s

 

future at risk.

 

One alternative is to invest the $10,000 in a bank certificate of

 

deposit (CD) currently paying about 10 percent interest. CDs are

 

available in maturities from 6 months to 10 years, and interest can

 

be handled in one of two ways?the buyer can receive interest

 

payments every 6 months or reinvest the interest in the CD. In the

 

latter case, the buyer receives no interest during the life of the CD,

 

but receives the accumulated interest plus principal amount at

 

maturity. Since the goal is to accumulate funds over 5 years, all

 

interest earned would be reinvested.

 

However, Tom must also evaluate some other alternatives. His

 

client is considering spending $8,000 on home improvements this

 

year, and hence he would have only $2,000 to invest. In this

 

situation, Tom?s client plans to invest an additional $2,000 at the end

 

of each year for the following 4 years, for a total of 5 payments of

 

$2,000 each. A final possibility is that the client might spend the

 

entire $10,000 on home improvements and then borrow funds for his

 

daughter?s first year of college.

 

To check your skills at DCF analysis, place yourself in Janet?s

 

shoes and take the following test. Questions

 

1. Consider a 1-year, $10,000 CD.

 

a. What is its value at maturity (future value) if it pays 10.0

 

percent (annual) interest? 3 Part I Fundamental Concepts b. What would be the future value if the CD pays 5.0 percent?

 

If it pays 15.0 percent? c. The First National Bank of San Francisco offers CDs with a

 

10.0 percent nominal (stated) interest rate but compounded

 

semiannually. What is the effective annual rate on such a

 

CD? What would its future value be? d. Pacific Trust offers 10.0 percent CDs with daily

 

compounding. What is such a CD?s effective annual rate and

 

its value at maturity? e. What nominal rate would the First National Bank have to

 

offer to make its semiannual compounding CD competitive

 

with Pacific?s daily-compounding CD? 2. Now consider a 5-year CD. Rework Parts a through d of Question

 

1 using a 5-year ending date.

 

3. It is estimated that in 5 years the total cost for one year of

 

college will be $20,000.

 

a. How much must be invested today in a CD paying 10.0

 

percent annual interest in order to accumulate the needed

 

$20,000? b. If only $10,000 is invested, what annual interest rate is

 

needed to produce $20,000 after 5 years? c. If only $10,000 is invested, what stated rate must the First

 

National Bank offer on its semiannual compounding CD to

 

accumulate the required $20,000? 4 Case 1 Discounted Cash Flow Analysis 4. Now consider the second alternative?5 annual payments of

 

$2,000 each. Assume that the payments are made at the end of

 

each year.

 

a. What type of annuity is this? b. What is the future value of this annuity if the payments are

 

invested in an account paying 10.0 percent interest

 

annually? c. What is the future value if the payments are invested with

 

the First National Bank which offers semiannual

 

compounding? d. What size payment would be needed to accumulate $20,000

 

under annual compounding at a 10.0 percent interest rate? e. What lump sum, if deposited today, would produce the same

 

ending value as in Part b? f. Suppose the payments are only $1,000 each, but are made

 

every 6 months, starting 6 months from now. What would be

 

the future value if the 10 payments were invested at 10.0

 

percent annual interest? If they were invested at the First

 

National Bank which offers semiannual compounding? 5. Assume now that the payments are made at the beginning of

 

each period. Repeat the analysis in Question 4.

 

6. Now consider the following schedule of payments:

 

End of Year

 

0

 

1

 

2

 

3

 

4

 

5 Payment

 

$2,000

 

2,000

 

0

 

1,500

 

2,500

 

4,000 a. What is the value of this payment stream at the end of Year

 

5 if the payments are invested at 10.0 percent annually? b. What payment today (Year 0) would be needed to

 

accumulate the needed $20,000? (Assume that the

 

payments for Years 1 through 5 remain the same.) 5 Part I Fundamental Concepts 7. Consider Bay City Savings Bank, which pays 10.0 percent

 

interest compounded continuously.

 

a. What is the effective annual rate under these terms? b. What is the future value of a $10,000 lump sum after 5

 

years? c. What is the future value of a 5-year ordinary annuity with

 

payments of $2,000 each? 8. The client is also considering borrowing the $20,000 for his

 

daughter?s first year of college and repaying the loan over a

 

four-year period. Assuming that he can borrow the funds at a

 

10 percent interest rate, what amount of interest and principal

 

will be repaid at the end of each year?

 

9. Assume that you are given a set of cash flows on a time line and

 

asked to find their present value. How would you choose the

 

discount rate to apply to these flows?

 

10. If you are using the Lotus 1-2-3 model, first examine the model

 

closely to see how it works and then complete the model. Don?t

 

hesitate to change input values to obtain a better grasp of the

 

model. Also, don?t forget to look at the graphs. After you are

 

thoroughly familiar with the model, write a short summary of

 

Lotus?s DCF capabilities. Include not only what spreadsheets can

 

do, but how they can be used in financial management decision

 

making. 6

 


Solution details:

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Sep 13, 2020

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.

Order Now