ONLY NEED EXCEL CALCULATIONS
Cost of Capital, Capital Structure, and Capital Budgeting Analysis – McKesson Corporation In this project, you are supposed to be a financial manager to apply the knowledge learn from Financial Manage
I have the files attached. I need the Excels for calculations.
In this project, you are supposed to be a financial manager to apply the knowledge learn from Financial Management to estimate cost of debt, preferred stock and common equity, capital structure, and weighted average cost of capital (WACC) for a publicly traded corporation of your choice. You will use the estimated WACC as the discount rate to perform capital budgeting analysis for a hypothetical project (the data is given below) considered by the selected company McKesson Corp, and decide whether the project should be taken.
Estimate Capital Structure
– Estimate the firm’s weights of debt, preferred stock, and common stock using the firm’s balance sheet (book value) using the most recent year data.
– Estimate the firm’s weights of debt, preferred stock, and common stock using the market value of each component using the most recent year data.
Compute Weighted Average Cost of Capital (WACC)
– Estimate the firm’s before-tax and after-tax component cost of debt; (Note: If the information about the current corporate tax rate is not available, you can estimate the tax rate based on the firm’s historical tax payments).
– Estimate the firm’s component cost of preferred stock if the firm has issued preferred stocks.
– Use three approaches (CAPM, DCF, bond-yield-plus-risk-premium) to estimate the component cost of common equity for the firm.
– Calculate the firm’s weighted average cost of capital (WACC) using the market-based capital structure weights obtained from Step (3).
Capital Budgeting Analysis
– Using the WACC obtained from in Step (4) as the discount rate for this project, apply capital budgeting analysis techniques (NPV, IRR, MIRR, PI, Payback, Discounted Payback) to analyze the new project.
– Perform a sensitivity analysis for the effects of key variables (e.g., sales growth rate, cost of capital, unit costs, sales price) on the estimated NPV or IRR in order to demonstrate the sensitivity of the model. The Scenario analysis of several variables simultaneously is encouraged (but not required). A document Sensitivity Analysis using Data Table in Excel is provided for the introduction of the Data Table function in Excel.
– Discuss whether the project should be taken and summarize your report.
ONLY NEED EXCEL CALCULATIONS Cost of Capital, Capital Structure, and Capital Budgeting Analysis – McKesson Corporation In this project, you are supposed to be a financial manager to apply the knowledg
McKesson Corporation Financial Management Project Student Name Affiliation Course Instructor Due Date McKesson Corporation Financial Management Project Estimated Capital Structure The capital structure of an organization refers to the mix of debt and/or equity used to finance its operations and assets. A debt-to-equity ratio or a debt-to-capital ratio are widely used to define the financial structure of a corporation. A company’s operations, capital expenditures, acquisitions, and other investments are financed by debt and equity capital. Managers will balance the two to create the appropriate capital structure regardless of whether businesses choose to finance their operations with debt or equity. The debt-to-equity ratio that results in the company having the lowest weighted average cost of capital is often used to determine the ideal capital structure for corporations. McKesson has a low net debt to EBITDA ratio of only 0.20. Additionally, its EBIT more than 15.2 times exceeds its interest expense. We therefore have a casual attitude toward its extremely prudent use of debt. Another positive development is that McKesson’s EBIT increased by 11% over the previous year, further enhancing its capacity to control debt. When examining debt levels, it makes sense to start with the balance sheet. However, future earnings will ultimately determine McKesson’s capacity to maintain a healthy balance sheet going forward (Pettit, 2003). The last point is that a business can only pay off debt with actual cash, not accounting earnings. The next natural step is to determine what percentage of that EBIT is actually matched by free cash flow. Fortunately for any shareholders, during the past three years, McKesson has actually generated more free cash flow than EBIT. We experience the same kind of excitement as the crowd at a Daft Punk concert when there is that kind of significant cash conversion. The chart below, shows that McKesson had US$7.07b in debt in March 2021 Capital Structure Estimation Analysis The ratio of debt to equity used by a corporation to finance both its current operations and potential future expansion is known as its capital structure. Equity capital derives from both ownership shares in and claims on a company’s future cash flows and profitability (Exley, 2006). Debt examples include things like loans and bond issuances, whereas equity examples include things like common stock, preferred stock, and retained earnings. Additionally regarded as a component of the capital structure is short-term debt. A firm funds its overall operations and expansion through its capital structure. Borrowed funds that must be repaid to the lender, frequently with interest charges, constitute debt. Without the need to recoup any investment, equity is the right to ownership in the business. The ideal capital structure of McKesson Corporation is between 30 and 49 percent, and to be prudent, medium company risk is taken into account. Given that it has been largely stable and falls within the desirable range, the target capital structure is estimated to be 45.7 percent using the average of the last 8 years. It looks as if management might be keeping its capital structure conservative when comparing it to its rivals in order to be on the lookout for potential acquisition opportunities in the future. (WACC)Weighted Average Cost of Capital The WACC is the amount that a company anticipates charging on average to all of the holders of its securities in order to fund its assets (WACC). The WACC is also known as the firm’s cost of capital. It’s significant that the external market, not management, controls it. To evaluate the financial stability of McKesson Corporation we must address the important component regarding the Weighted Average Cost of Capital. Currently, McKesson’s weighted average cost of capital is 5.56 percent. For McKesson, the ROIC rate is 8.02 percent (calculated using TTM income statement data). McKesson delivers higher returns on investment in comparison to the expense to the company of raising the funds necessary for that investment. It is generating excess returns. If a corporation believes it will continue to generate positive excess returns on fresh investments, its value will increase as expansion picks up speed. The weighted average cost of capital for McKesson at this time is 5.51 percent. McKesson WACC % Calculation The weighted average cost of capital is the amount that a company anticipates charging on average to all of the holders of its securities in order to fund its assets. The firm’s cost of capital is another name for the WACC. In general, debt and equity are used to finance a company’s assets. The WACC is the sum of the expenses of these funding sources, each of which is weighted according to how frequently it would be appropriate to employ them in a particular circumstance. The amount of interest the corporation is required to pay on each dollar of funding can be determined using a weighted average. WACC= E / (E + D)* Cost of Equity + D / E + D)*Cost of Debt * (1 – Tax Rate) Mostly because raising capital is costly. A corporation is making excess returns if it achieves a higher ROIC percentage than it costs it to raise the cash required for that investment. As growth accelerates, the value of a company that anticipates making positive surplus returns on new investments will rise, as opposed to a company that generates returns that fall short of its cost of capital and loses value over time. Component Cost of Debt Estimation The effective interest rate that a business pays on its debt, such as bonds and loans, is known as the cost of debt. The two primary elements of a company’s capital structure are stock and debt. Calculating the cost of debt requires determining the average interest rate paid on all of a company’s liabilities (Banerjee, 1999). McKesson’s interest expense (positive amount) as of March 2022 was $178 Mil. It has $8473.5 million in total Book Value of Debt (D). Cost of Debt equals 178 divided by 8473.5, or 2.1007 percent. Yield to Maturity Approach Based on a bond’s current market price, the yield is determined using the Yield to Maturity formula. The rate at which all potential future cash flows add up to $1,050 is known as the YTM. We can use a financial calculator to figure out I. I = 3.643 percent in this case yields the six-month yield. The yield to maturity is used by debt funds to calculate returns. However, it modifies in reaction to altering market conditions. The YTM of an open-ended Debt Fund differs from the scheme’s real returns as a result in practice. Before-Tax Component Cost of Debt Cost of debt is the price incurred by a corporation when obtaining finance through debt instruments like taking out a bank loan or issuing bonds. It can be stated more simply as the actual interest rate a business pays on its present debt. There are two ways to calculate the debt’s before-tax cost: the yield-to-mature (YTM) strategy (Le, 2021). After-Tax Component Cost of Debt The interest paid on the loan less any income tax savings from deducting interest payments are the debt’s after-tax costs. The cost of debt multiplied by the effective tax rate, which is divided by one, yields the cost of debt after taxes for a given company. After-tax cost of debt = Before-tax cost of debt x (100% – incremental tax rate) Component Cost of Preferred Stock Estimation The cost of preferred stock, which calculates the rate of return desired by preferred shareholders, is obtained by dividing the annual preferred dividend paid out (DPS) by the current market price. MCK preferred stock as of today is $0 Mil. Component Cost of Common Equity Estimation 6.9 percent is the estimated cost of common equity for McKesson Corporation. The aggregate contribution of the holders of Common Stock, Stock, Paid-In Capital, Retained Earnings and Special Reserves. Paid-in capital is the sum of the par value of the stock and the amount paid by stockholders to acquire newly issued shares. CAPM Cost of Equity The capital asset pricing model identifies the needed or expected rate of return for risky assets like the common stock of McKesson Corp. Variance and Covariance Rates of Return Estimation of Systematic Risk Rate of Return Assumed Bond Yield plus Risk Premium Cost of Equity Using the bond yield plus risk premium strategy, we can also estimate the value of an asset, in this case, the publicly traded stock of a corporation. By multiplying the yield to maturity of the company’s long-term debt by the equity risk premium, BYPRP enables us to calculate the needed return on an equity. Average Cost of Equity Cost of Equity = Risk-Free Rate of Return + Beta × (Market Rate of Return – Risk-Free Rate of Return) The estimated cost that businesses should pay their shareholders when assessing existing investments and upcoming expenses is known as the “Average Cost of Equity.” Weighted Average Cost of Capital The Weighted Average Cost of Capital is equal to the (Market Weight of Common Equity)*(Average Cost of Equity) + (Market Weight of Debt) * (Cost of Debt) * (1 – Effective Tax Rate) (Vélez-Pareja, 2009). Project Recommendations One of the biggest American suppliers of pharmaceuticals, medical equipment, and health information technology (IT) goods and services is McKesson Corp. In 1833, John McKesson and Charles Olcott founded the company in New York with a concentration on importing and wholesale distribution of medicinal goods. McKesson is well-positioned to advance cancer care. Our solutions have a favorable effect on patients’ ability to get their prescriptions quickly and efficiently. We are honoring two of our workers who work tirelessly to help us understand and value this vibrant and diverse community as well as Asian-American and Pacific Islander Heritage Month. McKesson help in distribution and the supply kit needed to administer COVID-19 vaccine. References Banerjee, S., Heshmati, A., & Wihlborg, C. (1999). The dynamics of capital structure. New York University-Salomon Center-Leonard N. Stern School of Business. Exley, C. J., & Smith, A. D. (2006). The cost of capital for financial firms. British Actuarial Journal, 12(1), 229-283. Le, H. T. T., Vo, X. V., & Vo, T. T. (2021). Accruals quality and the cost of debt: Evidence from Vietnam. International Review of Financial Analysis, 76, 101726. Pettit, J. (2003). Healthcare Cost of Capital Handbook. Available at SSRN 463020. Vélez-Pareja, I., & Tham, J. (2009). Market value calculation and the solution of circularity between value and the weighted average cost of capital WACC. RAM. Revista de Administração Mackenzie, 10, 101-131.