Category: CRE

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

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.

Rise of NERDS: The new wave of office markets

Traditional metros are out. Says a recent report from JLL. The hottest growth markets are Nashville, East Bay, Raleigh, Denver & Salt Lake City also dubbed the NERDS. These markets offer solid fundamentals along with great supply. Check out more in the report below.

Source: Rise of NERDS: The new wave of office markets

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

The old Circuit City become possessed.  

You see it every day on your way to work. That vacant storefront. What used to be your favorite CircuitCity is now just an eyesore. Only today as you drive past something catch’s your eye. A bright banner fluttering against the facade. The store has been given a new purpose, albeit a temporary one, selling Halloween costumes.

in the spirit of Halloween, Spirit Halloween stores take a temporary hold of vacant storefronts to satisfy the most temporary of market demands. Their orginzation ramps up from a few hundred to over 20,000 in just a few months. Coordinating these efforts requires a whole year of planning.

Employees scout for locations throughout the year…Merchandise starts rolling into Spirit Halloween’s warehouses in May. By the summer, sites have been chosen, and by mid-August, the stores are prepped to receive the goods. Trucks start arriving, and the locations go from bare walls and floors to racks and shelves bursting with costumes, accessories, props and home decor.

These temporary stores are huge money makers. Operating them is akin to a military operation. And this is a boutique as boutique CRE can get. Will these ad-hoc stores be the future of brick and mortar retail?

Source: Real estate zombies: shuttered stores return to life for Halloween – The Boston Globe

Are indoor mapping apps the answer?  

Imagine this. You’ve just walked into your local shopping mall when you hear that particular unmistakable jingle. As automatic as breathing your hand reaches into your bag and draws out your phone. Only it’s not a text or call your seeing, your phone is directing you down the crowded corridor. Instinctively you follow the line not sure where it’s leading, but you have no reason to fear, you’re phone has never lead you astray before. As the line ends you find yourself standing in front of a display case at Zales. Ah! You think that’s exactly what my spouse wants for our anniversary! You pull out your wallet and and get the clerk to wrap it up into a nice package, and voila anniversary gift shopping is done.

Retailers have  begun experimenting with technologies that enable shopper to find products they want using their smartphones. A variety of products exist however they all basically operate under the same concept.   By leading consumers right to the object of their desire brick and mortar stores are hoping to gain an edge over online shopping.

Two products in particular iBeacon & IndoorAtlas tackle the problem in different ways. iBeacon uses Bluetooth devices to alert the users of potential sale items and offers as they walk past the transmitter. IndoorAtlas  works more like a traditional map app such as Google Maps to navigate users to stores and even specific products within a store. One major issue, however, is that consumers will need to install these apps and then use them when they are in a shopping center. This leads to some delays in deployment as the shoppers need to participate in the roll-out of these services.

“That’s due in part to the fact that we’re trying to teach them new behaviors,” Kindred said.

Adam Silverman, an analyst at Forrester Research, said that customers who are looking for something in a store find it easier to just ask a sales associate, rather than installing an app.

“Although it makes sense on paper, the customer hasn’t been fully engaged in some of these beacon-based technologies, mainly because it doesn’t make their shopping easier, or more engaging,” Silverman said.

Once these technologies become more mainstream the uses can branch out to more than just shopping. For example these apps can give travelers directions to their gate or baggage claim in an airport, or directions to a particular suite in an office building.  Or even provide emergency evacuation instructions should an event arise.

Indoor mapping technology faces an uphill battle of user adoption. While it seems like a good idea only time will tell if it becomes a popular one. However, it gives us hope to reduce the labyrinth of today’s malls and airports to a manageable path. Can you see yourself using these apps?

Source: Can a Google Maps for indoors save shopping malls? – MarketWatch

Check your tax return before filing

Want to save yourself from thousands of dollars in penalties? It’s important to carefully review your tax returns, even if you pay someone to prepare them for you. In a recent ruling the IRS upheld a substantial penalty despite their claim that they relied on the practitioner for tax advice. Careful review and understanding of  each and every assertion on your tax return is crucial before signing and sending to the IRS.

Stough vs IRS is a complicated case, but the basic premise is that the taxpayer considered a payment for property improvement as a deductible expense. However, the court ruled that this payment was actually rent because it had the substance of rent. For example, the tenant was making this payment to reduce future rents, and the lease provision allowing this payment was in a section of the lease entitled ‘rent’.   After the finding, Stough requested the penalty be waived because they relied exclusively on their practitioner’s advice.

The court has a special set of criteria that must be met in order to substantiate that claim.

“The adviser is competent with sufficient expertise;
The taxpayer provided appropriate information to the adviser; and
The taxpayer “actually relied in good faith on the adviser’s judgment.”

In this case the third criteria was not met as the tax payer admitted that they didn’t review the return or Schedule E (the schedule where supplemental income is reported) prior to signing it. In addition the preparer didn’t discuss the return with them. According to the court unconditional reliance isn’t a defense especially when the taxpayer should have known about these tax provisions.

You must remember you are always responsible for the contents of your tax return. Always take a few minutes to examine the return before filing and don’t be afraid to ask questions. Keep this in your mind and you’ll keep your butt out of court.

Source: Is it rent? That depends on the lease

Goodbye open office, hello office neighborhoods – RealViews

Good Day All,

What concept does your office follow? Open, Closed or Neighborhood – JLL RealViews gives us some info on a new concept in office layout. The Neighborhood concept organizes work-spaces into functional groups.

Think of it as a ‘finance’ table or ‘marketing’ area for instance. In essence your groups are gathered around the people and resources they need to be productive. This layout, for some organizations, favors collaboration and minimizes floor space.

Would a neighborhood layout improve your productivity? Let me know in comments.

–Derek

Source: Goodbye open office, hello office neighborhoods – RealViews

JLL to Acquire Smart Building Specialist CoR Advisors | 2015-06-19 | ACHRNEWS

Good Day All,

JLL has signed a letter of intent to acquire CoR Advisors, a smart building consulting and solutions business. Founded in 2009 by Darlene Pope, CoR Advisors specializes in helping transform buildings into energy efficient, high-performance facilities. The acquisition is expected to close this month.

With this move JLL is expanding their reach into automated building systems and my predicted growth of IoT on CRE is confirmed.

 

–Derek

Source: JLL to Acquire Smart Building Specialist CoR Advisors | 2015-06-19 | ACHRNEWS

How the Internet of Things will impact CRE and Everything else

Good Day All,

The Internet of Things (IoT) is the next technological revolution. If you’re not familiar with the term undoubtedly it’s already changing your life. The IoT refers to the embedding of senors and computers into more and more aspects of our lives. For example, virtually all of us have a smartphone. Many of us wear fitness tracking bracelets like Fitbit. All of this data being fed into other devices to enrich our lives. How does this effect CRE? Think of all the new building automation systems and services that are out there. JLL’s Integrated Facilities Management on Demand service is one such example. But there are also more self driven products like Honeywell’s Smart Building Technology which provides the owner with detailed analytics about the assets performance. While many still say the true IoT is still years away, it is very much the way of business today. Check out the links and let me know your thoughts and opinions on the IoT in the comments.

–Derek

Source: How the Internet of Things will impact CPAs