AF495 - Case 2: Cost of Capital

NOTE: This is for AF495 ONLY. (Not AF325) AF495 - Case 2: Cost of Capital Submission Information DUE DATE: November 18th, 2025 at 11:59 PM D R AF T Submission: Post your write-up and spreadsheet on Canvas. Name your file to include the last name of at least one person in your group. For example: Case2Callahan.doc; Case2Callahan.xlsx Format: Please use only the extensions listed here. I cannot open other files. Excel - xls; xlsx Word - doc, docx Collect Bond Data from Bloomberg The Bloomberg Lab is on the 3rd floor of the library (behind the IT Help Desk) Entering Bloomberg Lab and Computer 37286* Login to computer: STUDENT Password: cmstudent ********** 1. Collect Bond data from Bloomberg. For each bond issue, you need the following: a.​ b.​ c.​ d.​ 2. YTM Coupon Rate Price Total face value outstanding (Amount Outstanding). (What you need is the total face/par value.) Here are instructions to help you find the data on Bloomberg NOTE: You must use all of your company’s bonds for your analysis. Get this information from Bloomberg. Analysis Spreadsheet D R AF T 1.​ Once you have collected your bond data, you will need to add columns to calculate the following information: a.​ Market Value b.​ Book Value weights c.​ Market Value weights d.​ MV weighted average price (you will use this with debt information from the balance sheet to compute the market value of debt and market value weight of debt) e.​ BV weighted average yield (this is your pre-tax bv cost of debt) f.​ MV weighted average yield (this is your pre-tax mv cost of debt) 2.​ Your spreadsheet must accommodate the data and computations in the screen grab on this spreadsheet. a.​ Reference this document which explains every calculation that you need to do for your case. Spreadsheet Guidelines 1.​ 3.​ 4.​ 5.​ 6.​ 7.​ 8.​ Always use reference cells. All of the data in your sheet should be connected through cell references. Each data point in your sheet should have only one origination point. This is the cell in which the data point was first entered or calculated. Every other time that data point is used, it should be referenced. This way, if you update or change the data, it will also update every other number associated with it. Make sure that it is formatted professionally and easy to read. Do not copy my format from the worksheet I gave you in class. Be sure to use dollar signs and percentages where appropriate. Use consistent formatting throughout the spreadsheet; e.g. For example, be sure that number of decimal places is the same; totals should be underlined, shaded, or bold… Label and name tables. For example: Table 1: Bond Data Format for printing. Even though you do not need to print this out, you must format it for printing. Make sure your page breaks make send (for example, you are not starting a new page in the middle of a table). Make sure that it would be readable if printed out - do not “shrink” tables or sheets so that they are too small to be legible. Use cell names for references in different sheets. Write-up Guidelines Required Information 1. State the purpose of the analysis, mention your company’s name and describe what you did (for example, you did research on the internet and…) Explain what the WACC is and how it can be used. 2. Summarize your company’s outstanding bonds. For example, number of bond issues, range of maturities, bond rating. (If your company had many bond issues, but you could not use them all because of missing information, then explain this.) 3. Summarize your estimates for your company’s cost of debt. What value will you use for your WACC calculation? Why? 4. What is your company’s tax rate? Describe where and how you got this information. 5. What is your company’s after tax cost of debt? 6. What is the estimate for the cost of equity? a. Describe how you came up with this estimate. For example, which models did you use D R AF T (CAPM)? What were your inputs? What did you use for Beta? 7. Does your company rely more heavily on debt or equity for financing? Talk about your estimates for the MV and BV weights. Explain why these are different. Explain how you calculated the MV of debt and why you did it that way. 8. What is your estimate for your company’s WACC based on both BV and MV? 9. What is your best estimate for the value that your company should use for its cost of capital? Explain. (for example, do you think that the BV or MV is better? Why?) Required Formatting 1.​ Use descriptive subheadings to structure your paper according to the required information above. 2.​ Do not directly copy any sentences from this document or any other source. Use your own words. 3.​ Include tables of your data. You can either directly copy tables from your spreadsheet, or you can create summary tables to include the relevant information. Academic Integrity Remember to use your own words. Do not copy any part of this document or information from class materials. If you use information from the internet or another source, be sure to document it appropriately; or just don’t - translate the information into your own wods. Academic Honesty It is expected that all students will uphold the UMass policy on academic integrity. All work submitted should be completed only by the student being evaluated. Students caught cheating or plagiarizing will, at the discretion of the instructor, fail either the assignment in question or the course and will have a description of the incident attached to his or her academic records.​ Downloading Corporate Bond Data From Bloomberg The purpose of this task is to download your company’s bond data so that you can calculate the WACC for your company. The steps are listed below. If you do not have a Bloomberg account, you must create one. Enter the FUNCTION SECF in the blue field at the top of the page. 2. Type your company’s ticker in the SEARCH field at the top of the page. D R AF T 1. 3. Find the row FIXED INCOME in the CATEGORY column. Click on it. A list of your company’s bonds will appear. 4. Click on COLUMN SETTINGS on the right side (see it below in yellow). Now you will choose the data that will be exported to your spreadsheet. The fields that you need are listed in the “Selected Items” list on the screenshot below. 5. From AVAILABLE ITEMS list on left, SELECT the items that appear on the “Selected Items” list on the right. 6. Remove items that are not in the list. 7. ​ Click on BACK from top of screen 8. ​ Click on EXPORT from top of screen 9. Your data should download. D R AF T 10. SAVE YOUR EXCEL FILE and EMAIL IT TO YOURSELF or upload it to the cloud. NOTE: YOU MUST SAVE IT AS AN EXCEL FILE (.xls).