+1(978)310-4246 credencewriters@gmail.com
  

Investment Portfolio
Investment Type
Symbol
Description
Bond Fund
Bond Fund
Bond Fund
Bond Fund
International Stock Fund
Domestic Stock Fund
Domestic Stock Fund
Domestic Stock Fund
International Stock Fund
International Stock Fund
Individual Stock
Individual Stock
Individual Stock
Individual Stock
Individual Stock
VFICX
VFSTX
VWEHX
VUSTX
VDMIX
VEIPX
VISGX
VIMSX
VTRIX
VEIEX
TRV
JNJ
KO
MSFT
IBM
Intermediate Investment Grade
Short Term Investment Grade
High Yield Corporate
Long Term Treasury
Developed Markets
Equity Income
Small Cap Growth
Mid Cap
International Value
Emerging Markets
The Travelers Companies, Inc.
Johnson and Johnson
Coca Cola
Microsoft Corporation
International Business Machine
Bond Fund
Growth Last
Year
Performance
Targets
Dividend / Yield
Yield
Strength
4.60%
3.20%
7.20%
4.00%
2.60%
2.50%
0.30%
1.00%
2.00%
1.40%
2.40%
3.60%
2.80%
2.50%
1.60%
Cost of
Purchase
Current
Investment
Value
$ 7,118
$ 8,010
$ 3,145
$ 5,500
$ 7,340
$ 11,675
$ 4,833
$ 5,603
$ 5,708
$ 5,935
$ 5,721
$ 7,995
$ 9,369
$ 2,102
$ 6,576
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
8,815
8,877
3,956
5,626
5,958
11,302
5,958
5,644
8,607
8,013
9,216
11,671
9,957
2,087
11,938
Gain
indicator
Unrealized
Gain/Loss
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
1,697
867
811
126
(1,382)
(373)
1,125
41
2,900
2,078
3,495
3,676
588
(14)
5,362
Percent
Gain/Loss
23.85%
10.82%
25.79%
2.29%
-18.82%
-3.20%
23.28%
0.74%
50.81%
35.01%
61.09%
45.98%
6.28%
-0.67%
81.53%
Current
Percent of
Portfolio
7.5%
7.5%
3.4%
4.8%
5.1%
9.6%
5.1%
4.8%
7.3%
6.8%
7.8%
9.9%
8.5%
1.8%
10.1%
Target
Percent of
Portfolio
10.0%
10.0%
10.0%
10.0%
7.0%
5.0%
5.0%
5.0%
7.0%
6.0%
5.0%
5.0%
5.0%
5.0%
5.0%
Current vs.
Target
-2.5%
-2.5%
-6.6%
-5.2%
-1.9%
4.6%
0.1%
-0.2%
0.3%
0.8%
2.8%
4.9%
3.5%
-3.2%
5.1%
Rebalance
Indicator
Buy
Indicator
Months
Owned
48
37
48
10
42
22
33
46
29
9
72
87
6
50
100
Investment List
Symbol
VMMXX
VUSTX
VFSTX
VWEHX
VFICX
VFISX
VBISX
VEIPX
VIMSX
VISGX
VFINX
VEIEX
VDMIX
VTRIX
MSFT
AAPL
IBM
GOOG
JNJ
KO
TRV
Description
Investment Type
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.
Money Market
Bond Fund
Bond Fund
Bond Fund
Bond Fund
Bond Fund
Bond Fund
Domestic Stock Fund
Domestic Stock Fund
Domestic Stock Fund
Domestic Stock Fund
International Stock Fund
International Stock Fund
International Stock Fund
Individual Stock
Individual Stock
Individual Stock
Individual Stock
Individual Stock
Individual Stock
Individual Stock
Risk Level
1
3
1
3
2
1
1
4
5
5
4
5
5
5
5
5
5
5
5
5
5
Growth Last
Year
0.07%
0.68%
3.79%
12.37%
7.88%
1.74%
2.85%
17.10%
24.94%
32.55%
15.49%
17.18%
10.49%
7.69%
-10.05%
50.97%
12.09%
-4.03%
-3.11%
16.24%
14.21%
5 Year Growth
2.45%
3.51%
4.92%
6.74%
6.63%
4.39%
4.98%
3.59%
4.39%
5.92%
2.54%
10.01%
1.41%
1.93%
3.49%
324.19%
72.65%
28.60%
-1.05%
58.17%
17.64%
Performance Targets
Very Conservative
Conservative
Moderate
Aggressive
Very Aggressive
Domestic
International Individual
Bond Fund
Stock Fund Stock Fund Stock
2.50%
5.00%
4.00%
8.00%
4.00%
6.00%
5.00%
10.00%
5.00%
8.00%
7.00%
12.00%
6.00%
10.00%
9.00%
15.00%
7.00%
12.00%
11.00%
20.00%
Answer the following questions by executing the required skills on the starter file listed above.
Answer each question in the order it appears. Questions that are preceded with the letters KO
indicate you must only use your keyboard and not your mouse to execute the required skill.
1) Apply the Freeze Panes command to the Investment Detail worksheet so rows 1 and 2 and
columns A, B, and C are visible while you scroll the worksheet.
2) Enter a SUM function in cell I1 on the Investment Detail worksheet to sum the values in the
Current Investment column. Add cell capacity to include row 25 in this function.
3) In cell D3 on the Investment Detail worksheet, enter a VLOOKUP function 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 D4:D17
when you are defining the arguments.
4) KO Copy and paste cell D3 into the range D4:D17.
5) In cell E3 on the Investment Detail worksheet, use an HLOOKUP function to show the
performance target for the benchmark in cell A3. The performance targets for each benchmark
can be found in the Performance Targets worksheet. The function should display the Aggressive
target values in row 6 on the Performance Targets 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 E4:E17 when you are defining the arguments.
6) KO Copy cell E3 and paste it into the range E4:E17.
7) On the Investment Detail worksheet, enter an IF function in cell J3. The output of the function
should be the word “Gain” if the Current Investment Value in cell I3 is greater than the Cost of
Purchase in cell H3. Otherwise, the output of the function should be the words “No Gain”.
8) KO Copy cell J3 and paste it into the range J4:J17.
9) On the Investment Detail worksheet, enter a nested IF function into cell G3. If the
Dividend/Yield value in cell F3 is less than 2%, show the word “Low”. If the Dividend/Yield value
in cell F3 is greater than or equal to 5%, show the word “High”. Otherwise, show the word
“Moderate”.
10) KO Copy cell G3 and paste it into the range G4:G17.
11) On the Investment Detail worksheet in cell P3, use the OR function within an IF function to
evaluate the Current vs. Target value. If the Current vs. Target value in cell O3 is greater than 2%
or less than −2%, show the word “REBAL”. Otherwise, show the word “OK”.
12) KO Copy cell P3 and paste it into the range P4:P17.
13) On the Investment Detail worksheet in cell Q3, 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 O3 is greater than 2% and if the Unrealized Gain/Loss value in cell K3 is
greater than 0, show the word “BUY”. Otherwise, show the word “hold”.
14) KO Copy cell P3 and paste it into the range Q4:Q17.
15) On the Investment Detail worksheet in cell D1, use the ISBLANK function within the IF function
to determine if cell A26 is blank. If cell A26 is blank then there should be nothing displayed in
cell D1. If there is anything entered into cell A26, the following should be displayed in cell D1:
Data exceeds function cell capacity.
16) Apply a data visualization technique to the data in column K. Use filled circle icons (traffic light
icons) to indicate the level of unrealized gains and losses. If there is gain greater than or equal to
$100, there should be a green circle. If the gain is greater than or equal to 0 but less than $100,
there should be a yellow circle. Any losses should be identified with a red circle.
Portfolio Summary
Investment Type
Number of
Investments
Average Yield
Percent of
Portfolio
Number of
Investments
Total Unrealized
Gain
Months Owned
Bond Fund
Domestic Stock Fund
International Stock Fund
Individual Stock
High Profit Investments
Investment Type
Bond Fund
Domestic Stock Fund
International Stock Fund
Individual Stock
Investment Portfolio
Data exceeds function cell cap
Investment Type
Symbol
Description
Bond Fund
Bond Fund
Bond Fund
Bond Fund
International Stock Fund
Domestic Stock Fund
Domestic Stock Fund
Domestic Stock Fund
International Stock Fund
International Stock Fund
Individual Stock
Individual Stock
Individual Stock
Individual Stock
Individual Stock
VFICX
VFSTX
VWEHX
VUSTX
VDMIX
VEIPX
VISGX
VIMSX
VTRIX
VEIEX
TRV
JNJ
KO
MSFT
IBM
Intermediate Investment Grade
Short Term Investment Grade
High Yield Corporate
Long Term Treasury
Developed Markets
Equity Income
Small Cap Growth
Mid Cap
International Value
Emerging Markets
The Travelers Companies, Inc.
Johnson and Johnson
Coca Cola
Microsoft Corporation
International Business Machine
Bond Fund
Growth Last
Year
7.88%
3.79%
12.37%
0.68%
10.49%
17.10%
32.55%
24.94%
7.69%
17.18%
14.21%
-3.11%
16.24%
-10.05%
12.09%
ata exceeds function cell capacity
Performance
Targets
6.00%
6.00%
6.00%
6.00%
9.00%
10.00%
10.00%
10.00%
9.00%
9.00%
15.00%
15.00%
15.00%
15.00%
15.00%
Dividend / Yield
Yield
Strength
4.60% Moderate
3.20% Moderate
7.20% High
4.00% Moderate
2.60% Moderate
2.50% Moderate
0.30% Low
1.00% Low
2.00% Moderate
1.40% Low
2.40% Moderate
3.60% Moderate
2.80% Moderate
2.50% Moderate
1.60% Low
$
117,625
Cost of
Purchase
Current
Investment
Value
Gain
indicator
Unrealized
Gain/Loss
$ 7,118
$ 8,010
$ 3,145
$ 5,500
$ 7,340
$ 11,675
$ 4,833
$ 5,603
$ 5,708
$ 5,935
$ 5,721
$ 7,995
$ 9,369
$ 2,102
$ 6,576
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Gain
Gain
Gain
Gain
No Gain
No Gain
Gain
Gain
Gain
Gain
Gain
Gain
Gain
No Gain
Gain
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
8,815
8,877
3,956
5,626
5,958
11,302
5,958
5,644
8,607
8,013
9,216
11,671
9,957
2,087
11,938
1,697
867
811
126
(1,382)
(373)
1,125
41
2,900
2,078
3,495
3,676
588
(14)
5,362
Percent
Gain/Loss
23.85%
10.82%
25.79%
2.29%
-18.82%
-3.20%
23.28%
0.74%
50.81%
35.01%
61.09%
45.98%
6.28%
-0.67%
81.53%
Current
Percent of
Portfolio
7.5%
7.5%
3.4%
4.8%
5.1%
9.6%
5.1%
4.8%
7.3%
6.8%
7.8%
9.9%
8.5%
1.8%
10.1%
Target
Percent of
Portfolio
10.0%
10.0%
10.0%
10.0%
7.0%
5.0%
5.0%
5.0%
7.0%
6.0%
5.0%
5.0%
5.0%
5.0%
5.0%
Current vs.
Target
Rebalance
Indicator
-2.5% REBAL
-2.5% REBAL
-6.6% REBAL
-5.2% REBAL
-1.9% OK
4.6% REBAL
0.1% OK
-0.2% OK
0.3% OK
0.8% OK
2.8% REBAL
4.9% REBAL
3.5% REBAL
-3.2% REBAL
5.1% REBAL
Buy
Indicator
HOLD
HOLD
HOLD
HOLD
HOLD
HOLD
HOLD
HOLD
HOLD
HOLD
BUY
BUY
BUY
HOLD
BUY
Months
Owned
48
37
48
10
42
22
33
46
29
9
72
87
6
50
100
Answer the following questions by executing the required skills on the starter file listed above.
Answer each question in the order it appears. Questions that are preceded with the letters KO
indicate you must only use your keyboard and not your mouse to execute the required skill.
1) Enter a COUNTIF function in cell B3 on the Summary worksheet. The function should count the
entries in the Investment Type column on the Investment Detail worksheet that match cell A3
on the Summary worksheet. Include cell capacity through row 25 when defining arguments that
require a cell range and use absolute references when needed.
2) KO Copy cell B3 and paste it into the range B4:B6 using the Paste Formulas command.
3) Enter an AVERAGEIF function in cell C3 on the Summary worksheet. The function should
compute the average of the entries in the Dividend/Yield column on the Investment Detail
worksheet where there is a match in the Investment Type column on the Investment Detail
worksheet with cell A3 on the Summary worksheet. Include cell capacity through row 25 when
defining arguments that require a cell range and use absolute references when needed.
4) KO Copy cell C3 and paste it into the range C4:C6 using the Paste Formulas command.
5) Enter a SUMIF function in cell D3 on the Summary worksheet. The function should compute the
sum of the entries in the Current Percent of Portfolio column on the Investment Detail
worksheet where there is a match in the Investment Type column on the Investment Detail
worksheet with cell A3 on the Summary worksheet. Include cell capacity through row 25 when
defining arguments that require a cell range, and use absolute references when needed.
6) KO Copy cell D3 and paste it into the range D4:D6 using the Paste Formulas command.
7) Enter a COUNTIFS function in cell B10 on the Summary worksheet. The function should count
the number of investments on the Investment Detail worksheet that match the investment type
in cell A10 on the Summary worksheet and achieved a gain that is greater than or equal to 15%.
Use the Percent Gain/Loss column on the Investment Detail worksheet to evaluate the gains for
each investment. Include cell capacity through row 25 when defining arguments that require a
cell range, and use absolute references when needed.
8) KO Copy cell B10 and paste it into the range B11:B13 using the Paste Formulas command.
9) Enter a SUMIFS function in cell C10 on the Summary worksheet. The purpose of the function is
to sum the values in the Unrealized Gain/Loss column on the Investment Detail worksheet
based on two conditions. The first is where the Investment Type column on the Investment
Detail worksheet matches the investment type in cell A10. The second condition is where the
value in the Percent Gain/Loss column on the Investment Detail worksheet is greater than or
equal to 15%. Include cell capacity through row 25 when defining arguments that require a cell
range, and use absolute references when needed.
10) KO Copy cell C10 and paste it into the range C11:C13 using the Paste Formulas command.
11) Enter an AVERAGEIFS function in cell D10 on the Summary worksheet. The purpose of the
function is to compute the average of the values in the Months Owned column on the
Investment Detail worksheet based on two conditions. The first condition is where the
Investment Type column on the Investment Detail worksheet matches the investment type in
cell A10. The second condition is where the value in the Percent Gain/Loss column on the
Investment Detail worksheet is greater than or equal to 15%. Include cell capacity through row
25 when defining arguments that require a cell range, and use absolute references when
needed.
12) KO Copy cell D10 and paste it into the range D11:D13 using the Paste Formulas command.
13) Enter a CONCATENATE function in the merge cell range beginning with cell A7 in the Summary
worksheet. The function should combine the phrase “Error Warning:” with the entry in cell D1
on the Investment Detail worksheet.

Purchase answer to see full
attachment

  
error: Content is protected !!