Your boss is seeking your technical advice as she contemplates the acquisition of an asset costing $3,000,000. She plans to pay $900,000 as a down payment and finance the balance on a 5-year, 7% note. The note will result in five equal principle annual payments of $420,000 due on December 31 of each year. NOTE: The cash payments on the loan will vary annually since the actual payments will include both principle and interest amounts.

The asset is assumed to have a 10-year life but we will use it for five years with a $500,000 salvage value at the end of five years. Assume we will sell the asset at the end of year five for $500,000. The boss prefers that depreciation be straight-line in a manner consistent with the other company assets.

The tax rate is 30% and the boss has an expected (hurdle) rate of return of 20%.

Assume all revenues and expenses from the asset involve cash receipts and cash payments.

The following schedule depicts the expected earnings (before interest, taxes and depreciation).

Yr 1	Yr 2	Yr 3	Yr 4	Yr 5
$2,200,000	$2,300,000	$2,240,000	$2,180,000	$2,120,000		Revenues
($670,000)	($680,000)	($710,000)	($670,000)	($640,000)		Less: COGS
$1,530,000	$1,620,000	$1,530,000	$1,510,000	$1,480,000		GrossProfit
($680,000)	($800,000)	($760,000)	($760,000)	($720,000)		Less:OpExp
$850,000	$820,000	$770,000	$750,000	760,000		EBITDA
					(Operating Income)

Required: (show all work)

Using Excel, prepare in good order, each of the following requirements. Create and label each tab and be certain that each tab fits a printable page.

1a. Prepare the amortization table for the five-year note.

b. Prepare the depreciation schedule for the asset.

c. Prepare the income statement for each of the five years (for the purposes of calculating the taxes).

d. Prepare the cash flows provided by the asset for each of the 5 years.

e. Calculate the present value of the cash flows.

Answer rating: 100% (QA)

1 a PARTICULAR INTEREST REPAYMENT BALANCE 300000000 Yr 0 90000000 210000000 Yr 1 14700000 42000000 1... View the full answer