What-If Analysis excel computer data
arianadj
Project6instructions-Spring2019_Rev3.docxProject 6 Instructions
Starting File: Project 6 – Ralston – Student.xlsx
The county commission of Ralston County is proposing to create an annual youth conference and fair, which would offer leadership and technology training as well as opportunities for the local youth to make new friends and share skills. Outside corporations and government agencies at all levels of government would be able to purchase vendor space to promote their products and/or recruit students for employment. To do this, the County will need to sign a long-term lease for a large 30-acre piece of property, and construct a moderate size convention complex on that property. The convention facilities will be built out over a 20-year period. This will require substantial startup costs, which they hope to recoup through student registration fees, vendor registration fees and vendor sponsorship fees. Before agreeing to this project, the County needs to do a first-level estimate of whether this venture will be worthwhile doing, from a financial perspective.
Notes:
· This workbook has 2 worksheets: the Documentation worksheet (for the history of this workbook) and the Proposal worksheet. Be sure to work on the correct worksheet as indicated in the instructions.
· You should use cell references in all formulas – do not re-enter data.
· Refer to the images provided at the end of this document to verify your results.
· You MUST use Excel’s Fill – Series features to do the extrapolation and interpolation steps. You may not use formulas.
· Remember: Project work must be your own individual work – no group work allowed!
Steps:
Download the starting file from the Assignment Dropbox and save it on your computer. Then open it to work on it.
1. In the Documentation worksheet, enter your name in cell B3, and the date you created this workbook in cell B4.
2. Enter a short purpose statement in cell B5.
Switch to the Proposal worksheet.
3. To start this project, the County must pay an initial lease signing fee of 1 million dollars. Enter this value in C5 (Note: all values in this worksheet are in millions, so you should enter this value as 1.0).
4. In Year 1, the Lease cost will be 0.35 million. Enter this value in D5. The lease contract stipulates that the lease cost will increase by a steady 0.125 million per year. Use the Excel Series features to extrapolate lease costs for Years 2-20. (Hint: use the Linear option.)
5. There will be no shutdown costs associated with the lease itself, so enter 0 in X5.
6. Building construction costs for the main facilities will be spread over 10 years. Startup construction costs will be 5.2 million. Enter this value in C6, In Year 1, an additional 0.7 million will be expended on additional construction of facilities. Enter this value in D6. In Years 2-10, the estimated construction costs will increase on a growth trend at 7% per year. Use the Excel Series features to extrapolate construction costs for Years 2-10.
8. There will be no shutdown costs associated with construction, so enter 0 in X6.
9. Building operation costs include maintenance, utilities, supplies, and labor. Because the first building will not be completed and ready for use until Year 2, enter 0 for building operation costs for Startup and Year 1.
10. Starting in Year 2, the County is budgeting 0.22 million for building operations. Enter this value in E7. It is expected that these costs will increase on a growth pattern by 6% per year. Use the Excel Series features to extrapolate building operation costs for Years 3-20.
11. At the end of Year 20, if the County decides not to negotiate an extension to the lease, they will be required to sell the buildings to the property owner. Shutdown costs will involve removing all of the furniture and equipment that the County installed in the facilities over the years. The County is budgeting 1.2 million for this shutdown work. The County will also have to remove all sponsorship signs and logos from the interior and exterior of all buildings. The expected cost for this will be 1.6 million. Enter the total shutdown building operations costs in X7. The County expects to earn 42.6 million for the sale of the buildings. Enter this value in X14.
12. There will be no income at Startup or in Year 1 for any category of income, so enter 0 in C11:D14. There will also be no income for any fees during shutdown, so enter 0 in X11:X13.
13. The County estimates that the facilities will only be able to accommodate a total of 120,000 students by Year 20, which will limit the total possible income from student registration fees. It is expected that student registration fees will produce 0.08 million in Year 2, and increase on a growth pattern through Year 20 to a final value of 2.10 million. Enter 0.08 in E11 and 2.10 in W11. Use the Excel Series feature to Interpolate in income from student fees in Year 3 through Year 19.
14. The County estimates that the facilities will only be able to accommodate a total of 200 vendor booths in the convention’s main hall by Year 20, which will limit the total possible income from vendor registration fees. It is expected that vendor registration fees will produce 0.17 million in Year 2, and increase on a growth pattern through Year 20 to a final value of 2.7 million. Enter 0.17 in E12 and 2.7 in W12. Use the Excel Series feature to Interpolate in income from vendor fees in Year 3 through Year 19.
15. The County will sell sponsorship opportunities within the facilities as well as sell the naming rights to the main convention hall. Yearly income for sponsorship fees is projected to be 0.2 million in Year 2, and increase on a growth pattern through Year 20 to a final value of 1.4 million. Enter 0.2 in E13 and 1.4 in W13. Use the Excel Series feature to Interpolate in income from sponsorship fees in Year 3 through Year 19.
16. Calculate totals in Rows 8 and 15 for each year (including shutdown and startup), and in column Y for expenses and income in each category.
17. In the Analysis section (rows 18-19), use cell references to display the total income and total expenses. In Row 20, calculate profit for all years in C20:X20. Calculate totals in Y18:Y20.
18. The County wishes to compare the use of the money for this project to simply investing the money at the prevailing interest rates. They want to know what interest rate would be required that would provide a better return on the money by investing versus doing this project. Enter the values 1% through 20% in D22:W22 to represent possible interest rates. Calculate Net Present Value in D23:W23 using the NPV function for each of those rates for Years 1-20 plus Shutdown, and then add that result to the initial Startup value from C20.
19. Calculate the internal rate of return in C25 using the IRR function, for the entire time period (including startup and shutdown).
20. Format all money values in rows 5-8, 11-15, 18-20, and 23 with Accounting style.
21. Insert a line chart comparing Profit to NPV for all years including Startup and Shutdown. Format as shown in the image below. Position the chart at C26:M40.
22. Set the page orientation to Landscape, and the margins to Narrow.
23. Set Rows 1-3 and columns A:B to repeat on every page. Set a page breaks between Years 10 and 11. Ensure that the chart show on page 1.
24. Save your file and close Excel. Submit your file into the Assignment Dropbox.
Copyright © 2019 by P. McDermott-Wells – All Rights Reserved.
May not be posted on any internet site except FIU Canvas.
Rev. 03/27/19
Figure 1
