Lifehacking – Derek Harlan

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?

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!

Learn More:
Link to example worksheet at OneDrive

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

Computer hardware: Accelerate your mouse

Sometimes the most productive tips are the simplest. Adjusting the speed of your mouse pointer can save seconds per each task, which can add up to minutes per day of extra time. Check out the link below for instructions on how to make this quick tweak to your system!

Source: Computer hardware: Accelerate your mouse

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.

Time management tips for CPAs via AICPA

With Great Performance Brings Great Productivity (or at Least Could)

Computer running slow? Well there are thousands if not millions of articles that offer advice on how to fix that. However they generally boil down to the same three or four steps. They are clear temp files, empty recycling bin, add more ram, install updates and more in that same vein.

This article goes a bit further and offers some unconventional advice. Hiding unused fonts, physically cleaning the case (to help the computer run cooler) and even assigning a static IP to cut down on time waiting for the computer to find one. In one way this article can be seen as a comprehensive guide to performance tips.

Also interesting, the author offers a practical benchmark to see if your system is up to par.

To quickly test its performance, simply reboot the system and then launch Excel and a browser. If the system reboots in less than a minute and the applications launch in less than two seconds each, then your system’s performance is adequate; otherwise, perhaps you should consider implementing some of the measures outlined below to rejuvenate the computer’s performance.

High performance doesn’t guarantee productivity, however, it prevents hindrances to it. If you don’t find your system up to snuff go ahead and try some of the suggestions in this article. With your computer at full performance you’ll be equipped to deliver your best work product.

Source: Boost your computer’s performance

How to reduce stress at work – AICPA

Feeling burned out? Let’s take a break to recharge our batteries and refocus our work. Sheon Ladson Wilson offers us some advice on how young CPA’s can de-stress their workday.

Wilson offers six points:

Find out exactly what your bosses want from you
Don’t take on too much work
Have reasonable expectations for yourself
Create a more relaxing physical environment
Don’t forget to exercise – and take some time out from work
Give yourself small rewards throughout the day

The first two are particularly interesting. Often times I like to take the ball and run with it, only to find out there was an easier way or my supervisor had different expectations.

[ask] whom she can contact for additional information, how the task was completed previously, and the expected outcome. “I like to know in advance what is considered a good job”

By knowing upfront the task your undertaking you can plan and save yourself a lot of stress. Wilson offers us some practical advice on how to improve our professional lives and life-work balance. So let’s all let out that deep breath and get back to work.

Source: How to reduce stress at work – AICPA

Always take the call…

When you’re searching for the next position in your career it’s important to be willing to work with executive search firms to help place you in the right position. In his article John Touey explains the importance of working with these search firms and also offers some practical advice to would-be job seekers. By following his advice one can hope to find that golden opportunity.

Touey offers four points of advice:

Always return the [executive recruiter’s] call
Research the field
Get a referral
Look for opportunities to strengthen your personal brand.

We can boil these down in a couple of ways. By talking with the recruiter you can tell them what you want. Of course to do that you’ll need to know what you want to do and how that can fit in with the industry or company you’re targeting. This is something you can’t do alone and so work with your connections in your network to find a recruiter who can help you and lastly always keep growing and improving your image through speaking, writing or blogging and so on.

Remember, the search firm works for the employer, not you. The only person who truly has your best interests at heart in the recruitment process is you.

With these tips under your belt you can put together some steps to increase the value of your personal brand. Making yourself more attractive to future employers and hopefully fulfilling your professional goals in the process. So next time opportunity calls – pick the up the phone.

Source: Don’t Miss Your Next CFO Job Opportunity

New ways of thinking vital for accountants, but so are old-school values

Good Day All,

In this article Amato describes how Accountants need to blend “old school” and “new school thinking”. He boils it down to a concept called anticipatory thinking or integrating current trends with your clients practices. For example he relates that an accountant who arrives at a meeting with paper files and folders would be perceived as out of date and not working as efficiently as they could be. So he suggests that accountants actively work to move clients forward, technologically speaking, and show them the benefits of “new thinking”.

Currently I work in a paperless company. So I’ve come to appreciate how that second monitor and Adobe Acrobat make life so much better. I also prefer electronic invoice presentment (EIP) over paper invoices. So I don’t really disagree with Amato here, but I don’t like the idea of automatically discounting an “old way”. In his view, the “blending” is simply retaining the values of old (Honesty, follow through) and using a tablet to present your reports. While these values are important and should be retrained, I don’t believe the methodology should be so easily forsaken. Followers of my site know I always look for new ways to do things, but I try to first understand why the “old way” came about. Sometimes, it hides an important step or rational which might not be obvious especially if the team has become complacent in the process.

One particular Gem is his idea of a “Pre-mortem” before taking on a project. Thinking about how you’ll work it and what could potentially go wrong and how to address these concerns. This is a crucial skill for accountants, and one that we should practice more often. For example before I begin a close, I have a standard checklist of all basic tasks (for example to reclass existing prepaids, check for new prepaids, record bank fees, etc.) and then I add any special client requirements (for example a particular work paper or maybe some kind of consolidation entry) and then I try to best plan the timing on these so as to minimize my stress and avoid error.

Do you pre-mortem in your firm or workday? Let me know in comments.

–Derek

Source: New ways of thinking vital for accountants, but so are old-school values

10 STEPS TOWARDS A BETTER, MORE PRODUCTIVE YOU IN 2015

Good Day All,

Just finished reading 10 STEPS TOWARDS A BETTER, MORE PRODUCTIVE YOU IN 2015 by Shawn Parr. He outlines some tips to incorporate into your life for a “better” year in the upcoming 2015.

I always enjoy lists like this, and for the most part this is a fresh take on this kind of advice. I especially enjoy his tips on finding meaning in everything you do. This is important – is everything you do have some meaning to it? It doesn’t always have to be the grandest of meanings, for example a simple chore like taking out the trash is meaningful in the way that it adds beauty to your home, or improves the hygiene of the kitchen and so on. Another gem is to “Shut up, and listen” some of the best advice I was given was to not fill the void of silence with words. For example, in negotiation when you ask your counterpart a question don’t give them answers. Let the silence fall, and wait while they form an answer.

Take a look and share some of your advice with me in comments.

–Derek

10 Steps Towards a Better, More Productive You in 2015 via Fast Company