Building A 3-Way Cashflow Forecast From Scratch In <13 Hours (That Secures £100k In Grant Funding!)
Posted on 15th February 2017 in Advisory
Written by Jane Aylwin
Jane Aylwin is the Managing Director of Added Perspective. The Sussex-based team help hundreds of business owners in the UK to get on top of their numbers. Jane’s driving force is helping her clients to understand the power behind their numbers. She loves forecasting the future, setting budgets and targets with the client, and supporting them in achieving their goals. In this guest post, Jane talks about how she made the impossible, possible, using FUTRLI to create a forecast for a client with a looming Sunday deadline.
You know how it is when one of your favourite clients calls you up at 16:30 on a Thursday and says…
Client: “We’ve got the opportunity to apply for a Coast-to-Capital grant for £100k of funding to extend our workshop. They’ve accepted our application in principle, but we need some figures to proceed to the next step, can you help?”
Me: “That’s great news! Yes, of course, I can help. What exactly are they looking for…?”
Client: “We need to show cash, profit & loss and balance sheet forecast(s) based on two scenarios, either getting the grant or not getting the grant, and it has to be for the next three years…”
Me: “Ok, that sounds doable, let’s get together and have a chat through the application, your exact plans and the targets we need to hit and then I can put it together for you – when are you free?”
And then they casually blow your weekend right out of the water
Client: “Ah, well, you see the thing is, we need to get this submitted by lunchtime on Sunday…”
The full story
The grant was match funding and the rest of the funding was to come from finance on brand new machinery for the workshop. The grant would then finance an extension to the workshop to accommodate the new machinery and upgrade the office systems. Ultimately, generating new employment opportunities and accelerating the growth plans for the company.
That’s kind of a big deal and no self-respecting advisor should ever have to turn around and say they can’t help.
However, it meant that not only did we need a 3-year, 3-way, 2-scenario forecast, we needed to allow for the purchase of new equipment, associated depreciation and loan interest and repayments.
Oh yes, and then we had to plan for the sensitive timing. The grant had to be paid over in full by 31 March 2017 (at the time, this was 5 months away), which meant the equipment had to be purchased (and some old equipment sold) and the building work completed by then.
The cash flow had to support that, as the grant would only be paid on production of validated invoices. The loan would be drawn down as the equipment was purchased, just for a bit of added complexity.
Of course, our application would be subject to due diligence performed by a highly respected local accountancy firm.
Well, luckily, I love a challenge… and I have FUTRLI!
Some, very honest, background info…
I have always loved FUTRLI, but when I first started using the system in February 2015, FUTRLI had been in the market for only six months and we had only really just started to fully embrace the Cloud Accounting revolution. We’d been on board with Xero in earnest for 6 months prior to this and I had trialled various add-on reporting and forecasting apps, with FUTRLI, hands-down, winning me over as the favourite.
But boy did I struggle in the early days. What we were producing in Excel was complex and I wanted my forecasts and boards to reflect these so that the client had continuity. Honestly, I found creating the layouts we needed too complicated at the time and that meant it was high maintenance.
As a result, we were then using the platform for producing highly visual and very engaging target versus actual Dashboards, but I was back on spreadsheets for the very complex reporting and forecasting, and sadly, had I stayed there for way too long as it turned out.
Anyway, I digress, my point is that I was suddenly in a bit of a situation, sure, our accounting information is real-time, obviously, so that was fine, no catching up to do on that front, and we had a cash flow forecast. But there’s no way I could create a profit & loss and balance sheet forecast using my spreadsheet.
I’d have needed days and someone who’s much better at Excel than I am! However, what an opportunity for the client, there had to be a way. I had to get to grips with FUTRLI, and fast. It was my only option.
Steep learning curve
My first thought was ‘wow!’. Everything I had wanted FUTRLI to do back in the early days, it now does, so I kicked myself for not getting back in the saddle earlier. Luckily, I didn’t have a lot of time to berate myself over that oversight!
I mapped everything over, got stuck into the help files, and chatting online to FUTRLI’s support team and, just 4 hours later, I had created the base layouts, current analysis and cash flow that matched what we already had in place for the year. Unbelievable.
I then met the client to go through the application form, the targets, delivery dates, employment figures and to drill down into exactly what the two scenarios would mean for their expansion plans.
Another 7 hours in and I had two perfect working scenarios. It would have been less, the reducing balance depreciation calculations stumped me for a good hour but I created a workaround that gave me one of those air-punch moments (that you’re always glad there’s no-one around to witness) and definitely increased my IQ by a good few points. I hear there are things on the roadmap to solve this too…!
I sent the forecasts over to the client, we made a few tweaks and BOOM! 13 hours of work, on a platform I had barely used for almost a year, and I had produced exactly what they needed.
I am happy to report that the grant was approved pending due diligence. Unfortunately, as is often the way, the hoops were too numerous, and the conditions on the loan funding too stringent, for the client to progress.
This was not the fault of the forecast, that was spot on, and I learned something amazing (and got right back on board with FUTRLI). As for the relationship I now have with the client…well, what do you think?