I need someone to help me put this information into an excel spread sheet.

Background

Beauty and the Beholder, plc is a cosmetics manufacturer based in the UK, which, despite being relatively new, has enjoyed significant success and achieved sales growth rates of over 20% in the last five years.

It has now identified another growth opportunity which involves exporting its products to Asia and South America and this opportunity would double the size of the company both in terms of long term financing raised and in terms of sales. In order to pursue this opportunity, the company will have to build a new factory for which they have identified a site, and they believe the factory could be up and running in 2 years.

Currently, Beauty and the Beholder, plc believes the level of risk of its activity is the same as the average level of risk for its industrial sector, resulting in a discount rate of 12.64% but there are currently differing views on the board of Beauty and the Beholder, plc on how this investment would affect that level of risk.

Financial Information

Beauty and the Beholder, plc has made the following assessment of the financials associated with this investment opportunity:

• Feasibility study on the site of the new factory has been conducted for 1million, of which half has been paid and the other half will be paid in six months time;
• Required investment in acquiring the land and building the factory is 4million, which will be spent 75% now and 25% at the end of this year;
• Sales are expected to be 750,000 units in year one at an average price of 2.5 per unit split evenly between the two geographical areas;
• Unit sales are expected to grow at 25% per year in Asia and 10% per year in South America for the first 4 years, after which they will stabilise, while prices are expected to remain unchanged in real terms for the whole period of the analysis;
• Variable production costs will follow the following patterns:
  • Materials 30% of price in year one, decreasing by five percentage points in each of the following 3 years, after which isstabilises;
  • Labour - 20% of price in year one, decreasing by 25% in each of the following 2 years, after which is stabilises;
  • Other costs 5% of price throughout the planning period;
• Administration costs are expected to be 15% of sales in year one. They will remain at the same absolute value until year four, at which time they will increase by 10%, and then will remain unchanged until the end of the planning period;
• Increase in working capital will be in line with increase in sales for the first three years and no change afterwards. Initial working capital is 0.5million;
• The factory is expected to last for 20 years, and will be depreciated over that period of time;

It is not the companys intention to make any significant changes to its existing business and it normally uses a 7-year planning period.

Inflation rate is expected to be 3.5% per year and the company pays Corporate Tax at a rate of 30%. Beauty and the Beholder, plc has no special agreements with the Inland Revenue, and as such capital allowance levels follow the general UK rules.