Help please!!! Problem Solving Cases in Microsoft Access & Excel - 15th edition - Case 10

profileouijabored95
TeachersPensionFund.xlsx
Home>Computer Science homework help>Help please!!! Problem Solving Cases in Microsoft Access & Excel - 15th edition - Case 10

Model

TEACHERS PENSION FUND
CONSTANTS 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046
Retiree Years of Service 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25 25
Average Increase in Teacher Salary 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01
Retiree Rate 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04
Mortality Rate 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05 0.05
Expected Average Final Salary $ 82,000 $ 82,820 $ 83,648 $ 84,485 $ 85,330 $ 86,183 $ 87,045 $ 87,915 $ 88,794 $ 89,682 $ 90,579 $ 91,485 $ 92,400 $ 93,324 $ 94,257 $ 95,199 $ 96,151 $ 97,113 $ 98,084 $ 99,065 $ 100,056 $ 101,056 $ 102,067 $ 103,087 $ 104,118 $ 105,159 $ 106,211 $ 107,273 $ 108,346 $ 109,429 $ 110,524
Expected Administrative Expense NA $25,000,000 $25,250,000 $25,502,500 $25,757,525 $26,015,100 $26,275,251 $26,538,004 $26,803,384 $27,071,418 $27,342,132 $27,615,553 $27,891,709 $28,170,626 $28,452,332 $28,736,855 $29,024,224 $29,314,466 $29,607,611 $29,903,687 $30,202,724 $30,504,751 $30,809,799 $31,117,896 $31,429,075 $31,743,366 $32,060,800 $32,381,408 $32,705,222 $33,032,274 $33,362,597
INPUTS
Cost of Living Adjustment 0.03
Long Term Rate of Return 0.075
Productivity Factor 0.005
Employee Contribution Rate 0.095
Final Salary Give Back $ - 0
State Contribution Factor 2.5
SUMMARY OF KEY RESULTS
NPV of Unfunded Liability
Ratio of Assets to Liability NPV
CALCULATIONS 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046
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
FUND BALANCE STATEMENT 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046
Beginning Balance NA
Add: Employee Contribution NA
Add: State Contribution NA
Add: Income on Investments NA
Less: Benefits Payout NA
Less: Administrative Expenses NA
Ending Balance $ 40,000,000,000
FUND LIABILITY 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046
Expected Benefits Payout NA
Net Present Value of Payouts NA
NPV of Unfunded Liability NA
Ratio of Assets to Liability NPV NA