A finance team that still builds every forecast by hand spends its time fixing links and checking totals. A team that builds the model once, tests it and updates it with new data spends its time on the decision. Financial modelling, forecasting and planning software do not replace judgement. They make the numbers faster, easier to check and easier to share.
This topic covers what financial modelling is and who builds models, the features of modelling software and examples of it, the main types of model (three-statement, merger and acquisition, forecasting, discounted cash flow, comparable company, asset and liability management, capital budgeting, initial public offering, buyout financed by debt and option pricing), forecasting techniques and tools, and the objectives of financial planning.
The previous topic covered emerging issues. The next area covers risk management. Neither the May 2014 nor the November 2014 Pathfinder sets a stand-alone question on this topic.
In 30 seconds
A financial model combines accounting, finance and business data to show a company’s position and predict its future. Models help with raising capital, acquisitions, growth, disposals, budgeting, capital allocation, valuation and analysis. They are built by accountants, corporate development analysts, investment bankers, equity research analysts and, above all, financial planning and analysis (FP&A) staff. Software offers fast, accurate calculation, scenarios, access anywhere, large databases and time savings.
Main models: three-statement, M&A, forecasting, DCF, comparable company, ALM, capital budgeting, IPO, LBO and option pricing. Forecasts may be qualitative, quantitative or causal. Financial planning sets capital needs, capital structure, financial policies and efficient use of funds.
Key terms in plain English
| Term | What it means |
|---|---|
| Financial model | A structured representation of a company’s finances, used to test decisions and predict results |
| FP&A | Financial planning and analysis, the team that builds the budgets, forecasts and models |
| Three-statement model | A model that links the income statement, statement of financial position and cash flow statement |
| Comparable company analysis | Valuing a business from the multiples of similar companies |
| Buyout financed by debt (LBO) | An acquisition financed mostly with debt |
| Causal forecasting | Forecasting that studies what drives a change, not only the past trend |
| Gantt chart | A bar chart that shows tasks against time, used to plan and track a project |
Syllabus check
This topic is in syllabus area A (the role of technology in financial management). It covers:
- areas of strategic financial management that need IT support;
- financial modelling, forecasting, planning and reporting tools;
- the objectives of financial planning.
CAMA 2020 and technology tools
The Companies and Allied Matters Act 2020 (CAMA 2020) is the company law of Nigeria. The sections below apply to this topic.
| Section | What it says | Link to this topic |
|---|---|---|
| s. 377(1) and s. 378 | Directors prepare statements that follow IFRS and give a true and fair view | Models that feed the statements must be reliable |
| s. 385 | The directors’ report covers performance and risk | Forecasts support the report on prospects and risk |
| s. 308 | A director must use the care, diligence and skill of a reasonably prudent director | Directors should question the assumptions in a model |
| s. 404(7) | The audit committee reviews policies and internal control | It reviews the control of models and data |
What financial modelling is
Financial modelling combines accounting, finance and business measures into a model that shows the current position and predicts future performance. It supports investment decisions, pricing of securities and plans for corporate transactions. Inside a company, executives use models to decide on raising capital (debt or equity), acquisitions, organic growth, disposals, budgeting and forecasting, capital allocation, valuation, ratio analysis and management accounting.
Who builds models
Accountants, corporate development analysts, investment bankers and equity research analysts. In a modern company, those in FP&A are most likely to build and use the models that steer the company.
Modelling software
Financial modelling software shows the relationship between finance and operations so that the business can study its response to good and bad situations and see the money effects in advance.
Features
- Calculation performance: complex calculations made easy and mostly error-free, with the current assumptions held in place.
- Business planning: economic, financial and operational scenarios, with built-in security and controls.
- Access: secure access to data and reports from anywhere, and the ability to link operational and financial models.
- Large databases: storage of any amount of data and as many models as needed.
- Time saving: faster than manual work.
Examples
ICAN lists Microsoft Excel (the first tool an analyst needs), Oracle BI, Business Objects (SAP), Hyperion (Oracle), Operis, IBM Cognos and Quantrix, and says the list is not exhaustive. Products and names change, so use the type of tool in an answer and name a product only as an example.
Types of financial model
- Three-statement model: forecasts the income statement, statement of financial position and cash flow statement together. It is the standard base for most others.
- Merger and acquisition (M&A) model: calculates the effect of a deal on earnings per share and compares it with the current EPS.
- Forecasting models: predict revenue, expenses and capital costs, and are compared with budget to review performance.
- DCF model: values a company, a share, a project, a cost-saving plan or anything that affects cash flow, using predicted future cash flows.
- Comparable company analysis (CCA): a simpler valuation that assumes similar companies have similar multiples, such as enterprise value to sales (EV/S), price to earnings (P/E), price to book (P/B) and price to sales (P/S).
- Asset and liability management (ALM): used by banks, insurers and pension funds to meet their obligations in any economic conditions, and reviewed regularly (often every three to five years) to reduce sensitivity to economic conditions, interest rates and exchange rates.
- Capital budgeting model: supports the annual budget, using quarterly figures and forecasts.
- IPO model: templates used by investment bankers to price a share issue. FP&A should know that they exist.
- Buyout financed by debt (LBO) model: analyses an acquisition financed mostly by debt. ICAN says typically 90% debt and 10% equity, with bonds that are below investment grade. It is high risk and high reward.
- Option pricing model: sets option premiums from the probability of ending in the money. It is used mainly by market makers and traders, and by firms that hold derivatives.
Forecasting
Forecasting looks at past trends and the current position to predict the future, and supports goal setting, budgeting and campaign planning. Three techniques:
- Qualitative: used when there is no hard data, for example a start-up with no history. It relies on expert opinion and market research.
- Quantitative: uses past data and statistics, and suits short-term planning.
- Causal: tries to understand relationships between variables. If sales fall, is it because of the economy or poor service?
ICAN names forecasting tools such as Demand Works (demand planning for inventory, capacity and sales and operations planning), QuickBooks (trend and forecast reports) and Tableau (business intelligence). The notes mention forecasting of interest and currency rates as an area needing IT support.
Financial planning and its tools
Financial planning estimates the capital needed and decides its composition, and frames the policies for raising, investing and managing funds. Its objectives are:
- Determine capital requirements: the cost of current and non-current assets, promotion costs and long-range plans, short and long term.
- Determine capital structure: the mix and proportion of capital, including the debt-to-equity ratio.
- Frame financial policies on cash control, lending and borrowing.
- Make sure scarce funds are used as well as possible at least cost for the best return.
Technology has changed financial planning. ICAN lists adviser tools such as eMoney, Envestnet, Money Tree, MoneyGuidePro, Redtail CRM, NestEgg Estimator, Black Diamond and Equisoft/plan, and the learning outcomes refer to project planning tools such as the Gantt chart and to reporting and presentation tools.
Tools and companies change, so in the exam name the function (for example a spreadsheet, a business intelligence tool or a project chart) and use a product name only as an illustration.
The rules in more depth
Why a model is only as good as its assumptions
Software calculates exactly what it is told. A wrong growth rate gives a wrong answer very precisely, so the assumptions must be stated, tested and reviewed.
Why scenarios matter
A single forecast hides uncertainty. Models let the team run good, expected and bad cases and see which assumptions matter most.
Why a three-statement model is the base
It links profit, assets and cash, so a change in one figure flows through the others and keeps the forecast consistent.
Why multiples are a quick check on DCF
A comparable company valuation is simpler. If it differs sharply from the DCF, the assumptions behind one of them need review.
Effect on analysis
In a question, say which model fits the decision, what data it needs, what it will show and what its limits are.
Worked examples
Example 1: choosing a forecasting method. (a) A new firm with no sales history. (b) A shop with five years of monthly sales. (c) A company whose sales have fallen and wants to know why.
- (a) Qualitative, using expert opinion and market research.
- (b) Quantitative, using the past data.
- (c) Causal, testing whether the fall follows the economy, price or service.
Example 2: an M&A model. Fortune Plc has net income of ₦300m and 150m shares (EPS ₦2.00). It buys a target with net income of ₦90m by issuing 30m new shares.
- Combined net income.
300 + 90 = ₦390m. - Shares = 150 + 30 = 180m.
- New EPS.
390 ÷ 180 = ₦2.17. - EPS rises from ₦2.00 to ₦2.17, about 8.3%, so the deal adds to earnings per share (it is accretive).
Example 3: comparable company analysis. A private company has earnings of ₦50m. Listed peers trade at a P/E of 12. It also has sales of ₦800m, and peers trade at an EV/S of 1.5.
- P/E value.
50 × 12 = ₦600m. - EV/S value.
800 × 1.5 = ₦1,200m. - The two differ a great deal, which suggests that the margin of the company is below its peers. Investigate before relying on either.
Example 4: an LBO structure. A buyer pays ₦1bn for a company, using 90% debt at 15% interest.
- Debt = ₦900m. Equity = ₦100m.
- Annual interest.
900 × 15% = ₦135m. - The company must earn enough cash each year to cover ₦135m of interest and repay debt, which shows the risk.
How to score full marks
- Match the model to the decision.
- State the inputs and assumptions.
- Show the result with a number where data is given.
- Give a limit of the model.
- Separate the tool (software) from the method (the model).
- Tie planning objectives to capital needs and structure.
Common mistakes in the exam
- Listing software names with no explanation of the function.
- Treating the model output as certain.
- Choosing a quantitative forecast when there is no data.
- Confusing a DCF model with a comparable company model.
- Forgetting that a deal model compares EPS before and after.
- Ignoring data security and control.
Quick revision
- Model: accounting, finance and business data to show and predict the position.
- Builders: accountants, analysts, bankers, FP&A.
- Models: three-statement, M&A, forecasting, DCF, CCA, ALM, budgeting, IPO, LBO, options.
- Forecasts: qualitative, quantitative, causal.
- Planning: capital needs, structure, policies, efficient use.
- CAMA 2020: ss. 377(1), 378, 385, 308, 404(7).
Practice questions
These are original questions written in the style of the Institute of Chartered Accountants of Nigeria (ICAN) exam. Try each one before you open the answer. Full model answers follow the written questions.
Recall check: multiple choice
The real ICAN paper has no multiple-choice section. These questions are for quick recall. The written questions that follow are closer to the exam.
Question 1. A three-statement model links:
- Income statement, financial position and cash flow
- Three tax returns
- Three banks
- Three years of audit
Show the answer
A. It is the base of most models.
Question 2. Which forecast suits a start-up with no history?
- Quantitative
- Qualitative
- Causal only
- None
Show the answer
B. It relies on opinion and market research.
Question 3. An M&A model mainly measures the effect on:
- EPS
- Tax
- Payroll
- Rent
Show the answer
A. It compares EPS before and after.
Question 4. Comparable company analysis uses:
- Multiples of similar firms
- Only past profits
- Bank rates
- Audit fees
Show the answer
A. Such as P/E and EV/S.
Question 5. An LBO is financed mainly by:
- Equity
- Debt
- Grants
- Retained profit
Show the answer
B. Typically about 90% debt.
Question 6. A Gantt chart is used to:
- Plan and track tasks over time
- Value shares
- Fix interest
- Pay tax
Show the answer
A. A project planning tool.
Question 7. Causal forecasting asks:
- What happened last year
- What drives the change
- Who owns the firm
- What the tax is
Show the answer
B. It studies relationships between variables.
Question 8. ALM models are used mainly by:
- Banks, insurers and pension funds
- Shops
- Farmers
- Schools
Show the answer
A. To meet obligations in any conditions.
Question 9. One objective of financial planning is to determine:
- Capital requirements
- The weather
- Staff holidays
- Office colours
Show the answer
A. And capital structure.
Question 10. Which section of CAMA 2020 sets the director’s duty of care and skill?
- Section 308
- Section 400
- Section 405
- Section 409
Show the answer
A. Directors should question a model’s assumptions.
Written questions
Question 11 (10 marks). (a) State the features of financial modelling software. (5 marks) (b) State who builds financial models and the decisions they support. (5 marks)
Question 12 (20 marks). Fortune Plc has net income of ₦300m and 150m shares. It plans to buy a target with net income of ₦90m by issuing 30m new shares. A private company it also considers has earnings of ₦50m and sales of ₦800m, while listed peers trade at a P/E of 12 and EV/S of 1.5. (a) Calculate the EPS before and after the first deal and comment.
(6 marks) (b) Value the private company using P/E and EV/S and comment. (6 marks) (c) Advise which forecasting technique suits a new product with no sales history, five years of sales data, and a sales fall of unknown cause. (4 marks) (d) State the tool you would use to plan the project steps of the integration. (4 marks)
Question 13 (10 marks). (a) State the objectives of financial planning. (6 marks) (b) Explain what an LBO model is and why it is risky. (4 marks)
Model answers
Show the model answer to Question 11
(a) Features (5 marks). Fast, mostly error-free calculation; scenarios for planning with built-in security; secure access from anywhere and the ability to link models; large databases; time saving over manual work. (1 each)
(b) Builders and decisions (5 marks). Accountants, corporate development analysts, investment bankers, equity research analysts and FP&A staff. (2) Decisions: raising capital, acquisitions, organic growth, disposals, budgeting and forecasting, capital allocation, valuation, ratio analysis. (3)
Show the model answer to Question 12
(a) EPS (6 marks).
- Before.
300 ÷ 150 = ₦2.00. (1.5) - After.
(300 + 90) ÷ (150 + 30) = 390 ÷ 180 = ₦2.17. (2.5) - EPS rises by about 8.3%, so the deal is accretive, though the model does not test the integration risk. (2)
(b) Valuation (6 marks).
- P/E.
50 × 12 = ₦600m. (2) - EV/S.
800 × 1.5 = ₦1,200m. (2) - The two differ widely, which suggests a lower margin than peers; investigate before relying on either. (2)
(c) Forecasting (4 marks). New product with no history: qualitative. (1.5) Five years of data: quantitative. (1.5) Sales fall of unknown cause: causal. (1)
(d) Planning tool (4 marks). A Gantt chart shows tasks, durations and dependencies against time and tracks progress. (4)
Show the model answer to Question 13
(a) Objectives (6 marks). Determine capital requirements. (1.5) Determine capital structure, including the debt-to-equity ratio. (1.5) Frame financial policies on cash control, lending and borrowing. (1.5) Use scarce funds as efficiently as possible at least cost for the best return. (1.5)
(b) LBO (4 marks). A model for an acquisition financed mostly by debt, typically about 90% debt and 10% equity. (2) It is risky because of the large interest cost, and bonds that are below investment grade; failure to meet cash flow leads to default. (2)
Pathfinder on Financial modelling, forecasting and planning tools
These are the ICAN Pathfinder questions that test this topic. ICAN owns the questions and the suggested solutions, so we point you to them and do not copy them. Open your Pathfinder, attempt each question under exam conditions, then check your answer against the notes above.
Neither the May 2014 nor the November 2014 Pathfinder sets a stand-alone question on this topic. Models and forecasts support valuation and acquisition questions. Worked examples 1 to 4 show how they are used.
Revise first: Worked examples
Questions ask you to match a model to a decision. See the section on types of financial model.
Revise first: Types of financial model
Source: ICAN Pathfinder, Strategic Financial Management. Diets covered: No stand-alone question in the May 2014 or November 2014 Pathfinder.
Spotted a mistake? Tell us and we will check it.
