I need to prepare a spreadsheet.

Austrochemicals Ltd (AL) is an Australian company that manufactures generic pharmaceutical products for supply to the Australian, New Zealand and Asian markets. The majority of the business is in Australia, but both the other markets are growing rapidly, albeit from a very low base. The company manufactures commonly used products that no longer have patent protection and the majority of its business comes from prescription products supplied under the Pharmaceutical Benefit Scheme (PBS) in Australia. The company does, however, conduct some limited research and development, and has in recent years launched two differentiated products. These products are not listed PBS products, but are sold over the counter (OTC) with limited medical claims. AL has manufacturing premises that operate under the code of good manufacturing practice and is licensed to manufacture human pharmaceutical products for distribution in its three markets. It also undertakes contract manufacturing for other companies. The business is successful and is listed on the Australian Stock Exchange.

Freddy Ferguson, the CEO of AL, is very keen on a project involving the launch of a new OTC product for the treatment of constipation in adults, as he believes the product has some unique features that will expand the business considerably, particularly with respect to sales to people in older age groups. The product is based on psyllium husk, a natural fibre product that is well known in the pharmaceutical industry. The new product is a powder that can be mixed in water, but is unique in that the mixture contains only natural products with no artificial flavours or colourings. Extensive testing has revealed that the side effects of the new product are considerably less than those of competing products currently on the market. AL currently markets a tablet that is based on a similar fibre, but this product does have some side effects, particularly in older people. The launch of the new product will necessitate considerable capital expenditure, as it will require new manufacturing equipment and some building modifications to accommodate the new equipment. The new equipment would have spare capacity as AL currently produces no other powder products. The need to make a decision on the new product project is becoming urgent as Tom Carter, the Manufacturing Director, has just returned from India where he identified a reliable source of psyllium husk and negotiated an exclusive deal with an Indian partner. The agreement has yet to be signed, but the Indian partner is impatient for the deal to be finalised.

Freddy recently called a meeting to discuss the project. In addition to Freddy and Tom, the meeting was also attended by Jennifer Brown, Research and Quality Assurance Director, Miguel Sanchez, Sales and Marketing Director, and Anna Chen, Financial Controller. Freddy and Miguel were very keen on the project and saw a great opportunity to increase sales, both in Australia and New Zealand, and presented a sales forecast at the meeting. Tom was concerned that based on the sales figures presented the new equipment would only be working to about 50% of its capacity and the cost could not be justified. Jennifer pointed out that research on the new product had cost $250,000 and AL needed to launch the new product to justify this expenditure. Freddy added that in addition to this research expenditure Tom had been to Europe to investigate suitable mixing and packing machinery, and that this trip had cost $30,000. His two trips to India had also cost $40,000 and legal expenses another $12,000. He maintained it was important that the project went ahead to recover these expenses. Tom had identified suitable machinery but was waiting for final quotes. A firm quote of $75,000 had already been received for the internal building modifications. Anna reminded the meeting that this level of expenditure would require Board approval, and that it would be necessary to provide the Board with a report on the project including NPV and IRR calculations and a final recommendation. Anna was concerned that the preliminary sales figures would not justify the expenditure and would like to see a higher usage of the equipment. Miguel undertook to investigate the possibility of more contract manufacturing to utilise the spare capacity. Freddy asked that all the attendees work together over the following two weeks to assemble all the data needed to obtain approval of the project, and stated he believed the project was vital for the continued growth of the company.

Two weeks later the management team met to discuss the data assembled for the project. Tom stated that he had received a firm quote of $1,260,000 for the new equipment. Tom and Miguel then presented sales and cost data. The forecasts covered both the sales of the new product and sales of additional powder products manufactured for other companies 3 (contract sales). The forecasts presented were only for three years because it was expected that in subsequent years sales and costs would only increase in line with an assumed general annual rate of inflation of 2%. It was also agreed that the new product project would reach the end of its life in six years time. This was confirmed by Jennifer Brown who believed AL would have developed a new product in that time.

Anna then detailed other increases in factory cost that she and Tom had identified as associated with the new product project. There would be an increase in factory wages. The operation of the machine was essentially an unskilled job, but would require an increase in the number of employees. The additional cost in wages for the first year would be $150,000. There was expected to be no further increase in the number of employees, but wage rates were expected to increase by 2% per year over the six year life of the project. Anna said there would be a significant increase in annual variable factory overheads, such as power, water, consumables etc, and she and Tom had completed an analysis and felt that these expenses varied directly with sales, and for forecasting purposes they would use 18% of both the new product sales and new contract sales as the estimate of this increased cost. Both she and Tom felt that this was accurate enough for the cost projections. Tom added that there was one other major cost that could not be ignored. In the first year the new equipment would be covered by warranty for major part replacement, but that after this an allowance should be included for spare parts replacement. Tom had estimated part replacement costs of $15,000 in year 2, and $30,000 in year 3. After that the maintenance cost would only increase by the forecast 2% per annum general increase in prices. Tom had also stated that inventory levels would increase by $150,000, mainly due to the psyllium husk and finished goods. He was confident however that, after the initial increase prior to the launch of the new product, inventory levels would not increase by any more than the $150,000 over the life of the project, as the logistics manager was maintaining tight control over inventory levels.

Miguel then tabled the advertising budget. Miguel stated that a significant amount would be needed in the first two years to ensure that the message regarding the elimination of side effects reached its target audience. He estimated $375,000 would be spent in years one and two. The advertising costs would then fall to $300,000 in year 3. From year 4 the advertising costs would be kept to a maximum of $150,000 per year, with reductions in volume offsetting any increases in price.

Anna then tabled some concerns that she had with the project. Her first concern was with the building modifications. The accountants had stated that, as the building modifications did not substantially improve the building or increase the life of the building, the tax office had given written advice that the modifications could be claimed as a tax deduction at the time the expense was incurred. The tax office would also allow prime cost depreciation of the new equipment to be claimed as a tax deduction over its six year life. The new equipment would have an expected sale value at the end of the project of $200,000. The companys accountants had also stated that all the preliminary expenditure, totalling $332,000 on research, overseas trips and legal expenses, could be written off to the profit 4 and loss statement over the life of the project. They had also confirmed that the tax deduction for these preliminary expenses is claimed in the year the expense is paid.

Annas major concern however was the inclusion of new contract sales in the analysis. It had taken a long time to build up the current contract manufacturing business and this business was very variable. Powder mixing was a new area of manufacture and there were many other competitors in this area. She felt the equipment should be justified by the new product only.

Anna also provided the following additional information:

1. The company is a profitable company and pays tax at the corporate tax rate of 30%.

2. ALs Board had stated that it requires a nominal annual rate of return of 15% on all investments in the pharmaceutical business.

3. Tom and Anna had agreed that if the forecast new contract manufacturing sales did not eventuate the increase in factory wages cost would be reduced by $50,000 in the first year of the project, in addition to the elimination of all the directly variable costs (raw materials, variable overheads) that would have been attributed to the forecast new contract manufacturing sales.

4. As the company did not have sufficient cash to pay for the machine in full, Anna had negotiated a fully amortised loan with the firms bank for $500,000 at a fixed rate of 7.5% p.a. that would be made available if the project went ahead. The monthly repayments on the loan would be $7,669 over 7 years. Anna has asked you to join the small finance group and assist her in the preparation of a recommendation to the Board.

Freddy instructed Anna to prepare a report and recommendation to the Board. The base case (most likely) project should be based on the six year forecast sales and variable cost data given by the Data Generation File including the contract manufacturing sales.

You are to prepare the following:

1. A readable spreadsheet clearly showing the discounted cash flow analysis (Capital Budget) for the base case project as required by the board. The spreadsheet should be based on the full six year project as instructed by Freddy Ferguson the CEO. The calculations must include the NPV and the IRR of the project with respect to its relevant incremental after tax cash flows.

2. A readable spreadsheet that analyses the concern that Anna has that the forecast new contract manufacturing sales will not eventuate (i.e. a spreadsheet similar to in (1) above, but with the relevant cash flows associated with these extra contract manufacturing sales removed).

3. A written advice to the Board including the following elements:

- A recommendation to accept or reject the project together with clear reasons for your recommendation based on your analysis.

- A statement of other factors the firm should consider with respect to the new product project. Here you should be able to specify limitations of the analysis, and any further factors the company might have overlooked or other factors that should have been taken into consideration.

Data Generation File :

Sales Forecasts Years 1 to 3
	1	2	3
New Product	2.440,000	2,940,000	3,690,000
Contract Manufacturing	505,000	555,000	630,000
Raw Material Costs Years 1 to 3
New Product	-732,000	-882,000	-1,107,000
Contract Manufacturiing	-126,250	-138,750	-157,500
Lost Sales of Current Product Years 1 to 3
Current Product	-830,000	-1,005,000	-1,255,000
Variable Costs for lost Sales of Current Product Years 1 to 3
Current Product	207,500	252,250	313,750

Note: That for all the above sales and costs the forecasts for years 4 6 only increase by 2% per year to allow for general inflation.