You have been hired as the financial administrator of Hummer Corp., an agricultural seed broker in Northern Utah. Mr. Bullen, the president, has asked you to decide whether or not the company should purchase, at a cost of $450,000, a warehouse and the seed distribution rights for a major seed producer for farms extending from Idaho into Oregon. If the regional sales area is expanded, it is estimated that an investment of $100,000 in working capital will be required. The company expects to operate in the expanded area for 10 years, after which time they anticipate being able to sell the rights for $600,000. Sales revenues are forecast at $400,000 per year for five years, and $300,000 per year thereafter. Operating expenses will be $50,000 annually, and cost of goods sold amount to 60% of sales. The full cost of the warehouse and regional sales area will be depreciated on a straight line basis over 10 years. If the firm acquires the assets in question, it will be able to dispose of certain fixed assets that it presently owns. These have a net book value of $40,000, a remaining (straight line) depreciable life of 4 years, and could be sold for $30,000. Mr. Bullen estimates the required rate of return for Hummer to be 14 percent. Assume a tax rate of 30%.

Please use excel format below and show work so I can learn/undersatnd

Initial Outlay

Increase in WC

Salvage Value (new)

Sales

Operating Expenses

COGS

years to depreciate

Depreciation expense (new)

NBV of old asset

Salvage of old asset

yrs to depreciate (old)

foregone depreciation

R

Tax

Time Period

Working Capital

in WC

Sales

-operating expenses

-COGS

+salvage value

-book vaue

-depreciation (new)

+ foregone depreciation

EBIT

-taxes

After Tax

+ depreciation (new)

-depreciation(old)

+book value

+ NWC

-Capex

Free Cash Flow

Discount

Present Value

Net Preset Value

.