MSMG-5220H (2025GW) – Financial Markets & Management

Integrated Case Analysis / Group Assignment (20% of Final Grade)

Part A: Bond Valuation and Comparative Analysis [Guided Case Study / Financial Modeling Exercise]

Suppose that you are a credit analyst with Scotiabank and you have been asked to develop estimates for yield spreads
that would be appropriate for corporate bonds or loans with different maturities and credit ratings. To get a rough idea,
you decide to start looking at recent data from the bond market:

1. Plot the current US Treasury yield curve in Excel. Begin by finding the current U.S. Treasury yield curve. At
the Treasury Web site ([Link]), search using the term “yield curve” and select “Daily Treasury Yield
Curve Rates.” Copy the most recent rates for the 1-year through 30-year Treasury bonds into Excel and plot
the current Treasury yield curve. Alternatively, this data can be easily found in Bloomberg by entering
“CRVF” <GO> and searching for the curve: “US Treasury BVAL Curve” in Bloomberg’s Curve Finder.

• A full tutorial on using Bloomberg’s Curve Finder (CRVF) can be found on the Blackboard.

[Q1] How would you characterize the current shape of the US Treasury yield curve? Is the curve upwards or
downwards sloping? What does this shape imply about market expectations for future interest rates and
economic growth – in the near future, and longer term?

2. Next, find the current yields for bonds issued by Microsoft Corporation (NASDAQ:MSFT). Detailed
information regarding a company’s outstanding debt issuances can always be found within its latest 10-K
annual report (and other filings) and used to construct yield curves, but the process can be complicated
(particularly when a corporation has issued many bonds, has provisions related to seniority or call provisions
that cause their yields to vary, etc). Thankfully, corporate yield curves can also be easily found within the
“Credit” tab of Bloomberg’s Curve Finder (CRVF). For example, Microsoft’s curve can be found as:
“USD Microsoft Corp BVAL Yield Curve” and exported to Excel (see the illustrated tutorial for help).

In Excel, sort the bonds by maturity and note the yields for bonds maturing within 1 to 30 years. Add a
plot of these yields to your graph of the Treasury yield curve, and label it using Microsoft’s current bond
rating (which can be found via Google or using Bloomberg’s “CRPR” function).

[Q2] What is Microsoft’s current bond rating? How does its yield curve compare to the Treasury yield curve?

3. Repeat the analysis in Step 2 for Wells Fargo & Co (NYSE: WFC) CVS Health (NYSE:CVS).

Note: Wells Fargo’s curve can be found in Bloomberg’s Curve Finder (CRVF) as:
“USD Wells Fargo BVAL Yield Curve” (see the tutorial)

[Q3] What is Wells Fargo’s current bond rating and how does its yield curve differ?

4. Then, repeat the analysis one last time for your corporation.
[Q4] How does your selected corporation’s yield curve compare with the previous ones?

Finally – using the yield curves you have found – suppose that you have been asked to estimate the value of a
hypothetical annuity that pays $1000 every 5 years over the next 25 years (i.e., five total payments) paid by either:
1. the U.S. Treasury
2. Microsoft
3. Wells Fargo CVS
4. your selected corporation.

[Q5] What is the value and implied yield to maturity for each security?
 MSMG-5220H (2025GW) – Financial Markets & Management
Integrated Case Analysis / Group Assignment (20% of Final Grade)

Part B: Equity Valuation [Guided Case Study / Financial Modeling Exercise]

1. Go to Morningstar ([Link]) and enter the symbol for your selected corporation. From the main page
for your corporation, record the current stock price (last trade) at the top of the page.
2. Next, click the “Dividends” tab above the quote, and record the current annual dividend per share amount.
3. Next, click the “Financials” tab and then “All Financials Data” below the table. Export the entire five years of
income statements into a new worksheet in your existing Excel file. Repeat this process for both the balance sheet
and cash flow statement for your selected corporation. Record the most recent total number of shares outstanding
from the Income Statement (use ‘diluted’ figures) and record it on your main worksheet.
4. Next, click the “Valuation” tab which should include a number of analyst estimates. From this tab, find the five-
year growth forecast for earnings and enter it into your spreadsheet.
5. Using the three financial statements, calculate the five-year historical average for your corporation’s Return on
Equity (ROE = Net income / Total stockholder’s equity) and average dividend payout rate (Dividend paid / Net
income).
6. [Q1] Determine the stock value based on the dividend-discount model:
a. Create a timeline in Excel for five years.
b. Forecast the next five annual dividends based on the current dividend amount (from 2) and the five-year
growth rate (from 4).
c. Determine the long-term dividend growth rate: , using your
corporation’s retention rate (1- avg payout rate) and expected return on new investments (use avg ROE).
d. Use the long-term growth rate to determine the stock price for year four using: .
e. Finally, determine the current stock price using:

7. [Q2] Determine the stock value based on the discounted free cash flow method:
a. Forecast the free cash flows using the historic data from the financial statements to compute the five-year
average of the following ratios:
i. EBIT/Sales: Calculate EBIT as EBITDA (from the Income Statement) less Depreciation and
Amortization (from the Statement of Cash Flow)
ii. Net Property Plant and Equipment/Sales
iii. Net Working Capital (excluding cash)/Sales
b. Create a timeline for the next seven years.
c. Forecast future sales based on the most recent year’s total revenue growing at the five-year growth rate
(from 4) for the first five years. Use a long-run revenue growth rate equal to half this value for year six.
d. Use the average ratios computed in part (a) to forecast EBIT, Net Investment (change in Sales * Net
PPE/Sales), and Increases in NWC (change in sales * NWC/Sales) for the next seven years.
e. Forecast free cash flow for the next six years using FCF=EBIT*(1-Tc)-Net Investment-Increases in Net
Working Capital and the current corporate tax rate (from the income statement).
f. Estimate the terminal enterprise value in year five using the free cash flow in year six and
.
g. Determine the enterprise value of the firm as the present value of the free cash flows.
h. Determine the stock price using .

8. [Q3] Compare the stock prices from the two methods to the actual stock price. What recommendations can you
make as to whether clients should buy or sell your corporation’s stock based on your price estimates?

9. [Q4] Explain why the estimates from the two valuation methods differ. Specifically, address the assumptions
implicit in the models themselves as well as those you made in preparing your analysis. Why do these estimates
differ from the actual stock price of your corporation?
 MSMG-5220H (2025GW) – Financial Markets & Management
Integrated Case Analysis / Group Assignment (20% of Final Grade)

Part C: Portfolio Allocation [Guided Case Study / Financial Modeling Exercise]

Suppose it is the first week of March 2025, you have just started your new job with a financial planning firm, and your
manager has shortlisted the following 12-stocks for inclusion in a new client’s portfolio:
Archer Daniels Midland (ADM) Boeing (BA) Caterpillar (CAT) Deere & Co. (DE)

General Mills, Inc. (GIS) eBay (EBAY) Hershey (HSY) International Business Machines Corp. (IBM)

JPMorgan Chase & Co. (JPM) Microsoft (MSFT) Procter & Gamble (PG) “Your Selected Corporation”

➔ First, you have been asked to determine the monthly average returns and standard deviations for: (1) each of the
aforementioned stocks individually, and (2) an equally weighted portfolio constructed from them.

1. Collect price information for each stock. Historical prices can be found on Yahoo! Finance ([Link]) or Bloomberg.
However – for your convenience – let’s suppose that this data has been provided to you. You can download monthly price data
for all 12-stocks over the past five years (i.e. from February 2020-2025) from the Blackboard.

2. Convert these prices to monthly returns as the percentage change in the monthly prices. (Hint: Create a separate worksheet
within the Excel file.) Note that to compute a return for each month, you need a beginning and ending price, so you will not be able
to compute the return for the first month.

3. Compute the mean monthly returns and standard deviations for the monthly returns of each of the stocks (Note: we
previously learned how to compute returns with stock price and dividend data, but the “adjusted close” series from Yahoo!
Finance is already adjusted for dividends and splits, so we may compute returns based on the percentage change in monthly
adjusted prices). Then, convert the monthly statistics to annual statistics for easier interpretation (i.e. multiply the mean
monthly return by 12, and multiply the monthly standard deviation by the square root of 12).
[Q1] What are the mean returns and volatilities for each of the 12-stocks? (monthly & annual)

4. Add a column in your Excel worksheet with the average return across stocks for each month. This is the monthly return to an
equally weighted portfolio of these 12 stocks. Compute the mean and standard deviation of monthly returns for the equally
weighted portfolio. Double check that the average return on this equally weighted portfolio is equal to the average return of all of
the individual stocks. Convert these monthly statistics to annual statistics (as described in Step 3) for interpretation.
[Q2] What is the mean return and standard deviation of the equally weighted portfolio? (monthly & annual)

5. Using the annual statistics, create an Excel plot with standard deviation (volatility) on the x-axis and average return on
the y-axis as follows:
a. Create three columns on your spreadsheet with the statistics you created in Questions 3 and 4 for each of the individual
stocks and the equally weighted portfolio. The first column will have the ticker, the second will have annual standard
deviation, and the third will have the annual mean return.
b. Highlight the data in the last two columns (standard deviation and mean), choose Insert>Chart>XY Scatter Plot. Complete
the chart wizard to finish the plot, and attach it as an Exhibit within your report.

[Q3] What do you notice about the volatilities of the individual stocks, compared to volatility of the equally weighted portfolio?

➔ Impressed by your analysis, your manager has next asked you to update the stock portfolio by:
(1) Rebalancing the portfolio with the optimum weights that will provide the best risk and return
combinations for the new 12-stock portfolio.
(2) Determining the improvement in the return and risk that would result from these optimum weights
compared to the previous method of equally weighing the stocks in the portfolio.
 Additional Instructions:
Use the Solver function in Excel to perform the following analysis (the time-consuming alternative would be to find
the optimum weights by trial-and-error). In case the Solver tool is not available, you must load it into Excel as follows:
1. On the File Tab, click Excel Options.
2. Click Add-Ins, and then, in the Manage box, select Excel Add-ins.
3. Click Go.
4. In the Add-Ins available box, select the Solver Add-in check box, and then click OK.

Tip: If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in. If you
are prompted that the Solver Add-in is not currently installed on your computer, click Yes to install it.
After you load the Solver Add-in, the Solver command is available in the Analysis group on the Data tab.

6. Begin with the equally weighted portfolio (from Steps 4-5). Establish the portfolio returns for the stocks in the portfolio using a
formula that depends on the portfolio weights. Initially, these weights will all equal “1/12”. You would like to allow the portfolio
weights to vary, so you will need to list the weights for each stock in separate cells and establish another cell that sums the weights
of the stocks. The portfolio returns for each month must reference these weights for Excel Solver to be useful.

7. Compute the values for the monthly mean return and standard deviation of the portfolio. Convert these values to annual numbers (as
described in Step 3) for easier interpretation.

8. First, compute the efficient frontier when short sales are not allowed. Use the Solver tool in Excel (on the Data tab in the
analysis section). To set the Solver parameters:
a. Set the objective to be the cell that computes the (annual) portfolio standard deviation. Minimize this value.
b. Set the “By Changing Variable Cells” to the cells containing the portfolio weights. (Hold the Control key and click in
each of the 12 cells containing the weights of each stock.)
c. Add constraints by clicking the Add button next to the “Subject to the Constraints” box. The first constraint is that the cell
containing the sum of all the portfolio weights must equal one. The next set of constraints is that each portfolio weight is
non-negative. You can enter these constraints individually, or check the box “Make Unconstrained Variables Non-
Negative.”
d. Compute and identify the portfolio with the lowest standard deviation. If the parameters are set correctly, you should get
a solution when you click “Solve.” If there is an error, you will need to double-check the parameters, especially the
constraints. [Q4] What are the weightings of each stock in this portfolio?

9. Next, compute portfolios that have the lowest standard deviation for a target level of the expected return.
a. Start by finding the portfolio with an expected return of 2% higher than the annual return for the minimum variance
portfolio you computed in Step 8, rounded to the nearest whole percentage. To do this, add a constraint that the (annual)
portfolio return equals this target level. Click “Solve” and record the standard deviation and mean return of the solution
(and be sure the mean return equals target—if not, check your constraint).
b. Repeat the previous step (a) raising the target return in 2% increments, recording the result for each step. Continue to
increase the target return and record the result until Solver can no longer find a solution. Next, repeat Step (a) by lowering
the target return in 2% increments from the return of the minimum variance portfolio, again recording each result.
[Q5] At what level(s) does Solver fail to find a solution? Why?
10. Plot the efficient frontier with the constraint of no short sales. To do this, create an XY Scatter Plot (similar to what you did in
‘Step 5’), with portfolio standard deviation on the x-axis and the return on the y-axis, using the data for the minimum variance
portfolio and the portfolios you computed in step 9. [Q6] How do these portfolios compare to the mean and standard deviation for
the equally weighted portfolio analyzed in ‘Steps 4-5’?
11. Redo your analysis to allow for short sales by removing the constraint that each portfolio weight is greater than or equal to zero.
Use Solver to calculate the (annual) portfolio standard deviation for annual returns in 5% increments from 0% to 40%. Plot the
unconstrained efficient frontier on an XY Scatter Plot. [Q7] How does allowing short sales affect the frontier?
12. Finally, redo your analysis adding a new risk-free security that has a 3% annual return, or 0.25% (0.0025) each month. Include a
weight for this security when calculating the monthly portfolio returns. That is, there will now be 13 weights, one for each of the 12
stocks and one for the risk-free security. Again, these weights must sum to 1. Allow for short sales, and use Solver to calculate the
(annual) portfolio standard deviation when the annual portfolio returns are set to 3%, 10%, 20%, 40%. Plot the results on the same
XY Scatter Plot, and in addition keep track of the portfolio weights of the optimal portfolio. [Q8] What do you notice about the
relative weights of the different stocks in the portfolio as you change the target return? Can you identify the tangent portfolio?