Part IV: Managing and Analyzing Data
diversification goals. Without such constraints, a portfolio problem becomes a no-brainer:
put all your money in the investment with the highest yield.
This example involves a credit union (a financial institution that takes members’ deposits
and invests them in loans to other members, bank CDs, and other types of investments).
The credit union distributes part of the return on these investments to the members in the
form of dividends, or interest on their deposits.
This hypothetical credit union must adhere to some regulations regarding its investments,
and the board of directors has imposed some other restrictions. These regulations and
restrictions comprise the problem’s constraints. Figure 32.16 shows a workbook set up for
this problem.
FIGURE 32.16
This worksheet is set up to maximize a credit union’s investments, given some constraints.
This workbook is available on this book’s website at http://www.wiley.com/go/excel2019bible. The
file is named investment portfolio.xlsx.
Allocating the $5 million portfolio is subject to these constraints:
■ The amount that the credit union invests in new-car loans must be at least
three times the amount that the credit union invests in used-car loans. (Used-
car loans are riskier investments.) This constraint is represented as
C5>=C6*3
■ Car loans should make up at least 15% of the portfolio. This constraint is repre-
sented as
D14>=.15
