DDatalytIQsCA35P Topic Workspace
Menu
Sign in
← CA35P dashboardTOPIC 03 · FREE ACCESS

Core Application of Data Analytics

Financial reporting and financial-management analytics.

GUIDED LEARNING · APPROX. 180 MINUTES

Learn, practise and produce evidence

Each lesson combines examinable concepts, a worked example, an applied exercise and an answer guide.

PRACTICE FILES

Download the controlled source files

Open the CSV files in Excel, then save your working version as an XLSX workbook.

Financial statements datasetCSV · three-year income and position dataDownload ↓Investment appraisal templateCSV · cash flows and scenario assumptionsDownload ↓
3.1

Financial reporting, ratios and forecasts

90 minutes
not started

By the end, you should be able to:

  • Prepare common-size and trend analysis.
  • Calculate and interpret liquidity, profitability, efficiency and leverage ratios.
  • Build a controlled forecast with explicit assumptions.

Common-size analysis

Express statement lines as percentages of revenue or total assets to compare structure across periods or entities.

Ratio interpretation

Ratios become evidence only when benchmarked, trended and connected to operational drivers.

Forecast control

Assumptions, formulas, checks and outputs should be separated and reconciled.

WORKED EXAMPLE

Revenue grows 12% while receivable days rise from 42 to 61.

  1. Calculate growth and margin trends.
  2. Compute receivable days using average receivables.
  3. Forecast cash collection under base and recovery cases.
  4. Explain the liquidity implication.
=Average_Receivables/Credit_Revenue*365

Interpretation: Growth is not automatically healthy: deteriorating collection days can weaken cash conversion and increase financing pressure.

DATASET MISSION · Financial statements dataset

Compute 2025 gross margin and trade receivables as a percentage of 2025 revenue. Compare 2023–2025 receivables growth with revenue growth and identify one cash-collection risk.

Reveal source-based answer guide

2025 gross profit is KES 8,810,000 and gross margin ≈ 38.0%. Receivables/revenue ≈ 19.28%. Receivables rise KES 2.13m → 4.47m (≈109.9%) while revenue rises KES 18.5m → 23.184m (≈25.3%); investigate credit terms and ageing.

Further practice

Analyse the three-year statements, identify two improving and two deteriorating indicators, and prepare a one-year forecast.

A complete answer reconciles the statements, shows calculations, distinguishes profitability from liquidity and states assumptions behind the forecast.

Sign in to record progress
3.2

Financial management, NPV and scenarios

90 minutes
not started

By the end, you should be able to:

  • Model loan and investment cash flows.
  • Calculate NPV and IRR using correctly timed flows.
  • Test sensitivity and scenario interactions before recommending a decision.

Time value

Cash flows are discounted because timing and risk affect economic value.

Investment appraisal

NPV measures value created at the required return; IRR is the discount rate that produces a zero NPV.

Risk analysis

Sensitivity changes one driver; scenarios change coherent groups of assumptions.

WORKED EXAMPLE

A project requires KES 4.5 million and returns KES 1.35 million annually for five years at 12%.

  1. Place the initial outflow at time zero.
  2. Discount annual inflows at 12%.
  3. Calculate NPV and IRR.
  4. Test a 10% inflow reduction and a one-year delay.
=NPV(12%,Year1:Year5)+Initial_Investment

Interpretation: A positive base NPV supports acceptance, but the recommendation must disclose whether realistic downside assumptions reverse the result.

DATASET MISSION · Investment model dataset

Calculate base NPV at 12% and downside NPV at its supplied 14% rate, keeping the KES 4.5m outflow at year 0. State whether either scenario clears the required return, and verify your Excel formula does not discount year 0 twice.

Reveal source-based answer guide

For five equal year-end inflows, the 12% factor is about 3.604776 and the 14% factor is about 3.433081. Base NPV ≈ KES 366,448; downside NPV ≈ −KES 328,807. Apply each scenario’s own discount rate. The downside reverses the recommendation.

Further practice

Use the investment template to calculate NPV, IRR and payback under base, downside and upside scenarios, then recommend an action.

The answer must preserve time-zero treatment, use consistent units, identify the binding risk and connect the recommendation to the required return.

Sign in to record progress
CASE QUESTION BANK

Apply the method before the quiz

4 original case questions with answer guides. Self-check practice does not change your assessment record.

  1. Revenue is KES 10 million and receivables average KES 1.5 million. Estimate receivable days assuming all revenue is credit sales.

    Show worked answer

    1.5 / 10 × 365 = 54.75, about 55 days. Disclose the credit-sales assumption and use actual credit revenue where available.

  2. An investment costs KES 100,000 now and pays KES 60,000 at each year-end for two years. Calculate NPV at 10%.

    Show worked answer

    NPV = −100,000 + 60,000/1.10 + 60,000/1.10² = approximately KES 4,132.23. Time-zero cost is not discounted again.

  3. A base-case NPV is positive but a realistic downside reverses it. What must the recommendation disclose?

    Show worked answer

    Present both cases, the drivers and likelihood or decision thresholds; do not describe the positive base case as guaranteed value.

  4. A forecast balances only after an unexplained plug to cash. What is the first integrity check?

    Show worked answer

    Reconcile cash movements and linked statements, inspect formula references and assumptions, and identify the missing flow before accepting a balancing figure.

DATASET SELF CHECK

Calculate, enter and interpret

Use the downloadable CSV, then enter your result. These checks give immediate feedback and do not affect your scored quiz or learner record.

Financial statements dataset

What is gross profit for 2025?

Financial statements dataset

Trade receivables as a percentage of 2025 revenue (two decimals)?

AUTO-SCORED ASSESSMENT

Topic 3 knowledge check

Five scored questions · pass mark 80% · unlimited attempts. Your latest score is retained with the attempt history.

Sign in to take the quiz
ASSESSED PRACTICAL

Submit a financial analytics decision pack

Prepare ratio and trend analysis, a controlled forecast, investment appraisal and scenario-based recommendation using the supplied files.

Assignment deliverables

  • Common-size and trend statements with reconciled ratios
  • Assumptions-led forecast and time-zero NPV/IRR calculation
  • Base, downside and upside interpretation with recommendation

Use the downloadable practice data. Check source totals, state assumptions, label figures and remove personal data before sharing evidence.

20-mark rubric

Structure, controls and data integrity 5Analytical method and accuracy 5Interpretation of evidence 5Decision recommendation and presentation 5
Sign in to submit evidence