Financial Analysis, Planning and Forecasting

Introduction

Strategic financial management connects historical performance with future decisions. Financial analysis diagnoses profitability, liquidity, risk and value creation; planning translates strategy into operating and financing commitments; forecasting estimates the financial consequences under uncertainty.

These activities are iterative rather than sequential. A sales forecast affects working capital and capital expenditure. These affect cash, borrowing and interest expense, which feed back into profit, taxes, retained earnings and the balance sheet. A credible model must represent these relationships explicitly.

Learning Objectives

After completing this chapter, students should be able to:

1. Interpret financial statements using ratios, trends, cash-flow analysis and value drivers.
2. Calculate and evaluate banking and insurance performance indicators.
3. Prepare operating budgets and integrated pro forma financial statements.
4. Construct auditable spreadsheet forecasting models.
5. Apply sensitivity, scenario and strategic forecasting techniques.
6. Communicate financial insights through effective data visualization.
7. Evaluate forecasting limitations, governance risks and ethical concerns.

1. Foundations of Financial Analysis

The income statement reports performance over a period, the balance sheet reports financial position at a date, and the cash-flow statement explains changes in cash. The statements must be interpreted together. Reported profit does not necessarily represent cash generation, while strong short-term cash flow may result from delayed investment or stretched supplier payments.

Before calculating ratios, an analyst should examine:

• accounting policies and changes in estimates;
• exceptional, discontinued or nonrecurring items;
• inflation, seasonality and foreign-exchange effects;
• acquisitions and disposals affecting comparability;
• related-party transactions;
• audit qualifications and internal-control weaknesses; and
• differences in accounting standards.

1.1 Common-size analysis

A common-size income statement expresses each item as a percentage of revenue. A common-size balance sheet normally expresses each item as a percentage of total assets. This facilitates comparison across periods and differently sized organizations.

If revenue is CU1,000 and cost of goods sold is CU600, the common-size cost ratio is 60%. “CU” denotes currency units and avoids assuming a particular national currency.

1.2 Trend and growth analysis

A trend index compares each observation with a base period:

Here, X_t is the value in period t and X₀ is the base-period value, measured in the same units.

For a value growing from X₀ to X_n over n years, the compound annual growth rate is:

CAGR assumes a constant compounded rate and conceals year-to-year volatility. If revenue increases from CU100 million to CU121 million in two years, CAGR is (121/100)^1/2-1=10%.

2. Ratio Analysis

Ratios have meaning only when compared with prior periods, competitors, targets or economically justified benchmarks. Differences in business models and accounting policies can make superficial comparisons misleading.

2.1 Liquidity and operating efficiency

Both are dimensionless. The quick ratio excludes inventory and other less-liquid current assets. A high current ratio may still conceal obsolete inventory or overdue receivables.

Working-capital efficiency can be assessed through days-based measures:

D is normally 365 days, or the number of days in the reporting period. If credit purchases are unavailable, cost of goods sold may be used as an approximation, with that limitation disclosed.

2.2 Profitability

EBIT is earnings before interest and tax. Average balance-sheet values are preferable because income is earned throughout the period.

The three-part DuPont model explains return on equity:

The components represent profit margin, asset turnover and financial leverage. The identity applies when definitions are consistent. A higher ROE produced solely by greater leverage may indicate increased risk rather than superior operations.

2.3 Leverage and coverage

Interest coverage assumes EBIT is a useful proxy for resources available to service interest. Analysts may supplement it with cash-based coverage where accruals or capital expenditure are material.

3. Cash-Flow and Value-Driver Analysis

3.1 Cash-flow diagnosis

Operating cash flow should be reconciled with profit. Persistent divergence may arise from rapid growth, weak collections, inventory accumulation, provisions, aggressive revenue recognition or delayed supplier payments.

Useful questions include:

• Is operating cash flow positive and recurring?
• Is capital expenditure sufficient to maintain operating capacity?
• Are dividends and debt repayments supported by internally generated cash?
• Is cash improvement caused by sustainable operations or working-capital compression?

Operating net working capital normally includes trade receivables, inventory and other non-interest-bearing operating current assets, less trade payables and other non-interest-bearing operating current liabilities. Cash and interest-bearing debt are excluded because they are financing items.

3.2 Free cash flow to the firm

For a nonfinancial operating company, free cash flow to the firm (FCFF) may be estimated as:

Where:

• T is the applicable cash tax rate on operating profit;
• D\&A is depreciation and amortization;
• NC is other noncash operating expense net of noncash operating gains included in EBIT;
• capital expenditure is cash investment in long-lived operating assets; and
• ΔONWC is the increase in operating net working capital.

The formula assumes financing costs are excluded from EBIT and that operating taxes can be estimated as EBIT(1-T). A positive increase in working capital consumes cash. Importantly, NC must exclude depreciation and amortization because these are already added back. Noncash gains should reduce the adjustment. Stock-based compensation is noncash for the period but may create dilution and therefore requires separate valuation attention.

3.3 Return on invested capital

Invested capital generally includes operating assets less non-interest-bearing operating liabilities, or equivalently interest-bearing financing plus equity less nonoperating financial assets. Consistent definitions are essential.

A firm creates operating value when ROIC exceeds its risk-adjusted weighted average cost of capital (WACC). Growth creates value only if incremental investments earn returns above the relevant cost of capital.

4. Financial Analysis for Banks and Insurers

Conventional industrial-company ratios cannot be applied mechanically to financial institutions. For banks, deposits are operating inputs, loans are operating assets and interest is a core operating item. Regulatory capital and liquidity constraints are central. For insurers, underwriting performance, reserves and solvency capital require special analysis.

4.1 Banking indicators

Net interest margin (NIM) measures net interest income generated by interest-earning assets:

The numerator and denominator must cover consistent periods. Interim NIM is usually annualized.

Common Equity Tier 1 ratio measures highest-quality regulatory capital relative to risk-weighted assets (RWA):

Total capital adequacy ratio is:

Required minimums and buffers depend on jurisdiction, institution and supervisory designation.

Nonperforming loan ratio assesses asset quality:

NPL coverage ratio measures allowance protection:

Definitions of nonperformance and allowances must follow applicable regulation and accounting standards.

Liquidity coverage ratio (LCR) evaluates short-term stress resilience:

Under the Basel framework, the ratio is generally expected to be at least 100% after transition, subject to national rules and permitted stress-period use.

#### Banking example

A bank reports net interest income of CU54 million, average interest-earning assets of CU900 million, CET1 capital of CU72 million, total regulatory capital of CU90 million and RWA of CU600 million. It also has gross loans of CU750 million, NPLs of CU30 million, allowances of CU21 million, high-quality liquid assets of CU130 million and stressed 30-day net cash outflows of CU110 million.

The figures require comparison with regulatory requirements, peer risk profiles and trends. For example, a high NIM may compensate for unusually high credit risk rather than indicate unqualified strength.

4.2 Insurance indicators

Loss ratio relates incurred claims and loss-adjustment expenses to earned premiums:

Expense ratio compares underwriting expenses with premiums, using the regulatory or managerial denominator consistently.

Under this denominator convention:

A combined ratio below 100% indicates an underwriting profit before investment income. Analysts must verify denominator conventions because some markets calculate the expense ratio using written premiums.

Under a Solvency II-type regime, the solvency capital ratio is:

Other jurisdictions use risk-based capital measures with different definitions; ratios should not be compared without adjustment.

If an insurer has earned premiums of CU500 million, incurred claims and adjustment expenses of CU310 million, and underwriting expenses of CU140 million, its loss ratio is 62%, expense ratio is 28%, and combined ratio is 90%. Eligible own funds of CU180 million against a solvency capital requirement of CU120 million produce a 150% solvency ratio.

5. From Strategy to Budgets and Forecasts

A budget is an approved financial plan and control benchmark. A forecast is management’s current best estimate of what is likely to occur. Treating a forecast as a target encourages biased reporting.

An integrated planning system commonly includes:

1. strategic assumptions and scenarios;
2. sales or business-volume forecast;
3. operating budgets;
4. staffing and overhead budgets;
5. working-capital schedules;
6. capital-expenditure and depreciation schedules;
7. financing, interest and dividend schedules;
8. tax estimates; and
9. pro forma income statement, balance sheet and cash-flow statement.

Banks typically forecast loan volumes, deposit mix, yields, funding costs, expected credit losses, RWA and regulatory capital. Insurers forecast premium volumes, claims frequency and severity, expenses, reserves, investment returns and solvency capital. Standard sales-driven FCFF models are therefore often unsuitable for valuing regulated financial institutions; dividend, excess-capital or equity-based approaches may be more informative.

5.1 Driver-based forecasting

A driver-based model links financial outcomes to operational causes. For example:

These relationships are applicable when the selected driver is economically meaningful. A percentage-of-sales approach may be inappropriate for step-fixed capacity costs or businesses undergoing structural change.

6. Integrated Statements and Financing Feedback

A forecast must preserve the accounting equation:

Financing cannot be added as an isolated final line. A cash deficit may increase debt; debt changes interest expense; interest changes profit, tax, operating cash flow, retained earnings and the balance sheet. Conversely, surplus cash may repay debt and reduce subsequent interest.

Figure: Integrated forecasting architecture, including the feedback from financing to debt, interest, cash flow and the balance sheet.

Circularity can be handled through spreadsheet iteration, algebraic solution or a simplified policy such as calculating interest on opening debt. The chosen method must be disclosed. Using opening debt avoids circularity but is less accurate when debt changes materially during the period.

7. Practical Spreadsheet Construction

A professional spreadsheet should separate inputs, calculations and outputs. Hard-coded assumptions should not be embedded repeatedly in formulas.

7.1 Recommended workbook structure

Suppose column B contains 2025 actual values and column C contains 2026 forecasts. A compact model can use the following formulas:

Here C3 is growth, C4 is tax rate, C5 is the COGS ratio, C7–C9 are working-capital ratios, C13 is depreciation and amortization, C15 is other qualifying noncash charges, and C24 is capital expenditure. C15 must exclude depreciation and amortization.

Model controls should include:

• balance-sheet check equal to zero;
• sources-of-cash less uses-of-cash check equal to zero;
• no unintended negative debt or cash;
• warning flags for covenant and regulatory breaches;
• consistent signs, dates and units; and
• comparison of forecast opening balances with prior closing balances.

7.2 Spreadsheet-based sensitivity analysis

A one-variable data table can vary one assumption, such as revenue growth, while recording FCFF or earnings per share. A two-variable table can vary growth and gross margin simultaneously. Scenario analysis should instead change a coherent set of assumptions—for example, recession may combine lower volume, longer receivable days, higher credit losses and increased funding costs.

Version control, cell protection, input validation and independent review reduce operational risk. Macros and external links should be documented and tested.

8. Data Visualization for Financial Decisions

Visualization should reveal patterns rather than decorate reports.

To build a trend chart in a spreadsheet, place years in the first column and revenue, EBIT margin and operating cash flow in adjacent columns. Select the range, insert a line chart, label units and avoid a dual axis unless scales genuinely require it. For cross-company growth comparison, convert each series to an index of 100 in the initial year.

A cash waterfall should begin with opening cash, show positive and negative operating, investing and financing movements, and end at closing cash. The ending value must reconcile with the balance sheet.

Best practice includes descriptive titles, readable labels, consistent colors and disclosure of whether values are actual, forecast or scenario-based. Truncated axes can exaggerate differences; three-dimensional charts distort perception.

9. Illustrative Integrated Case: Northstar Appliances

Background and problem

Northstar Appliances is a fictional distributor preparing its 2026 plan. Management expects 10% revenue growth but needs to determine cash generation and debt repayment while maintaining minimum cash of CU50 million.

The 2025 base includes revenue of CU1,000 million, COGS of CU600 million, cash operating expenses of CU200 million, depreciation of CU40 million, receivables of CU120 million, inventory of CU100 million, payables of CU80 million, cash of CU50 million, net PPE of CU300 million and debt of CU200 million.

Forecast assumptions

• Revenue growth: 10%.
• COGS: 60% of revenue.
• Cash operating expenses: 20% of revenue.
• Depreciation: CU45 million.
• Capital expenditure: CU70 million.
• Receivables, inventory and payables retain their 2025 activity ratios.
• Interest rate: 8% of average debt.
• Tax rate: 25%; taxable income remains positive and interest is fully deductible.
• Dividend: CU30 million.
• Excess cash is used to repay debt.

Operating analysis

Thus, ΔONWC=154-140=CU14 million.

No additional noncash charge exists in this example.

Financing feedback and algebraic solution

Let x be debt repayment in CU millions. Closing debt is 200-x, so average debt is 200-x/2. Interest is therefore:

Net income is:

Cash available for debt repayment after maintaining closing cash of CU50 million is:

Substitution gives:

Therefore, 0.97x=50.25 and x=CU51.804 million. Closing debt is CU148.196 million, interest is CU13.928 million, and net income is CU120.804 million.

Net PPE becomes 300+70-45=CU325 million. Equity becomes 320+120.804-30=CU410.804 million. With other assets of CU110 million and other liabilities of CU80 million, forecast assets and liabilities plus equity both equal CU727 million.

Sensitivity and outcome

At 10% growth, if the COGS ratio rises from 60% to 63%, EBIT falls to CU142 million. Inventory and payables move with COGS, producing forecast ONWC of CU155.1 million and ΔONWC=CU15.1 million. FCFF becomes:

The margin shock reduces FCFF by CU25.85 million. Management should therefore monitor supplier pricing and selling-price discipline, not merely revenue growth.

Learning points

• Growth affects working capital as well as profit.
• Debt repayment changes interest, tax, cash flow and retained earnings.
• The balance sheet provides an essential model-integrity check.
• Sensitivity analysis identifies which operating assumptions most threaten cash generation.

10. Forecasting Uncertainty and Strategic Use

10.1 Sensitivity, scenarios and simulation

Sensitivity analysis changes one assumption at a time and identifies exposure to individual drivers. Scenario analysis changes internally consistent groups of assumptions. Common scenarios include base, upside, downside and regulatory stress.

Monte Carlo simulation assigns probability distributions to uncertain inputs and repeatedly recalculates outcomes. Its usefulness depends on defensible distributions and correlations. It does not remove model risk or convert poor assumptions into reliable forecasts.

Time-series and regression models can support forecasts where sufficiently stable data exist. Statistical fit is not proof of causality, and structural breaks can invalidate historical relationships.

10.2 Rolling forecasts

A rolling forecast adds a new period whenever the current period ends, maintaining a constant horizon such as 12 or 18 months. It is particularly useful where demand, funding costs, claims or credit quality change rapidly.

11. Governance, Ethics and Contemporary Technology

Forecasts influence compensation, lending, investment and regulatory decisions. Ethical risks include deliberately optimistic revenue, delayed loss recognition, understated claims reserves and manipulation of assumptions to satisfy covenants or capital requirements.

Good governance requires documented assumptions, clear ownership, independent challenge, audit trails, access controls and reconciliation to approved source data. Actual results should be compared with forecasts, and errors should inform model improvement rather than be concealed.

Artificial intelligence can classify transactions, detect anomalies, generate forecast candidates and analyze unstructured data. However, AI outputs may reflect biased data, unstable relationships or opaque logic. Material forecasts require human review, explainability, privacy controls and monitoring for model drift. Management remains accountable for decisions.

Chapter Summary

Financial analysis combines statement interpretation, ratios, trends, cash-flow diagnosis and value-driver analysis. Ratios require consistent definitions and appropriate benchmarks. FCFF adds depreciation, amortization and qualifying other noncash operating charges to after-tax EBIT, then deducts capital expenditure and increases in operating working capital.

Banks require analysis of net interest margin, capital adequacy, asset quality and regulatory liquidity. Insurers require underwriting, reserve and solvency measures. Strategic forecasts integrate operating budgets, working capital, investment, financing, interest, tax and all three pro forma statements. Spreadsheet controls and balance-sheet reconciliation are essential. Sensitivity, scenarios and visualization help management understand uncertainty, while sound governance protects forecast integrity.

Key Terms

Common-size analysis: Presentation of statement items as percentages of a common base.

CAGR: Constant annual compounded rate linking beginning and ending values.

DuPont analysis: Decomposition of ROE into margin, asset turnover and leverage.

Operating net working capital: Operating current assets less non-interest-bearing operating current liabilities.

FCFF: Cash flow available to all capital providers after operating investment.

NOPAT: After-tax operating profit before financing costs.

ROIC: NOPAT divided by average invested capital.

Net interest margin: Bank net interest income relative to average interest-earning assets.

CET1 ratio: Common Equity Tier 1 capital relative to risk-weighted assets.

NPL ratio: Nonperforming loans relative to gross loans.

Liquidity coverage ratio: High-quality liquid assets relative to stressed 30-day net cash outflows.

Combined ratio: Insurer loss ratio plus expense ratio under consistent conventions.

Solvency capital ratio: Eligible own funds relative to the applicable solvency capital requirement.

Driver-based forecast: Forecast linking financial outcomes to operational causes.

Pro forma statement: Forecast financial statement based on specified assumptions.

Sensitivity analysis: Evaluation of an outcome after changing one input at a time.

Scenario analysis: Evaluation using a coherent set of mutually consistent assumptions.

Rolling forecast: Forecast that maintains a constant horizon by adding future periods.

Model circularity: Feedback in which an output, such as debt, affects an input such as interest.

Review Questions

Short Answer Questions

1. Why should financial statements be normalized before ratio analysis?
2. What is the difference between a budget and a forecast?
3. Why is an increase in operating net working capital deducted in FCFF?
4. What does a bank’s liquidity coverage ratio measure?
5. What does an insurer’s combined ratio below 100% indicate?

Long Answer Questions

1. Explain how ratio, trend and cash-flow analysis complement one another.
2. Discuss the DuPont framework and the risks of interpreting a high ROE without examining leverage.
3. Explain how bank analysis differs from analysis of a nonfinancial company.
4. Describe the construction of an integrated three-statement forecast.
5. Evaluate the governance and ethical controls needed for spreadsheet-based forecasting.

Analytical / Application Questions

1. A company forecasts rapid sales growth but has lengthening receivable and inventory days. Analyze the likely effects on profit, operating cash flow, borrowing and liquidity.
2. Construct base, upside and downside scenarios for a bank facing rising interest rates and deteriorating credit quality. Identify the drivers affecting NIM, NPLs, capital and liquidity.
3. Using the Northstar case, explain why ignoring the interaction between debt repayment and interest expense would produce an internally inconsistent forecast.

Multiple Choice Questions with Answers

Question 1

A company has current assets of CU240 million and current liabilities of CU160 million. What is its current ratio?

A. 0.67

B. 1.50

C. 1.67

D. 2.40

Correct Answer: B. 1.50

Question 2

A firm has EBIT of CU200 million, a 25% tax rate, depreciation and amortization of CU30 million, other qualifying noncash operating charges of CU5 million, capital expenditure of CU50 million and an increase in operating net working capital of CU10 million. What is FCFF?

A. CU115 million

B. CU120 million

C. CU125 million

D. CU135 million

Correct Answer: C. CU125 million

Question 3

A bank earns net interest income of CU48 million on average interest-earning assets of CU800 million. What is its net interest margin?

A. 4%

B. 6%

C. 8%

D. 16%

Correct Answer: B. 6%

Question 4

A bank holds CU120 million of high-quality liquid assets and estimates 30-day stressed net cash outflows of CU100 million. What is its liquidity coverage ratio?

A. 83.3%

B. 100%

C. 120%

D. 220%

Correct Answer: C. 120%

Question 5

An insurer has a loss ratio of 65% and an expense ratio of 27%, calculated using consistent denominator conventions. What is its combined ratio?

A. 38%

B. 65%

C. 92%

D. 108%

Correct Answer: C. 92%

Question 6

Revenue increases from CU100 million to CU121 million over two years. What is the CAGR?

A. 9%

B. 10%

C. 10.5%

D. 21%

Correct Answer: B. 10%

Question 7

Why can an integrated forecast contain circularity?

A. Revenue must always equal total assets.

B. Depreciation must equal capital expenditure.

C. Debt affects interest, while cash generation affects debt.

D. Current assets must equal current liabilities.

Correct Answer: C. Debt affects interest, while cash generation affects debt.

Question 8

Which spreadsheet check most directly tests whether a pro forma balance sheet is internally consistent?

A. Revenue minus expenses equals EBIT.

B. Assets minus liabilities minus equity equals zero.

C. Gross margin exceeds net margin.

D. Forecast revenue exceeds historical revenue.

Correct Answer: B. Assets minus liabilities minus equity equals zero.

Question 9

Which statement correctly distinguishes sensitivity analysis from scenario analysis?

A. Sensitivity analysis changes one input at a time; scenario analysis changes a coherent set of assumptions.

B. Sensitivity analysis uses only historical data; scenario analysis uses only forecasts.

C. Sensitivity analysis applies only to banks; scenario analysis applies only to insurers.

D. Sensitivity analysis eliminates uncertainty; scenario analysis measures accounting errors.

Correct Answer: A. Sensitivity analysis changes one input at a time; scenario analysis changes a coherent set of assumptions.

Question 10

In the Northstar base forecast, EBIT is CU175 million, the tax rate is 25%, depreciation is CU45 million, capital expenditure is CU70 million and the increase in operating net working capital is CU14 million. What is FCFF?

A. CU78.25 million

B. CU86.00 million

C. CU92.25 million

D. CU106.25 million

Correct Answer: C. CU92.25 million