One of the major problems with monthly reporting, is keeping figures correctly aligned in Board Reports say in Word Format and the accounts say in Excel. Invariably last minute revisions are made to the accounts and not the Word document and also human transposition error creeps in.
In my video showing the reporting model for my imaginary company BAR Fruits Ltd, I made extensive use of interactive text within Excel, which both eliminates the chance of error, and shows the correct figures for each month chosen.
The procedure is quite straightforward, simply joining your text using & and formula or text within quote marks. I would recommend, that you transfer the calculations to a calculations sheet, and use named ranges wherever possible.
Experiment yourself, but the following Formulas, especially in combination, are very powerful:
LARGE returns the n-th largest value in a range i.e. LARGE(array,n) and SMALL the reverse. I have used this to find the highest, second highest and lowest regional sales, and then combined with VLOOKUP to return the Regions name in text.
RANK returns the relative position of a number in a list i.e. RANK(number,array,order). I have used this to find how a regions is ranked in terms of its gross margin performance relative to the other regions.
MAX returns the largest value in a range i.e.MAX(array) and MIN the opposite. I have combimed this with HLOOKUP to return the name of the best sales month for a region over the year found by MAX.
ROUND will help format the number for inclusion in the text i.e. ROUND(Number, number of digits)
IF allows you you to choose different narrative for above or below budget i.e. IF(test,value if true,value if false)
ISERROR allows you to eliminate values when the formula might return an error, and can be combined with IF, i.e.ISERROR(Number,value if an error)
The following is an example of interactive text which is linked to the underlying figures and automatically updates on a monthly basis or when you make any revision to the figures in the Bar Fruits reporting model.
See a Mindmap click here and press + to expand narrative on any branch.