Financial Modeling Formulas for Balance Sheet and P&L

Balance Sheet Assets

Total Assets = Non-current assets + Current assets

• Net non-current assets = Gross asset + Accumulated amortization
• Gross asset = Gross asset (past year) + Capex
• Accumulated amortization = Gross asset * Cost of debt
• Current assets = Customer receivables + Inventory + VAT recoverable + Total cash
• Customer receivables = (Revenue / 12) * Average receivable periods (months) * (1 + VAT rate)
• Inventory = (Average supplier periods (days) * (Quantity (B) * Unit cost sale (B) + Quantity (A) * Unit cost sale (A))) / 360
• VAT recoverable = If Output VAT – Input VAT < 0; then Output VAT – Input VAT
• Total cash = Operating cash + Non-operating cash
  • Operating cash = Provided in statement
  • Non-operating cash = Past year + FCe

Balance Sheet Equity and Liabilities

Total Equity + Liabilities = Shareholders equity + Long-term debt + Short-term debt

• Shareholders equity = Equity capital + Reserves + Reserves (payout dividends) + Net earning
• Equity capital = Past year + Equity capital (provided)
• Reserves = Net earning (past year) + Reserves (past year)
• Reserves (payout dividends) = Past year – Dividend
• Net earning = BAI + Taxes
• Long-term debt = Total debt * (1 - % short-term debt)
• Current liabilities = Short-term debt + Payables + VAT payables
• Short-term debt = Total debt * % short-term debt
• Payables = (Purchases / 360) * Average payment periods * (1 + VAT rate)
• VAT payables = If Output VAT – Input VAT > 0; then Output VAT – Input VAT

Profit and Loss (P&L)

• Revenue = (Unit selling price (A) * Quantity (A)) + (Unit selling price (B) * Quantity (B))
• Purchases = -(Quantity (B) * Unit cost sale (B) + Quantity (A) * Unit cost sale (A)) + Change in inventory
• Change in inventory = Inventory past year – Inventory this year
• Cost of goods sold = -Purchases + Change in inventory
• Gross profit (margin) = Revenue + Cost of goods sold
• Overheads = -Overheads
• EBITDA = Gross margin + Overheads
• Amortization & Depreciation = -Gross asset * Cost of debt
• EBIT = EBITDA + Amortization & Depreciation
• Financial result = -Total debt * Cost of debt
• EBT (BAI) = EBIT + Financial result
• Taxes = -BAI * Tax rate
• Net earning = BAI + Taxes

VAT Calculations

• Output VAT = VAT rate * Revenue
• Input VAT = ((-Purchases) + (-General expenses) + Capex) * VAT rate
• Net VAT = Output VAT – Input VAT

FCe (Free Cash Flow to Equity)

• Net earning (P&L)
• Amortization & Depreciation (P&L)
• Capex
• VOWC (Variation in Operating Working Capital)
• V. Debt = Long-term debt (this year) + Short-term debt (this year) – Long-term debt (past year) – Short-term debt (past year)
• V. Capital = Equity capital (this year) – Past year
• V. Dividends = Reserves payout dividends (this year) – Past year
• FCe = Sum of all components

Cost of Equity

Cost of Equity = Risk-free rate + Beta * (Market return - Risk-free rate) + Premium