In Excel, as in life: together we really are stronger

Here’s a statement that strikes fear into my heart: “Sorry to bother you Mark, but I am sure this one will be easy for you”.

I get quite a few of these calls – and I’m happy to have them (it’s fun for me to see whether I can find a solution).

But experience tells me this.?If you’re a bright person (always true) who has spent quite some time through your career in Excel (also always true) and something you've wrestled with a bit for yourself before contacting me (pretty much always true too) a challenge that’s proving tough for you is likely to be tough for me.?The thing that really seems to surprise people in these conversations is that many day-to-day problems that are very easy to get your head around or explain to someone else prove surprisingly difficult to solve in Excel.

I have a suite of them that includes things like waterfalls, customer categorisation, proper debt modelling, and debtors (actually working capital modelling in general).?Straight line depreciation also goes in (why would that be difficult?). As does ‘layering up’ other things apart from the capital expenditure that’s feeding into your straight line depreciation e.g. if you’re doing something like a store roll out - with lots of line items multiplying up from revenue down.?All these challenges are pretty easy to understand and describe but pretty hard to implement.?If you’re an experienced modeller you’ll know what I’m talking about – and you already know you need a ready bank of solutions to all the above.

Here was this week’s “Mark I’m sure this will be easy for you” challenge: working out how many days in a calendar month a contract runs for.?It's one that's really easy to understand:

• each contract has a start date (any day in the year)
• each contract runs for an input number of days
• what we want to know is how many days each month the contract is running for.?First and last months will be part periods.

Remember good modelling practices would see you break things into steps

The usual good modelling practices come to the fore here. If you allow yourself the ability to break things apart into a few steps, the problem becomes clearer, easier to understand, easier to check, easier to solve and harder to mess up.?In a real life model like the one I was looking at this week I would strongly recommend breaking things into steps, even if the data quantity is large.?I’d even be happy to think about restructuring into a series of tabs that step through calculations.

Sometimes people are in a hurry though, and want everything in one place in one line (which will usually result in a long error-prone formula hard to interpret and prone to future breakage).?Interestingly, to safely create the formula in one place, you’re still going to want to break the formula into steps to check each one as you go, reassembling into a single formula at the end.?What, in a few understandable steps (with subsequent steps referring to preceding steps without confusing anyone too much), was elegant and a thing of probable beauty, now becomes quite ugly.?Which again makes you question whether that last step (banging everything together in one place) really is a smart one.

Where I totally bombed this week was in being over-confident and thinking I could create the formula in one place straight off.?I couldn’t.?As I tested (on reflection, unsurprisingly) it kept breaking.?Eventually, after lots of testing, lots of breaking, I smacked myself round the back of the head, went back to basics about 7pm.?Laid all the steps out and from that point things started working a bit better.

Remember team work really does make the dream work

And what I also forgot was (yes it’s true): “Together we’re stronger”.?This is what happened when I (eventually, I’m a slow learner) bothered to mention this to one of my own great high priests Rob Bayliss:?"Hmm I'm sure I’ve done this before”.?Pure formula poetry was the result:

It's all worth it in the end

As always, the pot of gold at the end of the Excel rainbow here is that sweet feeling of success when you know the problem is well and truly cracked and everything's working.?I expect it’s the same kind of adrenaline high jet fighter pilots enjoy.?But if you're a financial modeller you can get it from Excel, every day, and it keeps you coming back for more.

Anyway, it’s been a fun week.?And a reminder of all the usual things. Good modelling takes proper effort, careful application of robust practices, and friends.