Excel for Finance and Accounting Training Course

Participants can apply formulas that summarise, classify and validate accounting data reliably.

• SUMIFS, COUNTIFS and AVERAGEIFS summarise transactions by account and period
• XLOOKUP and INDEX-MATCH map chart-of-accounts codes to reporting lines
• IF, IFS and IFERROR build controlled exception and status logic
• Date functions allocate postings to months, quarters and fiscal years
• Dynamic arrays filter exception populations without copied helper formulas
• LET improves formula readability for repeated rates and calculation terms

Participants can prepare accounting data and investigate reconciliation differences systematically.

• Import general-ledger extracts with Text to Columns and data types
• Power Query cleans trial balance files through repeatable transformation steps
• Reconciliation schedules match subledger balances, bank data and control accounts
• Duplicate detection combines COUNTIFS, conditional formatting and transaction identifiers
• Month-end checklists document source, preparer, reviewer and sign-off evidence
• Exception logs classify timing differences, posting errors and unresolved items

Participants can build linked financial statements from trial balance data and supporting schedules.

• Trial balance mapping assigns accounts to income statement and balance-sheet lines
• Linking schedules carry opening balances, movements and closing balances
• Income statement builds gross margin, EBITDA and operating profit bridges
• Balance sheet schedules model working capital, debt and retained earnings
• Cash flow statement reconciles profit, non-cash items and working-capital movements
• Balance checks identify broken links, sign errors and unexplained variances

Participants can produce controlled management reports that explain financial performance clearly.

• PivotTables produce monthly actual-versus-budget reports by cost centre
• PivotCharts display revenue, margin and expenditure trends for management
• GETPIVOTDATA extracts controlled report values from approved PivotTables
• Variance analysis separates price, volume, mix and timing drivers
• Waterfall charts explain movements between budget, forecast and actual results
• Slicers and timelines filter recurring management packs without formula changes

Participants can build driver-based budgets and assess forecast uncertainty using scenarios and sensitivities.

• Driver-based budgets connect sales volumes, prices, headcount and operating costs
• Rolling forecasts update assumptions, actuals and remaining-period estimates
• Scenario Manager compares base, upside and downside planning cases
• Data Tables calculate two-variable sensitivity for volume and margin assumptions
• Goal Seek derives required sales or pricing to meet targets
• Forecast version control records assumption changes, approval dates and owners

Participants can prepare short-term cash forecasts and analyse working-capital funding requirements.

• 13-week rolling cash forecasts combine receipts, payroll, tax and supplier payments
• Accounts receivable ageing estimates collections using customer payment-pattern assumptions
• Accounts payable schedules sequence due dates, terms and planned disbursements
• Working-capital metrics calculate DSO, DPO, inventory days and cash conversion
• Debt schedules model interest, principal repayments, covenants and facility limits
• Minimum-cash tests flag funding gaps and short-term borrowing requirements

Participants can create accounting schedules for common balance sheet and profit recognition items.

• Fixed-asset registers calculate straight-line and declining-balance depreciation charges
• Depreciation schedules reconcile additions, disposals, accumulated depreciation and net book value
• Lease payment schedules separate interest, principal and closing liability
• Deferred revenue schedules release contracted amounts across service periods
• Tax provision schedules calculate temporary differences and deferred-tax movements
• IFRS and US GAAP presentation differences inform model assumptions

Participants can assess investment proposals using cash flow valuation and sensitivity methods.

• NPV and XNPV value projects using periodic and dated cash flows
• IRR and XIRR test returns against hurdle rates and funding costs
• Payback and discounted payback measure investment recovery periods
• Scenario and sensitivity tables expose key valuation assumptions
• Comparable-project analysis benchmarks margins, capital spending and cash generation
• Investment memoranda link model outputs to approval recommendations

Participants can review finance models and define improvements for their own reporting processes.

• Model review checklist tests logic, sources, signs, units and consistency
• Error checks use control totals, balance flags and reasonableness thresholds
• Workbook documentation records purpose, assumptions, refresh steps and ownership
• Version-control conventions distinguish draft, reviewed and approved reporting files
• Management commentary explains material variances, risks and required decisions
• Personal application plan prioritises one reporting or forecasting improvement

Participants should already be able to enter formulas, format worksheets, sort and filter data, and use simple functions. The course teaches intermediate finance methods, so it is not suitable for people entirely new to Excel.

Participants should bring a laptop with a current desktop version of Microsoft Excel, preferably Microsoft 365 or Excel 2021. They may also bring a non-confidential example of a reporting, forecasting or reconciliation challenge for their application plan.

The course is designed for accountants, financial analysts, FP&A staff, treasury analysts and finance managers who prepare or review Excel-based financial work. It is useful for professionals involved in month-end close, management reporting, budgets, forecasts and cash planning.

General Excel courses focus on broad productivity features, while pure financial modelling courses often concentrate on valuation and corporate finance. This course concentrates on the accounting and finance tasks most teams perform regularly: reporting, reconciliations, financial statements, budgets, forecasts and cash flow.

Participants can apply the templates and methods to improve monthly reporting packs, trial balance mappings, reconciliation files, rolling forecasts and cash forecasts. The final module helps each participant identify one practical workbook or process improvement to implement.

The five days contain approximately 32.5 contact hours, with a typical training day running from 08:30 to 16:30. Time is divided between instructor explanation, spreadsheet demonstrations, finance cases, group analysis, model review and application planning.