present value; interest; NPV; IRR

Calculate present value; interest; NPV; IRR etc. on spreadsheet

---
A plan is made to start a school with a one-year training course. The plan involves running this school for 10 years. In the first four years 20, 30, 40 and 50 students are expected to enroll, while for the remaining years the number is assumed to grow by 5% per year. Tuition is set at $6,000 per year in Year1, which in subsequent years will grow by the expected general inflation, assumed to be 4% per year. Space is to be leased for 10 years at $80,000 per year, with no inflation adjustment. The personnel consist of the director, who will earn $60,000 per year, an administrator with a salary of $30,000, and instructors with a salary of $45,000. All salaries will go up with inflation. Each instructor can have at most 30 students. Each of the persons employed and each student must have a computer workstation, which costs $3,000 to purchase in any year (no inflation adjustment needed), and which can be used in all subsequent years. Each computer's maintenance is $300 per year, increasing with inflation. The interest rate for discount purposes is 10 %( No need to adjust it for inflation). No residual value is assumed to remain after 10 years.

a) Create a well organized and well formatted spreadsheet to evaluate this project. Use a data section with range names. Calculate the net present value as evaluated in the first year, and the internal rate of return. [17 marks]
Hints:
- All costs and revenues happen at the beginning of each year.
- You need to calculate Numbers of students, Number of instructors, total number of computers and numbers of computers to be bought each year; to start your study.
- To calculate numbers of students use ROUND function to round the calculated number to the nearest integer number. To calculate numbers of instructors use ROUNDUP.

b) Calculate the interest-based cash balances for the 10 years, and graph these in a bar graph using titles for graph and both axis. Display units of the x-axis labels in thousands. Also calculate payback period and interest based payback period for this project [pay attention to the definition of payback period]. [7 marks]
c) Perform sensitivity analysis of the NPV and IRR with respect to computer workstation cost, with percentage of changes -10%,-5%, 0, 5% and 10%. [6 marks]
d) Use the Data, Table command to calculate NPV for tuition varying between
$0-10,000 by $2,000 and the number of students per instructor varying from 10
to 50 by 5. [10 marks]