have to show you full work in problems 1 and 2 in order to get the full credit.
spreadsheet with your answers for problem 3to be emailed to me separately. (All
functions have to be there; numbers without functions are not accepted). Use
Excel Functions we introduced in our notes to solve Q3
main goal of this project is to learn some useful techniques in calculating
TVM, NPV, and financial modeling functions using excel.
highly advise you to do this project on your own first and then compare your
answers with others.
on the birth of your daughter! Now is a
good time to start saving for her college education. Your spouse’s parents have offered to give
you $10,000 today to begin the fund.
Your parents are currently selling a large quantity of land. They expect to contribute an additional
$12,500 to your daughter’s college fund 3 years from today. You have no money of your own currently
saved, but you plan to begin a monthly savings plan at the end of this
month. You predict that your daughter
will need annual tuition payments once per year (assume they are at the
beginning of the year) with the first payment at her 18th birthday
when she enrolls in school.
Additionally, she will need annual living expenses at the beginning of
each year, starting with her 18th birthday and ending with her 21st
birthday. If she were starting college
today, tuition would $12,000 per year, and living expenses would be $12,000 per
year. Living expenses are payable on the
first day of each year. Assume that she will complete college in four years.
Assume that the cost of living and your savings will rise at the rate of
inflation which you expect to be 3% per year.
Assume that tuition costs will rise at the rate of 4% annually.
that you will begin a monthly savings program which will start one month from
today and that you will save through to the date of her last tuition payment
(her 21st birthday). You will
save $176.76 in the first month and your savings will increase at the rate of
inflation. Based on these plans, how
much money will you have on the date she begins college to buy her a car? The interest rate is 8%. All interest rates, growth rates, and
inflation rates are EAR’s. (25 points)
2. Donovan Industries, a large consumer
durables manufacturer, is introducing a new line of refrigerators for college
dorm rooms. Donovan will have to build a
new plant to manufacture the refrigerators.
This plant will cost $15 million to build. Two-thirds of the initial investment is
payable today, and the remaining one-third is due in one year. The new plant will start operating in year 2
and will produce 125,000 refrigerators per year. The price of the refrigerators would be $175
if Donovan could introduce the refrigerators today. Variable costs of production would be $65 if
Donovan could start production today. All of the variable costs are incurred
solely due to the new product.
Similarly, annual fixed incremental costs of production of $4.5 million
in today’s dollars will be incurred each year.
In addition, $1.25 million of allocated overhead based on the expected
annual costs of Donovan’s headquarters staff and firm-level costs has been
assigned by the accounting system. These
costs will be incurred regardless of whether Donovan undertakes the new project
or not. Donovan’s tax rate is 25%. The project is expected to be terminated
after 4 years of operations. Depreciate
the new plant down on the straight line basis over 4 years with no assumed salvage
value. Assume that refrigerator prices
and fixed and variable costs of production will increase at the rate of 4% per
year throughout the life of the project.
(a) Calculate the nominal cash flows for each relevant year.
(b) Suppose the required return for the project is 9%. What is the NPV? Should you accept the
project? Why? (Assume cash flows follow the end-of-year convention)
(c) Calculate the payback on this project. If the required payback is 4 years, and cash
flows follow the end of year convention, what is your recommendation for this
project? (25 points)
3.The following problems to be done on
using the spreadsheet, each on a separate sheet.
(a) Suppose that Keown
Motor Corp. paid a dividend per share of $0.62 last year. How long will it take Keown’s dividend to
increase by 50% if its dividends grow at a rate of 10% per year?(5 points)
(b) If Keown Motor had
earnings per share of $0.75 last year, at what growth rate will earnings have
to grow so that earnings per share and dividends per share are equal amounts
using the time period that you calculated in (a)?(5 points)
(c) Suppose that you
wish to purchase a $320,000 house. You
will pay 20% down and finance the balance with a 15-year mortgage at a rate of
4.80% (APR). What will your monthly
payment be?(5 points)
Suppose that your employer has a unit that bottles drinks. You need to replace the existing machine that
is used to bottle water. The Bottle
Master machine will cost you $1,020,000, has a 6 year life, and has before tax
cash operating costs of $50,000 per year.
Alternatively, Packard Machinery has a bottling machine that will cost
you $450,000, has a 3 year life, and has before tax cash operating costs of
$75,000 per year. Assume that
depreciation expense is calculated for both tax and reporting purposes using
the straight line method with no salvage value, the required return is 6%, and
that your tax rate is 40%. Assume the
productivity of the 2 machines is the same and the revenue streams associated
with them are equal. Compute first the after tax annual cash flow for each
machine, and then use a replacement chain to recommend which machine to
purchase. (10 points)
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more