Episode 2: The Real Role of Excel in Capital Forecasting

The transcript below has been lightly edited for readability while preserving the conversational format of the episode. It expands on the original source material by exploring the practical realities of capital expenditure forecasting: why Excel remains essential, where spreadsheet-based processes break down, how project managers and controllers experience forecasting differently, and why structured systems of record are needed to improve visibility, variance handling, auditability and forecast confidence.

00:00:00 Host 1
Imagine a billion-dollar skyscraper, right?

00:00:02 Host 2
Wow, okay. Setting the scene.

00:00:05 Host 1
Yeah, I mean, the steel is ordered, the concrete is pouring, the cranes are moving high above the city skyline. You would think that the financial system tracking this massive complex endeavor is just a masterpiece of precision engineering.

00:00:17 Host 2
You’d certainly hope so.

00:00:18 Host 1
Exactly. You assume you can just, you know, click a button, generate a crisp, clean report, and the executives can just point at the budget and say, there it is down to the cent.

00:00:27 Host 2
Yeah, but that is rarely the case.

00:00:29 Host 1
That’s not. The terrifying reality is that this billion-dollar corporate vision is probably being held together by a stressed project manager furiously updating a spreadsheet named forecast final v4 really final dot XLSX at like 11 P.m. on a Tuesday.

00:00:47 Host 2
It is the absolute definition. of diagnostic muddy waters. I mean, when you step into the actual world of project delivery and capital expenditure, the numbers exist, sure, but the organizational confidence in those numbers is practically 0.

00:01:02 Host 1
And that is exactly what we are tackling today. So welcome. We have put together a custom deep dive just for you, pulling from a really fascinating stack of sources, including articles and draft guides from IQX Business Solutions.

00:01:20 Host 2
Yeah, they detail the intricacies of CapEx or capital expenditure forecast preparation.

00:01:25 Host 1
Right. And our mission today, we want to uncover why predicting project costs is such a universal corporate headache and, you know, more importantly, how organizations can fix it without destroying the tools that the people on the ground actually use.

00:01:39 Host 2
It’s a massive multi-billion-dollar challenge because every number used in executive portfolio reporting, every major capital allocation decision a board makes.

00:01:48 Host 1
It all comes from the ground up, right?

00:01:49 Host 2
Exactly. It all originates from project level forecasts. So, if that foundational data is cracked, the entire corporate strategy just leans.

00:01:57 Host 1
Okay, let’s unpack this. Because before we can actually solve this forecasting crisis, you need to understand the anatomy of a forecast and why the current system inherently sets project managers up to fail.

00:02:06 Host 2
Yeah, the deck is kind of stacked against them.

00:02:08 Host 1
It really is. And by the way, I see you mapping out these massive urban infrastructure projects in your notes already, skyscrapers, transit lines, I love the dynamic cityscape you’ve got going on in your backdrop today, by the way. Very fitting. Oh, thanks.

00:02:22 Host 2
Yeah, just trying to keep the scale of this in mind.

00:02:25 Host 1
It’s wild to think that behind every piece of that steel and glass is a deeply flawed financial forecast. It really is.

00:02:33 Host 2
Because when you break down a CapEx forecast, you realize it’s actually a violent collision of two very different worlds. How so? Well, every single forecast relies on 4 core inputs. First, you have the budget, which is simply the.

00:02:47 Host 1
Okay, that’s straightforward.

00:02:48 Host 2
Second, you have the actuals, which is the money that is definitively left the building. That’s captured securely in the finances.

00:02:54 Host 1
Right, the receipts.

00:02:55 Host 2
Exactly. Third, you have commitments. This is your procured or contracted spend. So, money you’re legally bound to pay, like a signed purchase order for 1000 tons of steel.

00:03:06 Host 1
So far, so good. I mean, those first three inputs, budget, actuals, commitments, they’re highly structured.

00:03:11 Host 2
Very structured.

00:03:12 Host 1
Their system generated. They live safely inside corporate databases where, you know, nothing can easily be tampered with.

00:03:19 Host 2
Correct. But then you hit the 4th input. Yeah. Expected future spend. And this is where the entire process just derails.

00:03:28 Host 1
Why? What makes the 4th one so different?

00:03:30 Host 2
Because expected future spend does not live in a corporate database. It is entirely unstructured.

00:03:37 Host 1
I see.

00:03:37 Host 2
It’s based almost entirely on human knowledge. It’s a project manager’s understanding of delivery plans. shifting supplier behaviors, evolving project conditions.

00:03:48 Host 1
And sudden unforeseen delays, I imagine.

00:03:50 Host 2
Exactly. It’s the ultimate wild card.

00:03:52 Host 1
Right. I mean, getting the 1st 3 inputs, it’s like reading a strict step-by-step recipe, but that 4th input, that’s like trying to guess exactly what time dinner will be ready while the stove keeps malfunctioning.

00:04:04 Host 2
That is a great way to put it.

00:04:05 Host 1
And the delivery guy gets a flat tire on top of it. You have project managers out there dealing with real world physical chaos.

00:04:11 Host 2
Dirt, weather, broken machinery.

00:04:13 Host 1
Yes. And they are forced to translate that physical activity into a timeline and then somehow translate that timeline into a perfectly neat cash flow projection.

00:04:23 Host 2
Which is incredibly unfair to them.

00:04:25 Host 1
Exactly. Why do we force project managers to act like financial controllers when their job is on the ground delivery?

00:04:33 Host 2
This raises an important question, and it’s what we call the classic controller’s dilemma.

00:04:37 Host 1
Okay, the controller’s dilemma. What’s that?

00:04:40 Host 2
Well, why put the burden on the project manager? Ultimately, because They are the only ones close enough to the physical reality to make an educated guess.

00:04:49 Host 1
True. The finance team isn’t out there pouring concrete.

00:04:52 Host 2
Right. But the controllers sitting on the other side of the building, the ones managing the whole CapEx portfolio, they need three specific things to do their jobs. Visibility, comparability, and trust.

00:05:03 Host 1
They need it all to line up perfectly.

00:05:05 Host 2
Exactly. If forecasts from 50 different project managers working on 50 different sites aren’t mathematically consistent, the controllers are stuck. They’re left trying to interpret messy data rather than actually analyzing it.

00:05:16 Host 1
Which means they can’t make good decisions. So because controllers are demanding mathematical perfection from a completely unpredictable reality, project managers are basically forced into a corner.

00:05:28 Host 2
They are.

00:05:29 Host 1
And to survive the reporting cycle, they abandon the rigid corporate ERP systems, right? They retreat to the one place they actually have control. good old Microsoft Excel.

00:05:42 Host 2
Yes. The spreadsheet. The undisputed king of corporate workarounds.

00:05:46 Host 1
Here’s where it gets really interesting. The sources make a very bold claim here. And this is really the core focus of our deep dive today. They argue that Excel is actually not the problem.

00:05:55 Host 2
Right. A lot of people blame Excel, but it’s really not the villain here.

00:05:59 Host 1
The breakdown only happens when Excel is used as the system of record, right? When it becomes the final destination where forecasts are securely stored, distributed, and compared.

00:06:07 Host 2
Precisely. Excel is an incredible working layer for project managers to model changes. But using it to manage the whole portfolio, that leads to fragmented data extraction, manual compilation, and massive version control risks.

00:06:21 Host 1
You end up with 50 different files named final flying around in emails. The sources use this phrase, thumb sucking and re-keying data.

00:06:28 Host 2
Yeah, that phrase captures the sheer administrative pain perfectly. Because the data from those first three inputs, the finance systems and the procurement systems, rarely sits nicely in one place.

00:06:41 Host 1
So what do the project managers do?

00:06:43 Host 2
They spend hours every month manually extracting CSV files, aligning rows, fixing broken VLOOKUP’s.

00:06:51 Host 1
Broken VLOOKUP’s are the worst.

00:06:53 Host 2
They really are. And they pull it all into Excel just to establish a starting point for their forecast. It’s exhausting.

00:06:59 Host 1
It’s like Excel is essentially a kitchen prep counter. It’s perfect for chopping and mixing. You can be messy. You can adjust the recipe on the fly to see how it tastes.

00:07:07 Host 2
Right, you need that flexibility.

00:07:09 Host 1
But you wouldn’t store the finished meal on the prep counter for six months. You put it in a Tupperware container in the fridge, right? A structured system designed for preservation.

00:07:18 Host 2
I love that analogy. You absolutely need the fridge.

00:07:20 Host 1
But wait, if Excel is the root cause of these version control nightmares, these 50 files floating around in executive inboxes with conflicting numbers, Why don’t companies just burn it down?

00:07:31 Host 2
Ban it entirely.

00:07:32 Host 1
Ban Excel entirely and force everyone into a unified, strict software platform.

00:07:38 Host 2
Well, it is the most natural executive reaction in the world. We have a data problem, ban the tool. But banning Excel creates massive operational friction.

00:07:47 Host 1
Really.

00:07:48 Host 2
Why? You have to remember why project managers rely on it in the 1st place. Forecasting in the real world is rarely linear. It’s incredibly fluid.

00:07:57 Host 1
Because things are constantly changing on the ground.

00:07:59 Host 2
Exactly. Excel functions as a highly flexible working environment. They can rapidly translate their operational knowledge, like say, realizing the primary concrete supplier is going on strike next week into financial expectations.

00:08:11 Host 1
They can just add a column, tweak a formula and see what happens.

00:08:13 Host 2
Right. They can play with scenarios. If you force them into a rigid forms-based enterprise system that doesn’t let them think and model freely.

00:08:22 Host 1
They’ll just rebel.

00:08:23 Host 2
They will just build a shadow Excel spreadsheet anyway. They’ll do all the real work there and then just copy-paste the final number into your rigid system at the very last minute.

00:08:32 Host 1
Oh man, they will always find a way back to the spreadsheet.

00:08:35 Host 2
Always. So effective forecasting requires separating the preparation layer from the system of record. You let Excel remain the working layer.

00:08:44 Host 1
Because it fits how they actually think.

00:08:46 Host 2
Exactly. Project managers should absolutely still prep in Excel because it aligns with how human beings think about changing variables. But the final data, the output, must be governed.

00:08:59 Host 1
The final meal goes in the fridge.

00:09:01 Host 2
The meal goes in the fridge.

00:09:02 Host 1
Okay, so if we are keeping Excel as the prep counter, how do we actually fix the messy data extraction and establish that central system of record?

00:09:10 Host 2
That’s the $1,000,000 question.

00:09:12 Host 1
Because you can’t just have 50 people uploading random, differently formatted spreadsheets into a shared corporate folder and call it a day.

00:09:19 Host 2
No, you certainly can’t. The solution lies in introducing A structured environment around that Excel working layer. The sources highlight platforms like Stratex Online as a prime example of this architecture.

00:09:32 Host 1
Okay, so a structured environment, what does that actually look like for the person doing the work?

00:09:36 Host 2
The very first step is pre-populated data.

00:09:39 Host 1
Oh, meaning stop making project teams act like data miners hunting for their own actuals?

00:09:45 Host 2
Exactly. You physically stop making them extract data manually from the finance systems. The system should automatically provide a consistent, accurate baseline.

00:09:54 Host 1
So when I sit down to do my monthly re-forecast, what do I see?

00:09:58 Host 2
The budget, the actuals, and the commitments should already be populated right there in your template.

00:10:03 Host 1
That’s amazing. And the sources emphasize not just pulling in the raw financial data, but using the brought forward forecast as the foundational baseline, right.

00:10:12 Host 2
Yes. Basically using the prior period’s forecast as your starting point rather than starting from scratch with a blank sheet every single month.

00:10:19 Host 1
It’s the difference between doing a second draft of a novel where you only have to tweak a few paragraphs in chapter 3 versus being forced to retype the entire book from memory.

00:10:30 Host 2
That would be torture.

00:10:31 Host 1
You just focus your energy on what actually changed. But There’s a technical concept here I really need you to clarify for us because I think it’s crucial. Systematic variance handling or the roll forward.

00:10:43 Host 2
Right. What’s fascinating here is how this one specific systematic change eliminates countless hours of manual calculation and prevents massive portfolio errors.

00:10:54 Host 1
Walk us through it.

00:10:55 Host 2
Let’s do it. Imagine a project manager working on a massive $500 million pipeline project. Last month, they forecasted they were going to spend $10 million in May purely on trench digging.

00:11:07 Host 1
Okay, 10 million in May.

00:11:08 Host 2
But May ends, the actuals come in from finance, and they only spent 8 million.

00:11:13 Host 1
Wait, if I underspend by $2 million, doesn’t that just mean I came in under budget? Didn’t I just save the company 2 million bucks?

00:11:19 Host 2
Oh no.

00:11:20 Host 1
Why do I have to roll it forward at all?

00:11:22 Host 2
That is the exact trap that destroys capital portfolios. You didn’t save $2 million at all.

00:11:27 Host 1
But I spent less.

00:11:29 Host 2
Yes, but the physical length of the pipeline hasn’t changed. The scope of the project hasn’t shrunk. You only spent 8 million because it rained for 10 days straight and the digging crews couldn’t work.

00:11:41 Host 1
The physical dreg still needs to be moved.

00:11:43 Host 2
Exactly. So it’s a timing delay, not a cost saving. The company still owes that $2 million eventually.

00:11:48 Host 1
That makes total sense.

00:11:49 Host 2
Right. Now, in a completely manual Excel process, the project manager has to remember to manually take that $2 million and push it into June or July or sprint it out over the rest of the year.

00:12:03 Host 1
And if they forget?

00:12:04 Host 2
Which happens all the time when you’re managing thousands of line items, If they forget, the total forecast for the project is suddenly artificially short by $2 million.

00:12:12 Host 1
Oh wow. And then the controller sitting at headquarters looks at the spreadsheet, thinks they have $2 million of free unallocated capital, and redirects it to, I don’t know, a software upgrade project.

00:12:24 Host 2
Exactly. And then June hits. The digging crews finally get to work. The pipeline project needs that $2 million to pay the contractors.

00:12:32 Host 1
And the money is gone.

00:12:34 Host 2
It’s gone. You have a massive cash flow crisis simply because a spreadsheet cell wasn’t manually updated.

00:12:40 Host 1
That is terrifying. So how does the structured system fix this?

00:12:44 Host 2
With systematic variance handling. The central software like Stratex Online automatically rolls forward that variance.

00:12:52 Host 1
It does the math for you.

00:12:54 Host 2
It does. The system effectively says, you forecasted 10 million, you only spent eight. I am automatically taking this remaining 2 million and rolling it into your next period for you to review and allocate.

00:13:05 Host 1
So it removes the manual recalculation entirely. The project manager doesn’t have to play accountant. They can focus strictly on human insight. They can look at that 2 million and say, okay, the system rolled this into June. But the crews are fully booked, so I’m actually going to manually push it to August.

00:13:20 Host 2
Yes, they focus on the why and the when rather than the mechanical data assembly.

00:13:24 Host 1
And then once they’ve done their critical thinking in their Excel working layer, they just upload it back into the central system.

00:13:30 Host 2
Centralized validation. The file is uploaded into the structured environment, which immediately creates an immutable audit trail.

00:13:37 Host 1
Nobody’s guessing who changed what.

00:13:39 Host 2
Exactly. The CapEx controllers suddenly have portfolio-wide visibility. The data is uniformly structured, it’s comparable across all 50 projects, and it’s securely stored.

00:13:50 Host 1
Once that centralized, continually updated map is built, it fundamentally shifts the entire psychology of corporate spending, doesn’t it? According to the sources, it moves the culture from a retrospective autopsy into forward-looking active control.

00:14:05 Host 2
This is perhaps the most crucial mindset shift for an organization. I mean, most corporate finance departments spend way too much time obsessing over historical spend analysis.

00:14:14 Host 1
Looking at last month’s bills.

00:14:15 Host 2
Right. But analyzing historical spend is functionally meaningless without a highly accurate, updated forecast telling you what comes next.

00:14:24 Host 1
Wait, why is analyzing historical spend meaningless? I mean, if a project is 20% underspent against its year-to-date budget, the executives definitely want to know about it.

00:14:35 Host 2
Sure they do. But just like our pipeline example, variance alone is not insight. If I only tell you we are 20% underspent, you have no idea if that is cause for celebration or a massive red flag.

00:14:49 Host 1
Like did we negotiate incredible discounts on raw materials or are we just six months behind on securing building permits?

00:14:56 Host 2
Exactly. And if it’s the permits, it means we are about to breach our delivery contracts.

00:15:01 Host 1
So without the updated forecast, you are just blindly interpreting the past.

00:15:05 Host 2
What actually matters to the health of the company is the forecast at completion.

00:15:09 Host 1
Forecast at completion.

00:15:10 Host 2
Right. What is the total final cost going to be when the dust finally settles? And exactly when will that final check need to be cut? Real CapEx control is achieved exclusively through forward-looking forecasts, not retrospective autopsies of last month’s invoices.

00:15:24 Host 1
So what does this all mean? If you, our listener, are managing a project or overseeing A portfolio of projects, this architecture fundamentally changes how you are evaluated.

00:15:36 Host 2
It really changes the game.

00:15:37 Host 1
It’s no longer just about blindly hitting the original budget, right? It’s about accurately explaining the journey. The sources talk extensively about the importance of.

00:15:49 Host 2
Yes, the commentary is vital.

00:15:50 Host 1
It means mandating that project managers explain the why behind the shifting numbers. And this allows organizations to finally distinguish between what they call unavoidable change, like a sudden global supply chain shock, and avoidable inaccuracy.

00:16:05 Host 2
And let’s be honest, avoidable inaccuracy is usually just a project manager who is overwhelmed, lacks the right tools, and is wildly guessing at their cash flow every month just to get finance off their back.

00:16:17 Host 1
I can definitely see that. But let me push back for a second here. Doesn’t tracking every single variance and forcing mandatory commentary just breed intense, suffocating micromanagement?

00:16:26 Host 2
It sounds like it would, doesn’t it?

00:16:27 Host 1
Yeah. If I have to explain every single shifting dollar, I’m going to spend more time writing justifications than actually managing my construction site.

00:16:36 Host 2
I get that fear. But If we connect this to the bigger picture, it actually does the exact opposite. It dramatically reduces micromanagement.

00:16:45 Host 1
Okay. I’m struggling to see how adding more reporting requirements reduces micromanagement.

00:16:50 Host 2
Well, think about the psychology of why controllers micromanage in the 1st place.

00:16:54 Host 1
Why do they?

00:16:56 Host 2
Micromanage when they are terrified because they are in the dark. When a controller receives A standalone spreadsheet that shows a project is wildly off course, and there’s no context, no audit trail, and no explanation, they have to sound the alarm.

00:17:11 Host 1
Because they’re responsible for the money.

00:17:13 Host 2
Right. They have to call the project manager, send 20 urgent emails, demand cross-departmental meetings, and just aggressively interrogate the numbers.

00:17:22 Host 1
Because they have to answer to the board for that variance eventually.

00:17:25 Host 2
Exactly. Yeah. But if you have a structured system with centralized visibility where the variance is automatically tracked and the commentary is right there attached to the number.

00:17:34 Host 1
It’s like a little note that says, underspend by two 2 million due to unseasonal rain delay, crews rescheduled for August.

00:17:40 Host 2
Yes. If that’s in the system, the controller doesn’t need to make the phone call. The question is already answered.

00:17:46 Host 1
Trust is built through traceability.

00:17:48 Host 2
Exactly. By turning forecasting into a transparent, active control mechanism, controllers stop questioning the validity of the numbers and start actually using those numbers to make strategic portfolio decisions.

00:18:00 Host 1
The friction between operations and finance just dissolves.

00:18:04 Host 2
It really does.

00:18:05 Host 1
It turns forecasting from a dreaded administrative chore into a strategic superpower. But looking at all this structured beta, all this centralized visibility and historical tracking, it inevitably begs a pretty big question.

00:18:17 Host 2
Let me guess. Automation.

00:18:20 Host 1
Yep. We are living in the age of automation and machine learning. If the data is finally clean and centralized, why wouldn’t a corporation just fire all the human forecasters and hand this entire process over to artificial intelligence?

00:18:33 Host 2
It’s the existential question every single industry is asking right now. And the sources are surprisingly clear on this. AI definitely has a place in capital forecasting, but a highly specific bounded one.

00:18:46 Host 1
Okay, so what can it do?

00:18:47 Host 2
AI is undeniably incredible at analyzing historical data at a scale humans simply can’t comprehend. You can find hidden seasonal patterns. It can highlight anomalies in procurement spending that a human might miss.

00:19:00 Host 1
Like spotting a vendor that always overcharges in Q4 or something.

00:19:03 Host 2
Exactly. And it can generate a really solid, statistically sound baseline projection based on past project performance.

00:19:10 Host 1
But forecasting a multi-million dollar real-world physical project isn’t just a data extrapolation problem. It relies on real-time unfolding conditions that haven’t happened yet.

00:19:19 Host 2
That’s the crux of it. An algorithm can easily tell you that historically Phase 2 of a commercial build in this region takes four months and costs $12 million.

00:19:28 Host 1
Right.

00:19:29 Host 2
But the algorithm does not know that the primary steel supplier for phase two just declared bankruptcy at 8 A.m. this morning.

00:19:37 Host 1
Oh man.

00:19:38 Host 2
It doesn’t know there’s a category 4 hurricane forming in the Atlantic that is actively shifting its trajectory towards your job site next week.

00:19:45 Host 1
Because AI inherently lacks that project specific real world context. It does. It’s kind of like AI is basically a GPS that predicts your commute time based on yesterday’s traffic patterns. It’s incredibly useful, sure.

00:19:58 Host 2
Oh, absolutely.

00:19:59 Host 1
But the project manager is the actual driver looking out the windshield at a fresh 5 car pileup that happened 30 The GPS is giving you a baseline, but you would be insane to let it drive the car blind.

00:20:10 Host 2
That is the perfect way to look at it. AI is strictly complementary in this space. It can suggest a baseline. It can flag a historical risk. But interpreting those insights and dynamically adjusting the financial model for real-world operational changes, that remains a purely human process. So the humans are safe. The critical judgment required to produce a reliable forecast still sits securely with the people wearing the hard hats. They just need better structured tools to support that judgment rather than software that actively works against them.

00:20:43 Host 1
So to summarize the core value of this deep dive for you, Excel is a brilliant, necessary tool for modeling the unpredictable future, but it is a terrible place to store it.

00:20:54 Host 2
A terrible fridge.

00:20:54 Host 1
A terrible fridge. By recognizing the difference, by officially separating the flexible preparation layer from the structured system of record organizations, can completely eliminate the pain of manual data extraction.

00:21:06 Host 2
It really can.

00:21:07 Host 1
They can use pre-populated data, variance roll forwards, and structured environments like Stratex Online to give their project managers their time back, while finally giving their CapEx controllers the ironclad visibility they need to actually govern the portfolio.

00:21:22 Host 2
And as we wrap up, you know, spending all this time diving into source material, heavily focused on strict accuracy, variance tracking, and corporate accountability. It actually left me pondering a much deeper dynamic.

00:21:34 Host 1
Like what?

00:21:35 Host 2
A philosophical question, really. If A structured system makes project forecasting so incredibly transparent that forecast accuracy becomes the ultimate overriding metric of success, does this predictability actually make companies less likely to take risks?

00:21:51 Host 1
Oh, wow.

00:21:52 Host 2
Yeah. If you build a culture that strictly punishes bad predictions and budget variances, Do project managers unconsciously start proposing only safe, easily predictable, low-impact projects? Does demanding perfect foresight stifle major innovation in the process?

00:22:05 Host 1
That is a terrifying thought. I mean, if we optimize entirely for predictability, we might accidentally engineer out all the ambition. We just end up with perfectly executed, completely mediocre projects.

00:22:18 Host 2
It’s a tightrope walk for sure.

00:22:19 Host 1
We want you to mull that over as you tackle your own projects, budgets, and spreadsheets this week. Are you actively aiming for innovation and growth, or are you just trying to keep the stove from malfunctioning while you desperately guess what time dinner is? Thanks for taking this deep dive with us today.