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

Introduction to Excel

Efficient and controlled spreadsheet use for analysis and financial modelling.

GUIDED LEARNING · APPROX. 160 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.

Sales practice datasetCSV · 12 controlled transactionsDownload ↓Financial model templateCSV · assumptions and scenario structureDownload ↓
1.1

Excel productivity and controlled navigation

35 minutes
not started

By the end, you should be able to:

  • Structure a workbook so inputs, calculations and outputs remain traceable.
  • Use keyboard navigation and named ranges to reduce avoidable processing errors.
  • Apply basic workbook controls before beginning analysis.

Workbook architecture

Separate source data, assumptions, calculations and reporting into clearly named sheets. Avoid hard-coded values inside formulas.

Navigation discipline

Use Ctrl + Arrow, Ctrl + Shift + Arrow, Ctrl + Page Up/Page Down and Go To Special to inspect large workbooks efficiently.

Control checks

Document the data source, reporting period, units, refresh date and reconciliation totals in a visible control sheet.

WORKED EXAMPLE

A monthly sales workbook contains 240 transactions and a separate assumptions sheet.

  1. Convert the transaction range into an Excel Table named SalesData.
  2. Create named cells TaxRate and TargetMargin on the assumptions sheet.
  3. Add control totals for transaction count, total revenue and missing customer IDs.
  4. Use Freeze Panes and consistent number formats before analysis.
=COUNTBLANK(SalesData[Customer_ID])

Interpretation: A result above zero is an exception that must be resolved or disclosed before analysis.

DATASET MISSION · Sales practice dataset

Import the 12 transactions into a table. On a Control sheet record row count, missing Customer_ID count, the SUM of Revenue, and a formula check that Units × Unit_Price equals Revenue on every row. Name the first failing transaction if any.

Reveal source-based answer guide

Count 12; missing customer IDs 0; revenue KES 958,750. Every supplied Units × Unit_Price matches Revenue. Use a calculated exception column rather than trusting the printed total.

Further practice

Prepare the supplied sales dataset as a controlled workbook. Record the transaction count, missing-ID count and total revenue on a Control sheet.

Expected controls: 12 transactions, 0 missing customer IDs and total revenue of KES 958,750.

Sign in to record progress
1.2

Analytical tables, pivots and decision functions

55 minutes
not started

By the end, you should be able to:

  • Summarise transactional data with structured references and PivotTables.
  • Apply SUMIFS, COUNTIFS, XLOOKUP and IFERROR appropriately.
  • Explain the business meaning of a calculated result rather than merely reporting it.

Conditional aggregation

SUMIFS and COUNTIFS answer questions involving defined categories, periods or thresholds.

Reference lookups

XLOOKUP connects controlled reference data to transactions and provides an explicit not-found response.

Pivot analysis

PivotTables rapidly compare products, regions and periods; refresh status and source range must be controlled.

WORKED EXAMPLE

Management needs regional revenue and a list of transactions falling below the target margin.

  1. Create a PivotTable with Region in Rows and Revenue in Values.
  2. Use SUMIFS to reproduce one regional total as an independent check.
  3. Retrieve each product target using XLOOKUP.
  4. Create a Margin_Status field using IF to flag exceptions.
=SUMIFS(SalesData[Revenue],SalesData[Region],A2)

Interpretation: The independently calculated regional total should reconcile exactly to the corresponding PivotTable value.

DATASET MISSION · Sales practice dataset

Build a Region PivotTable and independently calculate Western revenue using SUMIFS. Flag each transaction with Margin_Percent below Target_Margin_Percent. Report region totals and all exception IDs.

Reveal source-based answer guide

Western KES 330,250; Nairobi KES 325,000; Nyanza KES 303,500. The six below-target IDs are T002, T004, T007, T008, T011 and T012. The regional totals must sum to KES 958,750.

Further practice

Produce a regional revenue table, identify the highest-revenue region and flag every transaction whose margin is below its target.

Western leads with KES 330,250. Six transactions fall below their respective target margins.

Sign in to record progress
1.3

Advanced formulas and auditable financial models

70 minutes
not started

By the end, you should be able to:

  • Build a driver-based model that separates assumptions from calculations.
  • Use scenario inputs without overwriting base data.
  • Apply formula checks and explain the effect of assumptions on outputs.

Driver-based modelling

Revenue, variable cost and fixed cost should be calculated from explicit assumptions that can be reviewed independently.

Scenario integrity

Base, downside and upside assumptions should be stored separately and selected through a controlled input.

Auditability

Formula consistency, balance checks, protection and documented assumptions make a model defensible.

WORKED EXAMPLE

A proposed service has an expected volume of 1,200 units, price of KES 2,500, variable cost of KES 1,450 and fixed cost of KES 820,000.

  1. Calculate revenue as volume multiplied by price.
  2. Calculate contribution as volume multiplied by price less variable cost.
  3. Deduct fixed cost to obtain operating profit.
  4. Calculate break-even units using fixed cost divided by contribution per unit.
=Fixed_Cost/(Unit_Price-Variable_Cost)

Interpretation: Break-even volume is 781 units when rounded up; the base forecast therefore provides a 419-unit margin of safety.

DATASET MISSION · Financial model template

Fill the base and downside output rows from the supplied volume, price, variable cost and fixed cost. Calculate profit and rounded-up break-even units for each scenario; explain the change in margin of safety.

Reveal source-based answer guide

Base profit = 1,200 × (2,500 − 1,450) − 820,000 = KES 440,000; break-even = 781 units; margin of safety = 419 units. Downside profit = 1,080 × (2,500 − 1,537) − 820,000 = KES 220,040; break-even = 852 units; margin of safety = 228 units.

Further practice

Use the model template to evaluate a 10% fall in volume and a 6% rise in variable cost. State whether the service remains profitable and identify the more influential risk.

The service remains profitable under either isolated scenario. The 10% volume fall has the larger adverse effect in the supplied base case.

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. A workbook has 1,200 sales rows. A PivotTable reports KES 4.8 million, while SUMIFS on the complete source reports KES 5.1 million. What should you investigate first?

    Show worked answer

    Check the PivotTable source range and refresh state, then filters, blank rows and numeric data types. Reconcile the row count and total before presenting either figure.

  2. Fixed cost is KES 820,000, price is KES 2,500 and variable cost is KES 1,450 per unit. Calculate break-even units.

    Show worked answer

    Contribution per unit is KES 1,050. Break-even is 820,000 / 1,050 = 780.95, rounded up to 781 units.

  3. A lookup returns #N/A for three new product codes. What is the sound control response?

    Show worked answer

    Flag and investigate unmapped codes against the controlled product master. Do not replace errors with zero revenue or silently exclude those records.

  4. What do you record when a two-input Excel data table changes price and volume?

    Show worked answer

    Identify the output formula, both input cells, units and baseline, then compare the sensitivity grid with the base model and document the decision threshold.

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.

Sales practice dataset

What is the sum of Revenue for all 12 transactions?

Sales practice dataset

What is Western region revenue?

AUTO-SCORED ASSESSMENT

Topic 1 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 your controlled Excel decision workbook

Use the sales dataset or model template. Explain your workbook structure, controls, principal finding and management recommendation.

Assignment deliverables

  • Controlled workbook with source, assumptions, calculations and output sheets
  • Pivot and independent reconciliation, formula checks, scenario table
  • One-page interpretation with break-even and management 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