Financial Management and Business Data Analytics · Data Analysis and Modelling

Data Modelling and Analytical Tools for CMA Intermediate

Updated 10 October 2026 · Fact-checked

Data modelling is the process of defining how data is structured, related and stored so it can be analysed. An analytical model then uses that data to explain, predict or decide. To answer exam questions, define the term, state its purpose, give a business example, and name a suitable tool such as Excel, Power BI or Python.

Understand Data Modelling and Analytical Tools

A data model describes the structure of data. It lists the items you hold (such as customer, invoice, product), what details each item has, and how items link to each other. Think of it as the blueprint of a database. A good blueprint avoids duplicates and makes retrieval easy.

An analytical model is different. It takes data and applies logic or mathematics to answer a business question. A regression that predicts sales from advertising spend is an analytical model. So is a break-even model or a sales forecast. The data model says how data is organised. The analytical model says what you do with it.

Data models are usually seen at three levels. A conceptual model shows the main entities and their relationships in business terms. A logical model adds attributes and keys. A physical model shows how tables are actually built in a database. Common structures are the relational model (tables linked by keys), hierarchical and network models. In a relational model, a primary key uniquely identifies a row, and a foreign key links one table to another.

Analytical models can be grouped by purpose. A decision model helps choose between alternatives, for example a decision tree or a make-or-buy comparison using expected values. A simulation model imitates a real situation with random inputs and runs it many times to see the range of outcomes. Monte Carlo simulation of project cash flows is a typical case. Predictive models forecast the future from past data. Prescriptive models recommend the best action.

Tools carry out this work. Excel handles tables, formulas, pivot tables, what-if analysis, Goal Seek, Solver and simple simulation. Power BI connects to many data sources, builds a data model with relationships, and produces interactive dashboards. Python is a programming language with libraries for cleaning data, statistics, machine learning and charts. Choose the tool by the size of data, the skill needed and the output wanted.

Key rules to remember

Data model vs analytical model: Data model = structure of data; Analytical model = logic applied to data to answer a question; The most common distinction asked in theory questions.
Relational link: Primary key (unique row identifier) ↔ Foreign key (reference in another table); Use this to explain how tables are related in Power BI or a database.
Expected value for a decision model: EV = Σ (Probability × Outcome); Choose the option with the highest EV for profit, or the lowest for cost, when the decision is based on expected value.
Simulation average: Average outcome = Σ (outcomes of all runs) ÷ number of runs; Simulation gives a range of results, not one certain answer.

How to solve Data Modelling and Analytical Tools questions

Use this order for any theory or short-case question on data modelling and tools.

1Read the verb. 'Define', 'distinguish', 'explain' and 'suggest a tool' need different answer shapes.
2Define the key term in one clear sentence.
3Say what it does in a business setting and why it matters.
4If it is a comparison, write two columns of points on the same basis: purpose, content, output, example.
5Give one short Indian business example, such as a retailer's sales data or a bank's loan data.
6Name the suitable tool (Excel, Power BI or Python) and give a reason linked to the data size or output.
7For a numerical decision model, set out the table, compute expected values, and state the decision.
8Close with a one-line conclusion.

Quickest way: Four-line answer frame

When to use it: Use this for 2-mark MCQs and 3 to 5 mark short notes when time is short.

Data model = structure and relationships. Analytical model = analysis, prediction or decision.
Decision model = chooses among options. Simulation model = repeated random trials.
Excel = small to medium data and quick what-if. Power BI = dashboards and linked data. Python = large data and advanced analytics.
For MCQs, spot the keyword: 'structure, keys, tables' means data model; 'forecast, optimise, simulate' means analytical model.

Common mistakes in Data Modelling and Analytical Tools

Treating data model and analytical model as the same thing.
Both use the word 'model' and both appear in analytics work.
Fix: Remember: data model is the blueprint of data; analytical model is the method that uses the data to answer a question.
Saying simulation gives the exact answer.
Students confuse a computed number with certainty.
Fix: Write that simulation produces a range of possible outcomes with likelihoods, based on assumed inputs.
Naming a tool without a reason.
Students memorise tool names only.
Fix: Always add why: Power BI for interactive dashboards, Python for large data and machine learning, Excel for quick calculations.
Mixing up primary key and foreign key.
Both are columns that look similar in a table.
Fix: Primary key is unique in its own table. Foreign key repeats in another table to point back to it.
Choosing the highest outcome instead of the highest expected value in a decision model.
Students ignore probabilities under time pressure.
Fix: Multiply each outcome by its probability, add them, and then compare options.
Giving a generic answer with no business example.
Students think definitions are enough.
Fix: Add one line of example, such as a retailer's sales forecast or a bank's customer table.

Worked examples

Example 1

Distinguish between a data model and an analytical model. Give one example of each from a retail business.

Show the solution

Purpose: a data model organises data. An analytical model uses data to explain, predict or decide.
Content: a data model has entities, attributes, keys and relationships. An analytical model has variables, assumptions and calculations.
Output: a data model gives a clean, linked structure for storage and retrieval. An analytical model gives a forecast, a recommendation or a insight.
Example of data model: a retailer links a Customer table, a Product table and a Sales table using customer ID and product ID as keys.
Example of analytical model: a regression using past data to forecast next month's sales from festival-season advertising spend.
Link: the analytical model works well only if the data model supplies clean, consistent data.

Answer: A data model is the blueprint of how data is structured and linked. An analytical model applies logic or statistics to that data to answer a business question. The first prepares the data; the second produces decisions.

Example 2

A firm must choose between Project X and Project Y. Project X gives a profit of ₹10,00,000 with probability 0.6 and ₹2,00,000 with probability 0.4. Project Y gives ₹8,00,000 with probability 0.7 and ₹3,00,000 with probability 0.3. Using a decision model based on expected value, which project should be chosen?

Show the solution

Identify the model: a decision model using EV = Σ (Probability × Outcome).
EV of X = (0.6 × 10,00,000) + (0.4 × 2,00,000).
0.6 × 10,00,000 = ₹6,00,000 and 0.4 × 2,00,000 = ₹80,000.
EV of X = ₹6,00,000 + ₹80,000 = ₹6,80,000.
EV of Y = (0.7 × 8,00,000) + (0.3 × 3,00,000).
0.7 × 8,00,000 = ₹5,60,000 and 0.3 × 3,00,000 = ₹90,000.
EV of Y = ₹5,60,000 + ₹90,000 = ₹6,50,000.
Compare: ₹6,80,000 is greater than ₹6,50,000.

Answer: Choose Project X, with an expected profit of ₹6,80,000 against ₹6,50,000 for Project Y. This rests on expected value alone and does not account for risk.

Exam tips

Expect MCQs asking you to match a term to its meaning, such as primary key, simulation or dashboard. Learn one-line definitions.
For tool questions, state both what the tool does and when you would choose it over the others.
In comparison answers, use the same heads for both sides so the examiner can award a mark per point.
For numerical decision problems, show the probability table and each product. Step marks are given even if the final figure slips.
Add a short example from an Indian business in every theory answer.

Practice questions from Data Analysis and Modelling

Data Modelling and Analytical Tools: frequently asked questions

What is data modelling in business analytics?

It is the process of defining what data a business holds, how each item is described and how items are related. The result is a structure, often a set of linked tables, that makes analysis reliable. It comes before the actual analysis.

What is the difference between a data model and an analytical model?

A data model describes how data is organised and linked. An analytical model uses data to explain, predict or choose an action. One is the structure; the other is the method applied on it.

Which tool should I use: Excel, Power BI or Python?

Use Excel for quick calculations, pivot tables and what-if analysis on moderate data. Use Power BI for linked data sources and interactive dashboards. Use Python for very large data, automation and machine learning.

What is a simulation model in simple words?

It is a model that imitates a real situation by using random values for uncertain inputs. You run it many times and study the spread of results. It helps you see the range of outcomes, not just one figure.