<\/p>\n
CREATING A SPREADSHEET FOR DECISION SUPPORT<\/strong><\/p>\n In this assignment, you produce a spreadsheet that models the problem. Then, in Assignment 2, you will use the spreadsheet to gather data and write a memorandum that explains your findings. In Assignment 3, you may be asked to prepare an oral presentation of your analysis.<\/p>\n A spreadsheet has been started and is available for you to use; it will save you time. If you want to use the spreadsheet skeleton, locate Case 10 in your data files and then select TeachersPensionFund.xlsx<\/strong>. Your worksheet should contain the following sections:<\/p>\n \u2022 Constants A discussion of each section follows.<\/p>\n <\/p>\n Constants Section<\/strong><\/p>\n Your spreadsheet should include the constants shown in Figure 10-1. An explanation of the line items follows the figure.<\/p>\n <\/p>\n \u2022 Retiree Years of Service\u2014On average, teachers work for 25 years before retiring. Inputs Section<\/strong><\/p>\n Your spreadsheet should include the inputs shown in Figure 10-2. Possible values are shown in the figure.<\/p>\n Each of the inputs applies to each of the 30 years modeled. An explanation of the line items follows the figure.<\/p>\n <\/p>\n \u2022 Cost of Living Adjustment\u2014By union contract, this adjustment is 3 percent. Ideally, plan administrators would like to negotiate this percentage lower. Summary of Key Results Section<\/strong><\/p>\n Your worksheet should include the key results shown in Figure 10-3. An explanation of the line items follows the figure.<\/p>\n <\/p>\n \u2022 NPV of Unfunded Liability\u2014The NPV of the pension fund\u2019s unfunded obligation is computed elsewhere in the spreadsheet and can be echoed here. Calculations Section<\/strong><\/p>\n 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.<\/p>\n <\/p>\n \u2022 Average Teacher Salary\u2014The average in a year is a function of the prior year\u2019s value and the expected rate of increase in the year. The latter value is from the Constants section. Fund Balance Statement Section<\/strong><\/p>\n This section shows a calculation of the pension fund balance at the end of each year, as illustrated in Figure 10-5. The pension fund\u2019s balance is increased by employee contributions, state contributions, and earnings on fund assets. The pension fund\u2019s balance is decreased by benefits paid and administrative expenses. An explanation of the line items follows the figure.<\/p>\n <\/p>\n \u2022 Beginning Balance\u2014The balance at the beginning of a year equals the balance at the end of the prior year. Fund Liability Section<\/strong><\/p>\n This section shows a calculation of the NPV of the pension fund\u2019s unfunded liability and the ratio of fund assets to this NPV, as illustrated in Figure 10-6. An explanation of the line items follows the figure.<\/p>\n <\/p>\n \u2022 Expected Benefits Payout\u2014The fund\u2019s payout in each year has been calculated elsewhere in the spreadsheet and can be echoed here. The series of values will be used in the NPV calculation. <\/p>\n Using the Spreadsheet to Gather Data<\/strong><\/p>\n You have built the spreadsheet to create \u201cwhat-if\u201d scenarios for the model\u2019s input values. The inputs represent the logic of a question and the outputs provide information needed to answer the question. The budget director\u2019s questions are discussed next.<\/p>\n Question 1 (Base Case)<\/strong><\/p>\n The budget director asks, \u201cWhat are the net present value of the unfunded liability and the ratio of assets to the net present value of the unfunded liability, given the current situation? This is the \u2018base case.\u2019 How bad are things right now?\u201d The inputs for the base case are shown in Figure 10-7.<\/p>\n <\/p>\n Enter the inputs and then observe the outputs in the Summary of Key Results section. Next, manually record the results in a summary area. You could use a second worksheet for this purpose, as shown in Figure 10-8 (values shown are for illustration only).<\/p>\n <\/p>\n Question 2 (Worst Case)<\/strong><\/p>\n The budget director says, \u201cIn the worst case, we cannot do anything about the cost of living adjustment, the stock market tanks, and we earn very little\u2014say 3 percent. Productivity goes to zero and other factors remain the same. That is the \u2018worst case.\u2019 How bad would that be?\u201d The inputs for the worst case are shown in Figure 10-9.<\/p>\n <\/p>\n Enter the inputs and then observe the outputs in the Summary of Key Results section. Next, manually record the results in the summary area.<\/p>\n Question 3 (Aggressive Case)<\/strong><\/p>\n The budget director says, \u201cIn my dreams, I take an aggressive line with the union and I win the battles. The cost of living adjustment is reduced to 1 percent. The productivity factor doubles to 1 percent. The employee contribution rate is increased to 10 percent. The salary give-back is $4,000, and the stock market comes back, so we earn 10 percent on our money. That is the \u2018aggressive case.\u2019 How good would things be? Surely the ratio gets to 80 percent then!\u201d The inputs for the aggressive case are shown in Figure 10-10.<\/p>\n <\/p>\n Enter the inputs and then observe the outputs in the Summary of Key Results section. Next, manually record the results in the summary area.<\/p>\n Question 4 (Rescue Case)<\/strong><\/p>\n The budget director says, \u201cI know the governor is going to ask what the state would have to do to bail out the current system. So, assume the conditions of the base case, except for the state contribution factor.\u201d Run a \u201cwhat-if\u201d scenario with that factor until you reach a ratio of 80 percent. How big a factor is needed? Call this question the \u201crescue case.\u201d How much extra money would the state have to contribute versus the base case contribution by the state? The inputs for the rescue case are shown in Figure 10-11.<\/p>\n <\/p>\n Enter the inputs and then observe the outputs in the Summary of Key Results section. The extra dollar amount that the state would contribute can be calculated by comparing state contribution amounts in the Calculations section in the two scenarios. Next, manually record the results in the summary area.<\/p>\n When you finish gathering data for the four questions, print the model\u2019s worksheet with any set of inputs. Print the summary sheet data as well, and then save the spreadsheet for the final time.<\/p>\n <\/p>\n Documenting your Findings and Recommendation in a Memo<\/strong><\/p>\n Document your findings in a memo that answers the budget director\u2019s four questions. The memo should also state your more general assessment of the fund\u2019s financial position: How bad or good is the situation? Use the following guidelines to prepare your memo in Microsoft Word:<\/p>\n \u2022 Your memo should have proper and standard headings, such as Date, To, From, and Subject. You can address the memo to the administrators of the state pension fund. Set up your memo as described in Tutorial E. <\/p>\n GIVING AN ORAL PRESENTATION<\/strong><\/p>\n Assume that the budget director asks you to be ready to present your analysis and results in an oral presentation to some key legislators. \u201cThese guys are always looking for the silver bullet\u2014you know, trying to fix the problem by changing only one thing. So, they want to see sensitivity data and they want it in chart format,\u201d she tells you. \u201cFor example, someone will want to know how much the asset-to-liability ratio would change if there was a change in the market rate of return. So get those kinds of charts ready.\u201d<\/p>\n Prepare to talk to the group for 10 minutes or less. Tutorial F explains how to prepare and give an oral presentation.<\/p>\n Your instructor will tell you what sensitivity analyses to prepare or may tell you to choose these analyses yourself. The example chart to which the budget director referred should look like the one shown in Figure 10-12.<\/p>\n","protected":false},"excerpt":{"rendered":" CREATING A SPREADSHEET FOR DECISION SUPPORT In this assignment, you produce a spreadsheet that models the problem. Then, in Assignment 2, you will use the spreadsheet to gather data and write a memorandum that explains your findings. In Assignment 3, you may be asked to prepare an oral presentation of your analysis. A spreadsheet […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","template":"","meta":[],"disciplines":[211],"paper_types":[],"tagged":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/questions\/17618"}],"collection":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/questions"}],"about":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/types\/questions"}],"author":[{"embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/comments?post=17618"}],"version-history":[{"count":0,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/questions\/17618\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/media?parent=17618"}],"wp:term":[{"taxonomy":"disciplines","embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/disciplines?post=17618"},{"taxonomy":"paper_types","embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/paper_types?post=17618"},{"taxonomy":"tagged","embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/tagged?post=17618"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n\u2022 Inputs
\n\u2022 Summary of Key Results
\n\u2022 Calculations
\n\u2022 Fund Balance Statement
\n\u2022 Fund Liability<\/p><\/blockquote>\n
\n\u2022 Average Increase in Teacher Salary\u2014Teacher salaries are expected to increase by an average of 1 percent each year for the next 30 years.
\n\u2022 Retiree Rate\u2014On average, 4 percent of teachers are expected to retire each year in the next 30 years.
\n\u2022 Mortality Rate\u2014On average, a pensioner receives payouts for 20 years. On average, 5 percent of pensioners are expected to die each year.
\n\u2022 Expected Average Final Salary\u2014The average final salary for teachers retiring in 2016 was $82,000. The average final salary is expected to increase somewhat each year, as shown.
\n\u2022 Expected Administrative Expense\u2014The pension fund has employees, rents office space, consults with experts in securities markets about investments, and has other expenses. The plan\u2019s administrative cost is expected to be $25 million in 2017 and to increase each year, as shown.<\/p><\/blockquote>\n
\n\u2022 Long Term Rate of Return\u2014A 7.5 percent return on investments is assumed. Plan administrators want to see the effects of changing this variable.
\n\u2022 Productivity Factor\u2014The total number of teachers has been declining by 0.5 percent each year in recent years. State officials hope for greater productivity in the future.
\n\u2022 Employee Contribution Rate\u2014Working teachers contribute 9.5 percent of their salary to the pension fund. Some state officials think this rate must increase in the future.
\n\u2022 Final Salary Give Back\u2014State officials want a reduction in the final salary for pension purposes. The reduction would be called the \u201cgive-back.\u201d
\n\u2022 State Contribution Factor\u2014By contract, the state contributes 2.5 times what the teachers contribute. This factor may need to be increased to ensure there is enough money to pay pensions.<\/p><\/blockquote>\n
\n\u2022 Ratio of Assets to Liability NPV\u2014The ratio of the value of fund assets to fund liabilities is computed elsewhere in the spreadsheet and can be echoed here.<\/p><\/blockquote>\n
\n\u2022 Number of Active Teachers\u2014This amount is a function of the prior year\u2019s value and the expected \u201cproductivity factor.\u201d The latter value is from the Inputs section.
\n\u2022 Number of New Retirees\u2014This 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).
\n\u2022 Number of Retirees\u2014The 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 year\u2019s mortality rate. The latter value is from the Constants section.
\n\u2022 Total Teacher Compensation\u2014This 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.
\n\u2022 Employee Contribution to Fund\u2014This value is a function of total teacher compensation (from the previous row) and the contribution rate (from the Inputs section).
\n\u2022 State Contribution to Fund\u2014This value is a function of the employee contribution (from the previous row) and the state contribution factor (from the Inputs section).
\n\u2022 Average Retiree Benefit\u2014The 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 hardcode), 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.
\n\u2022 Expected Benefits Payout\u2014The 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.<\/p><\/blockquote>\n
\n\u2022 Add: Employee Contribution\u2014This amount has been calculated elsewhere in the spreadsheet and can be echoed here.
\n\u2022 Add: State Contribution\u2014This amount has been calculated elsewhere and can be echoed here.
\n\u2022 Add: Income on Investments\u2014This amount equals the fund balance at the beginning of the year multiplied by the expected earnings rate. The latter value is from the Inputs section.
\n\u2022 Less: Benefits Payout\u2014This amount has been calculated elsewhere and can be echoed here.
\n\u2022 Less: Administrative Expenses\u2014This amount is taken from the Constants section and can be echoed here.
\n\u2022 Ending Balance\u2014This amount equals the beginning balance plus the employee contribution, the state contribution, and income on investments, minus the benefits paid and administrative expenses.<\/p><\/blockquote>\n
\n\u2022 Net Present Value of Payouts\u2014The NPV of a series of values is calculated using a discount rate applied to those values. Apply the NPV function to the series of expected benefit payouts using .075 as the discount rate. You can hard-code the discount rate.
\n\u2022 NPV of Unfunded Liability\u2014This value is the NPV of payouts minus the fund balance at the end of 2017.
\n\u2022 Ratio of Assets to Liability NPV\u2014This value is the ratio of the fund balance at the end of 2017 to the NPV of payouts.<\/p><\/blockquote>\n
\n\u2022 Briefly outline the situation. However, you need not provide much background\u2014you can assume that readers are familiar with the situation.
\n\u2022 Answer the four questions in the body of the memo.
\n\u2022 Include tables and charts to support your claims, as your instructor specifies. Tutorial E explains how to create a table in Microsoft Word. Tutorial F explains how to create charts in Excel.<\/p><\/blockquote>\n