Question

Can you please use EXCEL to analyze this question?

Project Background:

You are a staff engineer for an industrial company involved in the distribution of electrical parts. The firm currently has three warehouses throughout the country. The firm is considering the development of a fourth warehouse, somewhere in this area. The purpose of this is to reduce costs of transporting warehoused goods to customers. You are charged with doing a financial analysis of the proposed facility over a 10 year planning horizon.

The new facility is expected to reduce costs by about $1,200,000 per year due to lower transportation costs. This savings is expected to increase by 3% per year. (Annually compounded.)

The new facility will have land cost of $500,000 and construction cost of $4,800,000. There is machinery such as shelving and fork lift trucks with a cost of $1,200,000. These have a MACRS 7-year schedule. The machinery will have a salvage value of 20% of acquisition cost at the end of the 10 year period. (Do not inflate this value.) All capital costs are incurred on the first day of year 1. (This enables them to be included in year 1 depreciation. From a cash flow point of view, this is nearly identical to the last day of year 0.) The facility will have the following operating and maintenance costs.

Cost category	Year 0	Annual increase
Staff	$       300,000	3%
Property taxes	$          80,000	2%
Insurance	$          35,000	3%
Utilities	$          50,000	4%

The firm borrows 80% of the project cost at 6% per year compounded quarterly for a term of 10 years. The remainder is from corporate funds. Working capital requirement is $25,000 and increases by 5% per year which is fully recovered at the end of the 10 years.

At the end of the 10 year period, the land can be sold for $1,000,000 and the residual (salvage) value of the warehouse is $3,600,000 in today’s dollars. These values (land and warehouse) are inflates at 2% per year. The firm’s minimum acceptable after tax rate of return before inflation is 15%. Expected general inflation is 2.5%

Problem Statement:

               Compute the after tax rate of return for this facility and the present worth discounted at the MARR. Make a recommendation about whether the project should proceed.

Assumptions:

Federal income tax rate of 21%

Land improvements depreciate annually at (1/39) of the acquisition cost.

Answer 1