TABLE 4 - Company Information

some parts require excel, thanks

This is all the info that was given, this is a finance question.

CASE STUDY: The a2 Milk Company Limited In 2000 Dr Corran McLachlan a research chemist from the Wairarapa and Howard Patterson an entrepreneur from Dunedin founded the A2 Corporation Limited to market milk, and milk products, that were free from the A1 (B-casein) protein. The company had a difficult start with opposition from New Zealand's major milk-producing dairy cooperative (particularly over the health-claims made for the brand) but by 2003 it was able to launch its a2 and a2MILK brand products in New Zealand. Unfortunately, during that year both Dr McLachlan and Mr Patterson died and the company struggled financially. To help cash-flows the Company's Australian division was sold to a Singapore based food and beverages conglomerate. The New Zealand division gradually grew both its product range and sales volumes and by 2006 was able to buy back the Australian division. A2 Corporations first profit announcement was made in 2011 and in March 2013 the company listed on the NZX. In April the following year it changed its name to "The a2 Milk Company Limited" and in March 2015 it dual listed on the ASX. The a2 Milk Company Limited (a2) currently operates in New Zealand, Australia and either in its own right or through joint-ventures with local companies in China, the United States of America and the United Kingdom. a2 produces and markets not just A1 protein-free milk but a range of milk-based products such as whole milk powder, ice-cream and yoghurt all manufactured using A1 protein-free milk. Through clever management of the release of its infant formula in China and after the announcement of an agreement with its previous opposition Fonterra, a2 shares leapt in value and by 2018 it had become New Zealand's largest (my market value) listed company. The phenomenal growth that started in 2018 continued through 2019 with revenue and profits growing by 41% and 47% respectively and all markets, with the exception of the United Kingdom, demonstrating strong growth and high market penetration. In its 2019 Annual Report a2's Board Chair, David Hearn stated: "The company enjoys a robust balance sheet which, combined with its continued strong cash generation, gives us the flexibility to support our growth potential in the future. The Board and management continue to evaluate a broad range of investment options designed to support our future growth aspirations. As a consequence, we do not anticipate paying dividends in the near-term." The Investment Opportunity After your study at the University of Auckland you have been employed by a2 in its finance division. Your division works closely with the product development and marketing divisions and you have been working with colleagues from these areas on a new idea that reflects a2's objectives, as outlined by the chairman. Research in the product development division has shown that a yoghurt product derived from the company's premium infant formula a2 Platinum is particularly beneficial in geriatric nutrition. Your marketing colleague believes that with appropriate promotion of these nutritional benefits such a product is likely to do exceptionally well, particularly in the US and China where the aging population represents an immense, untapped marketplace. She has asked you to produce a financial analysis that will support a proposal to the board to proceed with introduction of the new product to market. You have gathered the following detail to assist in your analysis: Financial Information Tables: Table 1 - Product Market Estimates Immediate total potential worldwide market for product is NZ$800 million revenue (equivalent). Total worldwide market is expected to grow by 5% annually. Projected a2 world-wide market share is expected to be 3% in the first year of operation and increasing by 1% annually (i.e.4% of world-wide market in Y2,5% in Y3 etc.). Table 2 - Marketing and Production Estimates Estimated life of investment - 5 years (the assumption is that by the end of this period new products will be available that will supersede the geriatric yoghurt product and production will cease). The initial investment in production facilities is expected to be $40,000,000. It is not anticipated that there will be any increased working capital requirements as a result of investment in the project. Marketing expenditure will commence immediately with a $10,000,000 investment in worldwide advertising and product awareness education (assume this all occurs in Yx) followed by four years of reducing marketing expenditure at the end of each subsequent year i.e. $7,000,000 in Yz: $5,000,000 in Ys; $3,000,000 in Ya and $1,000,000 in Ys. Sales revenue is expected to be $2.00 per item. Variable production costs are expected to be 50% of revenue. Fixed costs of production (excluding depreciation) are expected to be $2,500,000 p.a. Production equipment will be depreciated on a straight-line basis at 20% p.a. with no residual value expected. Table 3 - Economic and other data New Zealand company tax rate -28% Assume tax in any year is payable in full at the end of that year. Assume that tax rebates can be off-set against other division profits and represent a cash inflow in the same year they occur. New Zealand inflation rate - 2.1% p.a. 30-day BKBM 2.40% p.a. 5-year fixed-rate borrowing rate - 3.00% p.a. The risk-free rate is currently 2%p.a. Due to extreme uncertainty during COVID-19 the expected market return is currently around 11%. The a2 Milk Company Limited July 2019 Balance Sheet 2019 S'000 2018 S'000 restated Notes Assets Current assets Cash and short-term deposits Trade and other receivables Prepayments Inventories D3 C1 464,805 52,748 340,455 59,131 36,015 49,693 C2 64,101 108,453 675,699 Total current assets 499,702 Non-current assets C4 10,296 9,701 C5 15,092 Property, plant and equipment Intangible assets Other financial assets Deferred tax assets Total non-current assets Total assets C6 B5 12,985 286,807 7,683 317,771 186,862 4,861 216,516 716,218 993,470 Liabilities C3 108,934 160,248 1,431 B2 898 43,710 50,557 205,389 160,389 Current liabilities Trade and other payables Customer contract liabilities Income tax payable Total current liabilities Non-current liabilities Trade and other payables Total non-current liabilities Total liabilities Net assets Equity attributable to owners of the Company Share capital Retained earnings C3 227 120 227 120 205,616 787,854 160,509 555,709 D5 144,495 578,442 64,917 787,854 141,566 290,701 123,442 555,709 Reserves D6 Total equity Table 4 - Company Information cont. The a2 Milk Company Limited NZX Listing Information Instrument Name The a2 Milk Company Limited Ordinary Shares ATM $19.530 Issued By The a2 Milk Company Limited ISIN NZATME000258 $0.000 / 0.00% 52 Week Change: | $3.560 /22.27% Type Ordinary Shares Performance Fundamental Activity Trading status Trades Trading $19.530 P/E 44.870 Open High 19 EPS $0.435 Value $0.00 Low $1.280 Volume 0 High Bid $20.470 Gross Div Yield 0.000% Capitalisation (000s) $14,448,883 Low Offer $19.340 Securities Issued 739,830,151 Table 5 - Other company information a2 has an equity beta of 0.90 The CFO of a2 has stipulated a maximum payback period of 3 years on most new investments. Question 1 Part a) Prepare a table showing the investment opportunity's cash flows over the five years. (10 marks) Part b) Determine a2's WACC that is applicable for use in a financial analysis. (6 marks) Part c) Calculate the NPV and IRR of the investment project. (2 marks) Part d) Calculate the payback period for the investment project. (2 marks) Part e) Write a brief report to Craig Louttit, Chief Financial Officer of a2, summarising your findings and recommending whether or not the investment opportunity is financially acceptable. (10 marks)