I need the following question done in excel and must use excel formulas but made to look neat. COMPREHENSIVE PROBLEM 3 GENERAL WEAPONS, INC. General Weapons, Inc. (Comprehensive time value of m

I need the following question done in excel and must use excel formulas but made to look neat.

COMPREHENSIVE PROBLEM 3 – GENERAL WEAPONS, INC.

General Weapons, Inc. (Comprehensive time value of money) Mr. Rambo, President of General Weapons, Inc., was pleased to hear that he had three offers from major defense companies for his latest missile firing automatic ejector. He will use a discount rate of 10 percent to evaluate each offer.

Offer I \$1,200,000 now plus \$625,000 from the end of years 6 through 15. Also if the product goes over \$60 million in cumulative sales by the end of year 15, he will receive an additional \$3,000,000. Rambo thought there was an 80 percent probability this would happen.

Offer II Thirty percent of the buyer’s gross margin for the next four years. The buyer in this case is Air Defense, Inc. (ADI). Its gross margin is 60 percent. Sales for year 1 are projected to be \$3.5 million and then grow by 35 percent per year. This amount is paid today and is not discounted.

Offer III A trust fund would be set up for the next four years. At the end of that period, Rambo would receive the proceeds (and discount them back to the present at

10 percent). The trust fund called for semiannual payments for the next four years of \$110,000 (a total of \$220,000 per year). The payments would start immediately.

NOTE: The computation of the FV is an “annuity due”. You then use the standard PV of a lump sum equation to compute the PV of the FV of the annuity due just computed.

If you insist on using the tables to complete this problem, To look up the future value of the annuity due in the tables, add 1 to n (8 + 1) and subtract 1 from the value in the table. Assume the annual interest rate on this annuity is 10 percent annually (5 percent semiannually). Determine the present value of the trust fund’s final value.

Required: Find the present value of each of the three offers and then indicate which one has the highest present value.

ATTACHMENT PREVIEW

Download attachment

COMPREHENSIVE PROBLEM 3 – GENERAL WEAPONS, INC.
General Weapons, Inc. (Comprehensive time value of money)
Mr. Rambo, President of
General Weapons, Inc., was pleased to hear that he had three offers from major defense
companies for his latest missile firing automatic ejector. He will use a discount rate of 10 percent
to evaluate each offer.
Offer
I
\$1,200,000 now plus \$625,000 from the end of years 6 through 15. Also if the
product goes over \$60 million in cumulative sales by the end of year 15, he will
receive an additional \$3,000,000. Rambo thought there was an 80 percent
probability this would happen.
Offer II
Thirty percent of the buyer’s gross margin for the next four years. The buyer in this
case is Air Defense, Inc. (ADI). Its gross margin is 60 percent. Sales for year 1 are
projected to be \$3.5 million and then grow by 35 percent per year. This amount is
paid today and is not discounted.
Offer III
A trust fund would be set up for the next four years. At the end of that period,
Rambo would receive the proceeds (and discount them back to the present at
10 percent). The trust fund called for semiannual payments for the next four years of
\$110,000 (a total of \$220,000 per year). The payments would start immediately.
NOTE:
The computation of the FV is an “annuity due”.
You then use the standard
PV of a lump sum equation to compute the PV of the FV of the annuity due just
computed.
If you insist on using the tables to complete this problem, To look up the future value
of the annuity due in the tables, add 1 to n (8 + 1) and subtract 1 from the value in
the table. Assume the annual interest rate on this annuity is 10 percent annually (5
percent semiannually). Determine the present value of the trust fund’s final value.
Required: Find the present value of
each
of the three offers and then indicate
which one has the highest present value.
Note:
I HIGHLY ENCOURAGE you to use EXCEL formulas OR a Financial Calculator to
compute these 3 Present Values.