Business Analytics and Decision Support

Introduction

Business intelligence becomes valuable when information is converted into a decision. A Power BI report is not merely a collection of charts; it is an analytical instrument used to identify patterns, explain performance, evaluate alternatives and recommend action. Business analytics extends descriptive reporting by asking increasingly demanding questions:

• What has happened?
• Why did it happen?
• What is likely to happen?
• What should the organisation do?

This chapter develops practical methods for trend, variance, profitability, segmentation, cohort, Pareto, what-if and scenario analysis. It also explains how these analyses support finance, sales, marketing, operations and human resources. Emphasis is placed on interpretation, assumptions, data quality, responsible communication and the distinction between evidence and managerial judgement.

Learning Objectives

By the end of this chapter, you should be able to:

1. Distinguish descriptive, diagnostic, predictive and prescriptive analytics.
2. Conduct trend and variance analysis using appropriate measures and comparisons.
3. Analyse profitability, customer segments, cohorts and Pareto concentration.
4. Build what-if parameters and compare business scenarios in Power BI.
5. Interpret analytical results responsibly and communicate recommendations.
6. Recognise limitations, risks, ethical concerns and governance requirements.

1. Business Analytics as Decision Support

Business analytics is the systematic use of data, statistical reasoning, business knowledge and technology to improve decisions. Decision support does not replace managerial responsibility. Instead, it provides structured evidence, makes assumptions visible and helps decision-makers compare options.

Power BI is particularly effective when a well-designed semantic model connects transaction data with dimensions such as Date, Product, Customer, Employee, Region and Channel. Measures should normally be written so that calculations respond correctly to filters. For example, a basic revenue measure may be represented as:

Here, i indexes transaction lines, Quantity is measured in units and Unit Price is measured in currency per unit. The formula assumes that each transaction line has a valid quantity and price and that returns, discounts and tax treatment have been defined consistently. In Power BI, the measure must also respect filter context.

A useful decision-support cycle is:

1. Define the decision and its time horizon.
2. Establish a trusted metric definition.
3. Explore performance and identify exceptions.
4. Investigate likely drivers.
5. Compare feasible actions.
6. Communicate a recommendation, uncertainty and owner.
7. Monitor results after implementation.

The final step is essential. A recommendation is not successful merely because it appears reasonable; its effect should be measured against the intended outcome.

2. Trend Analysis

Trend analysis examines movement over time. It may identify growth, decline, seasonality, cyclical behaviour or a structural change. A line chart of monthly revenue is useful, but interpretation should also consider volume, price, product mix, calendar effects and data completeness.

2.1 Core measures

Absolute change between a current period and a comparison period is:

where Xₜ is the value in the current period, Xₜ₋ₖ is the value k periods earlier, and ∆X has the same unit as X. Percentage change is:

This measure is applicable only when the comparison value is non-zero and meaningful. If the prior value is zero, percentage growth is undefined; the report should show “not meaningful” rather than an arbitrary percentage.

For a value changing from an initial amount V₀ to a final amount Vₙ over n periods, compound annual growth rate is:

CAGR assumes a positive initial value and describes a smoothed rate. It does not imply that actual growth was constant in each period.

A moving average reduces short-term fluctuation:

Here, m is the number of periods in the window. A three-month moving average, for example, should not be interpreted as an independent observation; it overlaps neighbouring windows.

2.2 Worked example

A retailer records quarterly sales of £100,000 in Quarter 1 and £115,000 in Quarter 2. The absolute increase is:

∆X = £115,000 − £100,000 = £15,000

The percentage increase is:

(£15,000 ÷ £100,000) × 100 = 15%

If Quarter 3 sales are £121,900, the two-quarter growth from Quarter 1 is 21.9%. However, management should not conclude that demand has permanently strengthened without checking prices, promotions, product availability and seasonality.

2.3 Power BI implementation

A trend report commonly includes:

• a continuous or categorical Date table;
• a line chart for the measure over time;
• comparison measures such as prior year and year-to-date;
• slicers for region, product and channel;
• a tooltip showing volume, average price and margin;
• an annotation for unusual events such as a price change or supply interruption.

Avoid sorting months alphabetically. Use a month-number column to sort month names, and mark a complete Date table where appropriate. A trend may be misleading if the latest period is incomplete, so the report should identify whether it compares complete with incomplete periods.

3. Variance Analysis

Variance analysis compares actual performance with a reference point, such as budget, forecast, prior year or standard cost. It is a control and learning mechanism rather than an automatic explanation of performance.

For an actual value A and budget B, the absolute variance is:

The percentage variance is:

The sign must be interpreted in context. A positive revenue variance is generally favourable, whereas a positive cost variance may be unfavourable. Reports should therefore include a favourable/unfavourable rule appropriate to each metric.

For sales revenue, a simple price-volume decomposition can be used when quantity and price data are available:

where subscripts a and b denote actual and budget, Q is quantity and P is price. A basic decomposition is:

These three effects sum to total revenue variance. The decomposition assumes that quantity and price are sufficiently comparable and that the chosen allocation of the interaction term is understood. Alternative methods allocate interaction differently.

Worked example

Budget quantity is 1,000 units at £20 each. Actual quantity is 1,100 units at £19 each.

Budget revenue = £20,000.

Actual revenue = £20,900.

Total variance = £900 favourable.

Volume effect = (1,100 − 1,000) × £20 = £2,000.

Price effect = (£19 − £20) × 1,000 = −£1,000.

Interaction = (100) × (−£1) = −£100.

The effects total £900. The organisation sold more units, but at a lower price. A management conclusion should therefore examine whether the additional volume generated acceptable margin.

4. Profitability Analysis

Revenue alone is not a sufficient measure of commercial performance. Profitability analysis connects sales with variable costs, fixed costs, discounts, returns, service costs and customer or product economics.

Gross profit is:

Gross margin percentage is:

The measure assumes consistent recognition of revenue and cost of goods sold. If revenue is zero, margin percentage is undefined.

Contribution margin focuses on the amount available to cover fixed costs and profit:

A product may have high revenue but low contribution because of delivery, commission, returns or promotional costs. Power BI can expose this by modelling costs at the appropriate grain. Allocated overhead should not be presented as precise customer profitability unless the allocation method is defensible.

A profitability matrix may compare products by revenue, gross margin percentage and contribution. The matrix should support questions such as:

• Which products generate volume but destroy margin?
• Which products have modest sales but strong contribution?
• Are discounts concentrated in particular channels?
• Does a customer remain profitable after fulfilment and service costs?

Profitability analysis supports pricing, assortment, promotion and customer-service decisions, but it should not encourage abandoning customers solely on the basis of incomplete short-term cost data.

5. Segmentation Analysis

Segmentation divides a population into groups that are meaningful for a decision. Segments may be based on geography, industry, product interest, recency, frequency, monetary value, profitability or employee characteristics.

A valid segment should be:

• measurable using reliable data;
• sufficiently substantial for action;
• distinct in behaviour or need;
• accessible through an appropriate intervention;
• stable enough for the decision horizon;
• reviewed for fairness and unintended exclusion.

RFM analysis is a common customer framework:

• Recency: how recently a customer purchased;
• Frequency: how often the customer purchased;
• Monetary value: how much the customer spent.

Scores may be assigned using quantiles, but quantile boundaries can change over time. A customer labelled “high value” in one period may not remain so. The method must document the reference period, treatment of returns and inactive customers, and minimum transaction requirements.

Segmentation is descriptive unless it is validated. A segment with low purchases may contain both disengaged customers and new customers. Marketing action should therefore test whether the proposed intervention changes behaviour rather than assuming that a label explains motivation.

6. Cohort Analysis

A cohort is a group sharing a starting event during a defined period, such as the month of first purchase, employee hire month or subscription start month. Cohort analysis compares groups over equivalent ages rather than simply calendar dates.

For a customer cohort, retention at age t can be calculated as:

The definition of “active” must be explicit. It could mean at least one purchase, a login, a paid subscription or another event. Cohort size should be large enough for meaningful interpretation, and recent cohorts may be incomplete because they have had less time to mature.

A Power BI cohort matrix commonly places cohort month on rows and months since acquisition on columns. Conditional formatting can reveal patterns, but colour should not substitute for numerical values or accessible labels.

Cohort analysis is useful for evaluating acquisition channels, onboarding programmes, product launches and employee retention. It can distinguish a fall in total retention caused by recent acquisition growth from a genuine deterioration in customer experience.

7. Pareto and Concentration Analysis

Pareto analysis ranks items by a measure and calculates their cumulative contribution. It is often associated with the 80/20 observation, but the 80/20 relationship is not a universal law. The actual concentration must be calculated for the organisation being studied.

If items are ordered from largest to smallest value vᵢ, cumulative contribution at rank r is:

The measure requires a defined population, ranking metric and treatment of negative values. Returns or negative margins can complicate cumulative percentages.

A Pareto chart combines descending bars with a cumulative line. It may reveal that 15% of customers generate 70% of contribution, or that a small number of defect categories account for most incidents. Concentration creates both opportunity and risk: key-account loss, supplier dependency and operational fragility should be considered alongside prioritisation benefits.

8. What-If and Scenario Analysis

What-if analysis changes an input to observe its effect on an output. Scenario analysis compares coherent sets of assumptions, such as conservative, expected and expansion cases.

A Power BI what-if parameter can represent a discount rate, price increase, staffing level or advertising budget. For example, adjusted revenue under a price change may be represented as:

This formula assumes quantity remains constant. That assumption is often unrealistic. If an estimated price elasticity e is available, a simplified demand response may be expressed as:

Elasticity is normally negative for ordinary demand, although exceptions exist. The relationship is an estimate, not proof of causation, and is most defensible over a range similar to the historical data used to estimate it.

A more complete scenario model may calculate:

Every scenario should document:

• baseline period and data source;
• changed assumptions;
• unchanged assumptions;
• constraints, such as capacity or budget;
• expected range and uncertainty;
• decision owner and review date.

A slider that changes a chart is not, by itself, a forecast. Scenario outputs should be presented as conditional statements: “If price increases by 5% and quantity falls by 3%, estimated contribution is…”

9. Communicating Insights and Recommendations

An effective analytical communication moves from evidence to implication to action:

1. Observation: State what the data shows.
2. Interpretation: Explain the most plausible drivers and alternatives.
3. Implication: Describe why the result matters.
4. Recommendation: Specify action, owner, timing and success measure.
5. Qualification: State uncertainty, limitations and risks.

For finance, communicate budget drivers, cash implications and margin quality. For sales, distinguish pipeline, orders, revenue and realised margin. For marketing, connect campaign exposure with qualified outcomes rather than reporting clicks alone. For operations, link service levels, capacity, defects and cost. For human resources, protect confidentiality and avoid treating workforce metrics as a mechanical ranking system.

Visual design should support the decision. Use a clear title stating the metric, population and period. Label units, use consistent colour meanings, provide accessible contrast and avoid unnecessary three-dimensional effects. A dashboard should make the important exception visible without encouraging users to infer causation from a correlation.

10. Applied Case: Northstar Homeware (Illustrative)

Background

Northstar Homeware is a fictional online retailer selling household products through its website and two marketplaces. Management reports that revenue grew during the year but operating profit did not increase proportionally.

Problem

The leadership team wants to decide whether to increase digital advertising, raise prices, reduce discounts or focus on retaining existing customers. The available data contains order lines, product costs, advertising spend, customer IDs, dates, channels and returns.

Analysis

The BI team first validates definitions. Revenue is net of recorded returns; gross profit excludes advertising; contribution includes payment, fulfilment and campaign costs. The team creates a complete Date table and checks that cancelled orders are not counted as sales.

Trend analysis shows annual revenue growth of 12%, but the latest quarter is incomplete. A like-for-like comparison of complete months shows 8% growth. Variance analysis shows that actual revenue exceeded budget by £180,000, while contribution exceeded budget by only £35,000 because average discounting increased.

Product profitability analysis identifies a high-volume storage range with a gross margin of 18%, compared with a portfolio average of 31%. After delivery and return costs, its contribution is close to zero. A Pareto analysis shows that 22% of customers generate 76% of contribution, creating both a retention opportunity and concentration risk.

Cohort analysis finds that customers acquired through a discount-heavy campaign have strong first-month sales but substantially lower three-month retention than customers acquired through content marketing. The result is associative, not proof that the campaign caused poor retention.

A scenario model compares three options:

Recommendation

The team recommends a controlled balanced pilot. It reduces discounts on products with low contribution, directs retention communications to high-contribution customers and tests advertising by channel rather than increasing total spend immediately. The pilot has a defined owner, a six-week review point and success measures: incremental contribution, repeat purchase rate, return rate and customer complaints.

Outcome and learning points

After the pilot, management should compare treatment and control groups where feasible, while accounting for seasonality and customer differences. The case demonstrates that revenue growth can conceal weak profitability, that cohort and Pareto analyses can reveal different priorities, and that scenario analysis is useful only when assumptions and evaluation criteria are explicit.

11. Limitations, Ethics and Governance

Analytical results are constrained by data quality, measurement design and context. Common problems include missing transactions, duplicate customers, inconsistent product hierarchies, changing definitions and survivorship bias. A dashboard may appear precise while being based on an incomplete population.

Responsible practice includes:

• documenting metric definitions and data lineage;
• restricting sensitive data through role-based access;
• aggregating or pseudonymising personal information where possible;
• applying retention and deletion policies;
• testing reports for discriminatory impacts;
• separating correlation from causation;
• presenting uncertainty and incomplete periods clearly;
• retaining an audit trail for important decisions;
• providing human review for high-impact employment or customer decisions.

In human resources, individual-level absence, performance or attrition analysis may expose sensitive information and create unfair labelling. In marketing, profiling should respect applicable privacy and consent requirements. In finance, assumptions affecting forecasts should be traceable and approved. Governance is not an obstacle to analytics; it is the basis for trustworthy analytics.

AI features may assist with natural-language summaries, anomaly detection or forecasting, but generated explanations require human verification. AI can reproduce historical bias, confuse correlation with causation or disclose confidential information through poorly controlled prompts. It should support, not obscure, accountability.

12. Best Practices for Power BI Decision Support

1. Begin with a decision, not a visual.
2. Use a star-schema-oriented semantic model where appropriate.
3. Define measures centrally and avoid inconsistent ad hoc calculations.
4. Distinguish actual, budget, forecast and scenario values visually and numerically.
5. Show denominators, units, date coverage and population scope.
6. Validate totals against authoritative source systems.
7. Use drill-through to support investigation without overcrowding the main page.
8. Test calculations at different filter levels.
9. Include data refresh time and known limitations.
10. Pair every major recommendation with an owner, action and success metric.
11. Monitor outcomes after implementation.
12. Review access, privacy and fairness controls regularly.

Chapter Summary

Business analytics transforms BI data into evidence for decisions. Trend analysis identifies movement over time, while variance analysis compares actual performance with a defined reference. Profitability analysis goes beyond revenue by incorporating cost and contribution. Segmentation groups entities for targeted action; cohort analysis compares groups according to the age of a shared starting event; and Pareto analysis reveals concentration and prioritisation opportunities. What-if parameters and scenario models allow decision-makers to examine conditional alternatives, but their assumptions must be explicit.

Power BI provides effective tools for these analyses through measures, dimensions, filters, visual interactions, parameters and semantic models. Nevertheless, a visual result is not automatically an explanation or a recommendation. Responsible decision support requires validated definitions, awareness of uncertainty, ethical governance, clear communication and post-decision evaluation.

Key Terms

Business analytics: The use of data, methods and business knowledge to improve decisions.

Decision support: Information and analysis designed to assist a decision-maker.

Trend analysis: Examination of movement and patterns over time.

Variance: The difference between an actual value and a comparison value.

CAGR: A smoothed compound growth rate over multiple periods.

Moving average: An average calculated over a rolling window of observations.

Profitability: The capacity to generate profit after relevant costs.

Contribution margin: Revenue less variable costs.

Segmentation: Division of a population into meaningful groups.

RFM: Recency, frequency and monetary value customer analysis.

Cohort: A group sharing a defined starting event or period.

Retention rate: The proportion of a cohort remaining active at a specified age.

Pareto analysis: Ranking items and calculating their cumulative contribution.

What-if analysis: Evaluation of output changes when an input is varied.

Scenario analysis: Comparison of outcomes under coherent sets of assumptions.

Elasticity: The responsiveness of one variable to a change in another.

Filter context: The active selections that determine a Power BI measure’s calculation.

Data lineage: The documented origin and transformations of data.

Governance: Policies and controls supporting reliable, secure and responsible analytics.

Correlation: Statistical association that does not by itself establish causation.

Review Questions

Short Answer Questions

1. Distinguish descriptive, diagnostic, predictive and prescriptive analytics.
2. Why can a positive variance be favourable for revenue but unfavourable for cost?
3. State two conditions that make a customer segment useful for decision-making.
4. What is the purpose of cohort analysis?
5. Why should a scenario output be described as conditional rather than certain?

Long Answer Questions

1. Explain how a Power BI report could combine trend, variance and profitability analysis to investigate disappointing operating profit.
2. Describe the construction and interpretation of an RFM segmentation model, including its limitations.
3. Explain how Pareto analysis can support operational prioritisation while also revealing concentration risk.
4. Discuss the assumptions and governance requirements of a what-if pricing model.
5. Develop a framework for communicating an analytical recommendation to senior management.

Analytical / Application Questions

1. A product has budget sales of 2,000 units at £15 and actual sales of 2,300 units at £14. Calculate the total revenue variance and decompose it into volume, price and interaction effects using the method in this chapter. Interpret the result.
2. Design a Power BI cohort report for a subscription business. Specify the cohort definition, activity definition, matrix structure, measures and two limitations.
3. A marketing campaign produces high first-month revenue but low three-month retention. Propose an analysis combining segmentation, cohort analysis and scenario testing, and state the evidence required before recommending more campaign spending.

Multiple Choice Questions with Answers

Question 1

Which analytical type asks, “What should the organisation do?”

A. Descriptive analytics

B. Diagnostic analytics

C. Predictive analytics

D. Prescriptive analytics

Correct Answer: D. Prescriptive analytics

Question 2

A measure increases from £80,000 to £92,000. What is the percentage change?

A. 10%

B. 12%

C. 15%

D. 20%

Correct Answer: C. 15%

Question 3

Which measure is generally most appropriate for evaluating the amount available to cover fixed costs?

A. Revenue

B. Contribution margin

C. Order count

D. Market share

Correct Answer: B. Contribution margin

Question 4

In cohort analysis, customers are commonly grouped according to:

A. Their dashboard colour

B. Their first purchase or other shared starting event

C. Their alphabetical customer name

D. The order in which they appear in a report

Correct Answer: B. Their first purchase or other shared starting event

Question 5

What does a Pareto analysis primarily calculate?

A. A probability distribution only

B. A product’s unit price

C. Ranked values and their cumulative contribution

D. The average age of customers

Correct Answer: C. Ranked values and their cumulative contribution

Question 6

Budget quantity is 1,000 units at £20. Actual quantity is 1,100 units at £19. What is the price effect using the basic decomposition in this chapter?

A. £2,000 favourable

B. £1,000 favourable

C. £1,000 unfavourable

D. £100 unfavourable

Correct Answer: C. £1,000 unfavourable

Question 7

Which statement about CAGR is correct?

A. It proves growth was identical in every period

B. It is a smoothed rate over multiple periods

C. It can always be calculated when the initial value is zero

D. It measures only the final period’s change

Correct Answer: B. It is a smoothed rate over multiple periods

Question 8

What is a key limitation of a what-if parameter?

A. It cannot change any displayed measure

B. It automatically proves causation

C. Its result depends on the assumptions built into the model

D. It can be used only for dates

Correct Answer: C. Its result depends on the assumptions built into the model

Question 9

Which practice most improves responsible analytical communication?

A. Hide the denominator to simplify the chart

B. Present correlation as proof of causation

C. State scope, assumptions, limitations and uncertainty

D. Remove all negative results

Correct Answer: C. State scope, assumptions, limitations and uncertainty

Question 10

Why may a positive revenue variance still require investigation?

A. Revenue is never relevant to profitability

B. It may have resulted from lower prices or higher discounts

C. Positive variances are always errors

D. Revenue cannot be compared with a budget

Correct Answer: B. It may have resulted from lower prices or higher discounts