D H 1 J K L M N 0 0 P Q Q R s U A Years Contributing 6 A Pre-Retirement APR: 9 A# of Years Withdrawing: Place the sensitivity diagram here: E F 1 Create 3 data tables in the range (H3:Q12) that separately demonstrate the impact on Retirement Analysis of Projected Distributions 2 the anticipated periodic distribution (output cell) by changing: INPUT AREA 3 (a) number of years contributing (D5) Pre-retirement % Change 4 (b) anticipated interest earned pre-retirement APR (D6) Current retirement account balance $100,000 5 (c) number of years withdrawing (012) Number of years contributing 30 0% Anticipated interest earned PRE-retirement (AL 7.50% 0% 7 Use the x change method to create the data table (NOT the substitution metho Periodic contribution amount $400 $ Modify the 3 input cells in column D. changing their contents from a discrete Contribution frequency value (i.e.30) to a formula (i.e. - 30x (1+change } Seler le momente 2 ev sem mom and'?/cv weet 10 Use the three worksheet cells designated in Column F to hold the change (growth rate). 11 LEAVE THE CHANGE RATE SET TO 0% Post-retirement: 12 Number of years withdrawing 25 0% 13 For each data table: Anticipated interest earned post-retirement 4.50% 14 "Use percent changes (row inputs) of -20% through +20% in increments of 5% Distribution frequency 2 15 "In each data table, use custom formatting to provide the row label 'Anticipated Seler le momente 2 ev serumom and'?/cv weet 16 Periodic Distribution for the reference to the output cell. 17 CALCULATIONS AREA 18 Create a sensitivity diagram (near cell H15) demonstrating all 3 input variables Pre-retirement 19 - add a dynamic chart title (link to cell C1), legend (placed at the bottom of the chart). Periodic rate earned 0.63% 20 and label both axes and all data series. The vertical axis values should have a minimu Number of periods 360 21 of $2,000, and the horizontal axis values should range from -20% to 20%. Total account value at retirement $1,481,132 22 23 Find the impact of each variable (slope) on projected periodic distribution (017:019) Post-retirement: 24 and identify which variable has the greatest impact on the anticipated periodic Periodic rate earned 0.192 25 distributions (M22). Complete the sentence in cell M29. Number of periods 600 26 27 OUTPUT AREA 28 Anticipated periodic distribution (i.e. payment) $4,114 29 30 Do NOT change any of the values in the retirement analysis 31 Slope Years contributed APR Years withdrawing Which variable has the greatest impact on anticipated periodic distributions? "Knowny's are the costslunits produced by the data table "Known a's are the percentage change Based on the slope value, if the number of years withdrawals are made increases, then the distribution (withdrawal) amount should D H 1 J K L M N 0 0 P Q Q R s U A Years Contributing 6 A Pre-Retirement APR: 9 A# of Years Withdrawing: Place the sensitivity diagram here: E F 1 Create 3 data tables in the range (H3:Q12) that separately demonstrate the impact on Retirement Analysis of Projected Distributions 2 the anticipated periodic distribution (output cell) by changing: INPUT AREA 3 (a) number of years contributing (D5) Pre-retirement % Change 4 (b) anticipated interest earned pre-retirement APR (D6) Current retirement account balance $100,000 5 (c) number of years withdrawing (012) Number of years contributing 30 0% Anticipated interest earned PRE-retirement (AL 7.50% 0% 7 Use the x change method to create the data table (NOT the substitution metho Periodic contribution amount $400 $ Modify the 3 input cells in column D. changing their contents from a discrete Contribution frequency value (i.e.30) to a formula (i.e. - 30x (1+change } Seler le momente 2 ev sem mom and'?/cv weet 10 Use the three worksheet cells designated in Column F to hold the change (growth rate). 11 LEAVE THE CHANGE RATE SET TO 0% Post-retirement: 12 Number of years withdrawing 25 0% 13 For each data table: Anticipated interest earned post-retirement 4.50% 14 "Use percent changes (row inputs) of -20% through +20% in increments of 5% Distribution frequency 2 15 "In each data table, use custom formatting to provide the row label 'Anticipated Seler le momente 2 ev serumom and'?/cv weet 16 Periodic Distribution for the reference to the output cell. 17 CALCULATIONS AREA 18 Create a sensitivity diagram (near cell H15) demonstrating all 3 input variables Pre-retirement 19 - add a dynamic chart title (link to cell C1), legend (placed at the bottom of the chart). Periodic rate earned 0.63% 20 and label both axes and all data series. The vertical axis values should have a minimu Number of periods 360 21 of $2,000, and the horizontal axis values should range from -20% to 20%. Total account value at retirement $1,481,132 22 23 Find the impact of each variable (slope) on projected periodic distribution (017:019) Post-retirement: 24 and identify which variable has the greatest impact on the anticipated periodic Periodic rate earned 0.192 25 distributions (M22). Complete the sentence in cell M29. Number of periods 600 26 27 OUTPUT AREA 28 Anticipated periodic distribution (i.e. payment) $4,114 29 30 Do NOT change any of the values in the retirement analysis 31 Slope Years contributed APR Years withdrawing Which variable has the greatest impact on anticipated periodic distributions? "Knowny's are the costslunits produced by the data table "Known a's are the percentage change Based on the slope value, if the number of years withdrawals are made increases, then the distribution (withdrawal) amount should