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.
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 break‐even 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 re‐computed 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)
D) Compute the break‐even 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.
Question 2: General (25 marks) Use the function SRCH on Bloomberg terminal to SEARCH and filter out Australian government and corporate bonds according to the following criteria: For the Universe of bonds select the following: 1- Asset Classes: corporates, governments 2- Sources: All securities Use the following fields to refine the search criteria 3- Security status: active (matured bonds will not be in the sample) 4- Country of Risk : Australia 5- Moody’s rating: Investment Grade only (do some search to define the limits) 6- Market capitalization larger than $1MMM 7- Maturity type: Bullet (we exclude bonds with optionality for now) 8- Maturity (Years from today) : Less Than 30 yrs Save your search Criteria (e.g. Australian_IG_Bonds). Take a screenshot of your searching result (showing all criteria, you might find the number of securities of your result changes based on the date you collect the information. You need to attach this as part of your solution for this question.) Then upload your results and Export them to Excel. (Hint: Use Tab Actions to Save and Export) Questions: Data for this section changes on a daily basis. Therefore, you need to state the date that you collect the data by attaching the screenshot you collect as above. 1. How many (main) sectors do you get (Based on BICS_Level1)? And what are those sectors (show a list of sector names)? (3marks) 2. What is the total amount outstanding per sector? (2marks) 3. What is the percentage of outstanding in each sector relative to all sectors in the sample? Use bar or pie plot to illustrate that (use excel for this). (5marks) 4. Which sectors have the main loading? Is that expected? Document. (5 marks) 5. Answer questions 1-4 when Moody’s rating is High Yield instead of Investment Grade. (Hint: Use saved search and change one criteria)
Question 3: Bond Valuation (30 marks) Consider now the bond with the Bloomberg id “AO262815”. Use the Bloomberg terminal to load the characteristics of this bond. (Hint: use FLDS function) Questions: 1. The issuer Full Name, sector, and market capitalization (as of the date you collect the data, provide screenshot) (3marks) 2. The date the bond was issued, the date it matures, the coupon rate, the coupon payment, type, and frequency per year. (3marks) 3. Find out if the issuer ever defaulted on his bond obligation before. (1mark) 4. Compute the remaining time-to maturity as of July 12, 2017. (1mark) 5. What is the maturity type of this bond? (1mark) 6. What is the amount outstanding on the bond as of July 12, 2017? (1mark) 7. Does the bond have a sinking fund? How do you identify it? (3marks) 8. What was the amount initially issued? (1mark) 9. What is the issued price? Was the bond issued at par/premium/discount? Why? (5marks) 10. Is the bond publicly traded? (Hint: use GP and HP functions). If the answer is “YES”, where is the trade reported? What is the price of the bond as of August 18, 2017? Plot the time series of the bond price for 2017. (5marks) 11. What is the yield on the bond? (Hint: use the YAS function) (2marks) 12. What is the spread over the 30-year Treasury bond as of the date you collect the data (2marks) 13. What is the accrued interest as of the date you collect the data (2marks) **For Q12 and Q13, you need to provide a screenshot for this and highlight or document your answer Question 4: Stock Valuation (25 marks) Consider now the issuer of the bond described above. Questions: 1. Are stocks of this issuer publicly traded? If yes, what is the share price as of August 18, 2017. Plot the share price for the last 5 years, provide screenshot. (Hint: use GP function) (4marks) 2. What is the number of shares outstanding? (Hint: use DES ) (2marks) 3. What are the latest reported P/E, adjusted EPS ratios? (Hint: use HP and FA function) (3marks) 4. What is the historical dividend/split summary? provide screenshot (Hint: used DVD function) (2marks) 5. Based on the dividend/split summary of the last 2 years (2015-2017), find the fair price of the share on August 18, 2017. (6marks) 6. Is the share over/under –valued? According to your calculation, is there an arbitrage opportunity? What would you recommend to a client interested in this stock?
Presentation Question 2-4 should be completed in a word document. You will be using excels to extract information from the Bloomberg terminal. You would also use excels to conduct some of the analysis. However, after you have finalized your analysis in excel, you will then need to copy and paste those Results to word document for submission (These result could be a table or a chart you created in excel, or if you want, you can write your result in paragraphs without table or charts, where applicable.). Do not submit your excel sheet (analysis) for Question 2-4. You can also attach screenshots of the Bloomberg Terminal to help you to answer questions. Fails to compliance with the requirement of the presentation for Part B of the assignment will get a deduction on mark as the penalty. Please aware, there is no particular format required in term of presenting your result. You can do any format as long as your answer is clear and easy to identify. It is ok to answer more than 1 question at a time.