A B D E Percent of Portfolio Portfolio Target Portfolio Summary Number of Average Investment Type Investments Yield # Bond Fund Domestic Stock Fund 5 International Stock Fund - Individual Stock 7 8 High Profit Investments Total Investment Type Number of Unrealized Average 9 Investments Gain Yield 10 Bond Fund 11 Domestic Stock Fund 12 International Stock Fund 13 Individual Stock 14 15 48N Growth Dividend Wald Description Last Year Yield Strength Intermediate investment Grade 4.00% Short Term Investment Grade 3.2009 High Yield Corporate 7,20% 6 Long Term Treasury 4.00% Developed Markets 2.60% Equity Income 2504 Small Cap Growth 0.305 10 Mid Cap 100% 11 International Value 2.00 12 Emerging Markets 12404 13 The Travelers Companies in 2 40 14. Johnson and Johnson 3.600 15. Coca Cola 2 BOX 16 Microsoft Corporation 2.50% 17 International Business Machine 1.6046 TOP Cost Purchase Valea 517150015 $2.0103 0.077 53.145 153.956 $5.500 55.626 5 7840 S5.958 5:41.675 5. 11,302 $4,833 $5958 $ 5,603 S 5644 $ 5.7088 007 $25.9355 8.013 $5,721 59216 576955111 671 $9.369 $9.957 $ 2.10252.087 $ 6,576 $.11.938 Current Target Unrested Percent Percent of Percent of Current balance Buy Gaines antes Portfolio Portfolio Target Indicator water and 311.697 23859 75 100N -2.5 48 $ 067 10,0275 100N 20 31 $ 811 25.79% 3.49 100% 48 3 120 2.29 100% 52 TO $0.3821-18.02 6.11 70 -1.91 42 (373) 3209 96% SON 46 22 S 1125 2321 518 5.0 ON 30 $41 0.74% 48% 50 02 46 $ 2.900 50 B1 7.3% 70 0.39 29 $ 2070 35.01 68 60 0.8% 9 $ 3,495 6109 78 50N 289 2 $3670 45 ON 9995 50% 49% 87 S 380 6289 85% 50% 3.5 6 $ 06 189 50% -32 50 35 362 810 10.1 50 100 TB She is Wonder Chew Z D3 B G 1 2 Symbol 3 VMMXX 4 VUSTX 5 VFSTX 6 VWEHX 7 VFICX 8 VFISX 9 VBISX 10 VEIPX 11 VIMSX 12 VISGX 13 VFINX 14 VEIEX 15 VDMIX 16 VTRIX 17 MSFT 18 AAPL 19 IBM 20 GOOG 21 JNJ 22 KO 23 TRV 24 25 26 olol D Investment List Growth Last 5 Year Investment Type Risk Level Year Growth Money Market 1 0.07% 2.45% Bond Fund 3 0.68% 3.51% Bond Fund 1 3.79% 4.92% Bond Fund 3 12.37% 6.74% Bond Fund 2 7.88% 6.63% Bond Fund 1 1.74% 4.39% Bond Fund 1 2.85% 4.98% Domestic Stock Fund 4 17.10% 3.59% Domestic Stock Fund 5 24.94% 4.39% Domestic Stock Fund 5 32.55% 5.92% Domestic Stock Fund 4 15.49% 2.54% International Stock Fund 5 17.18% 10.01% International Stock Fund 5 10.4996 1.41% International Stock Fund 5 7.69% 1.93% Individual Stock 5 -10.05% 3.49% Individual Stock 5 50.97% 324.19% Individual Stock 5 12.09% 72.65% Individual Stock 5 -4.03% 28.6096 Individual Stock 5 -3.11% -1.05% Individual Stock 5 16.2496 58.17% Individual Stock 5 14.21% 17.64% Description Prime Money Market Long Term Treasury Short Term Investment Grade High Yield Corporate Intermediate Investment Grade Short Term Treasury Short Term Bond Index Equity Income Mid Cap Small Cap Growth S&P 500 Index Emerging Markets Developed Markets International Value Microsoft Corporation Apple Inc. International Business Machine Google Inc. Johnson and Johnson Coca Cola The Travelers Companies, Inc. lo D Fortido Som tent fremti Pottle ton Type here to search Testa E Review View Data Help Enterprise Connect Add-on Formulas Home File Insert Page Layout IR Rule Formula Bor Split Hide ONU New Arrange Freeze Window All Panes- 15 ton Zoom 100% Zoom to Selection Grdines a Headings Normal PageBreak Page Custom Preview Layout Views Workbook Views Zoon Show Sheet View AS Moderate F G 2 3 Very Conservative 4 Conservative 5 Moderate 6 Aggressive 7 Very Aggressive 8 B @ D E Portfolio Targets TITETTATOTT Domestic I Stock Individual Bond Fund Stock Fund Fund Stock 100.00% 0.00% 0.00% 0.00% 70.00% 15.00% 15.00% 0.00% 40.00% 20.00% 15.00% 25.00% 30.00% 20.00% 20.00% 30.00% 0.00% 0.00% 0.00% 100.00% 9 10 11 12 13 14 15 16 17 1. Set the Freeze Panes command so that Rows 1 and 2 and Columns A and B are locked in place while scrolling through the Investment Detail worksheet 2. In the Investment Detail worksheet, enter an IF function into cell 13. The output of the function should be the word Gain if the Current Investment Value in cell H3 is greater than the Cost of Purchase in cell G3. Otherwise, the output of the function should be the words No Gain. 3. Copy the IF function in cell 13 and paste it into the range 14:117 using the Paste Formulas command. 4. In the Investment Detail worksheet, enter a nested IF function into cell F3. If the Dividend/Yield value in cell E3 is less than 2%, show the word Low. If the Dividend/Yield value in cell E3 is greater than or equal to 5%, show the word High. Otherwise, show the word Moderate 5. Copy the IF function in cell F3 and paste it into the range F4:F17 using the Paste Formulas command. 6. On the Investment Detail worksheet in cell 03, use the OR function within an IF function to evaluate the Current vs. Target value. If the Current vs. Target value in cell N3 is greater than 2% or less than -2% show the word REBAL. Otherwise, show the word OK. 7. Copy the IF function in cell O3 and paste it into the range 04:017 using the Paste Formulas command. 8. On the Investment Detail worksheet in cell P3, use the AND function within an IF function to evaluate the Current vs. Target value and the Unrealized Gain/Loss value. If the Current vs. Target value in cell N3 is greater than 2% and if the Unrealized Gain/Loss value in cell J3 is greater than 0. show the word BUY. Otherwise, show the word HOLD. 9. Copy the IF function in cell P3 and paste it into the range P4:P17 using the Paste Formulas command. 10. On the investment Detail worksheet apply a conditional format to the range Q3:017. If the Months Owned is less than 12, change the font color to red. Otherwise, the font color should remain black 11. On the Investment Detail Worksheet, enter a VLOOKUP function in cell D3 that displays the Growth Last Year for the symbol in cell B3. The growth last year for all investments can be found in Column E on the Investment List worksheet. Your function should look for an exact match to the lookup value. Consider that this function will be copied and pasted Into the range D4D17 when defining the arguments. 12 On the Investment Detail worksheet, format the range D3 D17 to a Avagn 249031657 Count ats ve at paste toelige U17 Weng magUIS 12. On the Investment Detail worksheet, format the range 03:017 to a percentage with two decimal places. Then copy the VLOOKUP function in cell D3 and paste it into the range D4D17 using the Paste Formulas command. 13. On the Portfolio Summary worksheet, use the COUNTIF function in cell B3 to count the number of investments that match the investment type in cell A3. The function should look for and count the number of investment types in the range A3:A17 on the Investment Detail worksheet. Consider that this function will be copied and pasted into the range B4:36 when defining the arguments. 14. Copy the COUNTIF function in cell B3 and paste it into the range B4:B6 using the Paste Formulas command. 15. On the Portfolio Summary worksheet, use the AVERAGEIF function in cell C3 to calculate the average Dividend/Yield for the investment type in cell A3. The function should calculate the average using the data in the range E2:E17 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3:A17 on the Investment Detail worksheet. Consider that this function will be copied and pasted into the range C4.Co when defining the arguments. 16. Copy the AVERAGEIF function in cell C3 and paste it into the range 04:C6 using the Paste Formulas command. 17. On the Portfolio Summary worksheet, use the SUMIF function in cehe D3 to calculate the Percent of Portfolio for the investment type in cell A3. The function should calculate the sum using the data in the range 13:L17 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3:417 on the investment Detail worksheet. Consider that this function will be copied and pasted into the range D4:06 when defining the arguments, 18. Copy the SUMIF function in cell D3 and paste it into the range D4:D6 using the Paste Formulas command. 19. On the Portfolio Summary worksheet, use the HLOOKUP function to display the Portfolio Torget in cell E3. The function should look for the Investment Type in cell A3 in Row 2 of the Portfolio Targets worksheet. The function should display the percentage in the Moderate row (Row S) for each investment type. The function should look for an exact match to the lookup value. Consider that this function will be copied and pasted into the range EA Es when defining the arguments 20. Copy the HLOOKUP function in cell E3 and paste it into the range E4E6 using the Paste Formulas command Aage 1240011657 Count its ve do pascere U17 WUmge are 12. On the Investment Detail worksheet, format the range D3:17 to a percentage with two decimal places. Then copy the VLOOKUP function in cell D3 and paste it into the range D4:017 using the Paste Formulas command. 13. On the Portfolio Summary worksheet, use the COUNTIF function in cell B3 to count the number of investments that match the Investment type in cell A3. The function should look for and count the number of investment types in the range A3:A17 on the Investment Detail worksheet. Consider that this function will be copied and pasted into the range B4:36 when defining the arguments. 14. Copy the COUNTIF function in cell B3 and paste it into the range B4:B6 using the Paste Formulas command. 15. On the Portfolio Summary worksheet, use the AVERAGEIF function in cell C3 to calculate the average Dividend/Yield for the investment type in cell A3. The function should calculate the average using the data in the range E3:E17 on the Investment Detail Worksheet. The function should look for a match to the investment type in the range A3:A17 on the Investment Detail worksheet. Consider that this function will be copied and pasted into the range C4:Co when defining the arguments 16. Copy the AVERAGEIF function in cell C3 and paste it into the range C4:C6 using the Paste Formulas command. 17. On the Portfolio Summary worksheet use the SUMIF function in ce D3 to calculate the Percent of Portfolio for the investment type in cell A3. The function should calculate the sum using the data in the range 13:L17 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3 A17 on the investment Detail worksheet. Consider that this function will be copied and pasted into the range D4:06 when defining the arguments, 18. Copy the SUMIF function in cell D3 and paste it into the range D4-D6 using the Paste Formulas command. 19. On the Portfolio Summary worksheet, use the HLOOKUP function to display the Portfolio Torget in cell E3. The function should look for the Investment Type in cell A3 in Row 2 of the Portfolio Targets worksheet. The function should display the percentage in the Moderate row (Row 5) for each investment type. The function should look for an exact match to the lookup value. Consider that this function will be copied and pasted into the range E4:E6 when defining the arguments 20. Copy the HLOOKUP function in cell E3 and paste it into the range E 4 E6 using the Paste Formulas command Court Sur A B D E Percent of Portfolio Portfolio Target Portfolio Summary Number of Average Investment Type Investments Yield # Bond Fund Domestic Stock Fund 5 International Stock Fund - Individual Stock 7 8 High Profit Investments Total Investment Type Number of Unrealized Average 9 Investments Gain Yield 10 Bond Fund 11 Domestic Stock Fund 12 International Stock Fund 13 Individual Stock 14 15 48N Growth Dividend Wald Description Last Year Yield Strength Intermediate investment Grade 4.00% Short Term Investment Grade 3.2009 High Yield Corporate 7,20% 6 Long Term Treasury 4.00% Developed Markets 2.60% Equity Income 2504 Small Cap Growth 0.305 10 Mid Cap 100% 11 International Value 2.00 12 Emerging Markets 12404 13 The Travelers Companies in 2 40 14. Johnson and Johnson 3.600 15. Coca Cola 2 BOX 16 Microsoft Corporation 2.50% 17 International Business Machine 1.6046 TOP Cost Purchase Valea 517150015 $2.0103 0.077 53.145 153.956 $5.500 55.626 5 7840 S5.958 5:41.675 5. 11,302 $4,833 $5958 $ 5,603 S 5644 $ 5.7088 007 $25.9355 8.013 $5,721 59216 576955111 671 $9.369 $9.957 $ 2.10252.087 $ 6,576 $.11.938 Current Target Unrested Percent Percent of Percent of Current balance Buy Gaines antes Portfolio Portfolio Target Indicator water and 311.697 23859 75 100N -2.5 48 $ 067 10,0275 100N 20 31 $ 811 25.79% 3.49 100% 48 3 120 2.29 100% 52 TO $0.3821-18.02 6.11 70 -1.91 42 (373) 3209 96% SON 46 22 S 1125 2321 518 5.0 ON 30 $41 0.74% 48% 50 02 46 $ 2.900 50 B1 7.3% 70 0.39 29 $ 2070 35.01 68 60 0.8% 9 $ 3,495 6109 78 50N 289 2 $3670 45 ON 9995 50% 49% 87 S 380 6289 85% 50% 3.5 6 $ 06 189 50% -32 50 35 362 810 10.1 50 100 TB She is Wonder Chew Z D3 B G 1 2 Symbol 3 VMMXX 4 VUSTX 5 VFSTX 6 VWEHX 7 VFICX 8 VFISX 9 VBISX 10 VEIPX 11 VIMSX 12 VISGX 13 VFINX 14 VEIEX 15 VDMIX 16 VTRIX 17 MSFT 18 AAPL 19 IBM 20 GOOG 21 JNJ 22 KO 23 TRV 24 25 26 olol D Investment List Growth Last 5 Year Investment Type Risk Level Year Growth Money Market 1 0.07% 2.45% Bond Fund 3 0.68% 3.51% Bond Fund 1 3.79% 4.92% Bond Fund 3 12.37% 6.74% Bond Fund 2 7.88% 6.63% Bond Fund 1 1.74% 4.39% Bond Fund 1 2.85% 4.98% Domestic Stock Fund 4 17.10% 3.59% Domestic Stock Fund 5 24.94% 4.39% Domestic Stock Fund 5 32.55% 5.92% Domestic Stock Fund 4 15.49% 2.54% International Stock Fund 5 17.18% 10.01% International Stock Fund 5 10.4996 1.41% International Stock Fund 5 7.69% 1.93% Individual Stock 5 -10.05% 3.49% Individual Stock 5 50.97% 324.19% Individual Stock 5 12.09% 72.65% Individual Stock 5 -4.03% 28.6096 Individual Stock 5 -3.11% -1.05% Individual Stock 5 16.2496 58.17% Individual Stock 5 14.21% 17.64% Description Prime Money Market Long Term Treasury Short Term Investment Grade High Yield Corporate Intermediate Investment Grade Short Term Treasury Short Term Bond Index Equity Income Mid Cap Small Cap Growth S&P 500 Index Emerging Markets Developed Markets International Value Microsoft Corporation Apple Inc. International Business Machine Google Inc. Johnson and Johnson Coca Cola The Travelers Companies, Inc. lo D Fortido Som tent fremti Pottle ton Type here to search Testa E Review View Data Help Enterprise Connect Add-on Formulas Home File Insert Page Layout IR Rule Formula Bor Split Hide ONU New Arrange Freeze Window All Panes- 15 ton Zoom 100% Zoom to Selection Grdines a Headings Normal PageBreak Page Custom Preview Layout Views Workbook Views Zoon Show Sheet View AS Moderate F G 2 3 Very Conservative 4 Conservative 5 Moderate 6 Aggressive 7 Very Aggressive 8 B @ D E Portfolio Targets TITETTATOTT Domestic I Stock Individual Bond Fund Stock Fund Fund Stock 100.00% 0.00% 0.00% 0.00% 70.00% 15.00% 15.00% 0.00% 40.00% 20.00% 15.00% 25.00% 30.00% 20.00% 20.00% 30.00% 0.00% 0.00% 0.00% 100.00% 9 10 11 12 13 14 15 16 17 1. Set the Freeze Panes command so that Rows 1 and 2 and Columns A and B are locked in place while scrolling through the Investment Detail worksheet 2. In the Investment Detail worksheet, enter an IF function into cell 13. The output of the function should be the word Gain if the Current Investment Value in cell H3 is greater than the Cost of Purchase in cell G3. Otherwise, the output of the function should be the words No Gain. 3. Copy the IF function in cell 13 and paste it into the range 14:117 using the Paste Formulas command. 4. In the Investment Detail worksheet, enter a nested IF function into cell F3. If the Dividend/Yield value in cell E3 is less than 2%, show the word Low. If the Dividend/Yield value in cell E3 is greater than or equal to 5%, show the word High. Otherwise, show the word Moderate 5. Copy the IF function in cell F3 and paste it into the range F4:F17 using the Paste Formulas command. 6. On the Investment Detail worksheet in cell 03, use the OR function within an IF function to evaluate the Current vs. Target value. If the Current vs. Target value in cell N3 is greater than 2% or less than -2% show the word REBAL. Otherwise, show the word OK. 7. Copy the IF function in cell O3 and paste it into the range 04:017 using the Paste Formulas command. 8. On the Investment Detail worksheet in cell P3, use the AND function within an IF function to evaluate the Current vs. Target value and the Unrealized Gain/Loss value. If the Current vs. Target value in cell N3 is greater than 2% and if the Unrealized Gain/Loss value in cell J3 is greater than 0. show the word BUY. Otherwise, show the word HOLD. 9. Copy the IF function in cell P3 and paste it into the range P4:P17 using the Paste Formulas command. 10. On the investment Detail worksheet apply a conditional format to the range Q3:017. If the Months Owned is less than 12, change the font color to red. Otherwise, the font color should remain black 11. On the Investment Detail Worksheet, enter a VLOOKUP function in cell D3 that displays the Growth Last Year for the symbol in cell B3. The growth last year for all investments can be found in Column E on the Investment List worksheet. Your function should look for an exact match to the lookup value. Consider that this function will be copied and pasted Into the range D4D17 when defining the arguments. 12 On the Investment Detail worksheet, format the range D3 D17 to a Avagn 249031657 Count ats ve at paste toelige U17 Weng magUIS 12. On the Investment Detail worksheet, format the range 03:017 to a percentage with two decimal places. Then copy the VLOOKUP function in cell D3 and paste it into the range D4D17 using the Paste Formulas command. 13. On the Portfolio Summary worksheet, use the COUNTIF function in cell B3 to count the number of investments that match the investment type in cell A3. The function should look for and count the number of investment types in the range A3:A17 on the Investment Detail worksheet. Consider that this function will be copied and pasted into the range B4:36 when defining the arguments. 14. Copy the COUNTIF function in cell B3 and paste it into the range B4:B6 using the Paste Formulas command. 15. On the Portfolio Summary worksheet, use the AVERAGEIF function in cell C3 to calculate the average Dividend/Yield for the investment type in cell A3. The function should calculate the average using the data in the range E2:E17 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3:A17 on the Investment Detail worksheet. Consider that this function will be copied and pasted into the range C4.Co when defining the arguments. 16. Copy the AVERAGEIF function in cell C3 and paste it into the range 04:C6 using the Paste Formulas command. 17. On the Portfolio Summary worksheet, use the SUMIF function in cehe D3 to calculate the Percent of Portfolio for the investment type in cell A3. The function should calculate the sum using the data in the range 13:L17 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3:417 on the investment Detail worksheet. Consider that this function will be copied and pasted into the range D4:06 when defining the arguments, 18. Copy the SUMIF function in cell D3 and paste it into the range D4:D6 using the Paste Formulas command. 19. On the Portfolio Summary worksheet, use the HLOOKUP function to display the Portfolio Torget in cell E3. The function should look for the Investment Type in cell A3 in Row 2 of the Portfolio Targets worksheet. The function should display the percentage in the Moderate row (Row S) for each investment type. The function should look for an exact match to the lookup value. Consider that this function will be copied and pasted into the range EA Es when defining the arguments 20. Copy the HLOOKUP function in cell E3 and paste it into the range E4E6 using the Paste Formulas command Aage 1240011657 Count its ve do pascere U17 WUmge are 12. On the Investment Detail worksheet, format the range D3:17 to a percentage with two decimal places. Then copy the VLOOKUP function in cell D3 and paste it into the range D4:017 using the Paste Formulas command. 13. On the Portfolio Summary worksheet, use the COUNTIF function in cell B3 to count the number of investments that match the Investment type in cell A3. The function should look for and count the number of investment types in the range A3:A17 on the Investment Detail worksheet. Consider that this function will be copied and pasted into the range B4:36 when defining the arguments. 14. Copy the COUNTIF function in cell B3 and paste it into the range B4:B6 using the Paste Formulas command. 15. On the Portfolio Summary worksheet, use the AVERAGEIF function in cell C3 to calculate the average Dividend/Yield for the investment type in cell A3. The function should calculate the average using the data in the range E3:E17 on the Investment Detail Worksheet. The function should look for a match to the investment type in the range A3:A17 on the Investment Detail worksheet. Consider that this function will be copied and pasted into the range C4:Co when defining the arguments 16. Copy the AVERAGEIF function in cell C3 and paste it into the range C4:C6 using the Paste Formulas command. 17. On the Portfolio Summary worksheet use the SUMIF function in ce D3 to calculate the Percent of Portfolio for the investment type in cell A3. The function should calculate the sum using the data in the range 13:L17 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3 A17 on the investment Detail worksheet. Consider that this function will be copied and pasted into the range D4:06 when defining the arguments, 18. Copy the SUMIF function in cell D3 and paste it into the range D4-D6 using the Paste Formulas command. 19. On the Portfolio Summary worksheet, use the HLOOKUP function to display the Portfolio Torget in cell E3. The function should look for the Investment Type in cell A3 in Row 2 of the Portfolio Targets worksheet. The function should display the percentage in the Moderate row (Row 5) for each investment type. The function should look for an exact match to the lookup value. Consider that this function will be copied and pasted into the range E4:E6 when defining the arguments 20. Copy the HLOOKUP function in cell E3 and paste it into the range E 4 E6 using the Paste Formulas command Court Sur