I don't know if my data of this question is correct, I need a example to compare with mine.

FINM2401 Financial Management, Semester 2 2017 FINM2401 Financial Management, Semester 2 2017 Team Project Assignment due: 17th Sep 2017. Submit electronically on Blackboard - one assignment per group. Overview: This assignment count towards 20% of your final grade. This pdf file is only for Question 1. The rest of the assignment questions require Bloomberg access and will be made available before the Bloomberg training session. Question 1: Capital Budgeting (40 marks) Eggbert's Egg Company, a company based in the US, is seeking to expand its production of chocolate eggs. A market research firm has determined that the Eggbert could sell 1 million additional eggs for $3 USD each for an annual revenue (incurred at the end of each year) of $3,000,000 USD per year over the next 10 years. The market research firm has billed Eggbert 100,000 USD for this research, to be paid in 1 year. Eggbert will need to build a custom factory to manufacture the additional eggs and purchase a specialized machine. Eggbert is considering building the new factory in either Australia or New Zealand. The Australian factory will require a land purchase of 5 million AUD and the cost of building the factory will be 3 million AUD. The cost of production (incurred at the end of each year) includes a fixed cost of $500,000 AUD plus a variable cost of $1 AUD per egg produced. Production costs are expected to remain constant. The New Zealand factory will require a land purchase of 5 million NZD and the cost of building the factory will be 3 million NZD. The cost of production (incurred at the end of each year) and includes a fixed cost of $500,000 NZD plus a variable cost of $1 NZD per egg produced. However, it is expected that all production costs in New Zealand will increase by 10% per year. In both countries, land prices are expected to stay the same, however the cost of the factory and machines are expected to fall every year by 5%. The specialized chocolate egg machines will be made in the USA and shipped abroad. The current cost of the machine is 500,000 USD. The assumption is that the new factory will be closed after 10 years and all assets will be sold at that time. 1 FINM2401 Financial Management, Semester 2 2017 For tax purposes, the factory and the machines are to be depreciated straight line over a 20 year life (assuming no salvage value at the end of 20 years). Eggbert will require 100,000 USD of working capital starting immediately. This working capital will be recovered at the end of 10 years. The corporate tax rate is 30%. Assume that the company's opportunity cost of capital is 10% across all 3 difference currencies. The market exchange rates on September 1, 2017 should be used for the analysis. The numbers above represent the most likely outcome (base case). To test the feasibility under alternative assumptions, you are asked to perform a scenario analysis (each of these is independent - start with the base scenario and make the changes indicated): Cost of Capital: Show the sensitivity of changes in the net present value of both options for changes in the cost of capital ranging from 5%25%, with the step increment of 0.5%. NZD/USD Rate: Show the sensitivity of changes in the net present value of the New Zealand option for changes in the NZD/AUD from 0.5 to 1.5 NZD/USD, with the step increment of 0.1. Cost Inflation: Show the sensitivity of changes in the net present value of the New Zealand option for changes in the cost inflation rate from 0% to 20% NZD/USD, with the step increment of 0.5%. Price change: Show the sensitivity of changes in the net present value to changes in price per egg of both options from $1 to $5, with the step increments of 25 cents. Break-Even Level: What is the breakeven level of the price per egg (that is, the price level that gives NPV=0) for both options. Required: A) Compute NPV and IRR for the base case (10 marks) B) Compute NPV and IRR for each of the above scenarios. (7 marks) Your spreadsheet should be constructed so that any of the above variables (cost of capital, exchange rates and price per egg) can be changed to any value and the output values (NPV & IRR) will be recomputed accordingly. C) Prepare a NPV profiles by plotting a graph to show how changes in the cost of capital, exchange rates, costs and price will impact NPV. (7 marks) 2 FINM2401 Financial Management, Semester 2 2017 D) Compute the breakeven level of price per egg using Excel's Goal Seek. (6 marks) E) Discuss the results of your analysis. (10 marks) In particular, where should Eggbert's Egg Company build their new factory? Identify what areas pose the highest risks to this decision? Justify your recommendation based on the theory taught in this course (e.g., the theory behind the decision rule that you use) Your discussion in part (E) should be no more than 600 words. Add word count next to your answers (Simply copy your answer to word document to do a word count). Answers exceed the word limited will not be marked. Answers without word count will get a mark penalty. Presentation (10 marks) Question 1 should be completed using Excel Spreadsheet. Accuracy: Your Excel file should include all workings and calculations. Formulas for the calculations should have cell references wherever possible. If you have computed a number incorrectly and just typed that number into the spreadsheet (or typed a formula using numbers when cell references could have been used), you will not receive partial credit for any portion of your computation that is correct. Presentation: Take a good use of the Excel tabs when you prepare your spreadsheet. Label the tabs clearly and keep your worksheet neat. 3