Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for treasury analysts

For treasury analysts and cash managers who build the cash position, forecast the weeks ahead and report liquidity in Excel. This page picks out the parts of The Complete Excel Course with AI Integration that treasury work uses: SUMIFS for balances by bank and account, date functions for maturities, a forecast against actual model, XIRR and FV, Power Query for bank statement files, and the charts and KPI cards of a liquidity report.

See the plans Watch free lessons

Taught by Sagnik Bhattacharya and Virginia Thorn

The Complete Excel Course with AI Integration: Sagnik Bhattacharya and Virginia Thorn

What you will be able to do

At work, you will be able to:

  • Build the daily cash position by bank, account and entity with SUMIFS, from statement lines combined with Power Query.
  • Set a short-term cash forecast against what actually cleared in a FinanceGuard-style model, with variance and percent variance signed and coloured by custom number formats.
  • Track deposits and borrowings to maturity with TODAY, EOMONTH and NETWORKDAYS, and list what matures in the coming weeks with FILTER and SORT.
  • Work out the return on an investment with XIRR and what a deposit grows to with FV, after turning an annual rate into a monthly one.
  • Flag balances above a counterparty limit or below a minimum with IF, AND and OR, and show each bank's share of the total with a DAX measure.
  • Report liquidity to management with KPI cards, a line chart of the balance and a waterfall from opening to closing cash.

Projects

What you will build.

The course has 14 projects. These four matter most here.

  • Budget vs actual dashboard in Excel (FinanceGuard): total budget, actuals and variance cards above a monthly line chart and a column chart by department.

    Project 09

    FinanceGuard

    The budget vs actual report finance teams send every month.

    Forecast against actual, read for cash: the same tables, SUMIFS model and variance columns compare the expected receipts and payments with what cleared the bank.

  • SIP and SWP retirement simulator in Excel (WealthFlow): withdrawal and investment plans beside four charts of corpus, withdrawals and portfolio growth over 30 years.

    Project 10

    WealthFlow

    A retirement plan that shows how long the money lasts.

    The rate side of treasury, studied apart from its retirement setting: compounding, XIRR for a rate of return, and FV for what a deposit grows to.

  • Power Query sales report in Excel (SalesFusion): sales by category, sales by region and a monthly trend, with slicers for channel, year, category and region.

    Project 12

    SalesFusion

    A sales report that refreshes itself from a folder of files.

    Practised on sales files, the steps a set of bank statements needs: cleaned, typed, stacked from a folder and grouped by bank and day before the cash position is built.

  • Nine Excel chart types from the Marketing Engagement Dashboard: combo, bar, funnel, Pareto, waterfall, histogram, box and whisker, sunburst and treemap.

    Project 04

    Marketing Engagement Dashboard

    Nine charts, each picked for the question it answers.

    Charts for a liquidity report, practised on marketing data: a line for the cash balance, a combo for balances against limits, and a waterfall from opening to closing cash.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 61 of them, 5 h 34 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 33 AND and OR with IF in Excel

Begin at Lesson 33 if you already use Excel every day: limits and cash totals start with AND, OR and SUMIFS. If IF formulas are new, take Lesson 28 first, and the PivotTable lessons from Lesson 57 help before the FinanceGuard pivot workspace. New to Excel? Begin at Lesson 1. If SUMIFS and lookups are routine, go to Lesson 115, where FinanceGuard begins, or to Lesson 153 for bank files in Power Query.

61 of 186 lessons · 5 h 34 min

04 Essential formulas and functions (IF, SUMIF and more) 7 of 13 lessons · 44 min AND and OR inside IF for a balance above its limit or below its minimum, COUNT and COUNTA to check that every account has reported, SUMIF and SUMIFS for cash by bank, account and day, and TODAY with date arithmetic for days to maturity.
  1. 33 Start here AND and OR with IF in Excel Flag high-value deals with multiple conditions 8 min
  2. 34 SUM, COUNT and COUNTA in Excel SUM with FILTER and counting numbers with COUNT 7 min
  3. 35 SUM, COUNT and COUNTA in Excel COUNTA, unique counts and completeness checks 4 min
  4. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  5. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
  6. 38 The Excel function library Insert Function dialog and Formula AutoComplete 5 min
  7. 39 Date and time functions in Excel TODAY, NOW, MONTH and date arithmetic 6 min
06 Data visualisation: charts and graphs 8 of 9 lessons · 38 min Charts for a liquidity report: choosing the chart, column and line charts for balances over time, a pie for the split by bank, combo charts with a secondary axis for balances against limits, formatting, sparklines beside each account, and the waterfall from opening to closing cash. The lessons chart marketing data.
  1. 48 Choosing the right Excel chart Column, line, pie, combo and modern chart types 4 min
  2. 49 Column charts in Excel Insert, format and label a column chart 5 min
  3. 50 Line charts in Excel Show trends over time with a line chart 4 min
  4. 51 Pie charts in Excel Show proportions and parts of a whole 4 min
  5. 52 Combo charts in Excel Column and line chart with a secondary axis 4 min
  6. 53 Formatting charts in Excel Titles, data labels, colours and chart styles 7 min
  7. 54 Sparklines in Excel Line, column and win-loss mini charts in cells 5 min
  8. 55 Funnel, Pareto and waterfall charts in Excel Modern charts for business data 5 min
08 Advanced Excel functions: lookups, text and arrays 12 lessons · 54 min Lookups that bring the bank, entity and limit onto each account line (XLOOKUP, VLOOKUP, INDEX and MATCH, IFERROR), LEFT, RIGHT and MID for codes inside account numbers, TODAY, EOMONTH and NETWORKDAYS for maturity and value dates, and UNIQUE, FILTER and SORT for the list of what matures soon.
  1. 67 Lookup functions in Excel The PeopleInsight HR dataset and why lookups matter 2 min
  2. 68 VLOOKUP in Excel Find employee data across sheets with exact match 4 min
  3. 69 XLOOKUP in Excel Exact match, if not found and two-way lookup 6 min
  4. 70 IFERROR in Excel Handle lookup errors in VLOOKUP and XLOOKUP formulas 3 min
  5. 71 INDEX and MATCH in Excel A flexible alternative to VLOOKUP 6 min
  6. 72 CONCAT and TEXTJOIN in Excel Combine employee names and text with a delimiter 3 min
  7. 73 LEFT, RIGHT and MID in Excel Extract parts of a text code like DAL-SAL-0001 7 min
  8. 74 TODAY, EOMONTH and NETWORKDAYS in Excel Date calculations for HR data 5 min
  9. 75 UNIQUE function in Excel Extract a distinct list with dynamic arrays 6 min
  10. 76 FILTER function in Excel Dynamic array filtering with AND and OR criteria 5 min
  11. 77 SORT and SORTBY in Excel Sort data dynamically with formulas 4 min
  12. 78 Excel Ninja Challenge XLOOKUP, totals and text concatenation lookup challenge 3 min
12 Project: FinanceGuard budget variance analyser (budget vs actual Excel dashboard) 10 lessons · 52 min FinanceGuard read as forecast against actual: two tables, a model in SUMIFS, UNIQUE, VSTACK and LET, variance and percent variance, custom number formats, a pivot workspace, KPI cards, a combo chart and slicers. The lessons set budget against actual spending; a cash forecast against actual flows is laid out the same way.
  1. 115 FinanceGuard budget vs actual dashboard Excel project overview 3 min
  2. 116 Excel tables for budget and actual data Organise the FinanceGuard datasets 4 min
  3. 117 Budget vs actual model in Excel SORT, UNIQUE, VSTACK, LET, TEXTSPLIT, SUMIFS 8 min
  4. 118 Budget variance in Excel Calculate variance and percent variance 4 min
  5. 119 Custom number formats in Excel Show budget variances with colours and signs 2 min
  6. 120 PivotTables for budget vs actual The FinanceGuard pivot workspace 8 min
  7. 121 KPI cards in Excel Summary cards for a budget variance dashboard 4 min
  8. 122 Budget vs actual charts in Excel Combo chart with secondary axis for variance 5 min
  9. 123 Slicers and PivotCharts in Excel Interactive FinanceGuard budget dashboard 4 min
  10. 124 Finish the FinanceGuard dashboard in Excel Final polish and variance insights 10 min
13 WealthFlow investment and retirement simulator 1 of 11 lessons · 6 min Compounding, and the difference between a lump sum and regular amounts: the idea under every deposit and investment return. The lesson explains it for personal investing.
  1. 125 SIP and SWP calculators What is an SIP, lump sum and compounding 6 min
13 WealthFlow investment and retirement simulator 2 of 11 lessons · 12 min XIRR for a rate of return, an annual rate turned into a monthly one, and FV formulas for what a balance grows to, taught in the WealthFlow investment model.
  1. 127 WealthFlow SIP model in Excel Input assumptions, XIRR and monthly rate 6 min
  2. 128 WealthFlow SIP model in Excel Step-up SIP table and FV formulas 6 min
15 SalesFusion: advanced data tools and a Power Query modelling system 21 lessons · 2 h 7 min SalesFusion read for bank files: Power Query imports CSV files, cleans and types them, appends one period to the next, combines a folder of files and merges account details; Power Pivot relates the tables and DAX measures give totals and percent of total, such as each bank's share of cash. The lessons use sales files, and the Power Pivot lessons need Excel for Windows.
  1. 153 SalesFusion Power Query project Excel ETL pipeline and data model overview 5 min
  2. 154 Introduction to Power Query in Excel The ETL workflow explained 5 min
  3. 155 Import a CSV with Power Query Get data from text and transform data 2 min
  4. 156 Clean columns in Power Query Remove and rename columns for modelling 5 min
  5. 157 Clean text in Power Query Trim, clean, replace values and fix text case 6 min
  6. 158 Data types in Power Query Fix date, whole number, decimal and text types 7 min
  7. 159 Date columns in Power Query Add year, month and year-month fields for reports 7 min
  8. 160 Business logic in Power Query Custom and conditional columns for profitability 6 min
  9. 161 Group By in Power Query Summarise sales by month and region to validate data 5 min
  10. 162 Close and Load in Power Query Connection only, table or Data Model 5 min
  11. 163 Merge queries in Power Query Look up product and region names with a left join 5 min
  12. 164 Reuse a Power Query pipeline Duplicate queries for a new year of sales data 5 min
  13. 165 Append queries in Power Query Combine 2024 and 2025 sales tables 3 min
  14. 166 From Folder in Power Query Combine every sales file in a folder automatically 9 min
  15. 167 From Folder automation in Power Query Profit metrics and fiscal year columns 7 min
  16. 168 Build a sales fact table in Power Query Merge lookups and append history 6 min
  17. 169 Power Pivot Data Model in Excel Relationships and Diagram View 6 min
  18. 170 DAX measures in Power Pivot Total sales, margin percent and percent of total 10 min
  19. 171 PivotTable report from the Data Model Format the SalesFusion report in Excel 5 min
  20. 172 Interactive Excel dashboard Data model PivotTables, slicers and styling 10 min
  21. 173 Unpivot columns in Power Query Turn wide month columns into a long table 8 min

Instructors

Who teaches it.

Virginia Thorn smiling at the camera, dark hair tied back, wearing a navy top.

Instructor, Excel with AI

Virginia Thorn

Emmy Award winner. Teaches Excel with AI at Coding Liquids.

Questions

Asked before starting.

Is there an Excel course for treasury analysts?

Yes. The Complete Excel Course with AI Integration from Coding Liquids teaches the Excel a treasury desk relies on: the cash position by bank and account with SUMIFS, maturities tracked with EOMONTH and NETWORKDAYS, a forecast set against actual in a FinanceGuard-style model, returns with XIRR, bank statement files combined in Power Query, and charts for the liquidity report. The 61 lessons here come to 5 hours 34 minutes of the course's 16 hours.

Does it teach cash forecasting methods or hedging?

Not as methods. The Complete Excel Course with AI Integration has no lessons on statistical cash forecasting, foreign exchange or interest rate hedging, or treasury management systems. It gives the Excel side: receipts and payments totalled by date with SUMIFS, a forecast against actual model with variances, maturities by date, and returns with XIRR. Which flows go into the forecast stays your own judgement.

Can Power Query combine statements from several banks?

Yes, when each bank's files share a layout. In The Complete Excel Course with AI Integration From Folder combines every file in a folder in one step, Append stacks tables with the same columns, and the cleaning steps for types, dates and text repeat on refresh. A bank with a different layout needs its own query, and the lesson on reusing a pipeline shows how to duplicate one. The data model that follows needs Excel for Windows.

How do I calculate the return on an investment with XIRR?

The Complete Excel Course with AI Integration uses XIRR in WealthFlow, its investment model, where it gives the yearly rate of return on a series of dated amounts; the same lessons turn an annual rate into a monthly one and use FV for what a balance grows to. They model a personal investment plan; a deposit or fund holding is set out as dates and amounts in the same way.

Excel with AI

See the whole course.

The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.

See the plans Watch free lessons

The same course

For another job, level or topic.

By job

Finance and accounting
HR and office administration
Operations and supply chain
Data and reporting
Sales, marketing and customer service
Business and management
Engineering, construction and manufacturing
Retail, property and hospitality
Healthcare and life sciences
Education and careers
Public sector, non-profit and legal
Technology

By level and topic