Free course · Foundation · 6 lessons · about 90 minutes
Excel and financial modelling for finance teams
A practical, self-paced course for accountants and finance managers who build MIS, budgets and cash-flow models in Excel. You will learn how to lay out a model others can trust, use the functions finance teams rely on, reconcile and clean data, build a linked budget model and a 13-week cash forecast, and present scenarios and MIS that people actually read.
What you will be able to do
- Design a model with separate inputs, calculations and outputs and built-in error checks
- Write lookup, conditional-sum, date and dynamic-array formulas with correct syntax
- Reconcile two ledgers and clean data using formulas, Power Query and pivot tables
- Build a linked budget model with working capital, depreciation, debt and a balance sheet check
- Prepare and roll forward a 13-week cash flow forecast with variance analysis
- Run scenario and sensitivity analysis and review a model before it is shared
Lesson 1: Model design principles
Most spreadsheet errors are layout errors. A model that is laid out well is easier to check, easier to update and much harder to break.
Separate inputs, calculations and outputs
Keep three kinds of content apart, ideally on separate sheets:
| Area | What goes there | Rule |
|---|---|---|
| Inputs | Assumptions: growth rates, prices, debtor days, salary increments, opening balances, data dumps from the ERP | Typed values only. Each assumption appears once, with a label and a unit. |
| Calculations | Revenue build-up, cost lines, schedules for working capital, assets and debt | Formulas only. No typed numbers buried inside formulas. |
| Outputs | MIS pack, P&L, balance sheet, cash flow, charts, dashboard | Pull from calculations. No new logic here. |
The test: if the GST rate, the credit period or a price changes, you should change one cell on the inputs sheet and everything else should follow. A formula like =D12*1.18 fails that test. =D12*(1+Inputs!$C$8) passes it.
One consistent time axis
Every calculation sheet should use the same columns for the same periods. If column F is April 2026 on one sheet, it is April 2026 on every sheet. Put the period dates in one row at the top, and build them with a formula rather than typing them: start with the first month-end in E5, then in F5 use =EOMONTH(E5,1) and copy across. Add a flag row for actuals versus forecast, such as =IF(F5<=Inputs!$C$4,"Actual","Forecast"), where C4 holds the last closed month.
One formula per row
Within a row, the formula should be the same from the first period to the last. If March needs a different formula from February, the logic belongs in a flag or an input, not in a one-off edit. Consistent rows mean you can check one cell and trust the whole line, and tools like Formulas > Show Formulas (Ctrl + `) make breaks easy to spot.
Colour and labelling conventions
A widely used convention, and the one this course follows:
- Blue font: typed inputs and hard-coded numbers.
- Black font: formulas within the same sheet.
- Green font: formulas that link to another sheet or file.
- Light fill on input cells so users know where they are allowed to type.
- Units in a column next to every label: ₹ lakh, days, %, units.
Whatever convention you pick, write it on a cover sheet and apply it everywhere. A legend that nobody follows is worse than none.
Build the error checks in
Create a Checks sheet that collects every test and shows one master result. Typical checks:
- Totals agree:
=ABS(SUM(D10:D20)-D21)<0.5returns TRUE when the components add up to the total within 50 paise of rounding. - Balance sheet balances in every period (lesson 4).
- Opening equals last closing for cash, debt and fixed assets.
- Data imports are complete: the trial balance dump sums to zero.
The master check can be =AND(Checks!C5:C20), shown at the top of every output sheet with conditional formatting that turns red when it is FALSE.
=ABS(SUM(D10:D17)-D18)<0.5 returns FALSE because the difference is ₹10,000. Someone had typed over the total instead of fixing a cost line. Without the check, the MIS would have gone out wrong.Lesson check
Four questions. Get 3 right to complete the lesson. Your progress is saved in this browser.
Lesson 2: Functions finance teams use daily
Ten or so functions do most of the work in MIS, budgets and reconciliations. Learn their exact syntax and you will write faster and break less.
Lookups: XLOOKUP and INDEX-MATCH
=XLOOKUP(A2,Vendors[Code],Vendors[Name],"Not found") looks up the vendor code in A2 and returns the name. The arguments are: lookup value, lookup array, return array, and an optional value if not found. It defaults to an exact match. XLOOKUP is available in Microsoft 365 and Excel 2021 and later.
For older versions, or files shared with people on them, use =INDEX(Vendors[Name],MATCH(A2,Vendors[Code],0)). The 0 in MATCH means exact match. Leaving it out gives an approximate match on sorted data, a common source of wrong answers. Avoid VLOOKUP with a hard-coded column number: inserting a column silently breaks it.
Conditional sums and counts: SUMIFS and COUNTIFS
SUMIFS takes the sum range first, then pairs of criteria range and criterion:
=SUMIFS(Ledger[Amount],Ledger[Account],$B10,Ledger[Date],">="&E$5,Ledger[Date],"<="&EOMONTH(E$5,0))
This sums the ledger for the account in B10 for the month starting on the date in E5. Note how the operator is in quotes and joined to the cell with &. COUNTIFS follows the same pattern without a sum range: =COUNTIFS(Ledger[Invoice],A2) counts how many times an invoice number appears, so any result above 1 is a possible duplicate.
Decisions: IF and IFS
For a debtor ageing bucket based on days outstanding in D2:
=IFS(D2>90,"90+",D2>60,"61-90",D2>30,"31-60",TRUE,"0-30")
IFS tests conditions in order and returns the first true one, so put the largest bucket first. The final TRUE is the catch-all. The nested IF equivalent is =IF(D2>90,"90+",IF(D2>60,"61-90",IF(D2>30,"31-60","0-30"))).
Dates: EOMONTH and EDATE
| Formula (A2 = 15-Jan-2026) | Result | Use |
|---|---|---|
=EOMONTH(A2,0) | 31-Jan-2026 | Month-end of a transaction, for grouping |
=EOMONTH(A2,1) | 28-Feb-2026 | Next month-end |
=EDATE(A2,3) | 15-Apr-2026 | Same day three months later: renewals, EMIs, quarterly dues |
=A2+45 | 1-Mar-2026 | Due date on 45-day credit |
Working: January has 16 days left after the 15th, February has 28, so 16 + 28 = 44 days takes you to 28 February and day 45 is 1 March.
Presentation and errors: TEXT and IFERROR
=TEXT(E5,"mmm-yy") turns a date into a label like Apr-26 for headers. The result is text, so use it only for display, never in calculations. Format codes can behave differently on systems with non-English regional settings.
=IFERROR(D5/C5,0) returns 0 instead of #DIV/0!. Use it sparingly: IFERROR hides every error, including a broken link or a typo in a range. For lookups, prefer XLOOKUP’s own if-not-found argument so that only a missing match is handled.
Dynamic arrays: FILTER, UNIQUE, SORT
In Microsoft 365 and Excel 2021, one formula can return many cells, which spill into the cells below:
=SORT(UNIQUE(Ledger[Customer]))gives an alphabetical list of customers that updates itself.=FILTER(Debtors,Debtors[Days]>90,"None")lists every debtor over 90 days, or "None".=SORT(FILTER(Debtors,Debtors[Days]>90),3,-1)sorts that list by its third column, largest first.
If something sits in the way of the spill range, the formula shows #SPILL!. Clear the blocking cells. Refer to a whole spilled range with the # sign, for example =SUM(H2#).
Ledger[Amount] grow automatically when new rows are pasted, so your SUMIFS never miss the last week of data.Lesson check
Four questions. Get 3 right to complete the lesson. Your progress is saved in this browser.
Lesson 3: Reconciliation and data cleaning in Excel
Reconciliation is matching two lists that should agree and explaining every difference. Excel does the matching; your judgement does the explaining.
Clean the data first
Most failed matches are not real differences. They are formatting problems:
- Extra spaces:
=TRIM(B2)removes leading, trailing and repeated spaces.=CLEAN(B2)strips non-printing characters from ERP exports. - Numbers stored as text: they sit on the left of the cell and SUM ignores them. Convert with
=VALUE(B2)or Data > Text to Columns > Finish. - Case and punctuation in invoice numbers: standardise with
=UPPER(SUBSTITUTE(B2,"-",""))so INV-0042 and inv0042 match. - Signs: your payable is a credit, the vendor’s receivable is a debit. Flip one side so both are positive before matching.
Matching two ledgers with formulas
Build a match key on both sheets, such as cleaned invoice number plus rounded amount: =UPPER(TRIM(B2))&"|"&ROUND(C2,0). Then on each sheet ask whether the key exists on the other:
=IF(COUNTIFS(Statement[Key],[@Key])>0,"Matched","Only in books")
Filter on the unmatched items. These are your reconciling items. Also check duplicates within each side with =COUNTIFS([Key],[@Key])>1; a duplicate booking can hide behind a successful match. Prefer flagging duplicates with a formula over Data > Remove Duplicates, which deletes rows without leaving an audit trail.
Power Query basics
If you clean the same export every month, record the steps once in Power Query (Data > Get & Transform Data > From Table/Range, or From Text/CSV). Each step, such as removing top rows, trimming text, changing data types or filtering out blanks, is saved and replayed when you click Refresh.
| Task | Power Query feature |
|---|---|
| Stack twelve monthly files into one table | Append Queries, or From Folder |
| Bring the vendor name into a ledger | Merge Queries with a Left Outer join |
| List items in your books but not on the statement | Merge Queries with a Left Anti join |
| Turn a month-per-column report into rows | Unpivot Columns |
The anti join is the fastest reconciliation tool in Excel: run it both ways (books against statement, statement against books) and you have both lists of unmatched items.
Pivot tables for the summary
Append both sides into one table with a Source column ("Books" or "Statement") and amounts signed so that the two sides offset. A pivot table with Party in rows, Source in columns and Sum of Amount in values shows each party’s difference at a glance. Drill into any number by double-clicking it. Remember that pivot tables do not update on their own: use Refresh All after new data arrives.
Lesson check
Four questions. Get 3 right to complete the lesson. Your progress is saved in this browser.
Lesson 4: Building a three-statement or budget model
A linked model connects the P&L, the balance sheet and the cash flow so a change in one assumption reaches all three. The balance sheet check tells you when the links are right.
The build order
- 1. Revenue drivers: volume × price, by product or customer group. Model the drivers, not the answer:
=Units*Pricerather than last year plus 12%. - 2. Cost lines: variable costs as a percentage of revenue or per unit; fixed costs as monthly amounts with step changes (an increment in April, a new office in October).
- 3. Working capital schedule: debtors, inventory and creditors from days assumptions.
- 4. Fixed asset schedule: opening, additions, depreciation, closing.
- 5. Debt schedule: opening, drawdowns, repayments, closing, and interest.
- 6. Statements: P&L, balance sheet, then cash flow by the indirect method.
Working capital from days
For an annual model, closing debtors = revenue × debtor days ÷ 365. Inventory and creditors days are usually based on cost of goods sold or purchases.
| Item | Base (annual) | Days | Working | Closing balance |
|---|---|---|---|---|
| Debtors | Revenue ₹7.30 crore | 45 | 7.30 × 45 ÷ 365 | ₹90 lakh |
| Inventory | COGS ₹5.84 crore | 60 | 5.84 × 60 ÷ 365 | ₹96 lakh |
| Creditors | Purchases ₹5.84 crore | 30 | 5.84 × 30 ÷ 365 | ₹48 lakh |
| Net working capital (90 + 96 − 48) | ₹1.38 crore | |||
In a monthly model, use the month’s revenue and the days in that month, or annualise consistently. The increase in net working capital from one period to the next is a cash outflow.
Fixed assets and depreciation
Keep a corkscrew: opening + additions − depreciation − disposals = closing, with the opening of each period linked to the previous closing. A machine costing ₹60 lakh with a 10-year life and nil residual value gives straight-line depreciation of ₹6 lakh a year, or ₹50,000 a month. Use your company’s accounting policy for lives and methods, and remember that tax depreciation is a separate calculation. confirm the useful lives and tax depreciation rates you apply with your CA or auditor. The depreciation calculator helps with the working.
Debt schedule
A term loan of ₹2 crore opening, repaid at ₹40 lakh a year, closes at ₹1.60 crore. At 10% on the average balance, interest is (2.00 + 1.60) ÷ 2 × 10% = ₹18 lakh. Interest on the average balance is fine for scheduled repayments. If repayments depend on the cash the model generates (a cash sweep or an overdraft), average-balance interest creates a circular reference. Calculate interest on the opening balance instead, or add a clearly labelled circularity switch, rather than leaving iterative calculation turned on silently. The EMI calculator checks instalment loans.
Cash flow and the balance sheet check
Build the cash flow from the other statements: profit, plus depreciation, less the increase in working capital, less capital expenditure, plus or minus debt movements and equity. Closing cash from the cash flow feeds the balance sheet. Then check:
=ROUND(TotalAssets-TotalLiabilitiesAndEquity,0)=0
across every period. If it fails, look for the first period where it breaks, and for an item that appears in one statement but not the other: a new balance sheet line not picked up in the cash flow is the usual culprit.
Lesson check
Four questions. Get 3 right to complete the lesson. Your progress is saved in this browser.
Lesson 5: A 13-week cash flow forecast
The P&L tells you whether the business is profitable. The 13-week cash forecast tells you whether it can pay salaries, suppliers and the bank on time.
Why 13 weeks
Thirteen weeks is one quarter: long enough to see a crunch coming and act on it, short enough to forecast from real invoices, orders and payment runs rather than broad assumptions. It is built on receipts and payments, not on revenue and expenses.
Structure
| Section | Typical lines | Source |
|---|---|---|
| Opening cash | Bank balances and available overdraft | Bank statements, linked to last week’s closing |
| Receipts | Collections from existing debtors, collections from new sales, other income, loan drawdowns | Debtor ageing with expected dates, sales pipeline |
| Payments | Suppliers, salaries, rent, GST, TDS, loan EMIs and interest, capital expenditure | Creditor ageing, payroll calendar, statutory calendar, loan schedule |
| Closing cash | Opening + receipts − payments | Formula |
| Headroom | Closing cash less the minimum cash buffer | Formula, with red formatting below zero |
Put week-ending dates across the top: type the first in E4 and use =E4+7 to the right. Map each invoice to a week with a formula rather than by hand. With an expected receipt date for each invoice in the debtor table:
=SUMIFS(Debtors[Amount],Debtors[ExpectedDate],">"&E$4-7,Debtors[ExpectedDate],"<="&E$4)
Statutory payments such as GST and TDS fall on fixed dates, so put them on their own lines from a calendar. confirm current due dates on the official portal or with your CA; the GST and TDS key deadlines page is a starting point.
Rolling it forward every week
- Lock the actuals: replace last week’s forecast with actual receipts and payments from the bank.
- Reconcile opening cash to the bank balance. If it does not agree, the forecast is built on sand.
- Drop the old week, add week 13 so the horizon always stays 13 weeks ahead.
- Refresh the ageing and move any receipt that has slipped to its new expected week.
Variance analysis
Keep a copy of each week’s forecast, then compare it with actuals line by line. Variance = actual − forecast. If forecast receipts were ₹32,00,000 and actual receipts were ₹27,50,000, the variance is −₹4,50,000, or −14.1% (4,50,000 ÷ 32,00,000). The question is always why: a timing difference (the customer pays next week) needs no action, a permanent difference (a disputed invoice) changes the forecast. Track forecast accuracy over several weeks; a forecast that is always optimistic on collections needs a haircut built in.
Lesson check
Four questions. Get 3 right to complete the lesson. Your progress is saved in this browser.
Lesson 6: Scenarios, sensitivities and presenting MIS
A forecast is one guess. Scenarios and sensitivities show management how wrong the guess could be, and good presentation makes sure they read it.
Scenario switches
Put the base, upside and downside values for each key assumption side by side on the inputs sheet, and add one switch cell, say C3, holding 1, 2 or 3 (use Data > Data Validation > List so nobody types 4). The live assumption picks the right column:
=CHOOSE($C$3,E10,F10,G10) or =INDEX(E10:G10,$C$3)
Every calculation uses the live column only. Show the active scenario name on every output page, for example =INDEX(E9:G9,$C$3) where row 9 holds the scenario names, so nobody mistakes a downside pack for the budget.
Sensitivity: what moves the result most
A 5% price increase at the same volume: revenue ₹10.5 crore, variable costs stay ₹6 crore, EBITDA ₹2.0 crore, up 33.3%.
A 5% volume increase at the same price: revenue ₹10.5 crore, variable costs ₹6.3 crore, EBITDA ₹1.7 crore, up 13.3%.
The same 5% on the top line has very different effects, which is exactly what a sensitivity table makes visible.
Excel’s Data Table (Data > What-If Analysis > Data Table) recalculates one output for a range of input values. For a two-way table, put the output formula in the top-left corner, one set of input values across the top row and another down the left column, select the whole block and give the row and column input cells. The input cells must be on the same sheet as the table. Data tables can slow a big file; the calculation option Automatic except for data tables helps, but remember to press F9 before reading them. Goal Seek, on the same menu, answers the reverse question: what price gives an EBITDA of ₹2 crore?
Charts that work
- Line charts for trends over time: monthly revenue, cash balance.
- Bar or column charts for comparisons: actual versus budget by department.
- Waterfall charts to bridge budget EBITDA to actual, or opening to closing cash.
- Avoid 3D effects, pie charts with more than a few slices, and two different scales on one chart unless clearly labelled.
- Title with the message, not the topic: “Collections fell 14% short in week 1” rather than “Receipts”.
Dashboard tips
Lead with five or six numbers that matter: revenue, gross margin, EBITDA, cash, debtor days, against budget and last year. Use the same layout every month so readers know where to look. Use conditional formatting for variances beyond a threshold, not for everything. Put the detail behind the summary, never in front of it.
Model review checklist
- Checks sheet is all TRUE, including the balance sheet check in every period.
- No hard-coded numbers in formulas (Go To Special > Constants on calculation sheets helps find them).
- Rows are consistent across all periods.
- Links to other files are listed and intended (Data > Edit Links).
- Scenario switch is back on base, and the version and date are on the cover.
- Sense check: margins, growth and days look plausible against history.
- Someone else has reviewed it. The builder is the worst person to find their own errors.
Lesson check
Four questions. Get 3 right to complete the lesson. Your progress is saved in this browser.
Final test and certificate
15 questions drawn from a bank, marked on the server. Score 11 or more (70%) for a certificate of completion with a public verification link and an “Add to LinkedIn” button.
Go to the final test- FEMA for importers
- Export compliance
- TDS and GST essentials
- Ind AS foundations
- Close and consolidation
- Internal controls and fraud
- FP&A essentials
- Corporate finance essentials
- Treasury and cash management
- Cost and management accounting
- Import procurement & SCF
- EPC projects and tenders
- Companies Act compliance
- Payroll compliance
- IFRS, US GAAP and Ind AS
- AI for finance teams
General information for learning, not professional advice. Laws, rates and standards change: confirm with the official source or your adviser before acting. Last reviewed 15 November 2026.