Spreadsheet: 787702d1657972733-how-to-solve-cash-on-hand-based-on-days-held-scraped-information
Sheet: S32

Ticker
Market [NAME] |  | https://www.intelligentinvestor.com.au/shares/asx-
10 Year bonds |  | /westpac-banking-corporation/financials
 |  | /commonwealth-bank-of-australia/dividends?page=1&size=100
Capital Structure
Historical Financials |  |  |  |  | Balance Sheet
Cashflow
Market and Earnings
Stock Valuation
Ratios |  |  |  |  |  | EPS |  | P/E
BVPS OE/shares |  |  |  |  | Share Price | 2020 | 2021 | 2018E | Growth | PEG
PE
PB ratio
PEG
 |  |  |  | Dividend [NAME] - mainly for financial stocks and other dividend stocks (or if commodities have affected mining stock value and it is an established mining company)
Assumptions |  |  |  | Price For Financial Companies |  |  | Rate of Return |  |  |  | Dividend growth
Tax Rate |  | 0.3 |  | Yield |  |  | Target Price Upside |  |  |  | Beginning Value
Discount Rate |  |  |  | Dividend |  |  |  |  |  |  | Ending Value
Perpetual Growth Rate |  | 0.03 |  | Expected Return |  |  | Market Value vs Intrinsic Value |  |  |  | Number of periods
EV/EBITDA Multiple |  |  |  | Div Growth |  |  | Market Value |  |  |  | The CAGR
Transaction Date |  |  |  |  |  |  | Upside
Fiscal Year End |  | 2022-06-30 00:00:00 |  |  |  |  | Intrinsic Value
Current Price |  |  |  |  |  |  | Buy/Sell
Shares Outstanding
Debt
Cash
CAPEX
Discounted Cash Flow Model - for valuation on all other stocks
Discounted Cash Flow |  | Entry | 2023 | 2024 | 2025 | 2026 | 2027 | Exit |  | Terminal Value
Date |  |  |  |  |  |  |  |  |  | Perpetual Growth
Time Periods |  |  | 1 |  |  |  |  |  |  | EV/EBITDA
Year Fraction |  |  |  |  |  |  |  |  |  | Average
EBIT
Less: Cash Taxes
Plus: D&A
Less: Capex
Less: Changes in NWC
Unlevered FCF
(Entry)/Exit
Transaction CF |  | 0
Transaction CF
Intrinsic Value |  |  |  |  | Market Value |  |  |  |  | Rate of Return
Enterprise Value |  |  |  |  | Market [NAME] |  |  |  |  | Target Price Upside
Plus: Cash |  |  |  |  | Plus: Debt |  |  |  |  | Internal Rate of Return (IRR)
Less: Debt |  |  |  |  | Less: Cash
Equity Value |  |  |  |  | Enterprise Value |  |  |  |  | Market Value vs Intrinsic Value
 |  |  |  |  |  |  |  |  |  | Market Value
Equity Value/Share |  |  |  |  | Equity Value/Share |  |  |  |  | Upside
 |  |  |  |  |  |  |  |  |  | Intrinsic Value
 |  |  |  |  |  |  |  |  |  | Buy/Sell
Instructions
Step 1: Get EBIT and D&A from the income statement
Step 2: Calculate the net working capital
There are two ways to find NWC:
1. NWC = Current Assets (less cash) - Current Liabilities (less debt)
2. NWC = Accounts [NAME]
WACC Calculation |  |  |  | Comparable Companies Unlevered Beta
Capital Structure |  |  |  | Company | Levered Beta | Debt | Equity | Debt/Equity | Tax Rate | Unlevered Beta
Debt to Total Capitalization |  |  |  | Company A | 1.23 | 520 | 1125 | 0.4622 | 0.31 | 0.93
Equity to Total Capitalization |  |  |  | Company B | 1.31 | 500 | 868 | 0.576 | 0.3 | 0.93
Debt / Equity |  |  |  | Company C | 1.15 | 460 | 788 | 0.5841 | 0.29 | 0.81
 |  |  |  | Company D | 1.12 | 600 | 1125 | 0.5333 | 0.33 | 0.83
Cost of Equity |  |  |  | Company E | 1.25 | 450 | 900 | 0.5 | 0.3 | 0.93
Risk Free Rate |  |  |  | Median | 1.23 |  |  | 0.5333 |  | 0.93
Equity Risk Premium
Levered Beta
Cost of Equity
Cost of Debt
Cost of Debt |  | 0.06
Tax Rate
After Tax Cost of Debt
WACC
 |  |  |  |  |  |  |  | Beta (β)
Beta Calculator
Individual Stock |  |  |  | ASX200 Index |  |  |  | ASX 200 Index - matched to Stock |  |  |  | Dividend history
 |  | Return |  |  |  | Return |  | Date | Close | Return