The Acme Company must solve a series of five problems that require you
to apply the concept of "time value of money," or TVM. The five
problems are listed below. Solving them will require the use of
Microsoft Excel and these TVM tables (downloadable Word document).
Before you begin your work, each student is to select a unique
nine-digit random number that contains no zeros, no "patterns," and
should use most of the digits between one and nine. This value will be
referred to as the student unique number (SUN). Further, digits within
the SUN are read from left to right. For example, if the SUN =
123456789, the first digit = 1, the second digit = 2, etc.
1. Acme plans to construct a new manufacturing facility in 12 years.
It is estimated that the cost in 12 years will be SUN (use all 9
digits). If Acme sets aside the money today in an account earning A%
(A = the first digit of SUN), how big should this lump sum deposit be?
Here is a HINT for solving this (and subsequent problems): Begin by
using a value that can be found in the tables (e.g., n = 5 years) to
solve this problem. Next, use an Excel TVM function to again solve the
same problem. (The Help function within Excel will provide you with
the necessary formula and format.) If you get matching results, you
probably utilized the Excel function correctly. Finally, remember to
replace your "test" value with the correct number to obtain your final
result.
2. Acme has decided to establish a sinking fund for its outstanding
preferred stock issue. SUN (use all 9 digits) represents the amount of
the issue that will be retired in 23 years. At the end of each of the
next 23 years, SUN will deposit an equal amount into an account that
earns B% (B = the second digit of SUN). What is the value of this
periodic deposit? Please restrict your assumed changes to the discount
rate, the FCFs, and/or the terminal value.
3. One of Acme's new projects will generate the following cash flows
at the end of each of the next three years: year 1 = SUN digits 1-3;
year 2 = SUN digits 4-6; year 3 = SUN digits 7-9. If these cash flows
are discounted at 11%, what is the sum of their present values? Note:
Each student's new assumed values must be unique. Accordingly, if you
chose to adjust the discount rate, please ensure that you use at least
three significant digits to the right of the decimal point (e.g.,
12.237%).
4. Acme is assessing its employee pension fund. At the end of each of
the next 27 years Acme will have to pay its retirees (use the first 4
digits of your SUN). If the fund is estimated to earn D% (D = the
fourth digit of SUN), how much does Acme need to have set aside today
to ensure that it can meet its future obligations? (At the end of the
27 th year, the balance should be drawn to zero.)
5. As part of a new labor contract, Acme has agreed to make a one-time
contribution of $1,000,000 to the construction of a new physical
fitness facility for its employees. This amount will be placed in an
account earning E% (E = the fifth digit of SUN). When the account
grows to $1,375,000, construction will commence. How long must
employees wait until construction of their new gym begins? (Your
answer should be accurate to two decimal places, e.g., 1.57 years).
Prepare for Acme's CFO an analysis that contains solutions for all
five problems. Provide a brief explanation of both the problem and the
solution. Be sure to clearly present and label all values and
variables and conclude each question with a brief interpretation of
your results. Your entire submission may be contained within an Excel
document. (Note: You must use Excel TVM functions to complete this
assignment; using a hand calculator and plugging values into a
template is not acceptable.)
Submit your analysis in Excel format, please. |
