tatasteelcs - PRELIMINARY STUFF AND INPUTS This spreadsheet...

Info icon This preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
PRELIMINARY STUFF AND INPUTS Objective This spreadsheet allows you to compute the optimal capital structure for a non-financial service firm. If you have a financial service firm use capstrfin.xls Before you start Open preferences in excel, go into calculation options and put a check in the iteration box. If it is already checked, leave it as is. Inputs The inputs are primarily in the input sheet. If your company has operating leases, use the operating lease worksheet to enter your lease or rental commitments. Units Enter all numbers in the same units (000s, millions or even billions) Income inputs The key income inputs are EBITDA, depreciation and amortization and interest expenses. Enter the most updated numbers you have for each (even if they are 12-month trailing numbers). If the most recent period for which you have data has an operating income that is abnormal, either because of extraordinary losses/gains or some other occurrence, use an average operating income over the last few years. From the statement of cash flows, also enter the capital spending from the recent period. P.S: If you have negative operating income and you expect to continue having negative operating income, your optimal debt ratio will be zero. Balance Sheet Enter the book value of all interest-bearing debt. If you have a market value enter that number. Alternatively, input the average maturity of the debt and I will estimate the market value of debt. Market Data Enter the current stock price, the current long-term government bond rate, the risk premium you would like to use to estimate your cost of equity and the current rating for your firm. If you do not have a rating, there is an option for you at the very bottom of the spreadsheet to compute a synthetic rating. Tax Rate Enter a marginal tax rate, if you can estimate it. Otherwise, use the effective tax rate. Default Spreads This spreadsheet has interest coverage ratios, ratings and default spreads built into it in the worksheet. This spreadsheet treats the imputed interest expense on operating leases as part of the interest expense when computing the interest coverage ratio. You can choose between ratings for large firms (firms with market capitalizations that exceed $ 5 billion is a simple cut off but you can deviate from it) a more conservatve for small or risky firms. If you want, you can change the interest coverage ratios and ratings in these tables. READING THE OUTPUT Summary The summary provides a picture of your firm's current cost of capital and debt ratio, and compares it to your firm's optimal debt ratio and the cost of capital at that level. It then uses the savings from the change in cost of capital to compute how much your firm value will change: - with constant savings: as the present value of a perpetuity - with a growth rate in the savings in perpetuity The firm value change, divided by the number of shares, yields a price change Details The details of the calculation at each debt ratio are below the summary.
Image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern