D E F G H I J K L M N P A 1 Complete a 2-dimensional data table (G4:M15). 2 The output cell is the Anticipated Periodic Distribution. 3 4 The horizontal input cell is post-retirement 5 years withdrawing (ranging from 15 to 25 years 6 in increments of 2 years) 7 8 The vertical input cell is post-retirement APR earned 9 (ranging from 3% to 8% in increments of 0.5%) 10 11 Use CUSTOM formatting to display a blank cell (empty 12 string "") in the cell containing the reference to the 13 output cell 14 15 Apply CONDITIONAL formatting to the data table to 16 display all cells with a periodic distribution greater 17 than $7,000 with bold font and a light blue fill. 18 19 What combination of Post-retirement APR and 20 post-retirement years withdrawing provides a 21 periodic distribution of approximately $7,750? 22 23 24 25 26 27 28 29 30 31 B Retirement Analysis of Projected Distributions INPUT AREA Pre-retirement Current retirement account balance $100,000 Number of years contributing 30 Anticipated interest earned PRE-retirement (APR) 7.50% Periodic contribution amount $400 Contribution frequency 2 (Select 1 for monthly, enter 2 for semi-monthly, and 3 for weekly) Post-retirement: Number of years withdrawing Anticipated interest earned post-retirement (APR) Distribution frequency 25 4.50% 2 (Select 1 for monthly, enter 2 for semi-monthly, and 3 for weekly) CALCULATIONS AREA Pre-retirement Periodic rate earned Number of periods Total account value at retirement Post-retirement: Periodic rate earned Number of periods 0.31% 720 $2,027,628 0.19% 600 OUTPUT AREA Anticipated periodic distribution (i.e. payment) $5,632 Do NOT change any of the values in the retirement analysis 32 Post-retirement APR Earned Post-Retirement Number of Years Withdrawing