Reference no: EM13984365

Evergreen College holds an endowment currently worth $700 million (an endowment is a permanent fund of money that generates income and also can be used to pay for projects). The Chief Investment Officer of the endowment has hired you to predict the performance of the endowment over the next 10 years. Given the current investment strategy, money invested in the endowment is expected to grow by 8% per year. Over the next 7 years, Evergreen's development office is running a major capital campaign among its alumni. During the entire 7-year campaign the college expects to collect a total of $300 million in contributions that will be added to the endowment, although the campaign may collect a total as low as $150 million and as high as $400 million. A portion of the endowment is also spent each year to support ongoing activities at the college (this is called the draw). During this past year, the draw was $65 million. During the next ten years the draw in each year will be calculated according to a formula that is the sum of two terms: (i) 70% of the previous year's draw and (ii) a certain percentage (p) of the value of the endowment at the beginning of the year. This percentage p depends on the level of the endowment itself, and is given in the following table: Endowment value (millions) draw percentage (p) Below $ 500 0.0% $500-600$ 1.0% $600-700$ 2.0% $700-800$ 2.5% $800 or above 3.0% For example, if the endowment is $650 million at the beginning of the year, then the draw is equal to (70%)*(previous year's draw) + (2%)*($650 million). a. Assume that the capital campaign collects $300 million. Build a spreadsheet to calculate the value of the endowment after the next 10 years. Your spreadsheet will be graded both for its technical correctness and for its adherence to the principles of spreadsheet engineering. Note that documentation of each calculation is not required, nor is extensive formatting for appearance. However, if you need to make any assumptions beyond the problem description given above, you should include an explanation of these assumptions at the top of the spreadsheet.