301 Information Systems

I need help with my project #4 in excel

Karake, Inc. has been doing very well recently and has accumulated an increased level in its cash holding. Ayeni Diamond, the Chief Financial Officer (CFO) was asked to look at ways to invest $2.5 million in cash in a way to maximize the company's return on its investment. Ayeni wants to determine an optimal portfolio of investments. As indicated, the main objective of Karake, Inc. is to maximize total dollar return from both growth and dividends over the course of the coming year. Ayeni has researched eight high-tech companies and wants the portfolio to consist of shares in these firms only. Three of the firms (C1-C3) are primarily software companies, three (C4-C6) are primarily hardware companies, and two (C7-C8) are Internet consulting companies. The management of the company has stipulated that no more than 40% of the investment be allocated to any one of these three sectors. To assure diversification, at least $100,000 must be invested in each of the eight stocks. The table below gives some financial estimates based on the past 10 years 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.

Stock C1 C2 C3 C4 C5 C6 C7 C8

Price/Share $40 $50 $80 $60 $45 $60 $30 $25

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

Ms. Diamond is seeking your help in developing an initial planning model (i.e., fractional outcomes for the decisions are acceptable).

(a)Formulate this as an optimization problem(1 point)

(b)Use Excel Solver to 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? (1 point)

(c)Consider the optimal solution above and trace the effects of additional investment, beyond the original $2.50 million. How will the portfolio change? (1) point)

(d)For how much of a change in the investment quantity will the pattern in (b) persist? (1point)