Replace £3,000/Month QS Admin with Claude AI | Excel Demo | Gather

Hello, my name is William Doyle, founder of construction software Gather. I also chat to CoinSurveyor. So today I'm going to talk to you about how we can use the latest AI tools within Excel, which is a little bit of a weird thing to say, but within Excel to actually replace what we spend £2,000-£3,000 of the QS time with every month and replace that with a £70 Cloud subscription. So I'm not meaning to offend people here and saying people QS is just spend time on Excel. We spend a lot of time on Excel, so let's try and remove that kind of manual effort, that admin, those working long days and weekends, and instead focus on how we can use that. Okay, so we are in an Excel spreadsheet, very simple, very familiar to everyone. We can connect this data to Claude, to Copilot, another option here I've connected some Gather data. We can do this through our reporting API for our MCP connection, which you just press add here. I've pasted the data in just so you can see it from our reporting API, actually. Simple data here, so progress report straight from your site record, your site diary allocation sheet, part of your site record within Gather, but very familiar kind of format for most people. So resource allocated to activities. Also two static datasets, which are obvious to most people. So equipment or plant rates, labor or people rates, depending if you're using IT terms. And we've got our scope. So this is an example £9 million job based on a job up in Cumbria, but lots of the sensitive data removed. So let's get straight into it. So I've logged into Claude, £75 a month for the kind of more use than I can manage and handle. We can type into this using kind of natural language. So I've got some example prompts here. So create a new tab showing earned value over time using the project data. Again, different models. I prefer Opus. I think a lot of the recommendations at the moment is to use Sonnet, which is their newest model from Anthropic. Very similar sort of approach within Copilot. He is OpenAI. So let's have a go and see what happens. Fundamentally, I think this will change how we use Excel. So now we're asking Excel or Claude within Excel to do our work for us. It reads the data, so you can see it's read the Progress Detail Report. It's looked at some of the sample data. There's lots of rows here, 70,000 rows in allocation, so quite complicated. The big thing we'll start to see is that we are often limited by our Excel ability. And most people watching this video were pretty good at Excel, or even better than I am. I think I'm a bit of an Excel expert. We don't need to be Excel expert anymore. We now use coding languages that we're perhaps not familiar with, things like Python, JavaScript, I'm sure will appear in a second, to make these calculations for you. I'm not asking it to be a quantity surveyor, I'm not asking it to be a project controls specialist. Understands that knowledge, how to do these calculations fundamentally. Doesn't need me to tell them how to do it. It will go through the data, it will break it up for you. So very simple approach. It will present the data, it won't show the calculations. We can get it to start doing that. If you've got existing spreadsheets, maybe a big Costa Complete template, maybe something that you've used for years and it works for you, it can add and amend and work in that framework as well as doing this kind of big bang approach, which I'm going to start off with. Last time I did this, when I was practicing this morning, it created some fantastic graph and a very simple table. So let's see if it does the same now. You will find yourself, if you start to use this, spending a lot of time multitasking. You will leave Claude or Copilot, whatever you want to use, to, to work by itself You can press dangerously allow permissions for it to crack on and work, and you'll be able to jump onto your emails, about to look at a different spreadsheet, read or write the appropriate letters or notifications in your contract management tool, whatever you need to do. This will work in the background. I treat it as a team member. It's working and doing things that I would delegate to someone else. I just made this a little bit bigger so you can have a look like this. There's a lot of data here. Claude is very good compacting the conversation. It does have a context window. That's that content window gets full, it will need to compact that conversation. So 70,000 allocation rows, probably 15, 20 columns, that's a lot of data for it to look at. So it will have to compact it. The progress report, the scope report is quite simple. I find that Opus— I should look this up really, and I will put it in the notes— Opus seems to have a bigger context window, seems to deal with more complex tasks. Sonnet is described as your everyday kind of worker, works really well. These do update constantly. If you're watching this video in a week after I've recorded it, I'm sure something will have changed again. So yeah, it is quite fast-moving what you do. OpenAI, which supports Copilot, that will probably have a similar approach. If you are nosy, you want to sit here and watch this, and I was worried about a lot of the blank space whilst we watch Excel work. You can click and see what it's doing. It's not particularly useful. You can start to see what sort of data, what reports it's looking at. But you just need to let it work. And once the outputs are there, interrogate it. I do like how it does explain to you what it's doing. So given the large allocation detail dataset, it's taking a slightly different approach, breaking into chunks, a bit like a human would do. We won't try and tackle the whole problem in one go. It's a bit daunting. Can we break it down to something a little bit more manageable? And we can check that. So again, compacting the conversation. A little bit more than I would like to, but we'll see how this goes. This approach is very Big Bang. We're creating something brand new. I'm not really giving it much context in terms of what it's trying to do. If you had an existing template, this would be much, much quicker. I feel like it really likes to remind us that it's got 70,000 rows, so maybe I should have picked a smaller dataset, but I wanted to pick a real job that people relate to. Again, the big change here is that it will process the data, encode, and then it will present the data. That is the big change that I found. I would use this for the past couple of weeks. That can be good. We can deal with large datasets much quicker, not limited by how we normally use Excel. It can be more difficult when you want to manually find the root cause of a number. And question that. So I imagine a lot of people watching or listening to this will find that quite unnerving. It's quite useful when you can start to get that data, then ask it to do its assumptions, break it down, have that conversation with Claude to try and get to the bottom of why it's that number. So let's see if we can do that now. Okay, so it's presented as a table. Looks pretty good to me. A little bit different to what he did when I practiced this morning. We've got the key metrics that we'd expect there from any kind of value analysis. Excel charts, everyone's favorites. Let's see how it handles these. Nice. It does also create a log of its changes, so you can see here it's taken what the— I asked it, got record of your prompts, action taken, and then the outcome. I would recommend reading the output as well, so let's have a quick look at that. So, key findings. Yeah, just in. This project's hugely in delay. It's about right. Amazing. So, everybody else has some nice charts. I like this one. Create an extra one. So it isn't perfect, I'm not saying it is, but we're doing things from scratch. This will then update in real time. These yellow tabs are reporting API or the MCB connection. This is again, every time a record is approved, this will update it with that data. So that's really good. So big thing that I learned whilst practicing for this video was we need to clear the conversation. So a new chat, clear the chat. I talked about context windows before. We compact this conversation several times, we'd start again. So let's go back to our prompt. So let's do this one a little bit more complicated. So I've looked at a cost to complete. We can look at maybe a CVR. I created one before, a little bit more telling it what to do. So this is for measured mail analysis, my least favorite activity when I was a QS. So I've asked it to behave as a quality surveyor. Let's Again, use the Opus model. We'll read it through in a second. Let's just get it working. So very different to the Earn Value prompt, which is shorter. I've told it how to behave— you are a quantity surveyor. I've told it what it is. I've given it specifics, what I want. I want the measured mile. I want to understand which periods are the biggest issues. I've told it how to do the calculation. Again, probably don't need to do this, so I just wanted to have an experiment and see exactly how this will affect the output. And I've told it exactly what I want in terms of the output. Do these calculations, give it me in this format. So much more prescriptive. This is quite useful. Assumptions. I want to know how the large language model has interpreted what I've said, what it's read the data, what the weaknesses, what are the things that I need to know. I can interrogate those a bit like with the, um, Bayou analysis when it gave us that detail. I'd probably want some assumptions to go with this to say this is how we calculated it, this is the things you probably should look at. It kind of give me that in the chat, but that is lost. So let's have a see how it's getting on. So quite interesting, I have to use the data in this spreadsheet, do the following for each drain type section. So there's lots of different activities in this project. It is a drainage project, so it has picked out that we want to do these 3 drains, all measured in meters. So for those that use Gather, you know that we use objective units, so measure. We don't use percentages and input, so much easier to do this when you've got that data and that level of granularity and consistency. Again, we're having to compact it, understand what it needs to do, compact it once, it's having another go at compacting it. It does tell us it's a large dataset. Again, it's got the allocations, which has got the understanding of the cost per activity. We could do this just for the— with the progress detail, which is 700 rows, I think it was, but we need to know the detail, we need to know what it's cost as well as the outputs. Python is the obvious choice from Claude's perspective to get this right. So interestingly, it's explaining that it's going to use Office JavaScript and then it's going to pass it to Python for processing, which is what I seem to do for the last one as well. [FOREIGN] So it's going a little bit off on tangent here. So we want those specific activities. I've added a few extra ones. I probably wasn't as prescriptive as I should be in the prompt. So we go back to the prompt: for each drain type section. In hindsight, I probably should have explained that these are the 3 or 4 activities it had pulled those out, I think. Carry drain, filter drain, and culvert. So in culvert And now it's a little thing, it's gone, oh, these might mean drains as well. They probably do. Yeah. So more prescriptive in the prompt might be more useful. We can obviously refine this. We are again in this Big Bang approach where we're creating this giant spreadsheet from scratch. Most people will have this done once and then be just updating it using real-time data from site. Again, hopefully through Gather and then using Claude or Microsoft Copilot to do that. Again, what I find really fascinating is it changes our interaction with Excel. Normally we just think about cells, rows, and columns. It's now doing proper data analysis. It's taken that, it's put it in JSON format, which is quite useful for what it needs to do, and it's doing that analysis away from where we can see it. I wouldn't normally sit here and stare at it. I would be doing other things, go and get a coffee, whatever I need to do. But it will be— it's quite interesting to see this kind of go for its process, think about what it's doing. Lots of data here in terms of how we're bringing out that kind of key information around plan versus actual as well. Again, compacting again. This is a large model in Opus 4.6 and a large dataset. In hindsight, I probably should have demoed this on something much smaller, but I wanted to show something that's quite real. Taking a whole year's worth of data or more than a year's worth of data and doing a measured mile analysis because something's gone wrong, you're in a claim, that is a common situation that people will spend days, if not weeks, doing. Days and weeks usually because they haven't got the data. Again, a shameless plug for Gather. Having Gather structured data makes this so much easier. At the start of this video, talked about replacing 2 or 3 grand of QS time. You replace 2 or 3 grand of QS time by just having proper records. The next 2 or 3 grand of QS time is replaced by using something as fantastic as Claude. Again, structured data with this level of intelligence, this level of speed, it's fantastic. I also love how positive Claude is. It's always having a good day, it's always helpful, always thinks it can solve any problem, which I really, really quite like. Not that I'm treating like a human, but you start to understand how these things think, in inverted commas, but certainly how they present themselves to humans is really interesting. Quite a new feature as of today is that you can start to allow it to work across multiple files, which I think is quite useful but also quite scary. So if you want to do a summary across 2 or 3 different workbooks, maybe you've got 10 different project CVRs and 10 different spreadsheets, you want to bring it all together for one monthly one for your whole framework, that would be quite cool. But you can start to bring context and again think about information security. We use the MCP connection which is built for large language models, Anthropic kind of with a thought leader on behind that, and it works really well. Everyone has to authenticate to connect together, to connect to whatever data source. It's a nice way of doing it. Since we're into a little bit of a problem here, too much data, we can start to see how it's going to deal with that. You can see what it's doing here. I think it is doing it wrong. Again, when I wrote that prompt, I think I realized my mistake. It's now aggregating it as weeks for the impact of delays or the unimpacted. So if something does happen in a week, I suspect it's saying, well, that's been impacted. Really want to look at this on a shift-by-shift basis. So again, I think better prompts, or maybe less prompts. I think my prompt was a little bit too much. The Envoy one seems to be really good. I'm looking here, it's done exactly what it says it would do. I would probably want to have it in a shift-by-shift rather than a weekly issue. Let's see what it says. I will share all these prompts, and there'll probably be a finished version of these sheets which people can play around with. You've got to see the prompts history in the Claude log, which is this, which tells you exactly what we did. So I will share that in the comments of this video. Excel does this. Okay, let's have— so it loads it weekly, which is interesting. I'll do a separate video on contractual use of this. Now you can link it back to NEC4 or whatever your contract is, where the ground condition is obviously quite interesting to approach. Doesn't really work for NEC on a shift-by-shift basis. Quite like this. It, it's useful. I think looking at a shift-by-shift basis is probably what's required. However, understanding the baseline rate across a week is quite useful. These outputs are shocking if I was to estimate this project. Be nowhere near it, and now it's been really affected by weather and physical conditions. Yeah, what is really good here is the assumptions. So any assumptions around time— 1 week, 5 days— again, we need to go back to work on a shift-by-shift basis. That's quite useful. So if anyone understands how you've got to that point This isn't perfect, this needs a little bit of work, and this is probably— I'd almost start again and make the prompt really simple, but it gives me exactly what I need to change. I'd also narrow it down to just using those activities that we talked about rather than something that is slightly unrelated. Does flag when you've got not enough data, which I think is good. This is really good. So variant category is often labeled as weather, but when it reads the whole shift, which is— it does— other non-weather-related issues come out. So that's the beauty of large language models. It does read the individual shift record item. I think, gather, we record every single line item, but then it reads the anecdotal stuff that goes with it. So that is really interesting. Okay, this is pretty good. I don't want to spend too much longer on this because this video will become extremely long, but it's a good start. Again, I would encourage people to have a look at this and give any feedback, and let's try and get to a better prompt where we get to perfect measured male analysis every time. Okay, what should we do next? Let's have a look. Let's do something that's a little bit easier. Cost to complete, maybe not easier, but let's find out. I've done it again. I've done a long prompt. I want to clear the chat. It's a new context window. Let's have a look. So use Opus. Let's try sonnet. This may go well, it may go horribly wrong, so let's find out. So just to explain the prompt, so we're saying use the data in this spreadsheet because now we can look at other spreadsheets, remember. What we want in the rows: labor and equipment, then by role name. Okay, we don't want to go down to name level, that's a bit annoying. Include, include rate, come from each resource, that is good. One column per week, hours and cost, that's exactly how I was taught to do it. Shout out to Matt Adams, who was my first QS who told me how to do this, first managing QS. Actuals Play all weeks. Yeah, it's good. And then add 10 blank weeks so we can see some forecast. That's really good. Okay, let's see how this goes. Again, it's having to chunk up the data. When you build this template for the first time, you'll probably have no data, a few weeks of data, so it will be easier. Now we're actualising it. I remember when I was a QS working for a contractor, one of my jobs every single day or every single week was actualising the cost to complete from the site diaries, paper, Excel sheets, and then we could understand roughly what our cost of work done was, and then we reconciled against our COINS report or cost download at the end of the month. So quite a nice activity that we've now or hopefully automated. So this is quite interesting because you start to change data, start things around. I would recommend you do allow once. The purpose of this video and speed, I want to dangerously allow for every single column. So let's see how this goes. Another thing to say is that Claude can delete all your data. Quite scary. So I would recommend you use the autosave if you're using kind of SharePoint or OneDrive. And then I would also recommend saving it a different version. So when I've started today's version, I've called it Gather Claude. The version I was playing on this morning was named with the date. So yeah, we can limit ourselves against that, but just be aware it Hasn't done it to me in full, but it can delete all your data. Again, that's another reason why you want to use an MCP connection. You want to use the Reporting API rather than copying and pasting raw data in. You just want to be able to feed the data in that you need, not having to just have this big manual upload that you're doing every single time. Okay, that was interesting. This is a little bit of a simpler task, but Sonnet seems to be a little bit quicker, so quite like it. Maybe I should read the instructions and listen to what people at Anthropic who produce Claude say. That's quite nice. There's this horrible thing where it freezes the rows, so I'm going to just kind of fix— it's not quite nailed merging cells and how humans view it, but we get the idea. Okay, much quicker. It's gone through the sources quite quickly. Okay, so really good kind of assumptions just reading through those. So tells exactly where it's getting its rates from, so cost of the allocation report takes the resource rate book and applies that, grouped it, but also we can start to see separate roles. We might have separate rates this particular Price Resource Rate Book or Price Book doesn't have different rates for supplies, but that is a real thing. Does include what we call productive time, so activities, breaks, and non-productive time, which is called NPT. So yeah, you might want to have that as a separate thing, which is quite interesting. You might not pay breaks, all sorts of good stuff. And we've got the forecast week. So yeah, quite happy with that. Everyone's covered, probably, for formatting. You start to see the Forecaster, and we can set it to autocomplete. Let's see if this works. Let's assume same resource the last 3 actual weeks or next 10. Can you have to spell it perfectly? It's pretty good. How does that affect the AFC? Let's see if he knows what AFC is. This is another great use of it. Didn't really think about this straight away, but you don't have to get it to write anything. Doesn't have to update your spreadsheet, doesn't have to create you a brand new spreadsheet. You can just talk to it about your data. What would be the impact of this? Things that you might want to talk to a colleague about, things that you might can't be bothered to work out those different scenarios. What would happen if we did this? You can just type, you can do 3 different versions straight away. Yeah, it's quite nice. 3 weeks was average 41K a week, so let's see if it adds another 400 grand of— Yeah, this is quite good. So yeah, I would encourage people to use this to really hammer in lots of scenarios. What if this— what has happened? What if we work 12 hours, not 10 hours? Obviously that doesn't— you can't show that in cost to complete, but you might be able to factor in overtime and all that kind of impact. We can work in the cost of that. What if we finished 4 weeks earlier than we did? What were the impacts in the cost? So stuff we can all do, but do it really quickly is quite nice. So let's do one or two more. So I'll do interim application another time when we do our contract. Let's do a labor costing one. Okay, new chart, clear the chart. I always do that. Start again. Let's see, we'll use Sonnet. How are we getting on? So allocation progress detail. So linking the reports, obviously Shift Record IT, we, again, we've been really prescriptive in the prompt here. We'll look at weekly, we're going to look at cumulatively, and then break it down that way. We also want to break it down by supplier. I want to be able to get this, look at my Cadigilab supplier, and say roughly how much I've spent. So let's see how this goes. It's already compacting, which cannot be good. In terms of writing these prompts, it's entirely up to you. I will share these. I was practicing a few hours ago and I kind of wrote some bullet points out, then got Claude to format them, so it just kind of worked it through. I would try and go for the simpler ones. Maybe we'll try that for the blank cost tracker, but that would be different. Earn value, see where we get to. Sometimes less is more. Do you want to do the CVR one? Do you have a longer and a shorter prompt for it? So let's try the short prompt after we've done this. I do think they should perhaps improve the thinking and just saying 100% through it is— I appreciate that it's still working out the problem, but lots of thinking or responding sometimes is a little bit worrying. Okay, this is good. For that, let's have a look at this prompt. So I think we did the CVR. I've got a shorter prompt for this. I think the only other thing I'd probably want to do is Just a bit easier to read. Add in— direct— let's play— let's download this one. So we get real-time indication of costs from your site records, but doesn't include some of the more kind of abstract rules around payroll. Plant delivery charge might get missed and you want to reconcile against your cost tally at the end of the month. So we'll think about that. So let's listen back to the labor crossing. Sorry, not made much progress. Probably should have picked Opus. This is a bigger task than I thought. The thing about skipping sections and fast forwarding— I will do times and so people don't have to listen to me later on, but I I think I wanted to show realistically how long this took. What can I achieve in an hour? Interesting, because this is definitely not how I would have done it. This is why it's taking so long. We don't need it by activity. It's giving us by activity. It's effectively recreated the allocation report. The more I do this, the more I think that shorter prompts are better. Get your spreadsheet up without any kind of actual data in and then put your data in. The activity element is— it was just the allocation report from Keller, and all I've done is present it in a slightly different way. There looks good though. I've done this horrible thing where it can't freeze to paint correctly. It's currently fighting me, but let's see how we get on. So interesting. So probably the worst result I think is it's misinterpreted the need for that. Yeah, let's pause that. Pretty good, I think it's all there, but it's just the allocation report return. So let's create a new chat, clear chat. Let's have a look at the Shaw CVR prompts. Let's just make sure. So create a CVR tab using the allocation progress return pricing schedule. Okay, let's call it the scope. So we're going to link it back to the area and the activity. Call it activity location in progress detail. Okay, makes sense. Contract values from the pricing schedule— we don't call it pricing schedule, call it scope. Definitely an open task. Let's, let's get some. Sorry, tell him it's going to be difficult. Thank you. I can— most companies will have their own CVR format, so this will be not building a CVR from scratch. This will be how do I update it, how do I check it, how do I put the what-if scenarios, how to link it back to my cost to complete so I can understand what my outcome should be. That's where this will be really useful. The other huge Time saving. We talked about the Measure Mile Analysis, which we might do slightly differently. We'll do a separate video on this. Claim consultants, I think good structured data is what most claims consultants are crying out for. It's the reason I started Gather. You've got that now with Gather. We've got millions of records, structured data straight into your large language model of choice, into Excel, into your other softwares that you use. And then we've now got this ability to interrogate that data in natural language. You might be a— claims consultant's got some really good templates that you like to use. They can now just be done for you. So the huge amount of time you spend on admin, getting data, manipulating data, that's gone. And those people that are genuine program contract experts will just fly. I think all that kind of grunt work will be gone. The cost of doing claims will be far cheaper. We might have inadvertently created a claims culture where it's so easy now to put a claim in and so easy to have that backup. So we do that more often. And maybe that's the right thing. Maybe we want to get to the right answer quicker rather than look at claims or whatever the equivalent is in your contracts as the last resort. Maybe just getting to the right answer quicker is what we're trying to do with this structured data with a live language model. Okay, CVR's already starting to build, which is great. I feel like they're actually creating the tab is that one of the last things it does, so it's got through it quite quickly. I've gone on my rant about claims consultants, so let's see how this goes. Does like to tell us that it's a huge or massive dataset, every single opportunity. I think we have to put some background music on this video. Lift music, because we— I spent a lot of time just waiting for Claude. 45 minutes gone so far, so let's see how this goes. Okay, this is interesting. This is a CVR like you've never seen a CVR before. Okay, this is fantastic. So let's just talk through what it's done. So we've given us our scope. So just remind you what it looks like: area, activity, planned quantity, unit measure, unit rate. Value. It's done all that easy stuff, got our contract value, and we've broken it down by area. What we've actually achieved to date, again, it's used the real-time progress data. Simple equation of what we've got left, earned value today, cost to date in terms of labor and equipment, total cost to date, again worked out from your allocation sheet, forecast cost to complete because we've How does it work that out? Okay, that's very interesting. So it's worked out our forecast cost complete based on its new unit rate if you keep producing this activity. So this is an easy one to explain. Let's just make it something that's easier. So installing catch pit. And 42, and we spent 52 grand. Let's see the remainder. It's gonna cost us 69.5 grand. So really nice simple logic. You could probably replace that calculation or have it alongside your actual cost to complete if you've got that level of detail. Anticipate final cost. Any VOs would obviously go in there. Final value, so The final value is just simply variations plus the scope. Yep. And then the margin. So this job, we know, huge issues with disruption. Fantastic. And you can put your monthly reconciliation terms cost there. So what a way to lose 212% in terms of a check. Let's just make sure it's got this. So 8.9, yeah, is the scope. We've got verifying each of those. By today, 2 million. Cost date, and that's 4.7, that's correct. So yeah, if we're going to complete this job in the same fashion, it's going to cost us 28 million quid to do. £9 million of a scope. So yeah, really interesting, really good stuff. This is probably the best one. I quite think this is a level of detail that people just don't do at the moment. Don't have the data, can't bothered— can't bothered is strong— don't have the time to do it because they're doing hundreds of different things. Now you can do this really quickly. So this is 50 minutes or so of trying to use Claude in Excel, link back to your structured site data. Can it replace 2,000 or 3,000 pounds of a QS time? 100%. Can it replace QSs? No, we've seen that in the measurement analysis, we've seen that in some of the labor costings. Someone who knows how to use these tools become very efficient and very valuable. So hope that's been useful. We'll do a few more videos on this, individual prompts, individual sheets, and some around the kind of contractual side with maybe some NEC, how we do that. Thank you much for watching. Cheers.