Problem 1-11 Financial Analysis PDF

Problem 1 How to construct financial statements? Fin 1303 1 What are financial statements? Financial statements are a set of documents that show company’s financial status at a specific point in time. They include key data on what company owns and owes and how m...

Problem 1 How to construct financial statements? Fin 1303 1 What are financial statements? Financial statements are a set of documents that show company’s financial status at a specific point in time. They include key data on what company owns and owes and how much money it has made and spent. There are three main financial statements: Balance sheet Income statement Cash flow statement Financial statements may be prepared for different timeframes. Annual financial statements cover the company’s latest fiscal year. Financial statements generally give information for both the latest period and the prior period to make comparisons easier. For example, a financial statement covering January 1 to December 31, 2021, would include the statements for both that year and the previous 2 year—January 1 to December 31, 2020. Elements of financial statements 1. Income statement An income statement shows the profitability of company’s business. It details how much money a company earned and spent. The income statement is also sometimes referred to as a profit- loss statement or an earnings statement. It shows: Revenue from selling products or services Expenses to generate the revenue and manage your business Net income (or profit) that remains after your expenses 3 Elements of financial statements 2. Balance sheet The balance sheet, or statement of financial position shows what the company owns and how much it owes at the end of the period. It is a summary of the financial balances of a sole proprietorship, a business partnership, a corporation, or other business organization. The balance sheet is based on the equation: Assets = Liabilities + Shareholders’ Equity 3. Cash flow statement The cash flow statement, or a statement of changes in financial position, shows how money has moved through company’s business during the period. 4 Common size financial statement 1. Common size income statement An income statement in which each account is expressed as a percentage of the value of sales. This type of financial statement can be used to allow for easy analysis between companies or between time periods of a company. 2. Common size Balance sheet A balance sheet that displays both the numeric value of all entries and the percentage each entry is relative to the total value of related entries. On a common size balance sheet, an asset is compared to total assets, a liability to total liabilities and stockholder equity to total stockholder equity. 5  Problem 1 Consider the following information Aspen Industries for the Years Ended December 31, 2020 and 2021. 1- Create the income statement and balance sheet using formulas wherever possible. 2- Create the common-size income statement and common-size balance sheet for 2020 and 2021. 3- Create a statement of cash flows for the year 2021. Problem 1 6 7 8 Problem 2 Financial Analysis Fin 1303 9 What is financial analysis? Financial analysis can be defined as a process that evaluates businesses, budgets, projects, and entities for analysis purpose. This evaluation is done with the purpose of determining the suitability for investment by a business. Usually, the main purpose of financial analysis is to analyze the stability, solvency, liquidity, and profitability of a business. The process of financial analysis is carried out by professionals who work by preparing reports with the help of ratios containing information from financial statements and other similar reports. The main objectives of financial analysis include: - Solvency - Profitability - Liquidity - Stability 10 DuPont System of Ratio Analysis DuPont analysis is a financial ratio used to analyze a company’s overall performance. The DuPont equation breaks down return on equity (ROE) into three separate components. These are profit margin, asset turnover, and leverage. Components Of Dupont Analysis The Dupont analysis is composed of three ratios: 1. Profit Margin 2. Asset Turnover Ratio 3. Equity Multiplier 11 Financial Distress Prediction Financial distress can be the reflection of corporation's management condition. Consequently, the distress score of corporations should be considered as a new predictor variable in predicting the financial distress. Altman Z-Score model It is a multivariable formula for measuring a company’s potential bankruptcy. It is a function of the five financial ratios: profitability, leverage, liquidity, solvency, and activity ratios. 12 Decision Rules 13  Problem 2 1- By considering the financial statements treated in problem 1, calculate the liquidity, efficiency, leverage, coverage and profitability ratios for Aspen Industries for the Years Ended December 31, 2020, and 2021 in sheet Pb 2 (1). 2- Regarding the different averages’ ratio of the sector, make analysis of the financial situation of Aspen Industries (Good, Ok, or Bad) in sheet Pb 2 (1). 3- Calculate by different ways the ROE in sheet Pb 2 (2). 4- Using Altman's model for privately held firms, calculate the Z-score for Aspen Industries in sheet Pb 2 (3). Does it appear that the firm is imminent danger of bankruptcy? Problem 2 14 15 16 Problem 3 Financial Forecasting Fin 1303 17 What is financial forecasting? Financial Forecasting is the process of predicting or estimating future stats of an organization i.e. how the business will perform in the future based on historical data by analyzing the income statement, position statement, current conditions, past trends of the financial, future internal and external environment which is usually undertaken with the objective of preparing and developing a budget and allocating available resources to ensure best possible utilization. 18 What is financial forecasting? Financial Forecasting is the process of predicting or estimating future stats of an organization i.e. how the business will perform in the future based on historical data by analyzing the income statement, position statement, current conditions, past trends of the financial, future internal and external environment which is usually undertaken with the objective of preparing and developing a budget and allocating available resources to ensure best possible utilization. 19 Simple linear regression Simple linear regression forecasts metrics based on a relationship between two variables: dependent and independent. The dependent variable represents the forecasted amount, while the independent variable is the factor that influences the dependent variable. The equation for simple linear regression is: Y = BX + A Formula breakdown: Y = Dependent variable (the forecasted number) B = Regression line's slope X = Independent variable A = Y-intercept 20  Problem 3 Consider the following information about the Aspen Industries Sales for 2015 – 2019: 1- Plot a linear graph for Profit over time. 2- Insert a trend line (choose three -3- forward periods), the equation and R-squared on the chart. 3- Use the forecast function to expect Profit for the years 2020 - 2022 4- Use the trend function to expect Prices for the years 2020 - 2022 21 Problem 3 22 Problem 4 Break Even Analysis Fin 1303 23 What is Break Even Analysis? Break-even analysis in economics, business, and cost accounting refers to the point at which total costs and total revenue are equal. A break- even point analysis is used to determine the number of units or dollars of revenue needed to cover total costs (fixed and variable costs). 24 Problem 4 Key Highlights  Break-even analysis refers to the point at which total costs and total revenue are equal.  A break-even point analysis is used to determine the number of units or dollars of revenue needed to cover total costs.  Break-even analysis is important to business owners and managers in determining how many units (or revenues) are needed to cover fixed and variable expenses of the business. 25 Problem 4 Break Even Analysis Formula The formula for break-even analysis is as follows: Break-Even Quantity = Fixed Costs / (Sales Price per Unit – Variable Cost Per Unit) where:  Fixed Costs are costs that do not change with varying output (e.g., salary, rent, building machinery)  Sales Price per Unit is the selling price per unit  Variable Cost per Unit is the variable cost incurred to create a unit 26 Problem 4 Problem 4 Consider the following information : 1- Calculate the Total Variable Cost per Unit. 2- Calculate the Break-Even number of units by using formula. 3- Fill in the second table and calculate net profit for different units sold. 27 Problem 4 Problem 4 4- According to the second table, Insert a line chart showing Total cost and Revenue. 5- Use the properties of Excel to determine the Break-Even number of units by a second method. 28 Problem 4 29 Problem 5 Time value of money Fin 1303 30 What is Time Value of Money? The time value of money (TVM) is the concept that a sum of money is worth more now than the same sum will be at a future date due to its earnings potential in the interim. The time value of money is a core principle of finance. A sum of money in the hand has greater value than the same sum to be paid in the future. The time value of money is also referred to as the present discounted value.. 31 Problem 5 Key Highlights  The time value of money means that a sum of money is worth more now than the same sum of money in the future.  The principle of the time value of money means that it can grow only through investing so a delayed investment is a lost opportunity.  The formula for computing the time value of money considers the amount of money, its future value, the amount it can earn, and the time frame.  For savings accounts, the number of compounding periods is an important determinant as well.  Inflation has a negative impact on the time value of money because your purchasing power decreases as prices rise. 32 Problem 5 Time Value of Money Formulas in Excel 33 Problem 5 Problem 5 5-1 According to the table presented in worksheet PB 5 (1), calculate the present value, future value, payment, interest rate, and the number of periods. 5-2 Consider four investment funds (money market funds, long term bond funds, conservative common stock funds, and aggressive common stock funds). For each fund, calculate the non annual future value. 5-3 Consider an investment project with uneven cash flows. Calculate the present value of these cash flows. Calculate by different ways the future value of these cash flows. 34 Problem 5 Problem 5-1 35 Problem 5-2 36 Problem 5-3 37 Problem 6 Capital Budgeting Fin 1303 38 What is Capital Budgeting? Capital budgeting is a type of financial management that focuses on the cash flow implications of making an investment, rather than resulting profits. It involves estimating the amount and timing of cash outflow : “money that leaves the business to pay for a purchase or investment, such as new equipment”, and cash inflow, or new sources of cash that come into the company, such as increased sales revenue made possible by the increased output from the new equipment. Two important concepts that underlie many capital budgeting methods are opportunity cost and the time value of money. Both apply due to the long-term nature of most capital projects. 39 Problem 6 Capital Budgeting Methods Businesses can choose to use one or more types of capital budgeting methods to help value and evaluate capital projects. They are also helpful in comparing competing projects and developing rankings. Payback Period This method focuses on how quickly a company recoups its capital investment. Discounted Payback Period This method is an improved version of the payback period method because it also reflects the time value of money, which always decreases as the years pass. Net Present Value The net present value (NPV) 40 Problem 6 Capital Budgeting Methods Profitability Index The profitability index is a technique that calculates the cash return per dollar invested in a capital project. Equivalent Annuity Method The equivalent annuity method is a way to evaluate the NPV of capital projects that are mutually exclusive and have different project lengths. Internal Rate of Return The internal rate of return (IRR) method looks to find the discount rate that causes a project’s NPV to be zero. Modified Internal Rate of Return (MIRR) This method is an extension of the IRR. It also calculates a yield percentage on a project when the NPV is zero, but in a more complex and accurate way. The MIRR uses different rates for discounting cash inflows than for cash outflows when calculating the NPV. 41 Problem 6 Problem 6 You are evaluating two mutually exclusive projects, A and B. 1- For both projects, calculate the payback period and the discounted payback period. Compare the projects by using logical function if. 2- For both projects, calculate the NPV, PI, IRR and MIRR. Compare the projects by using logical function if. 42 Problem 6 43 44 Problem 7 Stock Returns Fin 1303 45 What is a share price? A share price – or a stock price – is the amount it would cost to buy one share in a company. The price of a share is not fixed but fluctuates according to market conditions. It will likely increase if the company is perceived to be doing well or fall if the company isn’t meeting expectations. 46 What Is a Rate of Return (RoR)? A rate of return (RoR) is the net gain or loss of an investment over a specified time period, expressed as a percentage of the investment’s initial cost. When calculating the rate of return, you are determining the percentage change from the beginning of the period until the end. The effects of inflation are not taken into consideration in the simple rate of return calculation but are in the real rate of return calculation. The formula to calculate the rate of return (RoR) is: 47 Return Relative The return relative (RR) is also often used to calculate investment returns, which is simply the total end value of the investment plus income divided by the initial value of the investment. Hence, it is the return relative to the initial price. The formula for calculating return relative is : 48 Compound Annual Growth Rate What Is the Compound Annual Growth Rate? The Compound Annual Growth Rate (CAGR) measures the value of money in your investment over a long period (more than one year period). How Can You Calculate the Compound Annual Growth Rate? Calculating the CAGR is a little more complex than our previously mentioned methods. The formula goes as follows: 49 Total Return The Total Return Formula is a calculation used to measure an investment’s overall performance, considering both capital appreciation (or depreciation) and income generated by the asset. The formula for calculating total return is : Total Return = (Ending Value – Beginning Value + Dividends or Interest) / Beginning Value * 100. 50 Problem 7 The information below are relative to Oracle, Microsoft and Nvidia trading stocks: 1. Calculate the annual return and the annual return relative for each stock (percentage and 2 decimals) 2. Calculate the Total and the compound return for each stock (percentage and 2 decimals) 3. Calculate the average of return by using arithmetic mean and geometric mean for each stock (percentage and 2 decimals) 4. Calculate the minimum and the maximum returns and their corresponding dates. 5. Create a line chart showing Oracle, Microsoft and Nvidia closing prices. Enter a chart title and axis labels. 6. Create a column chart showing Oracle, Microsoft and Nvidia returns. Enter a chart title and axis labels. 51 52 53 Problem 8 Loan Amortization: Basics and Sensitivity Analysis Fin 1303 54 What is Loan Amortization?  Loan amortization is the process of gradually paying off a loan over a set period through regular payments.  Each payment consists of two parts: interest and principal.  Initially, a larger portion of the payment goes towards interest, but over time, more of the payment is applied to the principal balance.  This method ensures that the loan is fully paid off by the end of the term. 55 Problem 8 What is Loan Amortization?  The formula for calculating the periodic payment on an amortized loan is: 𝑖 𝑃𝑀𝑇 = 𝑃 1− 1+𝑖 PMT: is the total periodic payment. P: is the principal loan amount. i : is the periodic interest rate. n : is the number of payments. 56 Problem 8 Problem 8 You have borrowed 300,000 SAR from a bank for 30 years. The loan calls for fixed annual payments at the end of each year over the next 30 years. The annual interest rate on the loan is 8%. 1. What must the annual payment be? 2. Prepare the loan repayment schedule. 3. Set up the table using the sensitivity analysis to test the interest rate per year sensitivities for the interest component (consider 7% and 8% interest rates). 4. Set up the table using the sensitivity analysis to test the interest rate per year sensitivities for the principal component (consider 7% and 8% interest rates). 57 Problem 8  58 Problem 8  59 Problem 8 Problems 9 and 10 Valuation of Bonds And Stocks Fin 1303 60 Valuation of stocks and bonds Bond and stock valuation are crucial financial concepts that aid in calculating the securities' intrinsic values. Investors can evaluate the value of bonds and equities using valuation methodologies, which helps them make investment decisions. What Are Stocks? What Are Bonds? Stocks, or shares, are units of equity — or Bonds are simply loans made to an organization. ownership stake — in a company. The value of a They are a form of debt and appear as liabilities in company is the total value of all outstanding stock the organization’s balance sheet. While stocks are of the company. The price of a share is simply the usually offered only in for-profit corporations, any value of the company — also called market organization can issue bonds. Indeed, the capitalization, or market cap — divided by the governments of United States and Japan are among number of shares outstanding. Stocks of a company the largest issuers of bonds. Bonds are also traded are offered at the time of an IPO (Initial Public on exchanges but often have a lower volume of Offering) or later equity sales. transactions than stocks. 61 Stock valuation : Dividend Discount Model (DDM)? The dividend discount model (DDM) is a quantitative method used for predicting the price of a company's stock based on the theory that its present-day price is worth the sum of all of its future dividend payments when discounted back to their present value. It attempts to calculate the fair value of a stock irrespective of the prevailing market conditions and takes into consideration the dividend payout factors and the market expected returns. If the value obtained from the DDM is higher than the current trading price of shares, then the stock is undervalued and qualifies for a buy, and vice versa. 62  Stock valuation : Dividend Discount Model (DDM)? The Dividend Discount Model, also known as DDM, is in which stock price is calculated based on the probable dividends that one will pay. They will be discounted at the expected yearly rate. It is a way of valuing a company based on the theory that a stock is worth the discounted sum of all of its future dividend payments. In other words, it is used to evaluate stocks based on the net present value of future dividends. Dividend Discount Model = Intrinsic Value = Sum of Present Value of Dividends + Present Value of Stock Sale Price. 63  Stock valuation : Dividend Discount Model (DDM)? The Dividend Discount Model, also known as DDM, is in which stock price is calculated based on the probable dividends that one will pay. They will be discounted at the expected yearly rate. It is a way of valuing a company based on the theory that a stock is worth the discounted sum of all of its future dividend payments. In other words, it is used to evaluate stocks based on the net present value of future dividends. Dividend Discount Model = Intrinsic Value = Sum of Present Value of Dividends + Present Value of Stock Sale Price. 64 Stock valuation : Dividend Discount Model (DDM)? Zero-Growth Dividend Discount Model : This model assumes that all the dividends paid by the stock remain the same forever until infinite. Stock’s Intrinsic Value = Annual Dividends / Required Rate of Return Constant-Growth Rate DDM Model : The constant-growth dividend discount model or the Gordon growth model assumes dividends grow by a specific percentage each year. D1 = Value of dividend to be received next year D0 = Value of dividend received this year g = Growth rate of dividend K = Discount rate 65 Stock valuation : Dividend Discount Model (DDM)? Two-stage DDM : This model is designed to value the equity in a firm with two stages of growth, an initial period of higher growth and a subsequent period of stable growth. The two-stage dividend discount model is best suited for firms paying residual cash in dividends while having moderate growth. 5 steps to calculate fair value of a stock: Step1: Calculate the dividends for each year till the stable growth rate is reached Step2 : Apply the dividend discount model to calculate the terminal value (price at the end of the high growth phase) Step3 : Find the present value of all the projected dividends Step4 : Find the present value of the terminal value Step5 : Find the fair value – the PV of projected dividends and the PV of terminal value. 66 Problem 9 Consider a company that has paid a dividend of $4.45 this year—assuming a higher growth for the next 5 years at 10% and stable growth of 7% thereafter. If we assume that the leverage cost of equity capital is 12%, calculate the value of the stock using a two-stage dividend discount model. 67 68 Bond valuation Corporations sell bonds to borrow money from the investors. As a financial instrument, a bond represents a contractual agreement between the corporation and the bondholders. Eventually the corporation has to repay the principal to the investors and pay interest to them in the meantime. The present value of a bond is simply the present value of all future cash flows from the bond, discounted at the risk-adjusted discount rate. Bond value = PV of coupons + PV of lump sum Bond Value = CP (PVIFA kd, n) + MV (PVIF kd, n) 69 Problem 10 You intend to purchase an 8-year, 300 SAR par value bond that has an annual coupon rate of 10%. If the current market rate is 2.35%, what is the current bond price (use four different methods)? 70 71 Problem 11 Weighted Average Cost of Capital (WACC) Fin 1303 72 Weighted Average Cost of Capital A firm’s overall cost of capital must reflect the required return on the firm’s assets as a whole If a firm uses both debt and equity financing, the cost of capital must include the cost of each, weighted to proportion of each (debt and equity) in the firm’s capital structure This is called the Weighted Average Cost of Capital (WACC) 73 Weighted Average Cost of Capital In general, the minimum required rate of return must be a weighted average of the individual required rates of return on each form of capital provided. Therefore, we refer to this minimum required rate of return as the weighted average cost of capital (WACC). The WACC can be found as follows: WACC  wD  k D  wP  k P  wCS  kCS where w’s are the weights of each source of capital, and the k’s are the costs for each source of capital. 74 Cost of Equity The cost of equity is the return required by equity investors given the risk of the cash flows from the firm There are two common methods for computing the cost of equity Dividend growth model The Cost of Equity may be derived from the dividend growth model as follows: P = D / RE – g Where the price of a security equals its dividend (D) divided by its return on equity (RE) less its rate of growth (g). We can invert the variables to find RE as follows: RE = D / P + g 75 Cost of Equity SML or CAPM Many financial managers prefer the security market line/capital asset pricing model (SML or CAPM) for estimating the cost of equity: RE = Rf + βE x (RM – Rf) or Return on Equity = Risk free rate + (risk factor x risk premium) 76 Cost of Preferred Equity Preferred stock can be viewed as a special case of the common stock with the growth rate of dividends equal to zero. We can carry this idea to the process of solving for the preferred stockholders’ required rate of return. Therefore, Return on preferred equity can be calculated as follows : DP / VP. DP : dividend of preferred stock VP : value of preferred stock 77 Cost of Debt The cost of debt is the required return on our company’s debt The required return is best estimated by computing the yield- to-maturity on the existing debt We may also use estimates of current rates based on the bond rating we expect when we issue new debt The cost of debt is NOT the coupon rate. 78 Weighted Average Cost of Capital (WACC) WACC weights the cost of equity and the cost of debt by the percentage of each used in a firm’s capital structure WACC is calculated as follows: WACC=(E/ V) x RE + (D/ V) x RD x (1-TC) (E/V) = Equity % of total value (D/V) = Debt % of total value (1-Tc) = After-tax % or reciprocal of corporation tax rate Tc. The after- tax rate must be considered because interest on corporate debt is deductible 79  Problem 11 The managers of the Rocky Mountain Motors (RMM) are considering the purchase of a new tract of land which will be held for year. The purchase price of the land is $10,000. RMM’s capital structure is currently made up of 40% debt, 10% preferred stock, and 50% common equity. Because this capital structure is considered to be optimal, any new financing will be raised in the same proportions. RMM must raise the new funds as indicated in the following table:  The current balance sheet shows capital structure as following: Source of funds Amounts Dollar cost After-tax cost Source of capital Total Book Value Debt $ 4,000 $ 280 7% Long-term Debt $400,000 Preferred stock 1,000 100 10% Preferred Equity 100,000 Common stock 5,000 600 12% Common stock 500,000 Total 10,000 980 9.8% Total 1,000,000 1- Calculate the book-value weights for each source of capital. 2- Calculate the market-value weights for each source of capital. 3- Calculate the Weighted average costs of capital (WACC) using both Book and Market-Value Weights for RMM. 4- According to the information below, calculate the component costs of capital. Additional data for calculating the cost of capital structure from RMM Additional Bond data The Cost of Preferred Stock : dividend = $10 Tax Rate 40% and the price = $100. Coupon Rate 10% The cost of Common Stock : dividend = 3.96, Face Value $ 1,000 growth rate = 6% and price = $ 70. Maturity 10 81 82 83 84