The capital budgeting problem â€“ Lenâ€™s Lenses Ltd

You are required to produce a capital budget using the information below and use this to select the better of two possible projects.

Lenâ€™s Lenses Ltd is a manufacturer of a wide range of optical devices including magnifying glasses, spectacle lenses, and microscopes. The managing director, Leonard â€œLenâ€ Penzias, has decided to expand the product range into telescopes. There are two distinct markets for telescopes in the Â£50-Â£500 range that Len is interested in, but due to other commitments within the company, he is only prepared to authorize one product within a five-year timescale. The options have been narrowed down to the following two products:

A) â€œAstromiconâ€; a small, refracting (lens-based) telescopes aimed at children. Relatively low-cost, low margin, these will be placed with an existing distributor; Scottish catalog-based shops, Angus Stores. This outlet is expected to give a high sales volume.

B) â€œBellatrix ZX Proâ€; a high-quality Newtonian reflector (mirror-based) telescope aimed at adult hobby astronomers. This will be a high-price, high-margin product with low expected sales volume.

Project A: â€œAstromicon 10 Refracting Telescopeâ€
Surplus tools and equipment can be adapted for this range at a cost of Â£25,000. An additional Â£75,000 will be required to purchase the remaining new equipment. All equipment is expected to last through the whole life of the project. At the end of the project, the machinery can be sold on as scrap for Â£32,000. This class of equipment qualifies for a writing down allowance of 25% per annum.

The marketing department estimates that in the first year of the project, the telescope will sell 52,000 units and in each subsequent year, sales will fall by 2% per annum as the novelty of the product wears off. The product line will be retired after five years. The initial price of each telescope will be Â£50.00 and is expected to be increased by 3% each year.

Expenses including labor and materials are estimated at Â£38.50 per unit, rising at 3% per annum after year 1. Tax will be levied at 20% one year in arrears. The project will require Â£120,000 in working capital in year 0 and in each subsequent year will need 3% of the total working capital to date. The discount rate will be 10%.
Project B: â€œBellatrix ZX Pro2500 Newtonian Reflecting Telescopeâ€
Entirely new equipment will be required for shaping and polishing mirrors for these products and will cost Â£350,000. The scrap value of this equipment is thought to be Â£120,000 at the end of the five years.

The expense per unit will be Â£199.95 rising by 4.1% each year. As for Project A, the writing down allowance is 25% per annum and tax is also 20% one year in arrears.

Since this telescope is aimed at serious hobbyists, the main source of advertising will be word-of-mouth recommendation, and so at an initial price of Â£275.00, the initial sales level will be relatively low at 4,600 for the first year, but sales should increase at 35% per year as the telescopeâ€™s reputation grows. Price is expected to rise at 3% per annum.

Initial working capital will be Â£58,000 and in subsequent years will be 3% of the total working capital to date. The discount rate will be 10%.

The balance of cost remaining on machinery can be recovered as a balancing allowance at the end of each project. Design work for both projects has already been completed and can be regarded as sunk. Assume that all units manufactured will be sold. The prices specified are those paid by retailers to the company. All production for whichever project is chosen will finish is year 5.

Requirements of assignment

You are required to:
i) Set up a capital budget for each project using the information above. With this existing information, which project should Lenâ€™s Lenses proceed with? Show your working in Excel and explain your decision. State any assumptions made.

ii) Optimistic and pessimistic forecasts have now been produced for several variables, shown below. Use Excelâ€™s various sensitivity analysis tools to examine the effects of these possibilities.

It is thought that the discount rate may be 7.5% rather than 10%. How would this change the figures? Does it change the companyâ€™s investment decision?

Continue the analysis using the original discount rate of 10%.

There are three estimates for the rate of growth in expenses on both projects:
Pessimistic 6.25%
Neutral 4%
Optimistic 1.3%
Examine these scenarios for each project. How would these change the figures? Do they change the companyâ€™s investment decision?

To meet proposed profit targets, the chosen project would have to produce an NPV after five years of Â£2,500,000. By making use of existing advertising contracts and adjusting the initial price of each unit, it may be possible to increase the initial number of sales and sales growth rates to produce this result. Using Excelâ€™s Solver facility, adjust the sales growth rate, the initial price and the expenses growth rate for each project according to the following constraints:

For project A, the rate of change in sales can be positive up to 1.5% growth per annum (as opposed to the current -2% shrinkage) and the expenses growth rate may be as low as 2.25% per annum. The maximum allowable price per unit will be Â£60.

For project B, the sales growth rate cannot exceed 35% per annum and the expenses growth rate may be as low as 3.75% each year. The maximum allowable price per unit will be Â£335.

Is it possible to achieve the target NPV for each project? If so, what adjustments are necessary to achieve it and what level of sales would initially be required? Which project should the company undertake to meet the target and why?

This section of the assignment grading is provided for the presentation of your written report and excel sheets contained within the report. You are required to produce a report outlining your findings and offering Lenâ€™s Lensesâ€™ directors recommendations on how to proceed given the possibilities. Overall, which project do you believe the directors should choose? Explain your reasoning.

The capital budgeting problem â€“ Lenâ€™s Lenses Ltd You are req...