Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Automatic commentary writing formula in Excel – Amazing LAMBDA

AUTOCOMMENTARY

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.

Commentary writing formula basic example

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.

Basic Example - updated 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

Commentary writing formula 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

Commentary writing formula 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

Commentary writing formula 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

Commentary writing formula 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:

AUTOCOMMENTARY Function in Excel AFE

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.

Name manager including the function

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


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published. Required fields are marked *