Reference no: EM132319794

Capital Budgeting Project Assignment In Excel -

You will analyze a hypothetical capital budgeting project for the publicly traded company you selected for the corporate governance paper. The first step for completing this project is to extract the annual balance sheet and income statement for your chosen company from FactSet. Your calculations obviously need to be correct as well as professionally presented. However, it is also important that you use Excel efficiently so any future changes require minimal work (i.e. be sure to use formulas in Excel). You will need to estimate the cash flow of the initial investment, annual operating cash flows, and the terminal year cash flow for your project. You should then calculate the NPV and IRR and make a recommendation regarding whether to accept or reject the project.

Here is the information you have about the potential project:

Cash flow information - you will use this information to estimate the project's cash flows.

This is a three year project.

The financial statements from FactSet contain many years of data. The starting point for this project is the most recent fiscal year provided by FactSet.

The Year 1 cash flows from revenue for the new project will be 15% of the 'Sales' listed on the income statement. Inflation will cause cash flows from revenue to increase by 3% in Year 2 and another 3% in Year 3.

The Year 1 cash flows from expenses for the new project will be 10% of the 'Cost of Goods Sold' listed on the income statement. Inflation will cause the cash flows from expenses to increase by 3% in Year 2 and another 3% in Year 3.

The new project will require the purchase of a new asset that is equal 12% of 'Net Property, Plant & Equipment' on the balance sheet. This will be a cash outlay in Year 0. In other words, this is the purchase price of the asset.

The new asset is depreciable on a MACRS 3-year life. o Net working capital (NWC) will increase by 2% of the purchase price of the new asset in year 0. Assume the NWC will be fully recovered in Year 3.

The tax rate is not typically publicly disclosed. Estimate the tax rate using data from the Income Statement in the below equations. If calculated taxes are negative, you should assume the full value of the tax credit is recognized that year. In other words, negative taxes are fine. Effective tax rate estimate=Income Taxes/EBT EBT=EBIT-Interest Expense o The asset will be sold at the end of Year 3 for 120% of the remaining book value at the end of Year 3.

The appropriate discount rate is 7%. (Note: you will actually estimate your firm's discount rate in a future assignment.)

Use Excel to create a professional looking spreadsheet with appropriate cash flows, depreciation table, NPV, and IRR. Clearly indicate your recommendation whether to move forward with the project. Include the Balance Sheet and Income Statement that you obtained from FactSet in a separate worksheet within your Excel document. Label all of your numbers in your step by step calculations. Make sure that all of your calculations are formula driven. Your Excel spreadsheet must be uploaded via blackboard.

Attachment:- Assignment Files.rar