UNIVERSITY OF CENTRAL FLORIDA HFT 4464 Fall 2014 Capital Budgeting Project (Part B)

INTRODUCTIONThis project is a continuation of the proposed Lohan Hotel capital budgeting project. Tocomplete this project, you are required to answer the ten questions at the end of theseinstructions and provide the calculations for your answers. You must list all revenue andexpense items in the calculation of your operating cash flows. You must use Excel toanswer questions 4, 8 and 9. Handwritten answers to the other questions are acceptable ifthey are written legibly.Two hard copies (printouts) are required for your computer analysis: one showing all thefigures you calculated and a second showing all the formulas you used. Formulas do nothave to fit onto one page. All explanations must be typed or legibly written.A good source of information for this part of the project is Appendix 10.2 of thetextbook.DUE DATEThe project will be due at 10:00 am on Monday, December 01, 2014. You can earn 5extra points if you turn the project in by Wednesday, November 26, 2014. Projects turnedin late will be penalized 20 points for every 24-hour period they are late.REQUIREMENTS1. All analysis will be completed using Excel. Handwritten or typed statements of cashflows are unacceptable. Any explanations necessary must be typed or legibly written.2. You must compile a 5-year statement of after-tax operating cash flows for the project.The after-tax operating cash flow statement should be reduced (using the automaticreduction in the software) to 1 sheet of paper. This should be printed landscape style(i.e. sideways) to make sure all 5 years fit on one page. You do not need to present anybalance sheets.3. In addition to the statements, you must print out the formulas you used for thestatement. This is done by showing the formulas on the screen instead of the numbers.When using Excel, for example, highlight the cell(s) you are interested in, pull down theTools menu, click on options, and click on the formulas box. This will thendisplay the formulas. The formula printouts are to be handed in with the statement. Theydo not have to all fit onto one page.14. Average checks should be rounded to the nearest penny. Number of customers shouldbe rounded up to the next number. All dollar figures should be rounded to the nearestdollar. Make sure that your statement is formatted with dollar signs and commas.5. Staple all of your analysis together so that it wont get lost or misplaced. Projectsthat are not clipped or stapled together will lose points.6. You must put the ID number you used on the first page (front) of your project.You can complete this project with the same partners asProject Part A.You are expressly forbidden to consult anyone else about thisproject (except the instructor).Other Pertinent Information1. The capital expenditure analysis will be for the calendar years 2015-2019.2. The project will be operated until the end of 2019 at which time the entire operation,including the equipment and property, will be sold. Management estimates the sales price(at the end of year 5) can be approximated by assuming that year 5’s after-tax operatingcash flow, OCF5, will be the cash flow generated by the property in perpetuity from year6 onward and finding the present value of this perpetuity. (Hint: the correct discount rateto use here is based on the approach to NPV which management uses).3. The cost of land is ID X 300; the cost of land improvements is ID x 200; the cost ofthe building is ID x 1,100. Neither the land improvements nor the building have asalvage value. The life of the land improvements is 10 years; the life of the building is 35years.4. The tax rate on income, capital gains and recaptured depreciation is 25 percent.FINANCING PARAMETERSYou are to analyze the project using the WACC (Weighted Average Cost of Capital)approach to project evaluation. The managers have determined that the debt used tofinance the project will have an interest rate (KD) of 8%. In order to estimate the owner’srequired rate of return, KE, for the projects, management uses the Security Market Line(SML). They estimate that the return on the market portfolio (Rm) is 15% and the riskfree rate, (Rf) is 6%.Remember that 60 percent of the project is being financed with long-term debt. The betaof the project is estimated to be 1.5.2Net Working Capital RequirementsNet working capital is needed for any hospitality operation. If you are unsure of what networking capital is, feel free to peruse your HFT 3431 text or look at Chapter 3 in our HFT4464 text. The following table indicates the net working capital needs under each option.Total Net Working CapitalYear 1$300,000Year 2$400,000REQUIRED ANALYSISPerform a capital expenditure analysis for the project by answering the followingquestions or completing the task required:1. What is the owner’s required rate of return (KE) for the project? Show yourcalculations and provide an answer to TWO decimal places (example: 17.65%).2. What will be the weighted average cost of capital (KA) for the project? Show yourcalculations and provide an answer to TWO decimal places (example: 9.04%).3. Calculate and provide the investment outflows (expenditures) for the project at time 0,time 1 and time 2.4. What will be the annual after tax operating cash flows (OCFt) for the project?Determine the after-tax operating cash flow for the project for 5 years. You must useExcel to answer this question and show your formulas.5. What will the net sales price be from selling the project at time 5? What is the totalnet cash flow (not just the sale) at time 5? Show your calculations.6. Put ALL of the net cash flows on a timeline. It should be such that you have one netcash flow per year, including time 0.7. What is the payback period (in years) for the project? Calculate the payback periodwith at least one decimal place and show your calculations.8. What is the IRR of the project? (Use the IRR function in Excel to calculate the IRRand show your formulas). Make sure that you report the IRR using two decimalplaces (Example: IRR=14.23%). In addition to finding the IRR via the computer, findthe IRR graphically by drawing the NPV profile of the project onto a graph (this will bedemonstrated in class). Graph at least 3 different NPVs for the project.NOTE: Given the variance in ID numbers, you could have a very high IRR or a very lowone. Do not let the absolute value concern you too much!39. Find the NPV of the project by using the NPV function in Excel and show yourformulas.10. Should the project be undertaken?Carefully explain your answers in paragraph form. A single sentence explanation isinsufficient; make sure you discuss all of the analytical tools you have used to reach yourconclusion.HELPFUL HINTSThis is a relatively complicated, but realistic capital budgeting exercise. It is intended tointegrate the material learned in class. Submissions are expected to resemble thosepresented to an upper level manager in a hospitality operation. Make sure they are legibleand presented in good order. YOU MUST FASTEN YOUR PROJECT TOGETHER!PLAN FOR DISASTER. Make back-up copies of your project files onto at least twodrives or disks. Lost or missing files will not warrant an extension. Take good care ofyour files and your back-ups.Good luck and ask questions if you dont understand.** Note: You must also submit the Excel file through assignment drop box via WebCourses with all group members names written on the worksheets.