Financial project
The Pennsylvania State University Project #1B INTRODUCTION This project deals with capital expenditure analysis which is based on the results of your first project, Cosmo’s. To complete this project, you are required to answer the ten questions at the end of these instructions and provide the calculations for your answers. You must list all revenue and expense items in the calculation of your operating cash flows. You must use Excel to answer questions 3, 8 and 9. Handwritten answers to the other questions are acceptable if they are written legibly. Two printouts are required for your computer analysis: one showing all the figures you calculated and a second showing all the formulas you used. Formulas do not have to fit onto one page. DUE DATE(S) EXTRA CREDIT (10 EXTRA POINTS): TUESDAY, MAY 1st IN-CLASS AT 1 PM REGULAR DUE DATE: THURDAY, MAY 3rd IN-CLASS AT 1 PM No late projects will be accepted. SUBMISSION REQUIREMENTS CHECKLIST Number Requirement Completed? (√) 1 You must provide your 4-digit ID number on the FRONT PAGE. If you do not, you will lose 5 points. 2 Answer (and display) the answers to the questions in numerical order. 3 Do not submit unnecessary materials (balance sheets, statement of retained earnings, etc.) 4 Do not split tables across pages! Readability counts! 5 Use must use Excel to answers questions 3, 8 & 9 and show your formulas. 6 Round cash flows to the nearest dollar. 7 Staple your project together. You can complete this project with one partner (it must be the same partner as Project #1a). You are expressly forbidden to consult anyone else about this project (except the instructor). Other Pertinent Information 1. The capital expenditure analysis will be for the calendar years 2019-2023. 2. The project will be operated until December 31 of year 5 when the property will be sold. Management estimates the gross sales price will be based on year 5's after-tax operating cash flow (OCF5), and that it will be generated in perpetuity. The discount rate to be used in the gross sales price calculation is KA. 3. The corporate tax rate is 20%. Initial Investment You recently purchased an option on a parcel of land. You may purchase the land for its current market price of ID x 160. The parcel of land is currently undeveloped; it would require a depreciable investment of ID x 100 in land improvements. The land improvements are expected to have an economic life of 10 years and no salvage value. The building will cost ID x 325. The building will be depreciated over 40 years and have no salvage value. Financing Parameters You are to analyze the project by using the WACC (Weighted Average Cost of Capital) approach to project evaluation. The managers have determined that the debt used to finance the project will have an interest rate (KDBT) of 7%. In order to estimate the owner's required rate of return, KE, for the projects, management uses the Security Market Line (SML). They estimate that the return on the market portfolio (KM) is 9% and the risk-free rate, (RF) is 4%. The beta of the project is estimated to be 1.4. Net Working Capital Requirements Net working capital is needed for any hospitality operation. If you are unsure of what net working capital is, feel free to peruse Chapter 3 in our 420 text. The following table indicates the net working capital needs under each option. Year Year 1 Year 2 Net Working Capital Needed $50,000 $100,000 You are to assume that working capital is to remain the same from year 2 onward. Assume that working capital needs are for all "other current assets," not including cash. Current liabilities will be $100,000 in year 1 and $150,000 from year two onward for the project. Financing Conditions Long-term Debt to fixed asset ratio Interest Rate % of net income paid as dividends 25% 7% 10% It is important to note that after the initial investment of capital, no new equity capital or long-term debt will be raised. Management also assumes that the total amount of long term debt will be in the form of bonds so that the book value of the debt will be constant over the 5 year period (i.e., no principal repayments will be made). All remaining earnings will be put back into the business. No additional investment will be made and all excess funds will be held as cash. REQUIRED ANALYSIS Perform a capital expenditure analysis by answering the following questions or completing the tasks. 1. What is the owner's required rate of return (KE) for the project? Using the Security Market Line equation from Chapter 4, show your calculations and display this answer with 2 decimal places (i.e. 14.76%). 2. a. What is the total amount of money spent on the gross fixed assets at Year 0? b. How much money is spent on working capital in Year 1? c. Given that we already have spent money on working capital in year 1, how much additional working capital will be spent in Year 2? 3. Use your full income statement from Part A and adjust it to determine the after-tax operating cash flow for the project for 5 years. Show your full statement, not a shortened or summary version. You must use Excel to answer this question and show your formulas. 4. What will be the weighted average cost of capital (KA) for the project? Show your calculations and display this answer with 2 decimal places (i.e. 12.12%). 5. a. What is the gross sales price for the property at the end of year 5? Show your calculations. b. How much is the capital gains tax? Show your calculations. c. How much is the recaptured depreciation tax? Show your calculations. d. Calculate the net sales price (after taxes) using a, b & c above. 6. Using the answers to questions #2a, #2b, #2c, #3, and #5d, put ALL of the cash flows on a timeline (both inflows and outflows). It should be such that you have one net cash flow per year, including year 0. 7. What is the payback period for the project? Calculate the payback period with at least one decimal place and show your calculations (i.e. 4.2 years). 8. What is the Internal Rate of Return (IRR) of the project? (Use the IRR function in Excel to calculate the IRR and show your formulas). Make sure that you report the IRR using two decimal places (Example: IRR=14.23%). 9. Find the Net Present Value (NPV) of the project by using the NPV function in Excel and show your formulas. 10. Should the project be completed? Why or why not? Carefully explain your answers in paragraph form. A single sentence explanation is insufficient; make sure you discuss the appropriate analytical tools you have used to reach your conclusion. HELPFUL HINTS This is a relatively complicated, but realistic capital budgeting exercise. It is intended to integrate the material learned in class. Submissions are expected to resemble those presented to an upper level manager in a hospitality operation. Make sure they are legible and presented in good order. YOU MUST FASTEN YOUR PROJECT TOGETHER! PLAN FOR DISASTER. Make back-up copies of your project files. Lost, missing or stolen file problems will not warrant an extension. Take good care of your files and your back-ups. 4