Good Morning, I assure you this is all of the information for this question.

Thank you so much for your help, I am really struggling!

Case Problem - Gladstone Investments Barbara Garcia, the chief financial officer with Gladstone Investments, is faced with investing $1 million of funds for clients. The fund has a balanced goal of immediate yield with long-range appreciation, while maintaining acceptable risks. After a preliminary study of available investments, Garcia decides to invest the total amount in common stocks and Certificates of deposit (CDs). Further study narrows the choices of investments to the following. This table lists the projected annual rates of yleld and appreciation: Projected Rate of Appreciation, %/Year Projected Yield, %/Year 7.3 Investment CDs 0.0 8.7 4.2 Shenandoah Power Company Nantucket Gas & Light Fairview Public Utility 7.6 5.2 8.1 4.4 3.4 Dentrex Biotechnology Grodymore Clones Bugadoom and Associates 15.4 24.8 2.6 2.9 19.2 To balance yield and appreciation with risk, Garcia uses the following guidelines: 1. The total investment in the three utilities (Shenandoah, Nantucket, & Fairview) cannot exceed four times the investment in CDs. 2. The total investment in the three biotechnology firms (Dentrex, Grodymore, & Bugadoom) cannot exceed twice the combined investments in CDs and utility stocks. 3. At least 30% of the investment must be in Utilities. 4. At least 20% of the investment must be in Biotechnology firms. 5. The total investment in any single stock cannot exceed twice the sum of the other two stocks in the same industry. The twin goals of the fund are to provide a yield rate of 8% per year and a growth rate of 15% per year. Prepare a well-labeled table that allows the results of the following parts to be easily compared: C. a. What amounts should be invested in CDs and the six stocks? What will be the rates of yield and appreciation for the portfolio selected? Assume shortfalls from both goals of yield and growth are equally important. b. How do your responses to part (a) change if the shortfall from the goal of an 8% yield is five times as important as the shortfall from the goal of 15% growth? How do your responses to part (a) change if the shortfall from the goal of 15% growth is five times as important as the shortfall from the goal of an 8% yield? d. How do your responses to part (a) change if your only goal is to maximize the yield rate of the portfolio selected? e. How do your responses to part (a) change if your only goal is to maximize the growth rate of the portfolio selected? Gladstone Investments Case Notes You will need 7 decision variables to tell you how much to invest in each option. But you should also use 2 "bookkeeping" variables to keep track of the shortfall from each goal: (.073CD +.0875 +.076N +.081F +.034C +.026G +.029B) + SFY > 80,000 [O.OCD +.042S +.052N +.044F+.154C + .248G +.192B) + SFG > 150,000 Here SFY is the shortfall from yield and SFG is the shortfall from growth. Because these are not decision variables, you cannot simply say that they are > 0, as you do with the other 7 variables. So you will need to use IF statements: Syntax: =IF(comparison,true,false) How you might implement this: Row 15 Row 16 Row 17 Row 18 Row 19 Row 20 Column C Column D 80,000 150,000 (above) (above) =C15-C16 =D15-D16 =IF(C17