Question
EXCEL EXCERCISES Karake, Inc . has been doing very well recently and has accumulated an increased level in its cash holding. Ayeni Diamond, the Chief
EXCEL EXCERCISES
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 companys 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 (C1C3) are primarily software companies, three (C4C6) are primarily hardware companies, and two (C7C8) 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).
- Formulate this as an optimization problem (1 point)
- 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)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started