Monday Morning Mulling: March 2019 Challenge

On the final Friday of each month, we set an Excel problem for you to puzzle over for the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.

Welcome to this month’s Monday Morning Mulling. I imagine a fraction of you worked out last Friday’s challenges…

The Challenge

As mentioned previously, in a recent financial model build, we needed to work out how much of a certain product was sold, which reduced each period, based on two multiplicative factors. Over the life of the project, the proportion that needed to be considered was:

In Excel, I just needed to sum this as efficiently as possible­ – returning the total as a fraction in its simplest form.

Therefore, the challenge was to create an Excel spreadsheet that can calculate the result and be sufficiently flexible to allow for alternative, similar sequences of varying length.  I stipulated you should not use dynamic arrays, VBA or megaformulae.  How did you do?

Suggested Solution

Often under time pressure, we resort to becoming hapless automatons that simply crunch numbers without thought.  Yes, I could just add the numbers up constructing an Excel summation.  However, the spreadsheet would return a decimal such as

which provides little insight.  Furthermore, I actually wanted the number expressed as a fraction.

It’s trivial to note that the numbers in the denominator were consecutive pairs in the sequence

i.e. the pairs were always a gap of three (3) apart (e.g. 5 – 2 = 3, 8 – 5 = 3, 1,001 – 998 = 3).

However, did you spot that

and, more generally,

This is where readers who are parents may note their children might have helped! The trouble is many of us learned fraction subtraction a lifetime ago and either (a) have long since forgotten it and / or (b) never understood it in the first place!

After this, the problem becomes straightforward:

The attached Excel file shows how this could be constructed automatically.

At this point some of you will state that they could have done this simply by formatting cells and choosing ‘Fraction’ for the result:

But that will not always work – like here.  If you did this with this month’s challenge you would have obtained 83/499 and not 333/2,002.  These two fractions are not equivalent.  Therefore, if you did this you would have calculated the wrong answer.

Even if you have done what I have done so far, there is still one more step.  The fraction has to be in its simplest form, i.e. where the numerator and the denominator share no common divisor grater than one (1).  This may be achieved using Excel’s GCD function.

In mathematics, the greatest common divisor (GCD), also known as the greatest common denominator or the highest common factor, of two or more non-zero integers, is the largest positive integer that divides the numbers without a remainder.  

The Excel function GCD returns the greatest common divisor of two or more integers.  The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder.  It has the following syntax:

For example, the GCD of 333 and 2,002 is 1, so this is the simplest fraction.  However, if we extend our sequence to

Then the result is

This is because GCG(400,2404) equals four (4), so both the numerator and denominator may be quartered.

Again, the attached Excel file shows how this may be achieved – and how you can use it for other similar problems.

Word to the Wise

Whether you took a similar approach, chose a more convoluted route or simply didn’t bother, do note that sometimes financial modelling is about thinking more simply. It’s not always about who knows IFRS, the latest fancy functions in Excel or VBA, sometimes it’s simply a case of spotting there’s a wood in those trees.

The Final Friday Fix will return on Friday 24 April with a new Excel Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business workday.