Hi Erylee88,

I would like to utilize your service again. Same format different questions.

Please find attached document for your review. Let me know.

Thank you,

Sam

_____________________________________________________

Use Davis Products' 2015 and 2016 annual balance sheets and income statements data to calculate two years' ratios in the "Ratios" worksheet.

(1) Calculate liquidity ratios, efficiency ratios, leverage ratios, coverage ratios, profitability ratios, and Z-score;

(2) Design the Ratio Analysis Expert System:

We define "Good" if a 2016 financial ratio is better than the 2015 ratio AND the industry average in 2016;

"OK" if a 2016 financial ratio is better than the 2015 ratio OR the industry average in 2016;

"Unacceptable" for all other situations

(3) Compute the economic profit of the company.

(4) Analyze the ratio performance of the company and discuss your findings briefly.

Balance Sheet MS High Tech Balance Sheet Assets Cash and Equivalents Accounts Receivable Inventory Total Current Assets Plant & Equipment Accumulated Depreciation Net Fixed Assets Total Assets Liabilities and Owner's Equity Accounts Payable Short-term Notes Payable Other Current Liabilities Total Current Liabilities Long-term Debt Total Liabilities Common Stock Retained Earnings Total Shareholder's Equity Total Liabilities and Owner's Equity 2016 152.00 882.00 906.00 1,940.00 657.00 166.20 2015 137.60 731.20 815.20 1,684.00 591.00 146.20 490.80 444.80 2,430.80 2,128.80 185.20 255.00 220.00 660.20 524.61 1,184.81 880.00 365.99 1,245.99 2,430.80 155.60 230.00 216.00 601.60 423.43 1,025.03 880.00 223.77 1,103.77 2,128.80 Page 1 Income Statement Davis Products Income Statement 2016 Sales 5,050.00 Cost of Goods Sold 4,150.00 Gross Profit 900.00 Selling and G&A Expenses 350.30 Fixed Expenses 110.00 Depreciation Expense 20.00 EBIT 419.70 Interest Expense 79.00 Earnings Before Taxes 340.70 Taxes 95.40 Net Income 245.30 Notes: Tax Rate 28% 2015 4,432.00 3,664.00 768.00 270.00 110.00 18.90 369.10 65.50 303.60 85.01 218.59 28% Page 2 MS High Tech's Financial Ratios (2015~2016) Ratio 2016 2015 Industry 2016 Liquidity Ratios Current Ratio 2.70 x Quick Ratio 1.00 x Efficieny Ratios Inentory Turnover 7.00 x A/R Turnover 10.70 x Average Collection Period 33.64 days Fixed Assets Turnover 11.20 x Total Asset Turnover 2.60 x Leverage Ratios Total Debt Ratio 50.00% Long-term Debt Ratio 20.00% LTD to Total Capitalization 28.57% Debt to Equity 1.41 x LTD to Equity 40.00% Coverage Ratios Times Interest Earned 2.50 x Cash Coverage Ratio 2.80 x Profitability Ratios Gross Profit Margin 17.50% Operating Profit Margin 6.25% Net Profit Margin 3.50% Return on Total Assets 9.10% Return on Equity 18.20% Return on Common Equity 18.20% Du Pont ROE 18.20% Financial Distress Prediction Market Value of Equity 1,352,800 1,178,890 Z-score 2.68 Expert Analysis Tax rate NOPAT Total Operating Capital After-tax Cost of Capital Dollar Cost of Capital Economic Profit 2016 28% 2015 28% 10% 10% Chapter 4 Financial Statement Analysis Tools 1. Define liquidity, efficiency, leverage, coverage, and profitability ratios. 2. Calculate the common ratios by using income statement and balance sheet. 3. Use financial ratios to assess a firm's performance, problems, and propose strategies for dealing with problems. 4. Build the ratio analysis expert system. 5. Calculate the economic profit of a firm. 1 Purpose of Financial Ratio Analysis Internal Use: Evaluating the performance of managers; identify problems; Planning for the future; setting goals. External Use: To decide whether to grant credit or loan; to forecast financial performance; to decide whether to invest in the company; to monitor financial performance. 2 1. Liquidity Ratios Liquidity: Refers to the speed with which an asset can be converted into cash without large discounts to its value. Liquidity Ratios: Describe the ability of a firm to meet its current obligation. Larger is better 1.Current Ratio: = 2.Quick Ratio: = Current Assets Current Liabilities Current Assets - Inventories Current Liabilities 3 2. Efficiency Ratios Efficiency Ratios: Describe how well a firm is using its assets to generate sales. Also called asset management ratios. 1.Inventory Turnover Ratio = Cost of goods sold / Average Inventory 2. Accounts Receivable Turnover Ratio = Credit sales / Accounts Receivables Note: The larger the two ratios, the better. 4 Efficiency Ratios (Con.) 3. Average Collection period days sales outstanding, DSO): How many days a firm takes to collect on a credit sales. = Accounts Receivables / (Credit Sales / 360) = 360 / Accounts Receivable Turnover Ratio Note: the smaller the ratio, the better 4. Fixed Assets Turnover Ratio: Larger is better = Sales / Net Fixed Assets 5. Total Assets Turnover Ratio: Larger is better = Sales / Total Assets 5 3. Leverage Ratios Leverage Ratios: Reveal the degree to which debt has been used to finance a firm's asset purchases. Also called debt management ratios. Most firms use some debt because of the tax deductibility of interest. 1.Total Debt Ratio: = Total liabilities / Total assets = (Total assets - Total equity) / Total assets 2. Long-term Debt Ratio: = Long-term debt / Total assets 6 Leverage Ratios (Con.) 3. Long-term Debt to Total capitalization Ratio= LTD/(LTD + Preferred equity + Common equity) 4.Debt to Equity Ratio =Total liabilities / Total equity = Total debt ratio / (1 - Total debt ratio) Note: we can convert between the Total Debt ratio and the Debt to Equity ratio. 5. Long-term Debt to Equity Ratio: = LTD / (Preferred equity + Common equity) 7 4. Coverage ratios Coverage ratios: describe the quantity of funds available to cover certain interest expenses. 1. Times Interest Earned ratio: Larger is better = EBIT / Interest Expense 2. Cash Coverage ratio: Larger is better = EBIT + Non-Cash Expenses Interest Expense Non-cash expenses such as depreciation 8 5. Profitability Ratios Profitability ratios: Indicate how profitable a firm has been over a period of time. The larger the profitability ratios, the better. 1. Gross Profit Margin = Gross Profit / Sales Where Gross profit = Sales - Cost of goods sold 2. Operating Profit Margin: = Net Operating Income / Sales Note: Net Operating income is EBIT in the Income Statement 9 Profitability Ratios (con.) 3. Net profit Margin: the percentage of sales remains for the shareholder of the firm. = Net Income / Sales 4. Return on Assets = Net Income / Total assets 5. Return on Equity = Net Income / Total equity 6. Return on Common Equity: = Net income - Preferred dividends Common Equity 10 6. The Du Pont Analysis Purpose: Analyze what factors cause the changes of a firm's ROA and ROE. ROA = Net Income = Net Income Sales Total assets Sales Total assets = Profit margin Total asset turnover 11 The DuPont Analysis (con.) ROE = Net Income / Total Equity = Net Income Total assets Total assets Total Equity = ROA Equity multiplier = ROA 1 / ( 1- Total Debt ratio) 12 The DuPont Analysis (con.) ROE = Net Income / Total Equity = Net Income Total assets Total assets Total Equity = ROA Equity multiplier = ROA 1 / ( 1- Total Debt ratio) 13 Extended DuPont Analysis Method: Further break net profit margin into the following three components: (1)Interest Burden = EBT / EBIT (2) Tax Burden = Net Income / EBT (3) Operating Profit Margin = EBIT / Sales So Net Profit Margin = Operating profit margin x Interest Burden x Tax Burden 14 7. Financial Distress Prediction 1. In 1968, Edward Altman developed a Zscore Model to predict the potential financial distress in a firm. 2. The Original Z-Score Model: Z = 1.2X1 + 1.4X2 + 3.3X3 + 0.6X4 + X5 X1 = Net working capital / Total assets X2 = Retained earnings / Total assets X3 = EBIT / Total assets X4 = MV of equity / BV of total liabilities X5 = Sales / Total assets 15 Original Z-Score Model (con.) Cutoff points of original Z-score Model: Z 2.675 No financial distress predicted Problems with the original Z-score model: X4 requires a firm's market capitalization (including both preferred and common equity). It is not suitable to the privately held firms. 16 Z-Score Model for Private Firms The Z-score Model for private firms: Z = 1.2X1 + 1.4X2 + 3.3X3 + 0.6X4 + X5 Note: The only variable with different definition: X4 = BV of equity / BV of total liabilities Cutoff points: Z 2.90 No financial distress predicted 17 8. Analysis of Financial Ratios Trend Analysis: Examines the financial ratios over time to see whether these ratios move in the right directions. i.e., 201420132012 Comparison to Industry Average: Comparing to similar firms (competitors) in the same industry. The industry average ratios are published by Robert Morris Associates and Standard & Poor's. 18 9. Automating Ratio Analysis Expert System Goal: It can analyze each ratio separately and determine/report whether a ratio is either \"Good\CHAPTER 1 1-1 Introduction to Excel 2013 Contents: Spreadsheet Introduction Components of the Excel screen Navigating the Excel worksheet Excel's built-in and user-defined functions Creating Graphics Excel and other applications Build good spreadsheet models 1-2 1. Spreadsheet Introduction Spreadsheet is a tool consisting of a matrix of cells, which can store numbers, text, and formulas for quantitative analysis. Advantage: its ability to recalculate results automatically if the value of some cells in the model change, appropriate for financial analysis. Managers need to be proficient in the use of a spreadsheet. 1-3 History of Spreadsheet 1979: VisiCalc for Apple I 1982: Lotus 1-2-3 in IBM PC 1985: Microsoft introduced Excel for the Apple Macintosh 1987: Excel 2.0 version in Microsoft windows Excel 2002, 2003 versions in XP Excel 2007 version (Version 12.0) Excel 2010 version (Version 14.0) Excel 2013 version (Version 15.0) 1-4 Spreadsheet uses A spreadsheet contains built-in analytical capacities for relatively complex analysis, from financial analysis to multivariate regression analysis. Useful for quantitative analysis in finance, marketing, management, engineering, statistics, or economics Excel 2007 have changed the user interface to the new Ribbon interface. Excel 2010 and 2013 are evolutionary than revolutionary versions. 1-5 2. Parts of the Excel Screen The File Tab The Quick Access Toolbar The Home Tab The Formula bar The worksheet Area Sheet Tabs Status Bar 1-6 The File Tab The File Tab in Excel 2013 takes the place of the Office Button in Excel 2007. It contains additional functionality: i.e. It opens in Backstage view, which takes over the entire window. The File tab (near the bottom of the tabs) is the pathway to setting the program options. Two way to select a menu: click on the menu with mouse; Alt + underlined letter in the menu name. Light gray color: unavailable for use 1-7 Quick Access Toolbars The Quick Access Toolbar (also known as the QAT) is located above the File Tab. Contains the most frequently used buttons, such as undo, redo, new, open, save... Customize Quick Access Toolbar: you can add or remove certain commands. 1-8 The Home Tab The Home tab displays various toolbars that replace menus of previous versions. Contains the most commonly used commands, such as Cut, Copy, Paste, and various cell formatting buttons. Some buttons on the Ribbon has a downward-pointing arrow. Other tabs: Insert, Page Layout, Formulas, Data, Review, View, Add-Ins. 1-9 Formula Bar & Worksheet Area The Formula bar displays the formula while the cell displays the result of the formula. It is one of the most useful features in Excel. The Worksheet Area is a Matrix of cells: 16,384 columns 1,048,576 rows. - Each cell is referred to by a column letter and row number. i.e., A85, C12. It can contain text, numbers, formulas, or graphics. 1 - 10 Sheet Tabs & Status Bar Sheet Tabs: Excel workbook can contain multiple related worksheets into one file. Sheet tabs can switch between sheets in a workbook. We can rename, copy, insert, delete, and move the existing sheets. Group and ungroup multiple sheets: anything done to one sheet gets done to all Status Bar: located below the sheet tabs, contains information regarding the current status of Excel. 1 - 11 3. Navigating The Worksheet 1. Moving methods: the arrow keys; the mouse; or the Go To command if you know the name or address of the cell. 2. Selecting a range of cells (Continuous & discontinuous ranges). 3. A Named range is a cell, or group of cells, for which you have supplied a name (Formulas Define Name) 4. Enter text & numbers (AutoFill feature) 1 - 12 Navigating The Worksheet (Con.) 6. Formatting and Alignment: Typeface; type size (1 inch = 72 points); Format cells Alignment (Horizontal & Vertical). 7. Formatting numbers (Format Cells Number): 1000 separator; decimal; negative/positive numbers, etc. 8. Adding Borders and Shading: Format Cells Border tab; Format Cells Fill tab 1 - 13 4. Entering Formula 1. Mathematical Operators: Addition -Multiplication: Exponentiation + * ^ Subtraction Division / 2. Parentheses & the order of Operations: Operations enclosed in parentheses are always evaluated first Exponentiation ^ Multiplication (*) & Division (/) Addition (+) & Subtraction (-). 1 - 14 Entering Formula (Con.) 3. Entering Formulas: The equal sign \"=\" must precede all formulas in excel. 4. Copying and Moving formulas: Excel will update the cell address to maintain the relative relationships. 5. Absolute references: always refers to the same cell, i.e., $A$5. 6. Mixed references: Only the column ($B5) or the row (B$5) remains constant. 1 - 15 5. Excel's Building-in Functions 1. Excel has hundreds of built-in functions and over 50 financial functions (click the following link for the details): http://office.microsoft.com/en-us/excel-he lp/financial-functions-reference-HP0103425 19.aspx 2. Before you use any function, you should know what the formula is doing (i.e. NPV). 3. Use Insert Function (fx): Select a category Select a function. 4. Use Formulas tab. 1 - 16 User-defined Functions 1. Uder-defined Functions: Similar to the built-in functions, except it was created by someone other than the Excel development team at Microsoft. 2. Can be purchased, downloaded from internet, or be created by you. 3. We can use Excel's macro (VBA: Visual Basic for Applications) to write userdefined functions in later chapters. 4. Add-ins (i.e., Famefncs.xlam). 1 - 17 6. Creating Graphics 1. Creating charts: Highlight the data series right click the tab for current worksheet and choose Insert chart (three additional tabs: Design, Layout, and Format); or directly Insert (Column, Pie, Bar, Line...). 2. Creating embedded chart: Can be saved, displayed, and printed on the same page as the worksheet data. 3. Creating Sparkline Chart: Tiny charts that are intended to be presented as a \"word\" in a line of test or next to data in a table. 1 - 18 Creating Graphics (Con.) 3. Formatting Charts: Every element of a chart is treated as a separate 'object'. So each element can be selected and edited separately from other elements. 4. Major objects: data series; the plot area; the gridlines; the axes; the axis title; the chart title; and other text strings. 5. Changing Chart Type: right-click the blank area around the outside edge of the chart and choose Changing Chart Type from the menu (or the button on the Design tab). 1 - 19 7. Printing 1. Print: click the File tab choose Print. 2. Set printing area (Page Layout Print Area Set or Clear Print Area) 3. Change various options for printing by clicking Page Layout tab (margins, orientations, breaks, etc.) 1 - 20 8. Saving and Open files 1. Excel 2013 default file format: \"*.xlsx\" File Save As: you can supply a name, choose file format and location. File Save: save data into original file. File Open: open new files. 2. We can save workbooks as files with various different formats, such as Excel Macro-enabled workbook (*.xlsm), web page, text files, PDF files, etc. 1 - 21 9. Excel & Other Applications 1. Using Excel with other applications (i.e., Microsoft Word) can combine the strengths of both programs: Excel: Computational and graphics power Word: Text processing power 2. Directly Copy from Excel (the copied contents in clipboard are available to any other application), and then Paste the contents in other applications (i.e., Word) 1 - 22 Excel & Other Applications (Con.) 3. Use Paste Special instead of Paste: allows much more freedom in how the data is stored inside the Word processor (in Word 2010: Home Paste Paste Special). 4. If we choose to paste the data as a \"Microsoft Excel Worksheet Object\" in Word 2010, just double-click on the object, the interface of Word Processor will change to that of Excel, and we can edit the data exactly as if we are in Excel. 1 - 23 10. Spreadsheet Models 1. A good Spreadsheet model: functional, flexible, wellorganized, easy to use, good interface. 2. The rules for good spreadsheet design: (1) Create an area specifically for the variables (inputs) in the model. (2) Don't enter a number directly into a formula. (3) Well-organized and nicely-formatted. (4) Document long and complex formulas. (5) Test models thoroughly before declaring it finished. CHAPTER 2 1-1 The Basic Financial Statements Understand the income statement, the balance sheet, and the statement of cash flows. Construct three financial statements. Link worksheets together so that formulas in one sheet can reference data in another. Use Excel's Outliner tool to display/hide some parts of financial statements. 1-2 Introduction to financial statements Income Statement: summarizes the results of the firm's operations over a period of time (annually, quarterly, monthly...). Balance Sheet: describes the assets, liabilities, and equity of the firm at a specific point in time (i.e., 12/31/2014). Statement of Cash Flow: outlines the sources of the firm's cash inflows and shows where the cash outflows went over a period of time (annually, quarterly, monthly...). 1-3 General Principles in Excel Principle 1: Make Excel do as much of the work as possible. Whenever possible, a formula should be used rather than entering numbers. In the long run this will minimize errors. Principle 2: Format the worksheet so that it is easy to understand. Borders, shading, and font choices can make important numbers stand out and get the attention they deserve if they are properly chosen. 1-4 I. Income Statement Gross Profit: = Sales - Cost of goods sold Earnings Before Interest and Taxes (EBIT): = Gross profit - all other expenses (SG&A, depreciation, etc) other than interest and taxes. Earnings Before Tax (EBT): EBIT-interest Taxes: = EBT Tax Rate Net Income: EBT-Taxes=EBT(1-Tax Rate) Used for dividend payments and retained earnings. 1-5 Common-Size Financial Statements Display the data as percentage instead of dollar amount. Advantages: (1) Allow for easy comparisons between firms of different sizes. (2) Help to find important trends which cannot be obvious in dollar amounts. Income Statements: divided by Sales. Balance Sheet: divided by Total Assets. 1-6 Custom Number Formatting In most annual reports, numbers in financial statement are commonly displayed in thousands of dollars ($ 000's). Highlight numbers Format Cells Number choose Custom category choose type like \" #,##0.00,\". Excel will display the numbers as if they has been divided by 1000. if displayed as divided by 1,000,000: choose type like \" #,##0.00,,\". 1-7 $ in formula calculation The $ in the formula will freeze the reference to a specific address. (1) $C$5: freeze both row 5 and column C (2) C$5: Only freeze row 5, but the column reference will change if you copy the formula to the right or left (3) $C5: Only freeze column C, but the row reference will change if you copy the formula to the up or down. (4) C5: Both row and column can change. 1-8 II. The Balance Sheet Balance: Total Assets (TA) = Total Liabilities (TL)+ Total Owner's Equity (TE) Assets: tangible and intangible things a firm owns. Liabilities: a firm's unpaid items (accounts payable) and debts. Equity: the difference between what the firm owns and what it owes to others. Including common stocks, preferred stocks, and cumulative retained earnings. 1-9 Balance Sheet Items Total Assets = Current Assets (Cash & equivalents, Accounts receivables, Inventory...) + Net Fixed Assets (= fixed Assets - Depreciation). Total Liabilities = Current Liabilities (Accounts payable, Short-term notes payable...) + Long-term debt Total Equity = Common stocks + Preferred stocks + Cumulative retained earnings 1 - 10 III. The Statement of Cash Flows Outlines a firm's cash inflows (sources of funds) and cash outflows (uses of funds). Determining the cash balance change: Beginning Cash Balance + Cash inflows (sources) - Cash outflows (uses) = Ending Cash Balance 1 - 11 The Statement of cash flows Sections There are three sections in the statement: (1) Cash Flows from Operations: Net Income; Depreciation expense; Changes in current assets; Changes in Accounts Payable and other current liabilities.... (2) Cash Flow from Investing: changes in Fixed Assets.... (3) Cash Flow from Financing: Change in Short-term Notes Payables; Change in Long-term Debt; Cash Dividends.... 1 - 12 How to determine cash inflow/outflows The signs of numbers: Cash outflow (-); Cash inflows (+). Asset account cash outflows: cash inflows: + Order: Older numbers - Newer numbers Liability/Equity: cash inflows: cash outflows: + - Order: Newer numbers - Older numbers 1 - 13 Dividend Payments (1) Sign: Dividend payments have negative signs in the statement of cash flows (2) Dividends Paid = Net Income - Retained Earnings (3) Retained Earnings = Newer Cumulative Retained Earnings - Older Cumulative Retained Earnings (in Balance Sheet) 1 - 14 Example: How to link different sheets? For example, we want to get the \"Changes in Inventory\" value in the Statement of Cash Flows (B8 in the Statement of Cash Flows) using the numbers in Balance Sheet (Inventory 2014 in B7; Inventory 2013 in C7 in the balance sheet). Type \"=\" in B8 of the Statement of Cash Flows then switch to Balance Sheet, select C7 using mouse then type \"- (subtraction)\" then select B7 using mouse finally press Enter. In B8 of the Statement of Cash flows directly shows the formula: 'Balance Sheet'!C7 - 'Balance Sheet'!B7 then press enter. 1 - 15 IV. Using Excel's Outliner Purpose: Help organize a worksheet by considering the major items first and progressively moving to details. (1) Excel SOMETIMES can apply an outline automatically: Data Group Auto Outline. (2) Manually Outline: Highlight the lines which you want to group. Shift + Alt + Right Arrow (or Data Group Group) (3) Clear outline: Data Ungroup Clear Outline. 1 - 16 Common-size Statement of Cash Flows Denominators: (1) Sales; or (2) The beginning cash balance. Note: Use CHOOSE (...) function to switch. CHOOSE(INDEX, VALUE1, VALUE2,...) Where INDEX is a number between 1-254. VALUE: the items from which to choose. Using Data Validation to prevent input errors. CHAPTER 3: The Cash Budget 1-1 Calculate expected total cash collections and disbursements. Calculate expected ending cash balance and short-term borrowing (investing). Use Scenario manager to test assumptions Debugging formulas Work through the calculations 1-2 I. Cash Budget Cash budget is a listing of the firm's anticipated cash inflows and outflows over a specified period. The cash budget only includes actual cash flows. It is different from both income statement and the statement of cash flows. (i.e. depreciation, debt principal payments) Purpose: Cash budget is particularly useful for planning short-term borrowing (investing) and the timing of capital expenditures. 1-3 II. Three parts of Cash Budget 1. The worksheet area: expected sales; accounts receivable collections; payments for inventory purchases. 2. A listing of cash collections and disbursements. 3. Calculation of the ending cash balance and borrowing needs. 1-4 II-1. The Worksheet Area Accounts receivables collections: Some portion of sales are made on credit (40% on cash, 60% on credit). Among 60% of credit sales, 75% will be collected 1 month later; and 25% collected 2 months later. Inventory Purchases and payments: Inventory purchase will be a percentage of the expected sales (i.e., 50%); payments for inventory may be deferred to later months.( 60% paid 1 month later; 40% paid 2 months later) 1-5 Date Functions and Text Strings Using Date Functions DATE (YEAR, MONTH, DAY) YEAR (SERIAL_NUMBER) MONTH (SERIAL_NUMBER) DAY (SERIAL_NUMBER) Calculating Text Strings CONCATENATE (TEXT1, TEXT2, ...) TEXT (VALUE, FORMAT_TEXT) 1-6 II-2. Collections and Disbursements Cash inflows (collections): mainly from sales, current borrowing, and selling shortterm securities. Cash outflows (disbursements): include inventory payments; wages; lease payments; interest; dividend payments; taxes; and capital outlays. 1-7 II-3. Calculate Ending Cash Balance Beginning Cash Balance (1) + Total Collections - Total Disbursements = Unadjusted Cash Balance + Current Borrowing = Ending Cash Balance (2) Current Borrowing = Minimum acceptable cash-Unadjusted cash 1-8 III. Logical function: the IF statement Formula: IF( LOGICAL_TEST, VALUE_IF_TRUE, VALUE_IF_FALSE) LOGICAL_TEST is any statement which can be evaluated as being either true or false, then it decides the values of VALUE_IF_TRUE, VALUE_IF_FALSE For example: If the unadjusted cash balance is less than the minimum, then we borrow an amount equal to minimum cash - unadjusted cash. Otherwise, current borrowing is zero. =IF(E29-$B33 0 then Current Borrowing = -Minimum (Cumulative Borrowing (Investing), Unadjusted Cash - Minimum Cash) Else Current Borrowing = 0 End If 1 - 16 VI. Debugging Formulas (1) Using the F9 function key: shows the contents of a cell address or the results of calculation. (Note: In order to avoid locking in the changes you've made, press ESC key rather than ENTER. (2) Color-Coded Cell Address: Excel colors the cell address and highlights each those cells in the same color. (3) Formula Auditing Tools: (Formulas Formula Auditing), i.e., Tracing Precedent and Dependent Cells. 1 - 17 Debugging Formulas (Con.) (4) Background Error Checking: Excel examines common types of errors. To disable the function, click Office Excel Options uncheck Error Checking (5) The Watch Window: (Formulas Watch Window) Help to speed up formula debugging by watching a distant cell without having to scroll to it. (6) The Evaluate Formula Tool: (Formulas Evaluate Formula): works like F9 function, but it can step through the entire formula one step at a time. Several Build-in Functions 1 - 18 (1) MAX functions returns the largest of the supplied arguments. MAX ( NUMBER1, NUMBER2,...) (2) MIN functions returns the smallest of the supplied arguments. MIN ( NUMBER1, NUMBER2,...) (3) AND statement evaluate several conditions and then returns true only if all of the arguments are true. AND (LOGICAL1, LOGICAL2,...) 1 - 19 VII. Calculating Current Investing (1) If the sum of the unadjusted cash balance and current borrowing is less than the minimum required cash, the firm needs to sell some investments (2) If the sum of the unadjusted cash balance and current borrowing is greater than the maximum acceptable cash, the firm must invest the excess cash in the short-term securities. 1 - 20 Calculating Current Investing (Con.) If Unadjusted cash + Current Borrowing Maximum Cash then Current Investing = Unadjusted Cash + Current Borrowing - Maximum Cash Else Current Investing = 0 End If 1 - 21 VIII. Work Through the Calculations (1) Purpose: In order to understand the cash budget results, we must work through the results line-by-line to check the results. (2) You are encouraged to experiment by changing values throughout the cash budget to see what happens. For example: change projected sales; collection and payment schedules; timing of large capital expenditures. Chapter 4 Financial Statement Analysis Tools 1. Define liquidity, efficiency, leverage, coverage, and profitability ratios. 2. Calculate the common ratios by using income statement and balance sheet. 3. Use financial ratios to assess a firm's performance, problems, and propose strategies for dealing with problems. 4. Build the ratio analysis expert system. 5. Calculate the economic profit of a firm. 1 Purpose of Financial Ratio Analysis Internal Use: Evaluating the performance of managers; identify problems; Planning for the future; setting goals. External Use: To decide whether to grant credit or loan; to forecast financial performance; to decide whether to invest in the company; to monitor financial performance. 2 1. Liquidity Ratios Liquidity: Refers to the speed with which an asset can be converted into cash without large discounts to its value. Liquidity Ratios: Describe the ability of a firm to meet its current obligation. Larger is better 1.Current Ratio: = 2.Quick Ratio: = Current Assets Current Liabilities Current Assets - Inventories Current Liabilities 3 2. Efficiency Ratios Efficiency Ratios: Describe how well a firm is using its assets to generate sales. Also called asset management ratios. 1.Inventory Turnover Ratio = Cost of goods sold / Average Inventory 2. Accounts Receivable Turnover Ratio = Credit sales / Accounts Receivables Note: The larger the two ratios, the better. 4 Efficiency Ratios (Con.) 3. Average Collection period days sales outstanding, DSO): How many days a firm takes to collect on a credit sales. = Accounts Receivables / (Credit Sales / 360) = 360 / Accounts Receivable Turnover Ratio Note: the smaller the ratio, the better 4. Fixed Assets Turnover Ratio: Larger is better = Sales / Net Fixed Assets 5. Total Assets Turnover Ratio: Larger is better = Sales / Total Assets 5 3. Leverage Ratios Leverage Ratios: Reveal the degree to which debt has been used to finance a firm's asset purchases. Also called debt management ratios. Most firms use some debt because of the tax deductibility of interest. 1.Total Debt Ratio: = Total liabilities / Total assets = (Total assets - Total equity) / Total assets 2. Long-term Debt Ratio: = Long-term debt / Total assets 6 Leverage Ratios (Con.) 3. Long-term Debt to Total capitalization Ratio= LTD/(LTD + Preferred equity + Common equity) 4.Debt to Equity Ratio =Total liabilities / Total equity = Total debt ratio / (1 - Total debt ratio) Note: we can convert between the Total Debt ratio and the Debt to Equity ratio. 5. Long-term Debt to Equity Ratio: = LTD / (Preferred equity + Common equity) 7 4. Coverage ratios Coverage ratios: describe the quantity of funds available to cover certain interest expenses. 1. Times Interest Earned ratio: Larger is better = EBIT / Interest Expense 2. Cash Coverage ratio: Larger is better = EBIT + Non-Cash Expenses Interest Expense Non-cash expenses such as depreciation 8 5. Profitability Ratios Profitability ratios: Indicate how profitable a firm has been over a period of time. The larger the profitability ratios, the better. 1. Gross Profit Margin = Gross Profit / Sales Where Gross profit = Sales - Cost of goods sold 2. Operating Profit Margin: = Net Operating Income / Sales Note: Net Operating income is EBIT in the Income Statement 9 Profitability Ratios (con.) 3. Net profit Margin: the percentage of sales remains for the shareholder of the firm. = Net Income / Sales 4. Return on Assets = Net Income / Total assets 5. Return on Equity = Net Income / Total equity 6. Return on Common Equity: = Net income - Preferred dividends Common Equity 10 6. The Du Pont Analysis Purpose: Analyze what factors cause the changes of a firm's ROA and ROE. ROA = Net Income = Net Income Sales Total assets Sales Total assets = Profit margin Total asset turnover 11 The DuPont Analysis (con.) ROE = Net Income / Total Equity = Net Income Total assets Total assets Total Equity = ROA Equity multiplier = ROA 1 / ( 1- Total Debt ratio) 12 The DuPont Analysis (con.) ROE = Net Income / Total Equity = Net Income Total assets Total assets Total Equity = ROA Equity multiplier = ROA 1 / ( 1- Total Debt ratio) 13 Extended DuPont Analysis Method: Further break net profit margin into the following three components: (1)Interest Burden = EBT / EBIT (2) Tax Burden = Net Income / EBT (3) Operating Profit Margin = EBIT / Sales So Net Profit Margin = Operating profit margin x Interest Burden x Tax Burden 14 7. Financial Distress Prediction 1. In 1968, Edward Altman developed a Zscore Model to predict the potential financial distress in a firm. 2. The Original Z-Score Model: Z = 1.2X1 + 1.4X2 + 3.3X3 + 0.6X4 + X5 X1 = Net working capital / Total assets X2 = Retained earnings / Total assets X3 = EBIT / Total assets X4 = MV of equity / BV of total liabilities X5 = Sales / Total assets 15 Original Z-Score Model (con.) Cutoff points of original Z-score Model: Z 2.675 No financial distress predicted Problems with the original Z-score model: X4 requires a firm's market capitalization (including both preferred and common equity). It is not suitable to the privately held firms. 16 Z-Score Model for Private Firms The Z-score Model for private firms: Z = 1.2X1 + 1.4X2 + 3.3X3 + 0.6X4 + X5 Note: The only variable with different definition: X4 = BV of equity / BV of total liabilities Cutoff points: Z 2.90 No financial distress predicted 17 8. Analysis of Financial Ratios Trend Analysis: Examines the financial ratios over time to see whether these ratios move in the right directions. i.e., 201420132012 Comparison to Industry Average: Comparing to similar firms (competitors) in the same industry. The industry average ratios are published by Robert Morris Associates and Standard & Poor's. 18 9. Automating Ratio Analysis Expert System Goal: It can analyze each ratio separately and determine/report whether a ratio is either \"Good\Chapter 5 Financial Forecasting 1. Use the \"percent of sales\" method to develop financial statements. 2. Use the Trend function for forecasting sales or any other trending variables. 3. Perform a regression analysis with Excel's built-in regression tools. 1 1. The Percent of Sales Method Purpose: Forecasting financial statements are planning for the future and providing information to the company's investors. Why the Percent of Sales methods? Many (but not all) income statements and balance sheet items maintain a constant relationship to the level of sales. For example: cost of goods sold may have a constant relationship with sales: 65%. 2 1.1 Forecasting the Income Statement (1) Cost of goods sold: related to sales (2) SG&A Expense: a conglomeration of many accounts. Some of which will change with sales, but other items don't. (3) Depreciation Expense: depends on the amount and age of the firm's fixed assets. (4) Interest Expense: a function of the amount and maturity structure of debt. 3 1.2 Forecasting Assets on Balance Sheet Difference: we cannot use common-size balance sheet because it is based on total assets rather than total sales. Cash balance: Not related to sales. Why? Accounts Receivable: proportional to sales Inventory: proportional to sales Gross plant and equipment: Historical purchase price, not directly related to sales. Accumulated depreciation expense: = previous year's accumulated depreciation + this year's depreciation expense. 4 Forecasting Liabilities on Balance Sheet Types of financing resources: Current liabilities; long-term liabilities; and Owner's equity. We divide them into two categories: (1) Spontaneous sources of financing: the sources of financing that arise during the ordinary course of doing business. Vary directly with sales. i.e., accounts payable. How about other current liabilities? (2) Discretionary sources of financing: bank loan, bonds, common and preferred stocks. Having no direct relationship to sales. 5 Forecasting Liabilities (Con.) Accounts payable: change directly with sales S-T Notes payable: discretionary financing Other current liabilities: accrued expenses, related to sales. Long-term debt: discretionary financing Common stock: discretionary financing Retained earnings: Accumulated item. = Previous retained earnings + this year's Retained earnings (= Net income- dividends) 6 Discretionary Financing Needed Problems: the pro-forma balance sheet does not balance. Discretionary financing needed: is the difference between total assets and total liabilities. It is the amount of discretionary financing that the firm should raise in the next year (financial forecasting Purpose) We should judge whether the item discretionary financing needed is positive or negative using IF statement. 7 Using Iteration to eliminate DFN Iteration: if DFN 0, we should change balance sheet accordingly. But this can't be done at one time. We have repeat many times until DFN = 0. i.e., if DFN = $38.12 and we seek longterm debt for the DFN deficit. Simply adding $38.12 to the long-term debt will lead to other changes: higher interest payments lower net income lower retained earnings DFN 0 again. 8 Using Iteration to eliminate DFN Circular errors: Excel will report circular errors when a formula refers to itself, either directly or indirectly through another formula. Enable iteration: File Options Formulas Enable iterative calculations. Default Maximum iteration: 100 Maximum Change: 0.001 9 II. Linear Trend Extrapolation Method: to examine whether there has a trend in some items (i.e. sales) over time using historical data, then predict future values. (1) Crate a chart which plots the sales data VS. the years. (Chart Wizard) (2) Use TREND function: use Excel's built-in function to determine a linear trend 10 TREND Function TREND (KNOWN_Y'S, KNOW_X'S, NEW_X'S, CONST) KNOWN_Y'S: The dependent variables. The range of data we wish to forecast. KNOWN_X'S: The optional range of data ( the independent variable) we want to use to determine the trend in the dependent variable. If we forecast a time-based trend, KNOW_Y's will be a range of years NEW_X'S: the continuation of the KNOWN_X'S for which we don't yet know the value of the dependent variables. CONST: is a True/False variable to tell Excel whether or not to include an intercept in the calculations. 11 TREND Function (Con.) Trendline: Excel can plot a trend line to the chart. (Select the plot line and click on it with the right mouse button. Click on Add Trendline). Excel can generates the equation for the trend line and uses this equation to make the forecast. 12 EPI Sales 2005~2009 with Trend 6,000,000 5,000,000 Sale s 4,000,000 f(x) = 525244.6x - 1054067869.2 3,000,000 2,000,000 1,000,000 0 2009 2010 2011 2012 2013 2014 2015 Ye ar 13 III Regression Analysis Regression Analysis: (1) a statistical technique for fitting the best line to a data set. (2) a powerful tool for determining the relationship between variables and for forecasting. (3) The best line is defined as the one that minimizes the sum of the squared errors. The errors are the differences between the actual data and the predicted value. 14 Regression Analysis (Con.) Example: If there is a constant relationship between sales and cost of goods sold, we can construct a a theoretical linear model, in which the cost of goods sold is the dependent variable (Y), and sales is the independent variable: Cost of goods sold = + (sales) + : is the intercept : is the slope of the line : is the random error term 15 How to Enable Analysis Toolpak? Click File Options Add-Ins. If Analysis ToolPak is under \" Active Application Add-ins\Chapter 6 Break-Even and Leverage Analysis 1. Fixed costs and variable costs 2. Calculate various break-even points. 3. Business risk and financial risk. 4. Use Excel to calculate the Degree of Operating Leverage (DOL), the Degree of Financial leverage (DFL), and the Degree of Combined Leverage (DCL). 5. How do sales changes affect DOL, DFL, and DCL? 1. Fixed and Variable Costs Variable costs: The costs are expected to change at the same rate as the firm's sales. It includes sales commissions, costs of raw materials, and hourly wages. (e.g., variable costs per unit = 60% of unit sales price). Fixed costs: the costs are constant regardless of the product quantity produced. Such costs as rent, salaries, depreciations, etc. 2. Break-even Points 1.Definition: the break-even point is defined as the level of sales that cause profits (several measures) to equal zero. 2.Operating Break-even Point: The unit sales required for the Earnings before Interest and Taxes (EBIT) to be equal to zero. EBIT = Q (P - V) - F Q = Unit quantity sold; V = Unit variable cost; P = Unit price; F = Fixed costs Break-even Points (Con.) 3. When we assume EBIT = 0, we can get the Break-even quantity: Q* = F / (P - V) Note: P- V is the contribution margin per unit. Example: A firm is selling a product at $30 per unit. The variable costs are $20 per unit and fixed costs are $150,000. What's the break-even quantity? Break-even Points (Con.) 4. What's the Break-even point in dollars? $BE = P Q* Q* = the break-even point in units. $BE = P F / (P-V) = F / [ (P-V) / P) ] = F / CM% CM% = (P-V) / P is the contribution margin as a percentage of the selling price Other break-even Points 1. Target break-even point: The units sold to obtain a target EBIT. Q*Target = (F + EBITtarget) / (P - V) Example: If target EBIT = $500,000, what's the target break-even points? 2. Cash break-even point: take the non-cash expenses (depreciation) into consideration. Q*Cash = (F - Depreciation) / (P - V) 3. Business Risk & DOL 1. Business risk: is defined as the variability of EBIT relative to change s in a firm's sales. We use operating leverage to measure this risk. 2.Operating leverage: is defined as the percentage change in EBIT to a given percentage change in sales. It is the Degree of Operating Leverage (DOL): DOL = % in EBIT % in Sales Degree of Operating Leverage Example: If sales increased by 10% while EBIT increased by 20%. DOL = 20% / 10% = 2.00 Explanations: leverage is a double-edged sword. A high DOL is desirable as sales increasing, but undesirable when sale are decreasing. For example: If sales decline by 10%, EBIT declines by 20%. Another way to calculate DOL Degree of Operating Leverage (DOL): DOL = (Sales - Variable Costs) / EBIT = Q (P - V) Q(P-V) - F Note: If calculating DOL in this way, you don't need to use two income statements. 4. Financial Risk Financial Risk: refers to the probability that a firm cannot meet its fixed financing obligations, such as interest and preferred dividends. Financial risk is determined by management: Because the amount of debt is determined by managerial choices. All other things being equal, the more debt a firm uses to finance its assets, the higher the interest costs will be, then the higher the financial risk. The Degree of Financial Risk The Degree of financial risk (DFL) is used to measure financial risk. It is defined as the percentage changes of Earnings Per Share relative to the percentage changes of EBIT. DFL = % in EPS % in EBIT DFL works both ways. When EBIT is increasing, EPS will increase even more. When EBIT declines EPS will decline more. Another way to calculate DFL Degree of Financial Leverage (DFL): DFL= EBIT EBT- PD/(1-t) PD = preferred dividends paid by the firm. t = tax rate paid by the firm PD/(1-t) = the amount of pre-tax dollars required to pay after-tax preferred dividends. 5. Degree of Combined Leverage The Degree of Combined Leverage (DCL): Most firms have both operating leverage and financial leverage. The combined effects of these two leverage is called combined leverage. DCL is defined as the percentage changes in earnings per share relative to the percentage changes of sales. DCL = % in EPS % in Sales Decompose DCL Decompose DCL into DOL and DFL. DCL = % in EPS % in Sales = % in EBIT % in EPS % in Sales % in EBIT = DOL DFL Leverage and Sales 1. If we assume the fixed costs remain constant, the amount of leverage (measured by DOL, DFL, or DCL) declines as the sales level increases. 2. In real world, fixed costs are not necessarily the same year after year. 3. Variable costs do not always maintain an exact percentage of sales over time. Leverage and Sales Linking Break-even Points and Leverage Measures Note: Connection between the break-even points and various measures of leverages. Degree of Operating Leverage (DOL): DOL = Q (P - V) Q(P-V) - F = Q_____ Q - Q*operating Linking Break-even Points and Leverage Measures Degree of Financial Leverage (DFL): DFL = EBIT EBT- PD/(1-t) = Q - Q*operating__ Q - Q*total Degree of Combined Leverage (DCL): DCL = DOL x DFL = Q ___ Q - Q*total Chapter 7 The Time Value of Money 1. 2. 3. 4. The concept of time value of money Calculate the PV and FV of cash flows PV and FV of annuities. Solve for the annual payment, # of periods, and interest rate. 5. Uneven cash flow streams. 6. Non-annual compounding periods. 1 1. Future Value Compounding: Finding future value of financial assets is called compounding. Earning interest on previously earned interest. At the end of 1st year: FV = PV ( 1+ i ) 2 At the end of 2nd year: FV = PV ( 1+ i ) .... N At the end of Nth year: FVN = PV ( 1+ i ) FV includes principal; interests earned on principal; and interests earned on interests. 2 Use Excel to find FV Use Excel's Build-in function FV: FV (RATE, NPER, PMT, PV, TYPE) RATE = the interest rate per period NPER = total number of periods PV = Present value PMT = Annuity TYPE = tell Excel whether the cash flows occur at the end or at the beginning of the period. 3 2. Present Value Discounting: finding present value of financial assets is called discounting. N PV = FV / (1+i) Use Excel's Build-in function PV: PV (RATE, NPER, PMT, FV, TYPE) Note: the parameters to the PV function are exactly the same as those for the FV function. 4 3. Annuities Definition: Annuities are a series of nominally equal cash flows, equally spaced in time. There are two types of annuities: Regular Annuity (Ordinary Annuity): the payments occur at the end of each period. (Mortgage, car loans, student loans) Annuity Due: the payments are made at the beginning of each periods. (rent, car insurance premium). 5 Regular Annuity & Annuity Due Ordinary Annuity 0 i% 1 2 3 PMT PMT PMT 1 2 3 PMT PMT Annuity Due 0 i% PMT PV FV 6 Relationship of Annuity Due vs. Regular Annuity PV of annuity due: = (PV of ordinary annuity) (1+i) FV of annuity due: = (FV of ordinary annuity) (1+i) 7 4. PV of an Annuity Principle of Value Additivity: the value of a stream of cash flows is equal to the sum of the values of the components. Two ways to find PV of an annuity: (1) Find the present value of each of the cash flows separately and add them together. (2) Use the closed form financial formula: N PVA = Pmt [ 1 - 1/(1+i) ] i 8 Use Excel to solve PV of Annuities Use the following Excel Build-in function: PV (RATE, NPER, PMT, FV, TYPE) Where: PMT = the dollar amount of the periodic payment. Type: = optional binary variable (0 or 1) which controls Excel to assume the payment occurs at the end of each period (Regular Annuity) or at the beginning of each period (Annuity Due). 9 5. FV of Annuities Two ways to find FV of an annuity: (1) Find the future value of each of the cash flows separately and add them together. (1) Use the closed form financial formula: N FVA = Pmt [(1+i) - 1] i 10 Use Excel to solve FV of Annuities Use the following Excel Build-in function: FV (RATE, NPER, PMT, PV, TYPE) Where: PMT = dollar amount of the periodic payment. Type = optional binary variable (0 or 1) which controls Excel to assumes the payment occurs at the end of each period (Regular Annuity) or at the beginning of each period (Annuity Due). 11 6. Solving for the Annuity Payment Purpose: solve for the payment you would have to deposit each year to accumulate a given sum after a number of years. Build-in function: PMT(RATE, NPER, PV, FV, TYPE) The parameters for the PMT function are similar to those for the PV and FV functions, 12 Solving for the # of Periods in an Annuity Purpose: solve for the # of periods during which you have to deposit an equal amount of money at each period to accumulate a given sum in the future. Build-in Function: NPER(RATE, PMT, PV, FV, TYPE) Note: Where all of the parameters are as previously defined. Both the PV and PMT parameters are made negative (cash outflows) in this function. 13 Solving for the Interest Rate in an Annuity Purpose: solve for the interest rate if you know the number of period, the annuity, and the accumulated sum in the future. Trial and Error Approach: There is no closedform solution for the rate of interest of an annuity. The only way to solve this problem is to use Trial-and-Error approach. Process: Guess an interest rate first calculate PV, adjust interest rate Recalculate PV...... until we find an rate making the calculated PV is close to the actual PV. 14 Solving for the Interest Rate in an Annuity Purpose: solve for the interest rate based on which you have to deposit an equal amount of money at each period to accumulate a given sum over a given period. Build-in Function: RATE (NPER, PMT, PV, FV, TYPE, Guess) Note: Where all of the parameters are as previously defined. Guess is the optional first guess at the correct answer. 15 7. Other Annuities Deferred Annuities: the annuities don't occur in the first several years. For example, the retirement annuities. Graduated Annuities: in which the cash flows are not all the same, instead they are growing (or decreasing) at a constant rate. For example, the retirees who are concerned about inflation can receive a series of payments that increase over time to maintain constant purchasing power. 16 PV of A Graduated Annuities The present value of a graduated annuity: PVGA Pmt1 1 g 1 i g 1 i N Where: Pmt1 = the first cash flow i = the interest rate g = the growth rate of the cash flows N = the number of cash flows 17 FV of A Graduated Annuities The future value of a graduated annuity: FVGA Pmt1 (1 i ) i g N 1 g 1 1 i N Where: Pmt1 = the first cash flow i = the interest rate g = the growth rate of the cash flows N = the number of cash flows 18 8. Uneven Cash Flow Streams Uneven cash flow streams: the cash flows are different in each period. Notes: we cannot use FV or PV functions because they assume equal (annuity) payments or a lump sum. We use NPV function, which are widely used for the actual corporate capital budgeting. 19 NPV Function Build-in Function for uneven cash flow: NPV (RATE, VALUE1, VALUE2,...) Where: RATE = the per period rate of return (i.e., discounted rate). VALUE1 = the first cash flow. VALUE2 = the second cash flow. Note: Excel can accept up to 255 cash flows in the list. 20 Solving for the Yield for Uneven cash Flow Stream To solve for the yield of an investment given its price and cash flows using IRR function: IRR(VALUES, GUESS) VALUES = a range of cash flows ( including the cost). GUESS = the optional guess at the correct interest rate because Excel is using Trialand-Error approach to solve for the rate. Sometimes Excel will not be able to get a solution without a GUESS being specified. 21 Several Issues Regarding IRR A few situations may cause an error when using the IRR function: (1) Excel may not get a IRR solution. If so, change the GUESS value. (2) If there is no negative cash flows at the beginning. (3) Multiple IRR values. It occurs when using non-normal cash flow streams. Normal Cash Flows: negative Cash Flows followed by a series of positive cash inflows. One change of signs. 22 Normal & Non-Normal Cash Flows 0 1 2 3 4 5 - + + + + + - + + + + - - - - + + + N + + + - - - N - + + - + - N NN NN 23 9. Non-Annual Compounding Periods Why? Some investments make payments semiannually, monthly, or daily. i.e., Bond. Nm FVN = PV ( 1 + i / m) Where: i = annual rate, N = number of years, m = the # of compounding periods per year 24 Continuous Compounding Continuous Compounding: is the type of compounding when the number of compounding periods per year is a infinity. The continuous compounding rate is: iN FVN = PV * e Note: Note Excel does not offer functions to solve for the PV and FV when compounding is continuous. But we can use the following EXP function: EXP(NUMBER) 25