do the assignment on excel these is due in 2 hour plz do it.

You are a CFO of an industrial components company, Magnum Inc., with current sales of $11.2 billion, which represents 25% of the industry at this time. You are reviewing an acquisition and then also trying to decide whether to proceed with a new facility. One of your analysts has been reviewing company forecasts and has developed a regression formula with the assistance of the economist at the Industry Trade Association. They have determined that due to the significant market share of your company that the correlation between the industry growth and your company is .90. Based on the following regression formula they have developed, you must forecast the company's sales (the forecast will be in $ billions over the next five years). (5 marks) Y=11.2+10.963X where X is the growth rate (%) of the industry Industry Growth Index Forecast (base year: 2015=100) Year 2016 2017 2018 2019 2020 Growth Index 102.5 106.2 113.5 119.6 125.4 In addition your analyst tells you that based on historical norms your CGS is 48.5% and the operating expenses typically run at 23.4%. Calculate the Net Income for each year based on the assumptions using a 28.5% tax rate, the Company is debt free and assuming all fixed assets are fully depreciated. ( 5 marks) Derive a value for the company based on the PV of the forecasted Net Income (2 marks) The Acquisition Opportunity The acquisition you are reviewing is a competitor, Hiller Inc. that will add $2 billon in incremental sales through geographic diversification. The Company is asking for approximately 2x sales: $4.0 billion. The competitor has supplied you with the following Sales and EBITDA forecast: Hiller Inc. Forecast Year 2016 2017 2018 2019 2020 Sales 2.15 2.34 2.65 2.95 3.26 EBITDA 0.73 0.80 0.90 1.00 1.11 You need to evaluate this acquisition (NPV using Hiller's Net Income and Magnum's discount rate) Use the following cost of capital to valuate the acquisition (2 marks): 50% from retained earnings (use CAPM to derive an equity return: 90 day TBills are yielding .98%, while the Beta of this very stable company is 0.8 and the market return has been 3.5% over the past year) will be used and the remainder will be raised in debt with a new Eurobond issue at a rate of 4% over ten years (use pre-tax value). The company being acquired is debt free and has a CGS of 48% with operating expenses of 19.5%. Hiller's equipment is fully depreciated; they have no debt and a tax rate of 30%. (5 marks for the calculation of the Net Income for each of the five forecasted years and 2 marks for the PV of the Net Income) The cost structure of the consolidated company will be slightly different, as the joint entity will realize some economies of scale. The CGS will be reduced to 47.5% and the operating costs at 22.1%. Utilizing these ratios and the sales forecast for the combined entity calculate the Net Income for each forecasted year (10 marks for the development of the consolidated Net income for each of the five forecasted years and 2 marks for the PV of the Net Income) and then make your final decision as to whether you should proceed with the acquisition and why (2 marks). Alternative - The New Facility Now you also have to determine whether you should undertake the building of the new facility - the cost is $1 billion to be done through a similar debt issue at 6% over ten years. You will also require a billion dollars in free cash flow for working capital and setup costs. The incremental revenue generated from the new facility and the interest expense to be paid on the bond issue each year is as follows ($ millions): Impact of New Facility Year 2016 2017 2018 2019 2020 Increment al Revenue 350.0* 733.0 777.0 817.0 857.0 Interest Expense 58.884 54.283 49.402 44.223 38.730 *operating only a half year during the first year after a six month construction period Using Magnum's current cost structure (pre-acquisition), amortizing the building over 20 years and its 28.5% tax rate, project five years of income statements and do a NPV calculation (using the same discount rate as above) on the projected Net Income of the company to ensure that the incremental revenue from the project provides sufficient profitability to be undertaken. (10 marks to calculate the EBITDA and Net Income for the facility and 2 marks each for the NPV of the EBITDA and Net Income) The company can afford to do both, but should they is the question to be evaluated. Which would be preferential if the market appetite for either bond issue diminished and the company could no longer do both at this time? Therefore, either project would then have to be funded from the available cash flow (retained earnings) - what would be the significant issue in pursuing this option? (10 marks for calculating the consolidated Magnum/facility Net Income - include the interest expense and amortization expenses in the calculations). Then calculate the PV of the Net Income (2 marks) and provide a detailed explanation of which opportunity/acquisition makes sense if any