Need help on this:

How do you calculate items as a function of another cell? Each item in the table is described below the table

Figure 10.1 - Constants Section

CONSTANTS	2016	2017	2018
Retiree Years of Service	25	25	25
Average Increase in Teacher Salary	0.01	0.01	0.01
Retiree Rate	0.04	0.04	0.04
Mortality Rate	0.05	0.05	0.05
Expected Average Final Salary	$ 82,000	$ 82,820	$ 83,648
Expected Administrative Expense	NA	$25,000,000	$25,250,000

Retiree Years of ServiceOn average, teachers work for 25 years before retiring.

Average Increase in Teacher SalaryTeacher salaries are expected to increase by an average of 1 percent each year for the next 30 years.

Retiree RateOn average, 4 percent of teachers are expected to retire each year in the next 30 years.

Mortality RateOn average, a pensioner receives payouts for 20 years. On average, 5 percent of pensioners are expected to die each year.

Expected Average Final SalaryThe average final salary for teachers retiring in 2016 was $82,000. The average final salary is expected to increase somewhat each year, as shown

Expected Administrative ExpenseThe pension fund has employees, rents office space, consults with experts in securities markets about investments, and has other expenses. The plans administrative cost is expected to be $25 million in 2017 and to increase each year, as shown.

Figure 10.4

CALCULATIONS	2016	2017	2018
Average Teacher Salary	$ 68,500
Number of Active Teachers	133,000
Number of New Retirees	NA
Number of Retirees	101,500
Total Teacher Compensation	NA
Employee Contribution to Fund	NA
State Contribution to Fund	NA
Average Retiree Benefit	NA
Expected Benefits Payout	NA

Calculations section

The Calculations section is shown in Figure 10-4. Some 2016 values are provided. Values for 2017 through 2046 are calculated by formula. Use cell addresses when referring to constants in formulas unless otherwise directed. Use absolute addressing properly. An explanation of the line items follows the figure

Average Teacher SalaryThe average in a year is a function of the prior years value and the expected rate of increase in the year. The latter value is from the Constants section.

Number of Active TeachersThis amount is a function of the prior years value and the expected productivity factor. The latter value is from the Inputs section.

Number of New RetireesThis amount is a function of the number of active teachers in the prior year (from the previous row) and the retiree rate for the year (from the Constants section).

Number of RetireesThe number of retirees in a year is the number of retirees in the prior year plus the number of new retirees in the year, minus the number of retirees who die in the year. The number of retirees who die is a function of the number of retirees in the prior year and the years mortality rate. The latter value is from the Constants section.

Total Teacher CompensationThis amount is a function of the average teacher salary in the year and the number of active teachers. Both values are from the Calculations section.

Employee Contribution to FundThis value is a function of total teacher compensation (from the previous row) and the contribution rate (from the Inputs section).

State Contribution to FundThis value is a function of the employee contribution (from the previous row) and the state contribution factor (from the Inputs section).

Average Retiree BenefitThe average retiree payout in a year is a function of the expected final salary in the year (from the Constants section), the .022 payout rate (a factor you can hard- code), and the expected years of service (from the Constants section). This amount should be increased by the expected cost of living factor and then reduced by any give-back amount; both values are from the Inputs section.

Expected Benefits PayoutThe total benefits to be paid in a year is a function of the average retiree benefit and the number of retirees in a year. Both values are from the Calculations section.