Excel & Power BI for Finance: The ERP-to-Excel Pipeline: Why Finance Lives in Spreadsheets

Download the AtlasParts dataset — the eight CSVs we use across the entire series are on GitHub: github.com/GoCelesteAI/excel-powerbi-for-finance. Clone or download once; reuse across all twelve episodes.

If you work in finance at a company that runs SAP, Oracle, or NetSuite, your Monday morning probably starts the same way every week.

You open the ERP. You run a report. You export it to Excel. And then you spend the next three hours stitching the export into something your CFO can read.

Welcome to the first episode of Excel & Power BI for Finance. Before we touch a single formula, we need to be honest about what’s actually happening in this picture — because most “Excel for finance” tutorials skip the part that matters most: where the data comes from, why it lands in Excel in the first place, and what shape it arrives in.

This series is for the people who already live this Monday-morning workflow. By the end of the twelve episodes across both seasons, the goal is that you can take any GL extract from any ERP and turn it into a finance report that actually answers the question your CFO asked.

The setup nobody talks about

Companies spend millions of dollars and several years rolling out ERP software. Then their CFO still asks for a P&L by region in Excel.

This is not a failure of the ERP. It’s the natural result of a tool optimized for recording transactions meeting an audience that needs flexible reporting. ERPs are systems of record. Excel — and increasingly Power BI — is where humans actually think.

A few of the reasons the export-to-Excel loop never goes away, even in companies with brand-new cloud ERPs:

• The ERP’s standard report layout doesn’t match how the CFO thinks about the business
• The ERP can’t easily cross-reference last quarter’s plan against this quarter’s actuals
• The ERP holds data in three currencies and the CFO wants USD, with FX translation at month-end rates
• The ERP exports columns the CFO doesn’t care about, and is missing one that operations just made up
• Finance needs to add a comment column. The ERP doesn’t have a comment column.

So out it goes to Excel. Every. Single. Month.

That’s not a problem to solve. It’s the job. The job of finance is the last mile between the ERP’s record-keeping and the human who needs to make a decision. This series is about doing that last mile well.

Meet AtlasParts Corp

Throughout all twelve episodes, we’ll work with one fictional company: AtlasParts Corp.

AtlasParts is a regional B2B distributor of automotive parts, with about 10 million dollars in annual revenue and a 5% net margin. They sell brake pads, oil filters, alternators, shock absorbers, and a few hundred other SKUs to auto repair shops, fleet operators, retail chains, and other wholesalers across the US, Canada, the UK, Germany, and France. Mid-size enough to feel real. Small enough that you can hold the whole business in your head.

Why this kind of company, specifically?

Distribution is a clean buy-and-sell flow. AtlasParts buys inventory from manufacturers like Brembo, Bosch, and Monroe, holds it in a warehouse, and resells it to customers at a markup. That gives us straightforward Cost of Goods Sold, Accounts Receivable, Accounts Payable, and inventory accounting — exactly the building blocks every finance pro deals with, without the complications of a manufacturer’s work-in-progress or a service business’s revenue recognition rules.

Auto parts are also tangible. You know what a brake pad is. You can imagine why Q4 might be heavier than Q2 (winter wear and tear, year-end fleet maintenance budgets). The vertical isn’t doing any teaching work that matters; it’s just a backdrop that lets your intuition help instead of fight you.

And the multi-country setup will pay off in Season 2 when we build a Power BI dashboard that slices revenue by region.

The three exports you’ll see all series

Every episode in this series uses one or more of three core data exports from AtlasParts’ ERP. Get familiar with these now — they’ll come back over and over.

One: the GL journal extract

This is the headline file. Every transaction the company books — every sale, every vendor payment, every monthly accrual, every depreciation entry — generates one or more rows in this file. For AtlasParts’ 2025 fiscal year, the extract has about 22,000 rows.

Each row has a posting date, an account number, a debit amount, a credit amount, a description, and a reference to the source document (sales order number, vendor invoice number, recurring entry tag, and so on).

If you’ve never read a GL extract before, this can look intimidating. Twenty-two thousand rows is a lot. But it’s also the raw material that every report in finance is ultimately built from. Episode 2 of this series is dedicated to making this file readable — before we touch a single formula.

Two: the customer master

The customer master is a reference file. It has one row per customer, with their ID, name, country, region, industry, credit limit, and payment terms. AtlasParts has 50 customers. The master is small.

The reason the customer master matters is that the GL extract doesn’t tell you who the customer is — it tells you that there was an Accounts Receivable entry for invoice SO-01284. To answer questions like “what was our revenue from UK customers in Q3,” you need to join the GL or the sales orders against the customer master.

This is also the file where reality starts to bite. Real customer masters are messy. The country column will have “US” in some rows and “United States” in others and “U.S.” and “USA” in still more. Customer names will have trailing whitespace. Episode 5 of this season is about cleaning these kinds of files using Power Query — because pretending data is clean is how junior analysts ship reports with subtotals that don’t match anything.

Three: the AP invoice list

The AP invoice list is the third file. One row per vendor invoice, with the invoice ID, vendor ID, invoice date, due date, amount, and status — paid or open.

We’ll use this file for AP aging analyses (how much do we owe whom, broken down by how overdue it is) and for vendor spend analysis (which suppliers cost us the most). It’s also the bridge into operational thinking — the file your treasury team actually opens on Friday afternoons.

What “stitching” actually looks like

So you have these three files. Now what?

A typical Monday-morning workflow in a finance department might go something like this:

1. Run three reports out of the ERP. Save them as CSVs.
2. Open Excel. Paste each CSV into its own sheet of a workbook.
3. Build a lookup formula to join the customer’s country onto each sales transaction.
4. Build another lookup to add the product category from the SKU master.
5. Construct a pivot table that summarizes revenue by country and category.
6. Format the result so it doesn’t look like Excel barfed onto a page.
7. Save the file with a name like Q3_revenue_by_region_FINAL_v3_use_this_one.xlsx.
8. Email it to the CFO. Receive feedback. Repeat steps 3 through 7.

Each of those steps has a specific Excel skill behind it. Lookups in step 3 and 4 — that’s Episode 3 of this season. Pivot tables in step 5 — that’s Episode 4. Cleaning the messy customer master before the lookup will work — that’s Episode 5. Making the workbook robust enough that step 8 doesn’t introduce new bugs — that’s Episode 6.

But none of this works if you can’t read the files first. Which is why Episode 2 is about literacy, not formulas.

Where Excel breaks

Excel is incredible, and it has been incredible for thirty-five years. Pivot tables in particular are one of the great pieces of software ever shipped. But Excel has a ceiling, and finance pros tend to hit it in roughly the same way.

The ceiling is not row count. Modern Excel handles a million rows. The ceiling is the model getting out of control.

Symptoms that you’ve hit the Excel ceiling:

• Your monthly close workbook has 14 sheets and no one quite remembers which formulas reference which other sheets
• A lookup formula in cell K847 secretly depends on a hardcoded number in cell C12 of a different workbook your colleague emailed you in March
• Refreshing the workbook for a new month requires “just a few manual steps” that take half a day
• Two different people produce two different “monthly revenue” numbers, and reconciling them requires opening their personal copies of the same broken model

This is where Power BI comes in. Power BI was built for exactly this problem: a real data model, a refresh button that actually works, and a single source of truth that multiple people can see. Season 2 of this series is about getting you there.

But Power BI is also overkill for many things Excel is great at. So Season 1 stays in Excel, builds the right habits, and gets you to a robust spreadsheet that wouldn’t embarrass you in an audit. Then Season 2 picks up where Excel breaks and shows the next step.

What this series is not

A few quick clarifications, because they save you time.

This is not an ERP tutorial. If you want to learn what SAP or Oracle or NetSuite actually do, watch the ERP for Beginners and SAP Concepts playlists on this channel. This series assumes you already know what an ERP is. You don’t need to be an expert; you just need to know that ERPs exist and that they store the company’s transaction data.

This is not a comprehensive Excel reference. Excel has 500 functions. We will not cover them all. We’ll cover the ones finance people actually use, on data that looks like real ERP output, with a path to graduate to Power BI when Excel can’t keep up.

This is not a CPA-prep accounting course. We assume you know what a debit and a credit are, and what a P&L is. If you don’t, Accounting for ERP People is a future series on this channel. For now, work along with us and the accounting will become familiar through repetition.

The data is fictional but realistic. Every CSV you’ll see is generated from a Python script with a fixed random seed, so the numbers are reproducible. AtlasParts Corp’s trial balance balances to the cent, foreign keys are consistent across all eight files, and the seasonal patterns are believable. You can download the same files from the codegiz tutorial page and follow along.

What’s coming

Twelve episodes across two seasons. Here’s the map.

Season 1 covers Excel:

1. The ERP-to-Excel pipeline — this episode
2. Reading a GL extract
3. Lookups: SUMIFS, XLOOKUP, INDEX and MATCH
4. Pivot tables: building a P&L from journal lines
5. Power Query: cleaning a messy customer master
6. Robust spreadsheets: tables, named ranges, dynamic arrays

Season 2 covers Power BI:

1. From Excel to Power BI
2. Power Query at scale
3. Star schema for finance
4. DAX fundamentals
5. Time intelligence: year-to-date, prior-year variance
6. Finance dashboard, end to end

By the end of Season 1, you’ll be able to take any of the AtlasParts files, build a robust monthly P&L, and ship it without fear. By the end of Season 2, you’ll have a Power BI report that refreshes from ERP exports on a schedule and replaces the entire Monday-morning workflow.

Coming up next

Episode 2: Reading a GL Extract. We’ll open the AtlasParts general ledger — twenty-two thousand rows of journal lines — and learn what every column means, before we touch a single formula. Because half the battle in finance is just understanding what your ERP just spit at you.

See you there.