Integrated Three Statement Financial Forecast Calculator

Purpose and model boundary

This model builds five linked annual income-statement, indirect cash-flow, and balance-sheet periods from synthetic or user-entered operating and financing assumptions. It is intended for transparent planning and roll-forward analysis. It does not produce audited financial statements, a forecast assurance conclusion, a valuation, or investment, accounting, tax, lending, or solvency advice.

Inputs and units

All financial amounts use one user-consistent currency in millions. The opening position includes cash, accounts receivable, inventory, net property, plant and equipment, accounts payable, debt, and retained earnings. Base revenue is the last actual-period revenue.

The annual grid contains exactly five complete rows. Each row supplies a period label, revenue growth, gross margin, cash operating expense as a fraction of revenue, depreciation and amortization as a fraction of revenue, capital expenditure as a fraction of revenue, a period tax rate, and net debt issuance or repayment. The tax-rate selector uses either the five grid rates or one visible single rate. Interest uses beginning debt or average debt. The tax-loss selector either prevents a modeled benefit on a pretax loss or applies the selected modeled tax rate to that loss.

Working-capital inputs are days for accounts receivable, inventory, and accounts payable. The day-count input sets the days per year. Interest, payout, margins, growth, expense, depreciation, capital-expenditure, and tax inputs are fractions unless their labels state otherwise.

Governing relationships

For period t, revenue is linked to the prior period:

Revenue_t = Revenue_(t-1) × (1 + growth_t)

Operating statement relationships are:

COGS_t = Revenue_t × (1 - gross margin_t)

Cash opex_t = Revenue_t × cash opex rate_t

EBITDA_t = Revenue_t - COGS_t - Cash opex_t

D&A_t = Revenue_t × D&A rate_t

EBIT_t = EBITDA_t - D&A_t

Ending debt equals beginning debt plus the entered net debt change. Interest equals the selected annual rate multiplied by beginning debt or by the arithmetic average of beginning and ending debt. Pretax income is EBIT less interest. The selected loss treatment determines whether a negative pretax amount receives a modeled tax benefit. Dividends equal the payout rate times positive net income and are zero on a loss.

Working-capital balances are:

Accounts receivable_t = Revenue_t × AR days / days per year

Inventory_t = COGS_t × inventory days / days per year

Accounts payable_t = COGS_t × AP days / days per year

Operating NWC_t = Accounts receivable_t + Inventory_t - Accounts payable_t

The indirect cash-flow and balance-sheet roll-forwards are:

CFO_t = Net income_t + D&A_t - change in operating NWC_t

Net PP&E_t = Net PP&E_(t-1) + capital expenditure_t - D&A_t

Cash_t = Cash_(t-1) + CFO_t - capital expenditure_t + net debt change_t - dividends_t

Retained earnings_t = Retained earnings_(t-1) + net income_t - dividends_t

Opening contributed capital is the residual that balances entered opening assets with liabilities and equity. It remains constant. Each period checks that total assets equal accounts payable, debt, contributed capital, and retained earnings.

Calculation sequence

1. Validate all three selectors, scalar assumptions, and the five complete grid rows.
2. Derive opening operating working capital and the contributed-capital balancing residual.
3. Roll revenue, operating profit, depreciation, debt, interest, taxes, and net income through the five periods.
4. Roll working capital, operating cash flow, capital expenditure, cash, net PP&E, and retained earnings.
5. Rebuild total assets and total liabilities and equity for every period and apply the workbook's balance-tie checks.
6. Return fifth-period values, cumulative operating cash flow, the balance-sheet check, the chart series, and status.

Outputs and interpretation

The headline results are fifth-period revenue, EBITDA, net income, ending cash, and cumulative operating cash flow. Supporting results show ending debt, retained earnings, total assets, total liabilities and equity, and their difference. The chart compares the five-period revenue and EBITDA paths. A near-zero balance-sheet check confirms arithmetic linkage only; it does not establish that the assumptions or accounting treatment are appropriate.

Validation and status logic

The workbook evaluates status in this order:

The zero-revenue check precedes the net-loss check. Hidden single-tax-rate input is ignored when the grid-tax route is active.

Assumptions and limitations

• The model uses five equal annual periods and one revenue stream.
• Accounts receivable is driven by revenue. Inventory and accounts payable are driven by cost of goods sold.
• Depreciation and capital expenditure are separate revenue-based drivers. No tax-depreciation or asset-class schedule is inferred.
• Debt changes are entered, and the interest calculation avoids circularity by using beginning or average debt.
• No revolver plug, cash sweep, share issuance, buyback, deferred tax, lease, acquisition, foreign-exchange, or minority-interest schedule is modeled.
• Negative cash, debt, or net PP&E is unsupported rather than silently corrected.
• Company-specific segment, accounting-policy, tax, and financing schedules require a fuller model and qualified review.

Restrictions and non-computing states

The request must retain exactly five complete forecast rows. All labels must be nonblank, all rate fields must remain within the published limits, and the day count must be from 1 through 366. Opening balances and base revenue cannot be negative. The single tax rate is required only in its visible mode. A state that produces negative cash, debt, or net PP&E, breaks a statement tie, or exceeds the supported numeric range does not compute a supported forecast.

Errors and warnings

Input checking can reject an unknown selector, a scalar outside its allowed range, or an incorrectly shaped grid before workbook calculation. Workbook NOT VALID identifies a domain or linked-roll-forward failure. Workbook CHECK identifies a computable zero-revenue or net-loss ending state. A connection or calculation-service error is not a forecast result and must not be interpreted as zero.

The statement relationships and accounting equation follow the general descriptions in the SEC Beginner's Guide to Financial Statements. The modeled line and roll-up structure also follows the public conventions described in the SEC EDGAR XBRL Guide. No filing facts, taxonomy data, company values, or source examples are embedded.