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
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