Accounting – Derek Harlan

Excel Functions for Financial Reviewers

For those of us who review accounting workbooks it’s helpful to have a few tools to make the review easier. In today’s blog post I’m going to list 3 of my favorite Excel formulas or shortcuts that improve the quality of my reviews.

View Formulas helps you remove plugs or audit formulas

Pressing control and the tilde key (i.e. CTRL + ~) will change Excel to show the formulas in each cell rather than the values. This is very helpful when reviewing workbooks to ensure there are no plug values and that the formulas are consistent have the right ranges, etc. Press CTRL + ~ to put the view back to normal

The shortcut key CTRL + ~ changes the cells to show their formula rather than the value

As you can see in the screenshot, with view formulas it’s very easy to spot formula inconsistencies.

TEXTJOIN for making lists

The TEXTJOIN formula allows you to create lists easily with a seperator character. This is particulatly useful in my trade for combining lists of entites to put into a filter in the accounting system. The syntax of TEXTJOIN is = TEXTJOIN (delimiter, Ignore Empty, cell range)

The delimiter is the character you want to separate the items with, it can be anything but I’ll use the carrot ^ character for this example.

Ignore Empty parameter is either TRUE of FALSE, normally you’ll use TRUE so that it doesn’t conisder any empty cells in the range.

In the screenshot below, you can see how I used the formula to easily create the list (i.e. propert1^property2^property3^property4)

SUMIFS for totaling by category

The SUMIFS formula is very handy to get a quick total. It’s syntax is

=SUMIFS(sum range, category range 1, category, [more categories])

The sum range is the values you want to add.

The category range is the labels for each value

The category is which label you want to add

Consider this example. We have two projects TI Project #1 and #2. If we want to add TI Project #1 interdependently we can do this very easily using the SUMIFS like this:

SUMIFS can easily add up categories for your review

Although we tend to look to excel functions for preparation, even reviewers can harness the power of Excel shortcuts and formulas to make them better reviewers and help reduce or eliminate errors.

Do you want to learn how to automate your financial workpapers? Do you want to get started with VBA?

My book, Beginning Microsoft Excel VBA Programming for Accountants has many examples like this to teach you to use Excel to maximize your productivity! It’s available on Amazon, Apple iBooks and other eBook retailers!

Resources:

TEXTJOIN https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c

SUMIFS https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b

Automate Formula Auditing in Microsoft Excel with FORMULATEXT

Technology: Office 365 as of July 2020

88% of spreadsheets have formula mistakes. A 2008 study by Havard economics found that many spreadsheets have significant calculation errors hidden within. The best way to avoid becoming a statistic is through diligent review. FORMULATEXT is a function that will help automate a diligent review of our workbooks.

FORMULATEXT is an Excel function that results in the formula of the target cell.

It’s usage is:

=FORMULATEXT([target cell])

This result is the formula of the target cell, i.e. the contents of the formula bar.

This alone makes reviewing the formula easier since it’ll be visible in the grid. Let’s take this one step further. In the example below we will compare formulas to a known formula and see if there’s any differences.

In this example we want to add two numbers and ensure that a SUM formula is used instead of directly adding the cells together.

Columns A and B have values
Column C has a formula to add A & B together
Column D has the FORMULATEXT function to show us the calculation in C
Column E has a text version of the formula we wanted used To type a formula as text put a single quotation mark before the equal sign (i.e. ‘=D3-E3)
Column F is an equality formula to show us if they are the same

We can see that Row 3 is not using the correct formula since there’s a False result in cell F4.

Here’s a real world example worksheet. This is a basic real estate escalation calculation. The goal is to ensure the formulas that determine the amounts owed or refunded are correct.

The mechanics of this worksheet are:

Columns A – F have the escalation calculation for each of the 3 tenants in this property
The formula in Column F should be the difference between the escalations billed in column E and the amount of expenses eligible for recovery in column D
Column H has a FORMULATEXT function getting the contents of column F on that row
Column I has the correct formula as text. To type a formula as text put a single quotation mark before the equal sign (i.e. ‘=D3-E3)
Column J compares H and I (i.e. =H3=I3) and will result in TRUE if they are the same or FALSE if they are different. I added some conditional formatting to highlight FALSE results in red

The False result here tells us that the formula in F4 is not the one we are expecting.

Another potential use of FORMULATEXT of is in creating dynamic documentation of the workbook. For example a sheet can be designated as the reference of all formulas contained in the workbook and through the formula they can link to the calculation sheets.

These are simple examples, but one could build upon the ideas. Combined with cell protections one could build a very robust formula review app. With a simple review mechanism such as FORMULATEXT, Microsoft Excel can help us ensure we have reliable worksheets that keep our clients in the 12%

Want to learn how to automate your financial workpapers? Do you want to get started with VBA?

Learn More:
Link to example worksheet at OneDrive

FORMULATEXT Function: https://support.microsoft.com/en-us/office/formulatext-function-0a786771-54fd-4ae2-96ee-09cda35439c8

TEXTJOIN function makes list building easy

Technology: Office 365 as of 2020

Microsoft introduced a new function which makes combining the contents of cells even easier than before. In addition to concatenating, or joining, the contents of cells the TEXTJOIN function also allows you to insert a delimiter between each item. This makes it more powerful than the old concatenate formula.

The ability to add a delimiter is what makes this function very useful. In the past we had the CONCAT and CONCATENATE functions which would join together text however, depending on the application, you usually need some character between your values.

Here’s a common example. Pasting a list of email addresses from Excel into an email in Outlook. The application expects a semicolon or comma between each email address or name, however you may not have included these characters in the list in Excel as it wouldn’t make sense to store this with the data.

If you require functionality similar to the old CONCATENATE function you can set the delimiter to “” which joins the text together without any character between each item. There is also the ability to include or exclude blank cells in the cell range from the results. If you need to do this depends on your use case.

The syntax is =TEXTJOIN([delimiter], [ignore blank cells], [cell range])

Where:

Delimiter is any character you want between your list. The character need to be enclosed with quotes, i.e. “;” This is required.

Ignore blank cells is either True or False depending if you want blanks included in your list. This is required

And the cell range is your list items you’re wanting to join together. You can also have multiple ranges in this field if you need them.This is required.

Let’s look at some examples

This first example presents a case where we want to email some individuals, to put their email addresses in the To field of our email client we need them separated by a semi-colon.

This next example provides a result similar to the old concatenate function. In this example the list of numbers is joined together without any delimiter.

The TEXTJOIN function saves a lot of time for those of us who use Excel to work with data needed in other applications. The power of adding a delimiter means you can quickly transform a list stored in a cell range to suit the needs of your application. It’s a simple & powerful formula to add into your repertoire.

Do you want to get started with VBA? Want to learn how to automate your financial workpapers?

Learn More:

Example Workbook:View Workbook on Microsoft OneDrive

TextJoin Function: TEXTJOIN at Microsoft Office Support

N Function Allows for Documentation in Excel’s Formula Bar

Microsoft Excel’s N function has many uses, including Switching and in line documentation

We commonly use Excel’s comment or note function to explain formulas or where the cell values come from. But, have you ever wanted to put this information in the formula bar itself? Sometimes it’s more helpful to document our formulas and data right where the user is reading them. Enter Excel’s N function.

The N function converts it’s parameter to a number. This is not to be confused with the VALUE function which converts text to number values. The N function will convert any text given to it to the value zero. If that doesn’t sound useful, keep in mind the additive property of zero anything added to zero is the original value. With this in mind, you can use this function to insert comments into the formula. For example if you had a formula to give an annual average and increase it by 3% your formula might be =AVERAGE(A1:A12)*1.03+N(“Returns a 3% increase in the average”). This use is shown in the featured image.

Switching in Formulas

Another interesting property of the N function is the way it converts TRUE or FALSE values. N will return the value 1 for a TRUE or 0 for a FALSE. Considering the multiplicative properties of 1 and 0 we can use this to create switches in our formula. A switch is where an operation is included in the final result if it’s multiplied by 1. For Example, this formula would result in the value 2 =(2*N(FALSE))+(2*N(TRUE)) if you expanded it it would be (2 x 0) + (2 x 1), change the first FALSE to TRUE and the value will increase to 4. With some careful workbook design, the N function can be used to replace nested IF statements which would serve a similar purpose. Let’s look at a more sophisticated example.

The sample workbook features quarter ending dates and the date of the current workpaper. By simply changing the workpaper date the Year To Date amortization updates appropriately. I’ve also used the comment N technique described earlier to demonstrate that use.

The N function is deceptively powerful. On the surface it would be easy to dismiss, but with a little ingenuity and careful workbook design it’ll allow you to simplify your workpapers and provide great documentation in the formula bar.

Do you want to get started with VBA? Want to learn how to automate your financial workpapers?

Download the sample workbook (Excel 2010 and higher):

N-Workbook-Example Download

Learn More:

https://support.office.com/en-us/article/n-function-a624cad1-3635-4208-b54a-29733d1278c9

Excel EOMONTH Function Leads to Consistent Expense Accruals

The Excel EOMonth Function is a handy formula which will return a date representing the last day of a particular month. The month end date returned is determined by the second parameter.

The syntax is simple =EOMONTH(Start Date, Number of Months) where number of months can be positive or negative integer .

Any date can be used as a starting point in the first parameter. Regardless if it’s the first or thirtieth you’ll always get a date at the end of the month.

The second parameter is what makes the formula so interesting. You can specify the number of months to forward or backwards to return. To move backwards provide a negative number.

One potential use – would be to automate your monthly expense accruals. In the above sample workbook, the accountant only needs to update the most recent paid invoice. The formulas then automatically determine the period to accure.

The Accure From is simply the period end date plus one and then the EOMONTH in cell F8 determines the end of the following month. Using a formula like this would help you apply a consistent accrual methodology throughout your financials.

For more information on EOMONTH check out the official documentation https://support.office.com/en-us/article/eomonth-function-7314ffa1-2bc9-4005-9d66-f49db127d628

You can download the sample workbook below

EOMONTH Example Workbook Download

Microsoft Excel Date Functions Round Up – Derek Harlan on Excel EOMONTH Function Leads to Consistent Expense AccrualsAugust 5, 2020
[…] Excel EOMONTH Function Leads to Consistent Expense Accruals […]

Creating a Chart of Accounts from a General Ledger in Microsoft Excel using VBA

Recently I was given the task of extracting a chart of accounts (COA) from a general ledger report. There are many ways I could have done this. Using Auto Filter comes to mind. Being me, I chose to write a small program in Visual Basic for Applications (VBA). In this post I will walk you through the design and code of the program I made.

Design:

First we need to determine the goals for our output or in this case the chart of accounts. I needed a report with:

account number
account description

In the source file we see that the account number is in Column A and the account descriptions are in Column E. We also see that rows that begin with a number are the rows that have the account number/description. Since that’s all we need we’ll ignore everything else.

With that information we know that our solution will have to do the following:

find or determine the first and last rows on the source worksheet
examine each row in the source worksheet to see if it starts with a number
if it does start with a number then
take the account number and
take the account description and
write them to the destination sheet
lastly increment the row in the destination worksheet

It’s obvious that we need a loop in this case we’ll use a FOR loop to examine each row of the source worksheet. It’s also clear we’ll need an IF statement to determine if the first character of the row is a number or not.

Code:

 
Sub ExtractCOA()
 
    'This program will extract the chart of accounts from a Yardi 7
    'default General Ledger (analytics) report run to Excel 
    'Derek Harlan April 2018 - Licensed in the Public Domain 

    'Setup Object Variables for the worksheets 
    'src will be the Source worksheet 
    Dim src As Worksheet 
    'dest will be the Destination worksheet 
    Dim dest As Worksheet 
    'Setup some counter variables 
    'firstRow / lastRow will be the first and last rows of the Source workbook 
    'nextRow will be the row to write in the Destination workbook 
    'i will be the counter in the For loop 
    Dim startRow as Long 
    Dim lastRow As Long 
    Dim i As Long 
    Dim nextRow As Long 
    'Set the initial values for the variables 
    Set src = Sheets("Report1") 
    Set dest = Sheets("Sheet1") 
    startRow = 6 ' this is easy to determine so I just set it the row number 
   'Find the last row by searching the workbook from the bottom row up 
   'and assign it to the variable 
    lastRow = src.Range("A1048576").End(xlUp).Row 
    i = 0 
    nextRow = 2 'Row 1 will be the headers in the worksheet so Row 2 is where the data starts 
    'We'll loop through each row between the first and last rows of the Source workbook 
    For i = startRow To lastRow Step 1 
       'If the first character of the value of the cell in A and the current row (i) is a number then 
       'that row has the account number and name on it 
        If IsNumeric(Left(src.Range("A" & i).Value, 1)) Then 
            'set the values in the destination worksheet from the values on the 
            'current row of the source worksheet 
            dest.Range("A" & nextRow).Value = _ 
               src.Range("A" & i).Value 
            dest.Range("B" & nextRow).Value = _ 
               src.Range("E" & i).Value 
            'lastly increment the nextRow in the destination worksheet 
             nextRow = nextRow + 1 
        End If 
    Next i 
End Sub

Running it creates this output –

With both of the requirements met I can say this was a success!

This project took about 20 minutes, from start to finish. In this instance I’m not sure if I saved any time however should I need to do this again I already have the program ready. When evaluating alternatives think about how often you’ll be asked to do this. It may take time to come up with the initial program but once you have it, you see a colossal increase in productivity!

Do you want to get started with VBA? Or want to learn what a FOR loop is?

Check out my book for more uses of Excel VBA in your daily work:

Beginning Microsoft Excel VBA Programming for Accountants

available on Amazon, Apple iBooks and other eBook retailers!

Questions or comments? Connect with me on

Facebook https://www.facebook.com/DerekHarlanWriter/

Twitter https://twitter.com/tazbirdinvader

New Lease accounting rules issued from the FASB

The FASB has issued new lease rules and in order to help us prepare I’ve gathered some facts about the change

What’s changing

The FASB implemented a new standard that changes the way leases are accounted for. In accounting leases are grouped into one of two categories operating and capital as described in ASC . Operating leases are the simplest to account for and were treated as an current period expense (i.e. rent expense) which was only reflected on the income statement in addition it was disclosed in the notes to the financial statements. Capital leases, or leases that met certain criteria and were more akin to financing arrangements, were a bit more complex and are recorded as an Asset & Liability on the balance sheet.

Under the new standard all leases greater than one year will be capitalized on the balance sheet. This will add potentially trillions of liabilities to American companies. The new standard classifies leases as Type A or Type B. Type A are financing type leases, similar to the capital leases of the old standard, and type B are similar to operating leases. While both will now be on the balance sheet as a right-of-use asset and lease liability, the main difference is how these effect the income statement. Type B will be recognized as a single straight line amortization of the liability while Type A will require amortization of the right-of-use, interest on lease liability as well as impairment on the asset. It’s important to note that the IASB standard treats all leases as a Type A.

Why

This change was made to improve the transparency of balance sheet. Operating leases are not reflected on the balance sheet. However these leases often carry long term considerations and obligations which affect the credit worthiness of the company. The intention is to reduce the use of off balance sheet financing and increase the usefulness of the balance sheet to reflect the true liabilities of the firm.

When does the change take place and how to prepare?

The change takes effect on 12/15/18 for public companies and 12/15/19 for private firms, however you can implement it earlier if you are ready. To get ready it’s best to update your firms lease inventories. Get a handle on all of the existing lease obligations. It will be advisable to invest in a system for managing leases and identifying new internal controls with the asset acquisition process.

New opportunities

For accounting providers we have an opportunity to help clients navigate the new standards. For the more technical minded we can design systems to store and analyze these contracts and assist in recognizing the asset, liability and expenses these generate. Since virtually every firm has these obligations there are many opportunities available.

Conclusion

Lease accounting is undergoing a major change in the way these assets are treated. The FASB’s new standard will increase transparency for investors. Although it presents many new challenges for our firms we must work hard to advise them and help them navigate the changing financial landscape.

IBM Watson enters ‘Big Four’ accounting firm duel for A.I. dominance – New York Business Journal

In the near future auditors will be aided by power artificial intelligence systems. Near future, is a little inaccurate, in fact it’s already happening. As big data becomes ever more a part of our work lives, computers are needed to make sense of this information and help us preform audit engagements.

The major players are starting to emerge. And big blue is throwing the power of Watson into the mix. KPMG is using their human auditors to teach Watson how to do it’s job. Currently, the software can aid auditors by analyzing contracts, legal documents by looking for keywords and other patterns.

Watson aside – this is becoming the norm in the industry.

A quarter of the 180 CPAs surveyed listed big-data analysis as one of the top industry challenges in the future and 20 percent listed increased complexity and scrutiny in engagements.

Big data will only continue to become more prevalent and we must build the tools needed to audit these systems. Artificial intelligence is poised to be the go to solution when working these engagements. Learning how to use this in our day to day business is a must.

Source: IBM Watson enters ‘Big Four’ accounting firm duel for A.I. dominance – New York Business Journal

9 time-saving tech tips for the busy CPA

In the average day you probably spend at least an hour or more doing the various steps to accomplish one goal. The time you spend highlighting a sentence in order to paste it into google. The time you spend finding file paths so you can share it with a colleague.

These mini-tasks are not only tedious they waste precious seconds which add up to minutes. Combine with with the few minutes it takes us to switch between tasks, and you’ll see how it adds up quickly! The only solution is to best optimize your workflow so that you reduce the number and time of these mini-tasks.

A quick Google search will reveal thousands of articles on this topic. Although many of them have the same suggestions. However this particular article has some unique points and is well worth a read. One that I find really useful, and I didn’t know until I saw it here, is to use the Copy as Path option.

In Windows, to copy the path of a folder (such as M:\Important Stuff\Firm Holiday Schedule.docx), hold down the Shift key and right-click the file or folder that you want and select Copy as path. The path will be copied to your Clipboard, and you can then paste it where you need to. This tip can also come in handy when you’re attaching files to emails or filling in dialog boxes.

Another is to use MS Office’s built in PDF functionality. I’ve used this for a long time and I prefer it to using Acrobat Pro’s PDF printer or even other many of the third-party utilities. The Save As PDF feature is much less prone to crashes and faster than the apps out there. Plus it’s already built in to the app so there’s no additional IT cost.

We’re always going to be juggling tasks in an effort to get through the day. It’s a part of our job, but it’s something we need to master. By taking every available shortcut we can maximize our own productivity and take back our minutes.

Source: 9 time-saving tech tips for the busy CPA

Prioritize your way to victory

Work-life balance isn’t easy to achieve. Many days we’ll leave the office felling like very little was accomplished with the “work” time and thus feel compelled to spend the “life” time making up for the work. Time management techniques can help us better use our work day to be effective.

No matter what your role, work in accounting comes in waves. If you’re a tax professional your wave is first quarter, if your CRE counter like myself, your wave depends on your client’s cutoff, and so on. One simple step is to list out all of the tasks on your plate. This gives you a visual representation of what’s on your plate and knowing what needs to be done. is the first step to getting these things done.

Learning to do just that has helped Michael Elliott, CPA, a partner with Dittrick and Associates in the Cleveland area and a graduate of the AICPA Leadership Academy. Like Deshayes, he is married with a young child, and has another on the way. “My wife has to listen to me go home every night and say, ‘I didn’t get anything done!’” he said. “She’d say, ‘Well, did you make a list?’” Simply writing out his top priorities for the week, Elliott said, has helped him get a better handle on his work.

Through setting priorities and then focusing on tasks in order you can leave your office, or shutoff your computer knowing you’ve made a dent and have carried the good fight. Now let’s get stuff done.