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.