Please help solve this corporate finance case study on Excel, and show some important key steps!

Many Thanks and happy holiday in advance!

Case Study: JMJ Corp.

JMJ Corp. is an Indiana based U.S. manufacturer of soap products. The firm has a subsidiary in Croydon, England, JMJ Ltd, that makes luxury soap for the UK market. The Croydon plant manufactures and sells 3,000,000 units of the product per year at a price of GBP 20 each. The unit variable cost is GBP 10.

ABC Corp. is considering a four-year medium-term expansion project. This would involve JMJ Ltd. opening a separate facility near Birmingham. The new plant would manufacture a similar product but to be marketed in Scotland and the north of England. It would also use more efficient technology, bringing the unit variable cost down to GBP 8.

Market analysts for JMJ Corp. have estimated that sales of the new product, at a price of GBP 21 each, would be as follows:

Year 1 400,000 units

Year 2 600,000 units

Year 3 800,000 units

Year 4 500,000 units

However, they also estimate that these sales estimates of the new product include the cannibalization of 5% of the sales of the existing product made by the Croydon facility in each of the four years of the project. The lower Croydon sales would, however, allow the Croydon operation to reduce its investment in working capital by GBP 150,000 for the duration of the project.

The opening of the new Birmingham facility would require an initial cash investment in plant and equipment of GBP 8.0 million. This would have a residual value of about GBP 4.0 million at the end of the four-year project life. Insurance, maintenance and other fixed charges associated with the new facility are estimated to be GBP 1.0 million per year.

JMJ Corp. owns the site upon which the new facility would be located. Birmingham Council recently made an offer of GBP 3.0 million for the site for the location of a conference center. In the event that JMJ decided not to proceed with the proposed project, the company would probably take this offer.

The project would require no initial investment in working capital. However beginning in the first year, the balance sheet amount of working capital would need to be 10% of the total annual sales of the Birmingham plant in each of the four years.

The U.K. tax depreciation capital allowances on the GBP 8 million investment are estimated as follows:

Year 1 2 3 4

Amount 1,440,000 1,180,000 968,256 793,970

As in the U.S., there is either a tax benefit or tax liability on the difference between the tax basis and the residual sale value of an asset at disposal.

The effective U.K. tax rate for JMJ Ltd. is 19%. The cost of capital used by JMJ Corp. for domestic projects is 16%. However, due to the higher degree of uncertainly of non-U.S. projects, JMJ Corp. adjusts this rate by 2% as a risk adjustment. The short-term risk-free borrowing rate in the U.S. is currently 1% p.a. The equivalent rate in the UK is 4% p.a. The spot exchange rate is $1.25 = GBP 1.00.

Required:

Prepare a full analysis of this project for the executive management of ABC Corp. This will entail the completion of seven schedules. Conclude with a recommendation supported by your analysis.