1. Optimal excel settings

File > Options (Windows: Alt+F+T; Mac: cmd+comma)
Formulas:
  • Workbook Calculation — Automatic
  • un-ticket — Enable iterative calculation
Advanced
  • un-ticket — After pressing Enter, move selection
  • un-ticket — Allow editing directly in cells
notion image

2. History: sub-total

which is calculating: Gross profit, Operating profit, Total assets, Total liabilities, Total equity……
completing the history terms in statements.
make sure Balance sheet is balance: A=L+E
 

3. Forecast income statement for Year1

  • every project Year’s figures in three statement are remain in the same column.
    • eg. income statement Project.Year1 is in column D, so Balance sheet Project.Year1 is in column D.
  • History.Year0* (1+Assumption) = Project.Year1;
  • or Project.Year1 = Assumption.

Income statement:

  • Sales —> Gross income —>Operating income —> Net income
  • Weighted average shares outstanding
  • Dividend
  • Earning per share —> Net income/Weighted average shares outstanding

Balance sheet:

Asset
  • Current asset
    • Cash skip for now, fill after cash flow statement completed.
    • Account receivable
    • Inventories (inventories is driven by the cost-COGS)
  • PP&E (tangible asset) —> Calculations: PP&E beginning balance —> PP&E ending balance
    • Capital expense increase as sales increase; depreciation as % of PPE.
  • Liability
    • current liability —> account payable
    • Longterm debt —> Debt: beginning balance-Issuance-Scheduled repayment —> end balance
    • Interest
  • Equity
    • Common stock (ordinary share capital) —> assumption: no issue of shares/ buybacks
    • Retained earnings (accumulate profit) —> calculations: beginning balance —> net income/dividends paid —> ending balance.

Cash flow statement

Beginning cash
Change in cash from CFS (CFO+CFI+CFF)
Net cash available at end of the year (result move to Balance sheet)
  • Cash flow from operations (CFO)
    • Net income
    • Depreciation
    • Increase in working capital (outflow) : Calculations —>Accounts receivable-Inventories-accounts payable —> operating working Capital
    • Total cash from operation
  • Cash flow from investing (CFI)
    • (Capital expenditure)
    • Total cash from investing
  • Cash flow from financing (CFF)
    • Increase (decrease) in LTD
    • Increase (decrease) in Common stock
    • (Dividends)
    • Total cash from financing
 

4. Balance B/S

5. Extend formula from Year1 to subsequent years

6. Beauty save the model

  • Delate Highlights, scripts.
  • Make sure zoom at 100% for every page.
  • Ctrl+home for every page to move selection to A1
Harrykunye
Harrykunye
一个金融学生的博客
公告
type
status
date
slug
summary
tags
category
icon
password
暂无公告
-- 感谢您的支持 ---