Excel Solver: 6.3 Selecting an Investment Portfolio: An investment manager wants to determine an optimal portfolio for a wealthy client. The fund has $2.5 million to invest, and its objective is to maximize total dollar return from both growth and dividends over the course of the coming year. The client has researched eight high-tech companies and wants the portfolio to consist of shares in these firms only. Three of the firms (S1S3) are primarily software companies, three (H1H3) are primarily hardware companies, and two (C1C2) are Internet consulting companies. The client has stipulated that no more than 40% of the investment be allocated to any one of these three sectors. To ensure diversification, at least $100,000 must be invested in each of the eight stocks. Moreover, the number of shares invested in any stock must be a multiple of 1000. The table below gives estimates from the investment companys database relating to these stocks. These estimates include the price per share, the projected annual growth rate in the share price, and the anticipated annual dividend payment per share.

	S1	S2	S3	H1	H2	H3	C1	C2
Price per share	$40.00	$50.00	$80.00	$60.00	$45.00	$60.00	$30.00	$25.00
Growth rate	0.05	0.10	0.03	0.04	0.07	0.15	0.22	0.25
Dividend	$2.00	$1.50	$3.50	$3.00	$2.00	$1.00	$1.80	$0.00

1. Determine the maximum return on the portfolio. What is the optimal number of shares to buy for each of the stocks? What is the corresponding dollar amount invested in each stock? 2. Compare the solution in which there is no integer restriction on the number of shares invested. By how much (in percentage terms) do the integer restrictions alter the value of the optimal objective function? By how much (in percentage terms) do they alter the optimal investment quantities?

Please show Solver menu and all equations

What I have so far:

Data S3 H3 C1 Price per share Growth rate Dividend Stock S1 S2 $40.00 0.05 $2.00 $50.00 0.10 $1.50 H1 $80.00 0.03 $3.50 H2 $60.00 0.04 $3.00 $45.00 0.07 $2.00 $60.00 0.15 $1.00 $30.00 0.22 $1.80 $25.00 0.25 $0.00 S2 S3 H1 H2 H3 C1 C2 Decision Variables S1 Shares $ spent for shares Return $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 IS Return $0.00 Objective Function S1 Return/share as a pct. S2 S3 H 1 H2 H3 C1 C2 $40.00 $50.00 $80.00 $60.00 $45.00 $60.00 $30.00 $25.00 LHS RHS Constraints Fund limit 40% in 5 40% in H 40% in C XXXXXXX 2500 amount invested is less than 2.5 million 1000 1000 1000 100 amount invested in S1 >= 100k 100 amount invested in S2 >= 100k 100 amount invested in S3 >= 100k 100 amount invested in H1 >= 100k 100 amount invested in H2 >= 100k 100 amount invested in H3 >= 100k 100 amount invested in C1 >= 100k 100 amount invested in C2 >= 100k Data S3 H3 C1 Price per share Growth rate Dividend Stock S1 S2 $40.00 0.05 $2.00 $50.00 0.10 $1.50 H1 $80.00 0.03 $3.50 H2 $60.00 0.04 $3.00 $45.00 0.07 $2.00 $60.00 0.15 $1.00 $30.00 0.22 $1.80 $25.00 0.25 $0.00 S2 S3 H1 H2 H3 C1 C2 Decision Variables S1 Shares $ spent for shares Return $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 IS Return $0.00 Objective Function S1 Return/share as a pct. S2 S3 H 1 H2 H3 C1 C2 $40.00 $50.00 $80.00 $60.00 $45.00 $60.00 $30.00 $25.00 LHS RHS Constraints Fund limit 40% in 5 40% in H 40% in C XXXXXXX 2500 amount invested is less than 2.5 million 1000 1000 1000 100 amount invested in S1 >= 100k 100 amount invested in S2 >= 100k 100 amount invested in S3 >= 100k 100 amount invested in H1 >= 100k 100 amount invested in H2 >= 100k 100 amount invested in H3 >= 100k 100 amount invested in C1 >= 100k 100 amount invested in C2 >= 100k