Introduction to Tools for Data Analytics: formula sheet

Key formulas

Descriptive analytics

Question: What happened?

Summarises past data using totals, averages, trends and dashboards.

Diagnostic analytics

Question: Why did it happen?

Finds causes through drill-down, variance analysis and correlation.

Predictive analytics

Question: What is likely to happen?

Uses historical patterns, such as regression and time series, to estimate future outcomes.

Prescriptive analytics

Question: What should we do?

Recommends an action using optimisation, simulation and decision models.

Order of increasing value and complexity

Descriptive → Diagnostic → Predictive → Prescriptive

Useful for answering comparison questions.

Analytics process sequence

Objective → Collection → Cleaning → Processing → Analysis → Interpretation → Reporting

No numerical formula applies. Write the steps in this order; cleaning always comes before analysis.

Big data 5 Vs

Volume, Velocity, Variety, Veracity, Value

Some texts list only 3 or 4 Vs. State which set you use and define each in one line.

Data types

Structured (tabular, fixed schema) | Semi-structured (tagged, flexible) | Unstructured (no schema)

Give one business example of each, such as a cost ledger, a JSON file and customer emails.

Common cleaning actions

Remove duplicates, correct errors, treat missing values, standardise formats, check outliers

Use these as a checklist when asked what cleaning involves.

Conditional sum

=SUMIF(range, criteria, sum_range)

Adds only the cells that meet one condition. Use SUMIFS for several conditions.

Lookup

=VLOOKUP(lookup_value, table, column_no, FALSE)

FALSE gives an exact match. Lookup value must be in the first column of the table. XLOOKUP has no such limit.

Logical test

=IF(test, value_if_true, value_if_false)

Used for flags such as accept or reject, or variance favourable or adverse.

Net present value

=NPV(rate, cash flows from year 1) + initial outflow

Excel NPV treats the first cash flow as one period away. Add the year 0 outflow separately.

Goal Seek inputs

Set cell = formula cell; To value = target; By changing cell = one input

The changing cell must be a typed input, not a formula. Only one variable changes.

Solver inputs

Objective cell (Max / Min / Value); Variable cells; Constraints

For linear problems choose Simplex LP and tick non-negative variables.

Pivot table areas

Rows, Columns, Values, Filters

Values can be Sum, Count, Average, Max or Min. Choose the summary that fits the question.

Chart selection rule

Trend over time → line; compare categories → bar; share of whole → pie (few parts); relationship → scatter; spread → histogram; bridge between two values → waterfall

Start from the question the viewer must answer, then choose the chart.

Variance for dashboard KPIs

Variance = Actual − Target; Variance % = (Actual − Target) ÷ Target × 100

Define favourable or adverse by the nature of the item: higher cost is adverse, higher profit is favourable.

Dashboard design rule

One audience + one purpose + few KPIs + comparison + exception flag + drill-down

A checklist, not a formula. Use it to structure descriptive answers.

BI workflow

Connect → Clean and transform → Model → Visualise → Publish and refresh

Same sequence in Power BI and Tableau, though tool names for the steps differ.

Tool-to-task rule: SQL

Data stored in databases → retrieve, filter, join, aggregate → SQL

Use SQL when the problem is getting the right data out of a database.

Tool-to-task rule: Python

Cleaning, automation, end-to-end pipelines, machine learning → Python

Choose Python when one tool must handle the whole workflow.

Tool-to-task rule: R

Statistical modelling, testing and statistical graphics → R

Choose R when the emphasis is on statistical analysis.

Basic SQL query structure

SELECT columns FROM table WHERE condition GROUP BY column HAVING condition ORDER BY column

WHERE filters rows before grouping; HAVING filters groups after aggregation.

Common SQL aggregate functions

SUM( ), AVG( ), COUNT( ), MIN( ), MAX( )

Used with GROUP BY to summarise, for example SUM of cost per department.

Machine learning split

Known outcome → supervised (regression, classification); no known outcome → unsupervised (clustering)

Regression predicts a number; classification predicts a category.

Quick revision

• Descriptive analytics asks what happened; diagnostic asks why it happened.
• Predictive analytics estimates what is likely to happen; prescriptive suggests what action to take.
• The process runs from defining the question to collecting, cleaning, analysing, visualizing and acting on data.
• Data cleaning comes before analysis, because poor data gives poor conclusions.
• Data can be structured, such as ledger tables, or unstructured, such as emails and documents.
• Sources can be internal, such as ERP and accounting systems, or external, such as market and industry data.
• Excel suits small and medium data, quick modelling and what-if analysis.
• Visualization and BI tools turn data into charts and dashboards for decision makers.
• SQL is used to query and retrieve data from databases.
• Python and R handle large datasets, statistics and advanced modelling.
• Choose the tool by the problem, data size and user skill, not by popularity.
• Analytics supports management judgement; it does not replace it.

Common mistakes

• Calling a variance report predictive analytics. Fix: A variance report explains past results. Finding why costs differed is diagnostic; just stating the variance is descriptive.
• Treating predictive and prescriptive as the same. Fix: Predictive estimates what may happen. Prescriptive recommends what to do about it. A demand forecast is predictive; the production plan chosen from it is prescriptive.
• Placing analysis before data cleaning Fix: Remember that dirty data gives wrong results. Cleaning always comes before analysis.
• Calling emails or PDFs structured because they are stored on a computer Fix: Judge by schema. If there are no fixed fields, it is unstructured.
• Using Goal Seek when the problem has several variables or constraints. Fix: Goal Seek changes one cell only and has no constraints. If limits or several decision variables appear, answer Solver.
• Choosing a cell with a formula as the Goal Seek changing cell. Fix: The changing cell must hold a typed value. Goal Seek overwrites it.
• Using a pie chart for many categories or for trends over time. Fix: Use pie only for a share of a whole with a few parts. Use line for time and bar for many categories.
• Writing a dashboard answer as a list of every possible KPI. Fix: Choose KPIs tied to the decision in the case and say why each matters.
• Saying SQL is a statistical or machine learning tool. Fix: Remember SQL is a query language for databases. It aggregates data but is not meant for building models.
• Claiming R or Python is better in all cases. Fix: Answer by task. Both can do statistics. Python is broader; R is more statistics-focused.

Exam tips

• For MCQs, find the question word (what, why, likely, should) and match it to the type before reading the options.
• In descriptive answers, always give a cost example; examiners reward application over definitions.
• When asked to compare types, use the same four points for each: question, technique, output, use.
• Do not present the four types as separate steps a firm must do in order; say they build on each other and are often used together.
• Link analytics to decisions such as pricing, cost control and product mix to connect with the wider paper.
• Write the process stages in order and add a one-line explanation for each. Marks are usually for sequence plus application.
• In case questions, name the actual data in the scenario for each stage rather than giving generic text.
• Prepare one business example each for structured, semi-structured and unstructured data and the 5 Vs.