Automatic commentary writing formula in Excel – Amazing LAMBDA

For a finance professional in industry, nothing is more annoying than writing the period-end or forecast commentary. (OK, maybe the words “last-minute change to provisions” is more annoying, but the commentary is right up there). You’ve created the reports, found the reasons for the variances, and now you need to write it as a text summary. It always takes longer than it should and generates more re-work than it should. Today I want to share an automatic commentary writing formula in Excel to automate that process.

NOTES:

• This is an example of what is possible with LAMBDA and dynamic array functions. I’m not suggesting that it’s perfect or will work for your scenario. However, if you can take it and adapt it to work in your business, it can save a lot of time and frustration.
• You will need a LAMBDA-enabled version of Excel to work along with this post (Currently only Excel 365).

Watch the video

https://youtu.be/Gx3VZ9J5tuQ

Watch on YouTube

Download the example file: Click the link below to download the example file used for this post:

Automatic commentary writing function

The function is built as a LAMBDA function; therefore, you can copy and paste it into your workbooks. But before we get to that, let’s look at how it works.

Basic Example

Look at the screenshot below. The commentary text in Cell D11 has been written by my AUTOCOMMENTARY function.

Because it has been written by a formula, when the values change, so does the commentary text. For example, look at the screenshot below; the variance description has changed based on the new values.

Syntax

The syntax of the function is as follows:

=AUTOCOMMENTARY(Heading, ComparativeName, SubHeadings, BaseValues, ComparativeValue, Reasons, SortOrder, PlusWord, MinusWord, StatementNumberFormat, VarianceNumberFormat, Threshold, DecimalAccuracy)

Yes, there are a lot of arguments, but this is to create the most flexible function possible.

Arguments

The arguments of the function are as follows (all arguments are required)

• Heading: The name of what the commentary is about (e.g., Sales, EBIT, Stock, Debtors, etc.)
• ComparativeName: The name of the comparative measure (e.g., Forecast, Budget)
• SubHeadings: The array of names by which values are categorized (e.g., EBIT might be Sales, Cost of Sales, Salaries, Overheads, Depreciation)
• BaseValues: The array of base values that correspond to the SubHeadings list
• ComparativeValue: The array of comparative values that correspond to the SubHeadings list
• Reasons: The array of values that contain the reasons for each variance
• SortOrder: The sort order for displaying variances in the commentary text:
  • 1 : Ascending order
  • -1 : Descending order
  • 2 : Absolute ascending order
  • -2 : Absolute descending order
• PlusWord: The words used to describe a positive movement (e.g., “higher than”, “favorable to”)
• MinusWord: The words used to describe a negative movement (e.g., “lower than, “adverse to”)
• StatementNumberFormat: The text string of the custom number format for the total base number (e.g., “$0.0,,\m;($0.0,,\m)”)
• VarianceNumberFormat: The text string of the custom number format for the variance numbers (e.g., “+$0.0,,\m;($0.0,,\m)”)
• Threshold: The minimum value to report variances on. Any values below this threshold are either ignored or grouped into a single comment (e.g., using 100000 in this argument ignores all variances lower than this amount)
• DecimalAccuracy: The rounding accuracy applied to numbers. Use negative numbers to round to 10’s, 100’s, 1000’s, etc. (e.g., -6 rounds to the nearest million).

Additional notes:

Applying the arguments in your scenarios can become complex, as simple mathematical concepts lose meaning in accounting. For example, what is considered as positive or negative, or favorable or adverse, can depend on the signage of a report and also differs between companies.

The following demonstrates the mixture of terms and how they are viewed in different businesses:

• Cost of Sales may be shown as a negative number as it is a cost, or it may be shown as a positive number because everybody knows it is a cost.
• Higher Trade Creditors may be considered positive variance because we didn’t pay out the cash, or negative variance because it is a higher liability.
• On credit balances, the word “higher” means a larger credit. But credit balances are shown as negative numbers; therefore a “higher” balance results in a “lower” number.

There are 4 arguments that all determine how numbers are compared and displayed StatementNumberFormat, VarianceNumberFormat, Threshold, and DecimalAccuracy. Take time to understand these arguments and how they impact the outcome of the text.

Once the formula is set up with the correct signage and numerical significance for each area, it should work correctly.

Examples

Let’s take a look at some examples. These are all available in the example file.

Example #1

The formula in cell D23 is:

=AUTOCOMMENTARY(A4,C4,A5:A8,B5:B8,C5:C8,D5:D8,-2,"higher than","lower than","$0.0,,\m;($0.0,,\m)","+$0.0,,\m;($0.0,,\m)",100000,-5)

This displays:

• Variances shown in absolute descending order (i.e., biggest impact to smallest impact)
• Numbers are displayed in 1 decimal place millions
• Values less than 100,000 are below the threshold
• Values are rounded to the closest 100,000 (i.e., decimal accuracy of -5)

Example #2

The formula in cell D36 is:

=AUTOCOMMENTARY(A16,C16,A17:A21,B17:B21,C17:C21,D17:D21,1,"higher than","lower than","$0,\k;($0,\k)","+$0,\k;($0,\k)",2000,-3)

This displays:

• Variances in ascending order (i.e., smallest number to biggest number)
• Numbers are displayed in thousands
• Values less than 2,000 are below the threshold. After detailing the key variances over 2,000, the remaining variance is zero; therefore, no further statement about the smaller values is required.
• Values are rounded to the closest 1000 (i.e., decimal accuracy of -3)

Example #3

The formula in cell D49 is:

=AUTOCOMMENTARY(A29,C29,A30:A34,B30:B34,C30:C34,D30:D34,-1,"favourable to","adverse to","$0\k;($0\k)","+$0\k;($0\k)",10,0)

This displays:

• Variances in descending order (i.e., smallest number to biggest number)
• Numbers are displayed with a k to indicate thousands even though the values in the source data are not in thousands (the assumption is that the source data is shown thousands already)
• Values less than 10 are below the threshold, and therefore swept up in the final sentence.
• Values are rounded to zero decimal places (i.e., decimal accuracy of 0)

Example #4

The formula in cell D61 is:

=AUTOCOMMENTARY(A42,C42,A43:A46,B43:B46,C43:C46,D43:D46,-2,"higher than","lower than","$0.0,,\m;($0.0,,\m)","+$0.0,,\m;($0.0,,\m)",100000,-5)

This displays:

• Total variances are below the threshold; therefore, no detailed variance commentary is returned.

The Function

Right, now let’s look at the function itself.

The code

OK, here goes; it’s a long one.

=LAMBDA(Heading,ComparativeName,SubHeadings,BaseValues,ComparativeValues,Reasons,SortOrder,PlusWord,MinusWord,StatementNumberFormat,VarianceNumberFormat,Threshold,DecimalAccuracy,LET(BaseTotal,ROUND(SUM(BaseValues),DecimalAccuracy),ComparativeTotal,ROUND(SUM(ComparativeValues),DecimalAccuracy),VarianceTotal,ROUND(BaseTotal-ComparativeTotal,DecimalAccuracy),VarianceValues,ROUND(BaseValues-ComparativeValues,DecimalAccuracy),SortOrderValues,IF(ABS(SortOrder)=2,ROUND(ABS(BaseValues-ComparativeValues),DecimalAccuracy),ROUND(BaseValues-ComparativeValues,DecimalAccuracy)),isAre,IF(RIGHT(Heading,1)="s","are","is"),TempArray,SORT(FILTER(HSTACK(VarianceValues,SortOrderValues,SubHeadings&" variance of "&TEXT((VarianceValues),VarianceNumberFormat)&IF(ISBLANK(Reasons),""," due to "&Reasons)),ABS(VarianceValues)>=Threshold,{0,0,"N/A"}),2,IF(SortOrder<0,-1,1)),ReasonList,TEXTJOIN("; ",TRUE,DROP(TempArray,,2)),UnanalyzedTotal,VarianceTotal-ROUND(SUM(TAKE(TempArray,,1)),DecimalAccuracy),ReasonsError,ISERROR(TAKE(TempArray,1,1)),IndexItem,SWITCH(TRUE,VarianceTotal=0,1,ABS(VarianceTotal)<Threshold,1,ReasonsError=TRUE,2,3),TempStack,VSTACK(Heading&" of "&TEXT(BaseTotal,StatementNumberFormat)&" "&isAre&" "&"inline with "&ComparativeName&".",Heading&" of "&TEXT(BaseTotal,StatementNumberFormat)&" "&isAre&" "&TEXT(VarianceTotal,VarianceNumberFormat)&" "&IF(VarianceTotal<0,MinusWord,PlusWord)&" "&ComparativeName&". This is driven by variances below "&TEXT(Threshold,StatementNumberFormat)&".",Heading&" of "&TEXT(BaseTotal,StatementNumberFormat)&" "&isAre&" "&TEXT(VarianceTotal,VarianceNumberFormat)&" "&IF(VarianceTotal<0,MinusWord,PlusWord)&" "&ComparativeName&"."&" This is driven by "&ReasonList&IF(UnanalyzedTotal=0,".","; the remaning varaince of "&TEXT(UnanalyzedTotal,VarianceNumberFormat)&" relates to items below "&TEXT(Threshold,StatementNumberFormat)&".")),CHOOSEROWS(TempStack,IndexItem)))

The formula is easier to read in the Advanced Formula Environment add-in:

How to use the code?

There are several ways to get this function into your workbooks.

Name manager

To use this in your workbooks, copy the function above into the name manager and use the name AUTOCOMMENTARY.

Copy from the example file

Alternatively, download the example file and copy and paste one of the cells into your workbook. This will move the LAMBDA function into your workbook for you to use.

Conclusion

Variance commentary writing from numbers is a time-consuming process. Using the latest Excel functions (LAMBDA, LET, Dynamic Arrays), we can apply complex logic to create (hopefully) meaningful sentences. If we get this right, it can save a lot of time having to update and re-write commentary.

Related Posts

• Dynamic arrays in Excel – Everything you NEED to know
• Using Slicers with dynamic array formulas in Excel