Exercise 1

Construct an amortization table in Excel to answer the following questions. You must use the corresponding Excel financial formulas whenever possible. Upon graduation, Federico Hernández, borrows $20,000 to finance a late model used car. The loan is made by a family member who was able to obtain an 8 % annual percent rate (APR). The loan is going to be payback in equal monthly payments over 5 years.

a) How much are the monthly payments?

b) How many total dollars of interest does Federico pay over the life of the loan?

c) How much of the third payment goes to pay interest? How much goes to pay principal?

d) How much of the 48th payment goes to pay interest? How much goes to pay principal?

e) Suppose that Federico decides to pay off at the end of year three. How much does he has to pay in order to pay off the loan in full.

Exercise 2

Construct a spreadsheet capable of generating the compound interest tables for any interest rate (i.e., if your change the interest rate the table you will automatically generate the values of the interest factors for that particular interest rate).

Please explain how to use and program each excel formula if possible

Thanks in advance

Decision #1:   Which set of Cash Flows is worth more now?

Assume that your grandmother wants to give you generous gift.  She wants you to choose which one of the following sets of cash flows you would like to receive:

Option A:  Receive a one-time gift of $ 10,000 today.    

Option B:  Receive a $1500 gift each year for the next 10 years.  The first $1500 would be

     received 1 year from today.              

Option C:  Receive a one-time gift of $18,000 10 years from today.

Compute the Present Value of each of these options if you expect the interest rate to be 3% annually for the next 10 years.   Which of these options does financial theory suggest you should choose?

       Option A would be worth $__________ today.

       Option B would be worth $__________ today.

       Option C would be worth $__________ today.

       Financial theory supports choosing Option _______

Compute the Present Value of each of these options if you expect the interest rate to be 6% annually for the next 10 years. Which of these options does financial theory suggest you should choose?

      Option A would be worth $__________ today.

       Option B would be worth $__________ today.

       Option C would be worth $__________ today.

      Financial theory supports choosing Option _______

Compute the Present Value of each of these options if you expect to be able to earn 9% annually for the next 10 years.  Which of these options does financial theory suggest you should choose?

      Option A would be worth $__________ today.

       Option B would be worth $__________ today.

       Option C would be worth $__________ today.

       Financial theory supports choosing Option _______

Decision #2:  Planning for Retirement

Erich and Mallory are 22, newly married, and ready to embark on the journey of life.   They both plan to retire 45 years from today.  Because their budget seems tight right now, they had been thinking that they would wait at least 10 years and then start investing $3000 per year to prepare for retirement.   Mallory just told Erich, though, that she had heard that they would actually have more money the day they retire if they put $3000 per year away for the next 10 years - and then simply let that money sit for the next 35 years without any additional payments – then they would have MORE when they retired than if they waited 10 years to start investing for retirement and then made yearly payments for 35 years (as they originally planned to do).   Please help Erich and Mallory make an informed decision:    

Assume that all payments are made at the END a year (or month), and that the rate of return on all yearly investments will be 7.2% annually.   

(Please do NOT ROUND when entering “Rates” for any of the questions below)

1. How much money will Erich and Mallory have in 45 years if they do nothingfor the next 10 years, then put $3000 per year away for the remaining 35 years?

2. How much money will Erich and Mallory have in 10 years if they put $3000 per year away for the next 10 years?

b2)  How much will the amount you just computed grow to if it remains invested for the remaining

35 years, but without any additional yearly deposits being made?

3. How much money will Erich and Mallory have in 45 years if they put $3000 per year away for each of the next 45 years?  

How much money will Erich and Mallory have in 45 years if they put away $250

4. per MONTHat the end of each month for the next 45 years?  (Remember to adjust 7.2% annual rate to a Rate per month!)

If Erich and Mallory wait 25 years (after the kids are raised!) before they put anything away for retirement,  how much will they have to put away at the end ofeach yearfor 20 years in order to have $1,000,000 saved up on the first day of their retirement 45 years from to

Question 1

The Horne Robinson Inc. has the following investment opportunities. Assume the discount rate the firm uses is 10%:

Required: Under NPV evaluation and assuming these machines are mutually exclusive, which machine(s) would Horne Robinson Inc. choose? Show all supporting calculations as required.

Question 2

A&B Enterprises is trying to select the best investment from among four alternatives. Each alternative involves an initial outlay of $100,000. Their cash flows follow:

Required: Evaluate and rank each alternative based on a) payback period and b) net present value (use an 8% discount rate).

"Bob got a fully amortizing 30 year fixed rate mortgage with monthly payments for $1,000,000 at an annual interest rate of 4.5%, compounded monthly. If Bob made the required monthly payment every month, how many dollars in interest will Bob pay in his 125th monthly payment?"

Blue Line machine shop is considering a four-year project to improve its production efficiency. Buying a new machine press for $570,000 is estimated to result in $240,000 in annual pretax cost savings. The press falls in the MACRS five-year class and it will have a salvage value at the end of the project of $96,000. The press also requires an initial investment in spare parts inventory of $30,000, along with an additional $3,500 in inventory for each succeeding year of the project. The shop tax rate is 30 percent and the project's required return is 8 percent.

A/ Calculate the NPV of this project

B/ Should the company buy and install the machine press?

How can a performance integrated budget be used to counter the archaic practice of across-the-board budget cuts?

A company is considering producing long telephoto zoom lens, iLongLens, attachment for the iPhone 5. The initial investment for this project will be $3 million. This amount is for depreciable equipment, which will be depreciated over 5 years using the straight-line method to zero book value. The iLongLens is expected to generate Earnings before Depreciation and Taxes of $1.5 million per year for 6 years. At the end of the sixth year the equipment will be scrapped for $300,000. The company’s tax rate is 40% and the appropriate discount rate for this project is 15%. a) List all 7 after-tax cash flows for this project (0 is the initial investment).

b) Compute the NPV.

c) Compute the IRR and payback.

6. Use the table below, based on data from students enrolled in BMGT 230/230B this semester, to answer/complete parts a. through e. (the cell values are frequencies):

a. Determine the probability a student selected at random is Conservative (meaning either Very Conservative or Somewhat Conservative).
b. Determine the probability a Male student selected at random is Conservative (meaning either Very Conservative or Somewhat Conservative).
c. Determine the probability a Female student selected at random is Conservative (meaning either Very Conservative or Somewhat Conservative).
d. Given your answers to Parts a, b, and c, are the two dimensions, Political Attitude and Sex, statistically independent?
e. Given your answer to Part d, are the Political Attitudes of Males and Females in the course generally Similar or Different?

Problem 12-07
Forecasted Statements and Ratios

Upton Computers makes bulk purchases of small computers, stocks them in conveniently located warehouses, ships them to its chain of retail stores, and has a staff to advise customers and help them set up their new computers. Upton's balance sheet as of December 31, 2016, is shown here (millions of dollars):

Sales for 2016 were $275 million and net income for the year was $8.25 million, so the firm's profit margin was 3.0%. Upton paid dividends of $3.3 million to common stockholders, so its payout ratio was 40%. Its tax rate was 40%, and it operated at full capacity. Assume that all assets/sales ratios, (spontaneous liabilities)/sales ratios, the profit margin, and the payout ratio remain constant in 2017. Do not round intermediate calculations.

1. If sales are projected to increase by $90 million, or 32.73%, during 2017, use the AFN equation to determine Upton's projected external capital requirements. Enter your answer in millions. For example, an answer of $1.2 million should be entered as 1.2, not 1,200,000. Round your answer to two decimal places.
   $  million
   
2. Using the AFN equation, determine Upton's self-supporting growth rate. That is, what is the maximum growth rate the firm can achieve without having to employ nonspontaneous external funds? Round your answer to two decimal places.
   %
   
3. Use the forecasted financial statement method to forecast Upton's balance sheet for December 31, 2017. Assume that all additional external capital is raised as a line of credit at the end of the year and is reflected (because the debt is added at the end of the year, there will be no additional interest expense due to the new debt).
   Assume Upton's profit margin and dividend payout ratio will be the same in 2017 as they were in 2016. What is the amount of the line of credit reported on the 2017 forecasted balance sheets? (Hint: You don't need to forecast the income statements because the line of credit is taken out on last day of the year and you are given the projected sales, profit margin, and dividend payout ratio; these figures allow you to calculate the 2017 addition to retained earnings for the balance sheet without actually constructing a full income statement.) Round your answers to the nearest cent.

   Upton Computers Pro Forma Balance Sheet December 31, 2017 (Millions of Dollars)
   Cash	$
   Receivables	$
   Inventories	$
   Total current assets	$
   Net fixed assets	$
   Total assets	$
   Accounts payable	$
   Notes payable	$
   Line of credit	$
   Accruals	$
   Total current liabilities	$
   Mortgage loan	$
   Common stock	$
   Retained earnings	$
   Total liabilities and equity	$

Assume that the Treasury bond futures price is 108-09. Which of the following four bonds is cheapest to deliver (CTD)?

Your division is considering two projects with the following cash flows (in millions):

1. What are the projects' NPVs assuming the WACC is 5%? Round your answer to two decimal places. Do not round your intermediate calculations. Enter your answer in millions. For example, an answer of $10,550,000 should be entered as 10.55. Negative value should be indicated by a minus sign.
   Project A    $   million
   Project B    $   million

   What are the projects' NPVs assuming the WACC is 10%? Round your answer to two decimal places. Do not round your intermediate calculations. Enter your answer in millions. For example, an answer of $10,550,000 should be entered as 10.55. Negative value should be indicated by a minus sign.
   Project A    $   million
   Project B    $   million

   What are the projects' NPVs assuming the WACC is 15%? Round your answer to two decimal places. Do not round your intermediate calculations. Enter your answer in millions. For example, an answer of $10,550,000 should be entered as 10.55. Negative value should be indicated by a minus sign.
   Project A    $   million
   Project B    $   million

2. What are the projects' IRRs assuming the WACC is 5%? Round your answer to two decimal places. Do not round your intermediate calculations.
   Project A   %
   Project B   %

   What are the projects' IRRs assuming the WACC is 10%? Round your answer to two decimal places. Do not round your intermediate calculations.
   Project A   %
   Project B   %

   What are the projects' IRRs assuming the WACC is 15%? Round your answer to two decimal places. Do not round your intermediate calculations.
   Project A   %
   Project B   %

3. If the WACC was 5% and A and B were mutually exclusive, which project would you choose? (Hint: The crossover rate is 90.37%.)
   -Select-Project AProject - BNeither or A, nor B

   If the WACC was 10% and A and B were mutually exclusive, which project would you choose? (Hint: The crossover rate is 90.37%.)
   -Select-Project AProject - BNeither or A, nor B

   If the WACC was 15% and A and B were mutually exclusive, which project would you choose? (Hint: The crossover rate is 90.37%.)
   -Select-Project AProject - BNeither or A, nor B

A pension fund manager is considering three mutual funds. The first is a stock fund, the second is a long-term government and corporate bond fund, and the third is a T-bill money market fund that yields a rate of 3.0%. The probability distribution of the risky funds is as follows:

The correlation between the fund returns is 0.18.

Solve numerically for the proportions of each asset and for the expected return and standard deviation of the optimal risky portfolio

A pension fund manager is considering three mutual funds. The first is a stock fund, the second is a long-term government and corporate bond fund, and the third is a T-bill money market fund that yields a sure rate of 3.0%. The probability distributions of the risky funds are: Expected Return Standard Deviation Stock fund (S) 12% 41% Bond fund (B) 5% 30% The correlation between the fund returns is 0.0667. What is the expected return and standard deviation for the minimum-variance portfolio of the two risky funds?

search current news (less than 6 months old) and find an article about a company reporting key financial news (e.g., landing a large contract, reporting unusual profits or losses, expressing concern for future profitability, etc.). Briefly review the news item and include the proper APA citation for that article. Why was your chosen financial event newsworthy, i.e., why do you believe this news was important? Also, why did this particular article catch your attention? Please do not duplicate articles; before you review an article, be sure no one else has already posted a review on that article. Your instructor is likely to have questions/comments on your original post so be sure to be prepared for those potential questions.