Question

Question 1: Construct a one way data table to calculate the portfolio unrealized gain/loss ($) while allowing the current quote of MSFT vary from 500 to 800, incrementing by 20.

Question 2: Construct a two way data table to calculate the portfolio unrealized gain/loss ($) while allowing the current quote of MSFT vary from 500 to 800, incrementing by 20, and allowing the amount of MSFT held vary from 0 to 400, increment by 50.

Portfolio Summary
Total cost of accounts			$418,003.50
Value of accounts			$408,105.00
Unrealized gain/loss $			$(9,898.50)
Unrealized gain/loss %			-2.37%
List of Investiments
Investment Name	Quantity	Purchase Price Per Share	Total Cost	Current Quote	Market Value	Gain/Loss ($)	Gain/Loss (%)
AAPL	300	$698.73	$209,619.00	$490.64	$147,192.00	$(62,427.00)	-29.78%
GOOG	250	$623.25	$155,812.50	$886.50	$221,625.00	$65,812.50	42.24%
MSFT	1200	$43.81	$52,572.00	$32.74	$39,288.00	$(13,284.00)	-25.27%

Answer 1

Step 1: Create the spreadsheet caculating Unrealized gain/loss $

1 Portfolio Summary Question 1: One-way Data Table 3 Total cost of accounts $418,003.50 4 Value of accounts 5 Unrealized gain/loss($9,898.50) 6 Unrealized gain/loss % ($9,898.50) 1200 500 $550,813.50 520 $574,813.50 540 $598,813.50 560 $622,813.50 580 $646,813.50 600 $670,813.50 620 $694,813.50 640 $718,813.50 660 $742,813.50 680 $766,813.50 700 $790,813.50 720 $814,813.50 740 $838,813.50 760 $862,813.50 780 $886,813.50 800 $910,813.50 $408,105.00 2.37% 8 List of Investiments Current Quote | S209,619.00 | $490.64 |$155,812.50| $886.50 $32.74 Gain/Loss Gain/Loss Purchase Price Per Share $698.73 $623.25 $43.81 Market Value |$147,192.00 |($62,427.00)| |$221,625.00 |$65,812.50| | Investment Name Total Cost Quantity 300 250 1200 10 -29.78% 42.24% -25.27% AAPL 12 13 14 15 16 17 18 19 MSFT $52,572.00 | $39,288.00 |(S13,284.00)| 21

Formula:

D11 =B11*C11

F11 =B11*E11

G11 =F11-D11

H11 =G11/D11

use fill handle to copy above formulas to the end of table (row 13)

B3 =SUM(D11:D13)

B4 =SUM(F11:F13)

B5 =B4-B3

B6 =B5/B3

Step 2: Create one-way data table

Write formula in cell J3 =B5

Create interval column in J4:J19 which show the current market price of MSFT.

In K3 write 1200, which indicates the Quantity of MSFT.

Select range J3:K19

On top ribbon of excel click What-if Analysis > Data Table > Row Input Cell: $B$13 ,  Column Input Cell: $E$13 > OK

One-way table is created.

($9,898.50)	1200
500	$550,813.50
520	$574,813.50
540	$598,813.50
560	$622,813.50
580	$646,813.50
600	$670,813.50
620	$694,813.50
640	$718,813.50
660	$742,813.50
680	$766,813.50
700	$790,813.50
720	$814,813.50
740	$838,813.50
760	$862,813.50
780	$886,813.50
800	$910,813.50

Question 2: Two-way data table

Write formula in cell J3 =B5

Create interval column in J4:J19 which show the current market price of MSFT.

Create interval row in K3:S3, which indicates the Quantity of MSFT.

Select range J3:S19

On top ribbon of excel click What-if Analysis > Data Table > Row Input Cell: $B$13 ,  Column Input Cell: $E$13 > OK

Two-way Data Table is created.

RI Question 2: Two-way Data Table 9 ($9,898.50 100 300 400 500$3,385.50 $26,195.00 $49,004.50$71,814.00 $94,623.50 $117,433.00 140,242.50 $163,052.00 $185,861.50 520 $3,385.50 $27,195.00 $51,004.50 $74,814.00 $98,623.50 $122,433.00 146,242.50 $170,052.00 $193,861.50 540$3,385.50 $28,195.00 $53,004.50 $77,814.00 $102,623.50 $127,433.00 $152,242.50 $177,052.00 $201,861.50 560$3,385.50 $29,195.00 $55,004.50 $80,814.00 $106,623.50 $132,433.00 $158,242.50 $184,052.00 $209,861.50 580$3,385.50 $30,195.00 $57,004.50 $83,814.00 $110,623.50 $137,433.00 $164,242.50 $191,052.00 $217,861.50 600 $3,385.50 $31,195.00 $59,004.50 $86,814.00 $114,623.50 $142,433.00 $170,242.50 $198,052.00 $225,861.50 620 $3,385.50 $32,195.00 $61,004.50 $89,814.00 $118,623.50 $147,433.00 $176,242.50 $205,052.00 $233,861.50 640 $3,385.50 $33,195.00 $63,004.50 $92,814.00 $122,623.50 $152,433.00 $182,242.50 $212,052.00 $241,861.50 660 $3,385.5034,195.00 $65,004.50 $95,814.00 $126,623.50 $157,433.00 $188,242.50 $219,052.00 $249,861.50 680 $3,385.50 $35,195.00 $67,004.50 $98,814.00 $130,623.50 $162,433.00 $194,242.50 $226,052.00 $257,861.50 700$3,385.50 $36,195.00 $69,004.50 $101,814.00 $134,623.50 $167,433.00 $200,242.50 $233,052.00 $265,861.50 720 $3,385.50 $37,195.00 $71,004.50 $104,814.00 $138,623.50 $172,433.00 $206,242.50 $240,052.00 $273,861.50 740 $3,385.50 $38,195.00 $73,004.50 $107,814.00 $142,623.50 $177,433.00 $212,242.50 $247,052.00 $281,861.50 760 $3,385.5039,195.00 $75,004.50 $110,814.00 $146,623.50 $182,433.00 $218,242.50 $254,052.00 $289,861.50 780$3,385.50 $40,195.00 $77,004.50 $113,814.00 $150,623.50 $187,433.00 $224,242.50 $261,052.00 $297,861.50 800 3,385.50 $41,195.00 $79,004.50 $116,814.00 $154,623.50 $192,433.00 $230,242.50 $268,052.00 $305,861.50 150 200 250 350 9 Gain/Loss 101 (96) 11-29.78% 12 42.24% 13 -25.27% 14 15 16 17 18 19 20 21 23 24