Excel Shortcuts & Financial Analysis Guide

Posted on May 7, 2024 in Economy

Excel Shortcuts

Navigation and Selection

  • Edit Cell/Highlight Precedents: F2
  • Go To: F5 or Ctrl + G
  • Go To Constants: F5, Alt + SOX
  • Go To End (Range Cont.): Ctrl + Arrow Keys
  • Go To Precedent Cell/Return: Ctrl + [ / F5, Enter
  • Highlight a Contiguous Range: Ctrl + Shift + Arrow Keys
  • Highlight Entire Column/Row: Ctrl + Spacebar / Shift + Spacebar
  • Deselect to the Original Cell: Shift/Backspace

Formatting and Data Entry

  • Anchoring Cells: F4 (Edit Cell: F2)
  • AutoSum: Alt + =
  • Borders – Outside: Area, Ctrl + Shift + 7
  • Borders – Remove All: Ctrl + Underscore
  • Clear: Alt + HE
  • Comment – Inserting: Shift + F2 or Alt + RC
  • Comment – Deleting: Alt + RD
  • Copy/Cut: Ctrl + C / Ctrl + X
  • Paste: Ctrl + V
  • Copy, Paste Special Format: Ctrl + C, Alt + HVST
  • Copy, Paste Special Formulas: Ctrl + C, Alt + HVF
  • Delete/Insert: Ctrl + – / Ctrl + +
  • Fill Right/Fill Down: Ctrl + R / Ctrl + D
  • Change Font Size: Alt + HFS
  • Format Cells: Ctrl + 1 or Alt + HOE
  • Function – Inserting: MF Alt or Shift + F3
  • Repeat: F4

Auditing and Review

  • Auditing Toolbar: Alt + M
  • Trace Precedents: Alt + MP
  • Trace Dependents: Alt + MD
  • Remove Arrows: Alt + MAA

Worksheet and Workbook Management

  • Move a Sheet/Copy a Sheet: HOM Alt, Alt + C
  • Name a Cell: Alt + MMD
  • Page Break Preview: Alt + WI / Alt + WL
  • Page Setup: Alt + PSP
  • Print: Ctrl + P
  • Print Preview: Alt + FPV
  • September Print Area: Alt + PRS
  • Rename Sheet: Alt + HOR
  • Tools Options: Alt + FT
  • Workbooks Toggle: Ctrl + Tab
  • Worksheets – Move Between: Ctrl + Page Up

Other

  • Redo: Ctrl + Y
  • Replace: Ctrl + H
  • Undo: Ctrl + Z
  • Zoom – Sizing/Return to 100%: Alt + WQ / Alt + W

Financial Analysis Guide

Key Ratios

  • AR Turnover (T/O): Sales / AR
  • AR Days (Per Collection): Days / (Sales / AR)
  • Inv T/O: COGS / Inv
  • Inv Days (Holding Per.): Days / (COGS / Inv)
  • AP T/O: COGS / AP
  • AP Days (Avg Per Settlement): Days / (COGS / AP)
  • Conv Cash Cycle: AR Days + Inv Days – AP Days
  • Net Fixed Asset T/O: Sales / Total Assets

Financial Modeling and Valuation

Strategies to Maximize Shareholder Value

  • Operating
  • Financing
  • Tax
  • Communication

Valuation Methods

  • Equity Value: $ x Diluted Equity Shares (Market Cap, Market Value)
  • Enterprise Equity Value: Equity Value + Net Debt + Preferred + Non-Controlling Interest (Firm Value, Overall Enterprise Value, Aggregate Value)
  • Enterprise Value: PV of FCF + PV of Terminal Value
  • Net Debt: Total Debt – Minority Int. + Pref Stock + Capitalize Leases + Short Term Debt – Cash & Cash Equivalents

Strategic Analysis

  • Risk in business & industry, competition, and end state.
  • Analyze competitors and operations: What they make, where they make it, lowest selling price, how they make it, how they move it.
  • Financial analysis: Size, market cap, revenue, growth.
  • Criteria for analysis: Profitability/margins, capital structure/leverage, management experience, shareholder base.

Ratio Analysis

  • Profitability + Growth = RRR for Investor (ROI)
  • Liquidity = Pay Int / Debt
  • Activity = Make More Efficient (How assets generate cash used)
  • Leverage + vs Debt Coverage = Cash Flow (Leverage = End risk of using debt vs equity + debt vs cash flow, Coverage = Ability to pay fixed payments)

Discounted Cash Flow (DCF)

  • Intrinsic (theoretical) value based on projected FCF.
  • Flexible analysis, requires scrutiny, challenges.
  • Possible bias, assumptions, sensitive to changes in growth margins, terminal value, discount rate.
  • Steps: DCF WACC, Forecast FCF, NPV FCF, Terminal Value of implied range
  • SP = Added value due to risk
  • FCF Forecast: Research/management, sensitivity analysis, own opinion. Life Cycle 5-10 years. CF approach steady state by terminal year. ROIC WACC starts to approach.
  • Numerator = Net operating profit after tax.
  • Life Cycle Theory + ROIC < WACC (Intro), ROIC and WACC passes goes up (growth), plateau (maturity), cash cow profitability to decline (decline).
  • DCF Steps: Line items, historical numbers, historical ratios, Forecast Unlevered FCFs = FCF + PV of terminal value – Debt + Equity Cash – value / shares = Intrinsic value.
  • Line Items: Sales, COGS (not dep.), Gross Profit, SG&A, EBITDA Less: Dep, Less. Amort, EBIT, TAXES, Tax EBIT, Plus Dep & Amort, Less. Capex Less: Addition intangibles to, change in working capital (less if increase). Unlevered free cash flow. Use last year of unlevered FCF in terminal value formula.

Working Capital Schedule

  • Sales, COGS, A/R, Inv, Other Current Assets, Total Non-Cash Current Assets, A/P, Accrued Liabilities, Other Current Liabilities, Total Non-Debt Current Liabilities, NWC, Increase in NWC, Calculate Cash Conversion Cycle

Capital Expenditure (CapEx)

  • Sales, CAPEX.
  • Depreciation on Existing PP&E: Existing PP&E (PP&E – Accumulated Depreciation from 10K) Less: Land, Depreciable PP&E (Useful Life, Depreciation Expense Projections [Sum-of-Years Digits]), (Depreciation on New CapEx. Year, CapEx (from previous projection), Useful Life (flatline), from new CapEx (that year / useful life / 2 [midyear convention] use as first year value for that column) [Original flatlined use CapEx that value from row for rest of years]). Total Depreciation Expense (Add from Existing PP&E & from new PP&E to get projections]. Then change useful life of Existing PP&E to make sure first projected year Dep Exp is less than last current year. Then change first projected year’s depreciation on new CapEx useful life total projected to make last depreciation expense as close to 100 as possible.

Additional Formulas and Concepts

  • EBITDA = Cash Flow (not working cap, Capex, Int. + tax)
  • COGS = No Depreciation Clean, Clean SG&A = Not Amortization, Add after EBITDA
  • Normalize: Take operating income and delete non-recurring expenses (restructuring charges, gains/losses on sale of divisions, asset impairment charge, legal settlements) remove Inc. – Less EBIT steps: obvious items in Income Statement, Selected Data order (item 6 – footnotes), Footnotes for all items in Income Statement, MD&A. Keep if part of the normal business financing and is not
  • Principles of Order Model = Consistency, Efficiency, Clarity

Additional Notes

  • This guide provides a basic overview of Excel shortcuts and financial analysis concepts.
  • It is important to consult with a qualified financial professional for specific advice.