Excel Data Modelling and Simulation Training Course

Duration 5 days 10 modules · 33 contact hours
Level Intermediate Certificate on completion
Who attends Business Analysts, Financial Analysts and others
Fee from USD 1,100 KSh 95,000 for Kenyan organisations
Where 52 cities + online See dates by city
Next start 7 Nov 2026 Course code SD-DA-056

Before you join: Participants should be comfortable entering formulas, using relative and absolute references, sorting and filtering data, and creating basic charts in Excel.

Dates & prices

Two start dates a month in every city, and live online on weekdays or weekends. Fees are per participant; classroom fees include tuition, materials, lunch and refreshments, and the certificate.

Suggest a date or city

Showing the next six months. Later dates, other cities and private deliveries for your team are available on request.

Course overview

Business decisions often depend on spreadsheets that combine operational data, assumptions, forecasts and financial calculations. When models are poorly structured, users struggle to trace figures, test alternative assumptions or explain why a recommendation changed. This course addresses that problem by teaching participants to build controlled Excel models for forecasting demand, assessing investments, allocating scarce resources and quantifying uncertainty.

Participants work through the full modelling process: defining decision questions, preparing source data, designing workbook architecture, analysing patterns, forecasting outcomes and building driver-based financial models. They use Excel Tables, Power Query, PivotTables, regression, Data Tables, Scenario Manager, Goal Seek, Solver and Monte Carlo methods. The course also covers formula auditing, reconciliation, version control and clear management reporting so that models can withstand review.

Teaching combines focused instructor briefings with guided case work, group analysis and spreadsheet-based simulations. Cases reflect decisions such as sales forecasting, cash planning, project appraisal, capacity allocation and service-demand uncertainty. Participants leave with a reusable Excel modelling workbook structure, an assumptions register, model-control checklist and an application plan for a live work problem.

The course suits professionals who already use Excel for reporting or analysis and now need to create models that support operational, financial or strategic decisions. It is particularly relevant where decisions must be explained to finance teams, senior managers, auditors, project sponsors or operational owners.

What you will be able to do

  • Structure auditable Excel models using Tables, named ranges, assumption registers and control checks
  • Prepare source data with Power Query, data-quality tests and reconciliation totals
  • Analyse business data using PivotTables, descriptive statistics, charts and correlation analysis
  • Build driver-based forecasting and financial models from operational and financial assumptions
  • Evaluate uncertainty through sensitivity analysis, scenarios, break-even analysis and Goal Seek
  • Optimise resource-allocation decisions using Excel Solver, linear programming and integer constraints
  • Simulate uncertain outcomes using probability distributions, random sampling and Monte Carlo trials
  • Validate and communicate decision-ready models through auditing, documentation and management dashboards

Benefits of attending

For you

  • Produce spreadsheet models that managers can review, challenge and reuse
  • Calculate forecast ranges instead of presenting unsupported single-point estimates
  • Assess investment, pricing and capacity choices with visible assumptions
  • Use Solver to recommend feasible allocations under real operational constraints
  • Explain model risk, uncertainty and limitations with greater professional credibility

For your organisation

  • Reduce spreadsheet errors through documented controls, checks and reconciliation routines
  • Improve forecast quality by separating drivers, assumptions and actual performance
  • Compare operational and financial scenarios before committing resources
  • Identify capacity, budget and service trade-offs using constrained optimisation
  • Create maintainable decision models that can be updated by more than one analyst

Course modules

10 modules over 5 days

Participants can define a decision problem and create a controlled workbook structure.

  • Decision questions translated into model inputs, outputs, constraints and assumptions
  • Scope statements distinguish operational decisions from reporting requirements and data requests
  • Structured workbook architecture separates inputs, calculations, outputs and control checks
  • Excel Tables create expandable ranges, readable formulas and controlled references
  • Named ranges and consistent units reduce formula ambiguity and review time
  • Assumption registers record source, owner, date, rationale and update frequency

Participants can prepare source data and establish checks before modelling.

  • Importing CSV files and workbook extracts with controlled data types
  • Power Query steps filter, split, merge and append source tables
  • Data quality checks identify duplicates, missing values, outliers and invalid codes
  • Reference lists standardise product, customer, cost-centre and date classifications
  • Date and text functions repair inconsistent period labels and identifiers
  • Reconciliation totals confirm transformed records match approved source extracts before modelling
  • Data dictionaries define fields, calculations, permitted values, ownership and rules

Participants can identify patterns, variation and relationships relevant to model assumptions.

  • PivotTables summarise volume, value, margin and variance by business dimension
  • Descriptive statistics calculate mean, median, standard deviation and percentiles accurately
  • Histograms and box plots reveal distribution shape, spread and extreme values
  • Correlation analysis distinguishes association from causation before model specification decisions
  • Scatter plots test linear relationships, segment differences and data anomalies
  • Control charts separate common-cause variation from exceptional process movement signals

Participants can build and assess short-term forecasts using time-series and regression methods.

  • Regression models estimate demand drivers using coefficients, residuals and R-squared
  • FORECAST.ETS produces seasonal projections with confidence intervals and timeline settings
  • Moving averages smooth volatile series before short-term operational forecasting use
  • Seasonality indices adjust baseline forecasts for monthly or quarterly patterns
  • Forecast accuracy compares MAE, RMSE and MAPE against actual results
  • Rolling forecasts incorporate new actuals and document changed assumptions promptly

Participants can build linked financial models for planning and investment decisions.

  • Three-statement model links profit and loss, balance sheet and cash flow statements
  • Driver-based revenue schedules connect volumes, prices, mix and collection timing
  • Working-capital calculations model receivables, inventory, payables and cash conversion cycles
  • Depreciation and debt schedules feed non-cash charges and financing costs
  • NPV, IRR and payback assess investment proposals under stated assumptions
  • Scenario cases compare base, upside and downside financial outcomes consistently

Participants can test how uncertain assumptions affect model outputs and decisions.

  • One-variable and two-variable Data Tables map assumption sensitivity across outputs
  • Scenario Manager stores named cases for controlled comparison and reporting
  • Tornado charts rank assumptions by influence on selected decision metrics
  • Break-even analysis identifies volumes, prices or costs required for viability
  • Goal Seek solves single-input targets such as margin or cash balance requirements
  • Switches and flags control scenario selection without rewriting formulas

Participants can formulate and solve constrained resource-allocation models in Excel.

  • Solver formulations define objective cells, changing variables and operational constraints
  • Linear programming allocates scarce capacity across products, projects or service queues
  • Integer constraints represent whole people, vehicles, batches and facility choices
  • Binary decisions model yes-or-no selections, fixed costs and policy rules
  • Solver reports diagnose binding constraints, slack and marginal values afterwards
  • Feasibility checks test whether recommended solutions satisfy business rules

Participants can model uncertain inputs and quantify ranges of possible outcomes.

  • Probability distributions represent uncertain demand, costs, durations and failure rates
  • RAND and inverse distribution functions generate repeatable random input samples
  • Monte Carlo trials estimate outcome ranges, percentiles and downside probabilities
  • Correlation assumptions prevent implausible combinations of related simulated input values
  • Risk measures report expected value, Value at Risk and shortfall probability clearly
  • Simulation outputs distinguish model uncertainty from controllable operational variation drivers

Participants can review model integrity and document models for controlled handover.

  • Formula auditing traces precedents, dependents and error values systematically throughout
  • Independent recalculation tests key outputs using alternative methods or samples
  • Balance checks reconcile model sections, subtotals and linked statements daily
  • Error traps use IFERROR, data validation and exception flags appropriately
  • Version control records model changes, approvals, release dates and owners
  • Documentation explains purpose, assumptions, logic, limitations and refresh steps clearly

Participants can present model findings clearly and plan controlled use of their models.

  • Management dashboards combine decision metrics, drivers, variances and scenario results
  • Waterfall charts explain bridge movements between actual, forecast and target
  • Executive narratives state recommendation, uncertainty, trade-offs and required actions
  • Model handover packs identify owners, refresh cycles, controls and dependencies
  • Decision logs record assumptions accepted, rejected, deferred or requiring escalation
  • Application plans schedule model improvements, stakeholder reviews and first use

Exercises and examples are adapted to each cohort's sectors and roles.

What you take back to work

  1. Excel model architecture template
  2. Assumptions register template
  3. Data-quality and reconciliation checklist
  4. Driver-based forecasting model
  5. Sensitivity and scenario analysis template
  6. Model validation and handover checklist

Who should attend

  • Business Analysts — build traceable models for operational and investment decisions
  • Financial Analysts — improve forecasts, cash models and investment appraisals
  • Planning and Performance Managers — test scenarios behind budgets, targets and resource plans
  • Project Managers — assess schedule, cost and risk assumptions in project business cases
  • Operations Managers — allocate capacity and evaluate demand, inventory and service trade-offs
  • Data Analysts — turn prepared datasets into usable decision models and simulations

Skills and tools covered

Excel model designForecasting analysisFinancial modellingSensitivity analysisSolver optimisationMonte Carlo simulation

Tools and standards

Microsoft Excel, Power Query, Excel Solver, Monte Carlo simulation

How the course runs

Training methodology

The course uses short instructor briefings followed by guided Excel build work, case analysis, group discussion and simulation tasks. Participants develop model sections step by step, then inspect assumptions, formulas and outputs as a reviewer would. Cases include demand forecasting, capital appraisal, cash planning, capacity allocation and uncertain project outcomes. Trainers use realistic datasets and adapt examples to the cohort's sector. The final sessions focus on model handover, management communication and an application plan for a participant's own modelling requirement.

Requirements and prerequisites

Participants should be comfortable entering formulas, using relative and absolute references, sorting and filtering data, and creating basic charts in Excel. Experience with functions such as SUM, IF, XLOOKUP or INDEX/MATCH is expected, although advanced formula construction is not assumed. Participants should understand the business measures used in their role, such as revenue, cost, volume, service levels or project duration. Prior knowledge of statistics, programming, financial modelling, optimisation or simulation is not required. The course does not require VBA, macros, Power BI or prior use of Solver.

A typical training day

08:30 – 10:30First session
10:30 – 10:45Refreshment break
10:45 – 12:30Second session
12:30 – 13:30Lunch and networking
13:30 – 15:00Third session
15:00 – 15:15Refreshment break
15:15 – 16:30Workshop and daily review

Live online deliveries follow the same structure in East Africa Time, with shorter screen blocks and longer breaks.

What the fee includes

  • Instruction by a practitioner facilitator
  • Full course workbook and materials
  • Exercise files, templates and case studies
  • Refreshments and lunch on classroom deliveries
  • Certificate of completion
  • Post-course application plan

Frequently asked questions

You should already be comfortable with formulas, cell references, sorting, filtering and basic charts. You do not need prior experience with Power Query, Solver, regression, financial modelling or Monte Carlo simulation.

Bring a laptop with a current desktop version of Microsoft Excel and access to your normal working files where permitted. A non-confidential example of a forecasting, budgeting, capacity or project-planning problem will help you apply the methods.

It suits analysts, finance professionals, planners, project managers and operations managers who build or review decision spreadsheets. It is intended for people moving beyond reporting toward modelling, scenario testing and quantified decision support.

Advanced Excel courses usually focus on functions, charts, automation and productivity features. This course focuses on modelling logic, assumptions, forecasting, optimisation, simulation, controls and decision communication; Power BI is not the main subject.

You can use the methods to improve forecasts, cash plans, investment cases, staffing models, pricing decisions and project-risk assessments. The workbook structure and control methods are designed for models that require review and periodic updating.

Each training day runs from 08:30 to 16:30 and provides about 6.5 contact hours. Time is divided across model design, data preparation, analysis, forecasting, financial models, sensitivity testing, Solver, simulation, validation and reporting.

Questions about this course?

Ask about dates, fees, group bookings or running it for your team. Our training team replies by email.

Ask on WhatsApp info@skillsetdevelopment.com

Related courses in Data Analytics