K2E Canada Inc
  • Home
  • TRAINING
    • WEBINARS
    • UPCOMING EVENTS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • CONFERENCES
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG
  • Home
  • TRAINING
    • WEBINARS
    • UPCOMING EVENTS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • CONFERENCES
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG

Dexterity + Next Generation

3/4/2021

0 Comments

 
Picture

On February 23rd, 2021 our friends at Receipt Bank announced a big change, with some great additions to the product and a new name DEXT (a fusion of two words; dexterity and next)

An update that will make a big change is a new single view dashboard combining insights from Dext Prepare (formerly Receipt Bank) and Dext Precision (formerly Xavier).
Picture

This new dashboard will provide your team with the knowledge to help clients improve their operations and simplify the burden of accounting. For example, the submission method results provide guidance on methods that are not being used and potential workflow improvements for your clients. Tracking your use of auto published items month over month let’s your team monitor their improvement in the utilization of this key automation tool.

If you have clients using desktop accounting software, you will soon be able to bring the speed and accuracy of Prepare (with Receipt Bank) through the Dext Connect App. The first one available now is Dext QuickBooks Desktop Integration .

Ward Blatch

0 Comments

3 Tips to Beat Work-From-Home Burnout

3/4/2021

0 Comments

 
Thanks to this year’s global pandemic, more people are working from home than ever before. As many are coming to realize, burnout often goes hand-in-hand with remote work. We thought that providing 3 tips to beat Work-From-Home burnout might help you. Not surprisingly, articles about how to prevent or avoid burnout have popped up all over the internet. Far fewer resources, however, address how to overcome burnout if you are already suffering.

How Do You Identify Burnout?

Recent reports indicate productivity dropped 4.8% in the last quarter of 2020. This productivity drop was the largest decrease since 1981. We have written other articles on being productive from home like Insight on Work at Home and Work-From-Home Strategies – “The Finer Points.” We have talked about Tips for Starting a Home Based Business and The Rise of “COVID-prenuers.” And, we have even created Continuing Professional Education (CPE) courses on Working at Home. But with all this guidance on how to work from home or be successful from home, burnout can kick in.

How do you know if you are among the 69% of remote workers currently suffering from burnout? While the symptoms can look different for everyone, these are a few of the tell-tale signs:
  • Feeling negative or apathetic toward work
  • Trouble falling asleep or staying asleep
  • Forgetfulness, or an impaired sense of the passage of time
  • Using substances as coping mechanisms
  • Decreased efficiency — for example, finding yourself re-reading the same sentence over and over

If you have noticed some of these red flags in your life lately, you could be burned out. Fortunately, there are several effective ways to banish burnout and restore your work-life balance. Consider these three ideas.

1. Establish Time Boundaries for Work-From-Home

One of the major causes of work-from-home burnout is the lack of time boundaries. If you work-from-home, you are probably acutely aware of how often you let your life interrupt your work, answering a child’s question or moving the laundry into the dryer, for example. However, you may not realize how often you let work interrupt your life. You may find yourself responding to an email while cooking dinner or absentmindedly logging in during TV time.

Many remote workers report feeling as though they are never “off” work. As Dr. Heather DeQuincey put it in a now-viral tweet: “I think we need to stop calling it ‘working from home’ and start calling it ‘living at work.’”

It is not hard to see how such nonstop connectivity can lead to burnout. Inc. explains one of the most effective ways to combat this problem is establishing firm temporal boundaries and sticking to them. Create a work schedule, write it down, and let your family and co-workers know that these are the times you will be available. It may be difficult at first, but in the end, it will save you countless wasted hours, not to mention your sanity.

2. Set Physical Boundaries to Minimize Burnout

If your current workspace lacks physical boundaries, it could contribute to feelings of burnout. Just because you can work from anywhere does not mean that you should work from everywhere. If you pop open your laptop or check work on your phone in every room of your house, your brain eventually associates all those areas with working. No wonder you are feeling burned out!

Establish a designated workspace in your home. Try to restrict your work to this place as much as possible. Real Simple notes that ideally, your work area should be near a window where you can get some natural light, or at the very least should be well-lit and at a comfortable temperature. Choose an area that is free from visible distractions, including piles of clutter. A disorganized or chaotic work area disrupts your energy flow. Further, clutter tends to breed negative thoughts, so it should be dealt with accordingly. Any distracting objects or unfinished projects should be out of sight.

3. Automate Tasks When Possible to Beat Tasks Sucking Up Time

There are many apps and programs available that you can customize to automate or nearly automate a vast array of tedious tasks. These sorts of tasks can suck up loads of your time and energy. Worse, unproductive tasks leave you feeling that you did not accomplish anything.

Remember that you can pick tips and tools that our K2 team recommends. Further, remember our guidance on outsourcing. Learning to say no will make a difference in your business and work-life balance. And yes, there is an app for that!

Still Not Enough?

If you have become burned out while working from home, you are not alone. These 3 tips to beat Work-From-Home burnout can help you establish a healthier, more sustainable relationship with your work.

However, if things do not get better for you, call for professional help. Unfortunately, I know of multiple people who have chosen suicide based on their stress levels in the last 60 days. Get help from places like the Canada Suicide Prevention Service at 1-833-456-4566 or from your medical professionals. While many of us offer our friendship and support, we often do not know our friends and colleagues are desperate for help. Watch out for the symptoms above in yourself, family, friends, and colleagues.

Randy Johnston

0 Comments

Excel's STOCKHISTORY Function is Now Available

3/4/2021

1 Comment

 
Recently added to Excel is the new STOCKHISTORY function. You can use this function to retrieve historical prices for stocks as of a given date or range of dates. Read on, and you will learn how to use this feature to query historical securities prices into an Excel workbook.

Introducing STOCKHISTORY

Excel’s STOCKHISTORY function is finally rolling out to subscription-based versions of Excel. Unfortunately, this function is not yet available with perpetual licenses of Excel, such as Excel 2016 and Excel 2019. Presumptively, Microsoft will add the function to the next new version of Excel.

With this feature, you can retrieve historical stock prices for stocks by simply entering a few variables into a formula. Moreover, you can retrieve values for a single date or a range of dates. Further, if you choose a range of dates, you can designate daily, weekly, or monthly intervals. STOCKHISTORY displays date and closing price by default. However, you can optionally choose to show opening price, high price, low price, and volume if desired.

Using STOCKHISTORY

The syntax for using the STOCKHISTORY function is relatively simple, as indicated below. However, note that of the arguments available, only the stock and start_date are required. Thus, a formula using STOCKHISTORY could be as simple as =STOCKHISTORY(“MSFT”,“1/29/2021”). Of course, this formula returns the closing price for a share of Microsoft stock on January 29, 2021.

Additionally, you can create more sophisticated formulas using STOCKHISTORY if your needs require additional information. Specifically, the full syntax of a formula can include all the following items.

STOCKHISTORY(stock, start_date, [end_date],[interval],[headers], [property0], [property1] [property2], [property3], [property4], [property5])
  • stock: The identifier for the financial instrument targeted. This reference can be a ticker symbol or a Stocks data type.
  • start_date: The earliest date for which you want information
  • end_date (optional): The latest date for which you want information
  • interval (optional): Daily (0), Weekly (1), or Monthly (2) interval options for data
  • headers (optional): Specifies if the formula returns additional header rows with the array
  • property0 – property5 (optional): Specifies which information to include in the result, Date (0), Close (1), Open (2), High (3), Low (4), Volume (5).

Extending the previous example, we can create more powerful formulas that use the new function. For instance, we can use the following formula to use the STOCKHISTORY function to generate a listing of closing prices for a range of dates:
=STOCKHISTORY(“MSFT”,“1/1/2020”,“12/31/2020”)
To illustrate, Figure 1 below provides an abbreviated set of results from the formula shown above.
Picture
Figure 1 - Sample Results Using STOCKHISTORY


Another Example of STOCKHISTORY

Next, the ensuing example of the STOCKHISTORY function incorporates optional arguments to add columns for the Open price, High price, Low price, and Volume for each trading interval.

=STOCKHISTORY(“MSFT”, “1/1/2020”, “12/31/2020”,,1,0,1,2,3,4,5)

For example, Figure 2 illustrates an abbreviated set of results using this formula.
Picture
Figure 2 - STOCKHISTORY with Optional Arguments

Of course, you can use STOCKHISTORY results in the same fashion as if you entered the data manually.

Summary

In short, STOCKHISTORY is one of the most widely-anticipated functions added to Excel in recent years. If you have a subscription-based version of Excel, you should already have access to this feature or receive access soon. Importantly, you can use STOCKHISTORY to retrieve stocks’ historical prices and incorporate them into other calculations in your spreadsheets. Therefore, the next time you need to perform research to obtain historical data about a stock, consider using STOCKHISTORY. Most importantly, if you do, you will reduce the amount of time you spend retrieving data.
You can learn more about STOCKHISTORY and other Excel features and functions by participating in a K2E Canada Inc. training class. Additionally, you can learn more about STOCKHISTORY by reading this Microsoft article.

View a Video Tip That Demonstrates STOCKHISTORY


Tommy Stephens

1 Comment

CES 2021 - Tech Trends Round-Table with K2 Enterprises

2/8/2021

0 Comments

 
CES is an annual trade show organized by the Consumer Technology Association. Normally held in January in Las Vegas, but held online this year for the first time ever. CES typically hosts presentations of new products and technologies in the consumer electronics industry. It's one of the most important and influential technology conventions in the world. It often features the lastest and greatest technology trends which will impact organizations of all sizes. This presentation will be a round-table discussion with K2 instructors, Randy Johnston, Ward Blatch, and Brian Tankersley. They are long time attendees of CES and attended this year's event from the safety and comfort of their homes. They will discuss their experience with this year's event and the major emerging technologies which made their debut. Tune in to learn the latest and greatest which emerged from this year's CES!
0 Comments

Forecast Sheet and Forecast.ETS

2/8/2021

0 Comments

 

Forecast Sheet

Excel’s Forecast Sheet feature not only creates forecasted values that account for seasonality, but it also can provide confidence boundaries around the forecasted values and create forecasted values for multiple future periods. Using the data shown below begin by clicking in the data you wish to use as the foundation for your forecast sheet. The data requires a time line with a consistent interval and corresponding values.  Up to 30% of intervals can be missing and the Forecast Sheet will still be accurate.
Picture
Next, click Forecast Sheet on the Data tab of the Ribbon. In the ensuing dialog box (shown below), make any adjustments necessary for the time periods, confidence level, etc. and click Create to build the Forecast Sheet. 
Picture

Clicking Create results in a new sheet being created to the left of the data sheet with a copy of the data, the results of the forecast, and a chart of the forecast. This is a copy of the data and any changes to the data will not be reflected in the forecast. To make any changes to your selections in the dialogue above you will have to edit the formulas in the forecast sheet or run the Forecast Sheet command again.
Picture


The FORECAST.ETS Function

The Forecast Sheet results above use the FORECAST.ETS as shown in the screen shot above. This function is an improvement over the FORECAST function which provided only linear forecasting capabilities. With FORECAST.ETS, you can now generate forecasts with exponential smoothing. The primary benefit to using the FORECAST.ETS function is that its exponential smoothing capacity allows you to create forecasts that account for seasonality in your data. To illustrate, consider the data below (with multiple rows hidden for presentation purposes). In it, a formula that uses the FORECAST.ETS function is computing forecasted sales for January 2021. Notably, because this formula employs the FORECAST.ETS function instead of the FORECAST function, it accounts for seasonality in the data. 
Picture
forecast_sheet_and_forecast.ets.xlsx
File Size: 32 kb
File Type: xlsx
Download File

Ward Blatch

0 Comments

The Rise of "COVID-preneurs"

2/8/2021

0 Comments

 
Many startup businesses begin after an economic downturn. Will we see the rise of the “COVID-preneurs” soon? During the 2007-08 financial recession, Ken Lin started the personal finance giant Credit Karma. Above all, it was a defeatist economy. Funding money was strapped, consumers were hesitant, and financial institutions had a cloud of distrust around them. In addition, by textbook timing, this was no time to start a finance company. However, more than ten years later, in 2020, Intuit (a financial technology company) acquired Credit Karma for an astounding amount. According to Intuit, Lin’s company was acquired for approximately $3.4 billion in cash and 13.3 million shares of Intuit stock and equity awards with a value of $4.7 billion. The bet paid off.

Is This the Time to Start a New Business?

History seems to be repeating itself for many risk-taking entrepreneurs during the coronavirus pandemic and the subsequent economic turbulence that followed suit. According to the U.S. Census Bureau, Americans have been filing new business applications at the fastest rate since 2007. Many have determined this is a great time for entrepreneurs with ideas. They have time to work on their ideas.

For some, starting a business during such chaotic times may seem completely counterintuitive, not to mention crazy. For others, it’s prime time to implement a sound strategy and capitalize on the opportunity. In addition, you have those scratching their head and wondering, “why on Earth would someone become an entrepreneur in a pandemic?!” we have some points that will make you think otherwise.

Necessity, the Mother of Invention or Action!

For instance, during penny-pinching times, necessity is a significant driver of entrepreneurial activity. The pandemic results in unprecedented unemployment rates. American unemployment surpassed 14% peaking during the pandemic compared with 10% during the Great Recession. With more people out of work, some are looking for creative and new ways to make money, like starting a small business or company.
In addition, people are seeing fresh opportunities as a result of increased time at home. Without commuting to work or traveling to school, more people are taking up side hustles that might evolve into a new company. Interestingly, 39% of entrepreneurs start a business to pursue a passion. So, those folks that have taken up bread baking during the pandemic and found it to be their passion might establish a full-blown bread baking company if well-thought-out.
Along with necessity and opportunity, new community needs have developed. Small businesses need to adapt quickly to online eCommerce. Rapid testing solutions need to be created. Smart healthcare technology needs to be advanced. In other words, these are just a handful of examples of the change in social needs due to the pandemic.

Have COVID-preneurs Already Built New Businesses?

This timing, coupled with increasing trends in entrepreneurship according to 2020 stats, has led to a skyrocketing rate of new business filings. The founders of such ventures cleverly nicknamed “COVID-preneurs,” might just well be the next Ken Lin. Will you help with the rise of the “COVID-preneurs?” Sources: LegalZoom  | Intuit
Click to view the Infographic from LegalZoom
86 Key Entrepreneur Statistics for 2020 and Beyond

Randy Johnston

0 Comments

QuickBooks Online Management Reports: A Feature You Should Use

2/8/2021

0 Comments

 
Serving over 4 million customers worldwide, QuickBooks Online is one of the leading accounting solutions used by small  businesses today. However, most of these companies rely on the “core” functionality and rarely take a close look at the advanced features. For example, one such tool is Management Reports. Read on, and in this article, you will learn how you can take advantage of this tool and why you should do so.

What Are Management Reports?

Upon hearing the term “Management Reports,” many immediately conclude these are reports that are operational in nature, such as sales reports, accounts receivable and accounts payable aging reports, and profitability margin by item reports. QuickBooks Online Management Reports could potentially include statements such as those mentioned. However, they are far more reaching in scope.

To clarify, Management Reports are “books” of user-defined, presentation-quality, customized reports. For example, these reports can contain cover pages, tables of contents, financial statements, operational information, compilation reports, and even management discussion and analysis commentaries. You can use any of the three pre-built Management Reports available in QuickBooks Online, or you can customize them to meet your needs and save them for future use. Further, you can distribute your management reports directly from within QuickBooks Online, save them as PDFs, or export them as Word documents for further editing.

Accessing QuickBooks Online Management Reports

To use the Management Reports feature in QuickBooks Online, click Reports in the left menu, followed by the Management Reports tab. There you will see three pre-defined sets of Management Reports you can use as-is or customize them. Next, clicking View on the Company Overview report allows you to examine this report’s content, as pictured in Figure 1.
Picture
Figure 1 - Sample Management Report in QuickBooks Online

If the report is suitable, can click Send, Export as PDF, or Export as DOCX in the Action column’s drop-down list. Then you can send it via email, export it to a PDF file or DOCX file, or share it using other means.

Customizing Your Management Report in QuickBooks Online

If you need to customize your Management Report, click Edit in the Action column’s drop-down list. Subsequently, the dialog box pictured in Figure 2 opens. Here you can perform the following customizations.

  • Change the Template’s name,
  • Edit the Cover Page’s style,
  • Add a logo,
  • Change the Title and Subtitle,
  • Edit the Report Period,
  • Add Prepared by and Prepared date fields,
  • Add or edit Preliminary pages,
  • Add other Reporting objects, and
  • Add or edit End notes.
Picture
Figure 2 - Customizing a Management Report

Upon completing your edits, click Save and Close in the lower right corner. Subsequently you can distribute the report using any of the techniques described previously. Of course, having saved the report, you can use it again in future periods, and all your customizations will appear automatically, saving you the time you would otherwise spend customizing that period’s reports.

Summary

In conclusion, many QuickBooks Online users fall victim to the notion that they cannot customize their reports when they can. Importantly, the Management Reports feature provides you with a robust set of tools to create a comprehensive report book. Further, you can customize Management Reports to meet your company’s financial and operational reporting needs or those of a client. Therefore, if you use QuickBooks Online, be sure to check out this excellent feature today.

Check Out These QuickBooks-Related Seminars from K2E Canada Inc.

8 Hour Session
4 Hour Sessions
K2's QuickBooks for Accountants
K2's Advanced QuickBooks Tips and Techniques
 
K2's Implementing Internal Controls in QuickBooks Environments

Check Out These QuickBooks-Related Online Options from K2E Canada Inc.

Web-based Learning
On-Demand Learning
K2's QuickBooks Online - What CPA's Need to Know
K2's QuickBooks Online - Tips and Tricks
K2's Turbocharging QuickBooks Reporting with Excel and Power Query
  

Check Out This Video Showing How You Can Use the Management Reporter


Tommy Stephens

0 Comments

The Changing Face and Pace of Small Business Accounting

2/8/2021

0 Comments

 
Small business accounting has changed in subtle and not-so-subtle ways. That statement should come as no surprise to anyone reading this article. Surprising to many, though, is the sheer volume of changes we have seen over the past decade. Both at a macro-level and a detailed level, these changes provide new opportunities for small businesses to improve their accounting functions in many ways. Read on to learn about two of the primary drivers of the changing face and pace of small business accounting.

The Move to the Cloud

Almost certainly, the most noteworthy shift in small business accounting has been the migration to Cloud-based accounting options. Cloud-based solutions such as QuickBooks Online, Sage 50cloud, Sage Intacct, Xero, Wave, Zoho Books, along with others, offer many opportunities for small businesses to leverage the Cloud as a strategic asset. And small businesses are responding in large numbers. In fact, most accounting solution providers now indicate that sales of Cloud-based solutions outpace those of traditional, on-premises applications. So, how does the Cloud potentially improve accounting for small businesses? Let us consider at least four potential advantages associated with Cloud-based accounting for small businesses.

Cloud-Based Solutions Continue to Roll-Out New Features

First, most developers are investing heavily in their Cloud-based accounting solutions relative to their on-premises deployments. In fact, in many cases, the Cloud-based options now have a more robust feature set than their desktop counterparts. Further, developers can roll-out enhancements and upgrades to the solution without small business team members needing to install any software because of the Cloud-based deployment.

The Cloud is the Network

Second, the very nature of Cloud-based solutions means that the Cloud is the network. With today’s decentralized workforce – including work-from-home environments – this factor means remote workers can easily access their accounting solution from practically any location, so long as there is an available Internet connection. Further, small businesses can typically provide their external accountants and others with log-in credentials. In turn, these trusted advisors can access the business’s books quickly and easily, without any troublesome exchanges of data files.

Reduced Dependency on Local Hardware

Third, Cloud-based solutions reduce a small business’s dependency on local hardware, such as desktop computers and servers. After all, these are cloud-based solutions; therefore, team members need access only to a device with a suitable web browser and an internet connection. Moreover, in the ever-changing world of mobile apps, most solution providers now offer robust mobile apps allowing team members to access critical data from their smartphones and tablets.

Reduced Costs Often Yield a Higher ROI

Fourth, in many cases, you can acquire these Cloud-based options for small businesses at price points that are less costly than traditional, on-premises implementations. This point is particularly true when you factor in savings associated with reduced dependency on local hardware. In combination with productivity increases resulting from deploying the Cloud-based solution, most small businesses adopting this strategy improve the return on investment associated with their accounting platform.

Ecosystems of Integrated Applications

In addition to Cloud-based deployments, the second primary driver of small business accounting’s changing face and pace is the nearly limitless number of integration options now available. It has not been that long ago since “integration” meant importing data from or exporting data and reports to Excel. Today, those options still exist, but so many more are available to extend the small business solution’s functionality.

Bank Feeds Cleared the Path for Integrated Applications

One of the earliest innovations attributed primarily to Cloud-based accounting solutions was bank feeds. With bank feeds, you can integrate your bank accounts to your accounting solution so that as transactions clear the bank account, they automatically download into the application. At that time, user-defined rules can automatically categorize the downloaded transactions to the appropriate account in the company’s chart of accounts. Further, for transactions already recorded in the accounting solution, the bank feed process can often match, effectively clearing them from the bank reconciliation. Small businesses that choose to use bank feeds benefit from time savings, improved consistency and accuracy in the accounting records, and nearly automatic bank reconciliations.

Example of Other Integration Options

For example, consider the number of small businesses that sell through e-commerce platforms such as Shopify, WooCommerce, Magento, and others. Virtually all the leading Cloud-based solutions integrate with these and many other e-commerce platforms. Among other items, these integrations typically allow you to download sales transactions from your e-commerce website into your accounting solution, eliminating the need to enter data manually.

Similarly, most leading Cloud-based accounting solutions can integrate with many of the top reporting and business intelligence platforms available today, including Power BI and Tableau. This type of integration facilitates real-time, interactive reporting of critical metrics without manually re-keying data into the reporting solution.

Other examples of tools that you can typically integrate with Cloud-based accounting solutions include:
  • Bill payment applications,
  • Customer Relationship Management tools,
  • Data synchronization tools,
  • Employee expense reporting solutions,
  • Payroll and human resources solutions,
  • Project management tools,
  • Sales tax tools, and
  • Time tracking and billing solutions.

As you can see, integration options abound for most of the Cloud-based accounting solutions available today. These third-party solutions facilitate customizing and extending your Cloud-based accounting solution’s utility to seamlessly become the “hub” of all business activities without requiring significant manual intervention. As more become aware of these integration options, expect to see small business managers move quickly to adopt them.

Summary

The face and pace of small business accounting have forever changed. With the popularity of Cloud-based platforms and improvements in “digital plumbing” that facilitate third-party integrations, today’s solutions offer compelling options for managing and growing a small business. Moreover, moderate price points, reduced hardware costs, and improved efficiency and productivity come together to yield a generally higher return on investment than traditional solutions. As you consider how to move forward with your accounting and business management platform, take advantage of the changing face and pace of small business solutions to reap the rewards for you and your team.

TO LEARN MORE

To learn more about small business accounting solutions, consider the following K2E Canada Inc. learning options:
Sessions
Other Learning Options
K2's QuickBooks for Accountants - 8 hours
K2's Technology Conferences
K2's Advanced QuickBooks Tips and Techniques - 4 hours
K2's On-Demand Learning
K2's Implementing Internal Controls in QuickBooks Environments - 4 hours
 

Tommy Stephens

0 Comments

Excel Dynamic Arrays

1/10/2021

0 Comments

 
A Dynamic Array formula is any formula that has the potential to return multiple results. There are several new functions in Excel for Office 365 that have this ability, including Sort, Filter, and Unique. In this post we will have a look at Sort and Filter.

The first thing to keep in mind is that the results from these Dynamic Array formulas will “spill” into adjacent cells; therefore, we need to plan for the possible spill range by allowing ample space in adjacent cells. This spilling is by design; and, in the event the spill range is not large enough for the results, Excel returns a #SPILL! error. 

The second item to consider is that references used in Dynamic Arrays between workbooks need both workbooks to be open; Excel returns a #REF! if they are not.

Spill

To understand better what spill means and how dynamic arrays work, let’s start with the data below and create a formula to summarize the information.
Picture
The data is in a table named Payments. In cell G5, enter the formula =payments and press Enter. The data is now in G5:J22 using this single formula. While in the data range, you will notice an outline around the data indicating that it is part of a dynamic array. The formula is only in cell G5 and, therefore, all other cells in the array will show the formula as a light gray in the formula bar as shown below.
Picture
As shown below, in the event of other data residing in the range used by the dynamic array formula – for example, cells M12:O12 – completing the formula will result in the #SPILL! error. Keeping cell L5 highlighted, Excel will show where the data will spill, providing clear guidance on which data is causing the error. To resolve the problem, either move the dynamic array formula cell or clear the data in the spill area. Additionally, as shown below, Excel provides an error-checking tool and a quick way to Select Obstructing Cells if you want to move them outside the spill range. Once you clear the obstructing cells from the spill range, the dynamic array formula should complete, unless one of the following four issues arises.
  1. The spill range includes merged cells;
  2. The dynamic array formula is inside of a table;
  3. The system is out of memory; and
  4. The sequence is volatile, such as with a RANDBETWEEN sequence.
Picture


Spill Range Reference

With only the one cell in the spill range containing the formula, the question becomes how to refer to all of the data in the range. The answer is to use the spill range operator which is the # symbol. In the previous example, to refer to the entire spill range, the reference would be =G5#. This does have a limitation in that it is not supported when linking to an external workbook.

Sort

The process to sort by formula before Dynamic Arrays was very complicated and, therefore, seldom used. With the Dynamic Array function SORT, the process is simply to use the formula below.
=SORT(array,[sort_index], [sort_order],[by_col
Using similar sample data, as in the previous example, in cell G2, input the formula =SORT(sortdata,3,1). The data is in a table named sortdata; the number 3 is the column to use as the sort, and the number 1 is for ascending order.

If you want to sort by more than one index, you can use an array constant. For example, to sort the data by Account ascending and Amount descending, the formula would be =SORT(SortData,{3;4},{1;-1}). The results of these formula-based sorts are shown below.
Picture


SortBy

The SORTBY function allows you to sort the contents of an array based on the values in a corresponding array. The syntax for the function is =SORTBY(array, by_array1, sort_order1,….). For example, to do the same sort as we did with the array constant, the formula would be as follows.
=SORTBY(SortData,SortData[Account],1,SortData[Amount],-1)

Filter

The next dynamic array function is FILTER, which allows filtering of data into a new array. The syntax for the function is =FILTER(array, include, [if_empty]). To have more than one condition, use the plus sign between them for either and the multiply sign between them for both.
​
As an example, in Figure 18, we want to retrieve all rows from the table named FilterData that have the item stamps. The formula would be as shown below.
=FILTER(FilterData,FilterData[Item]=I1,"Item not found")
​To retrieve the information related to stamps in the sales department the formula is as follows.
=FILTER(FilterData,(FilterData[Item]=I1)*(FilterData[Department]=I2),""
Picture
Dynamic Arrays at the time of creating this post is only available in the Office 365 Monthly Channel (Targeted)
dynamic_arrays.xlsx
File Size: 33 kb
File Type: xlsx
Download File

Ward Blatch

0 Comments

Five Technology Resolutions for 2021

1/10/2021

0 Comments

 
For many, the Holiday Season is a time to look back and reflect on the year gone by – and what a year it was! It is also a time to look forward, plan, and consider how we can do better in the coming year. Often, this results in making resolutions for the new year. In that spirit, let us consider five technology resolutions that you might consider adopting for 2021.

We Will Collaborate More Effectively

The first of our five technology resolutions centers on collaboration. In today’s work environment, collaboration is critical. Team members no longer work in isolation. Instead, they work collaboratively with customers, clients, vendors, and others who work for different organizations.  Recognizing this new reality, yesterday’s work methods are no longer optimal for today’s environment.

To illustrate, the age-old practice of emailing documents to others for review and revision is outdated. Instead, we can take advantage of collaboration tools to enable simultaneous, multi-user collaboration. For instance, we can use the co-authoring feature in Excel, Word, and PowerPoint to collaborate on common Microsoft Office documents. Likewise, we can use Adobe’s Creative Cloud to enable collaboration too. Further, tools and platforms such as Google Workspace, Microsoft Teams, and Zoho One provide excellent collaboration options. No matter the technology used, recognize the benefits of collaboration. A recent Forrester report indicated that team members save almost two hours per week using collaboration tools. No matter the type of business you are in, seek out opportunities to improve collaboration in 2021.

Information Security Will be a Part of Everything We Do

The second of our five technology resolutions is to incorporate information security into all we do. You have, no doubt, read the headlines and know that cybersecurity attacks continue to rise. Ransomware, spear phishing, and Internet of Things attacks are just a few of the threats we face from external forces. But we also need to address internal security issues. These risks include team members sending sensitive and confidential information as unsecured email attachments, failing to use encrypted Internet connections, and not taking advantage of multi-factor authentication when a device or application.

Information security must become an integral part of each of our businesses. A single security incident – such as a ransomware attack – could cost millions in remediation expenses and perhaps more in reputation damage. Further, with ever-expanding data privacy laws and regulations, fines for non-compliance could cripple organizations of all sizes. To address these threats, resolve to make information security a fundamental part of every activity in your organization. Among other actions, encrypt all your data, and mandate multi-factor authentication wherever possible. Consider adopting a “zero trust” security model to minimize risk. Above all, train every team member on identifying and responding to the dangers that will inevitably arise. The issue of information security is not going to disappear, so address it now and ensure that all business processes incorporate appropriate security measures and all data remains secure.

Equip Work From Home Team Members Appropriately

In the understatement of the century, 2020 was a year of unprecedented business change. At the outset of the pandemic, business leaders told millions of workers to “pack up your computer, take it home, and figure out how to work from there.” And these team members did an outstanding job of making the best of the situation. Along the way, many realized they prefer to work from home and will continue to do so in the future. Therefore, the third of our five technology resolutions centers on remote team members. We must ensure that our remote workers have the equipment and tools necessary for the “new normal.” Stated differently, they need to be at least as productive at home as they were in the office.

In addition to addressing hardware issues and fast and secure internet connections, also carefully consider issues such as desks and chairs.  Remember, employers typically have a responsibility to ensure that team members have the equipment they need to get their jobs done efficiently and safely. In most cases, workers’ compensation laws still apply, even if a team member works from home. Hence, it remains necessary to address workstation setup and safety measures, including those related to ergonomics. Further, do not let relatively inexpensive technology expenditures hamper productivity. For example, purchasing an inexpensive scanner or other hardware devices may pay big dividends in increased productivity for team members working from home or other remote locations.

We Will Leverage Our Investment in Existing Technologies to Improve Efficiency

Our fourth of five technology resolutions seeks to leverage existing investments. Most organizations have invested heavily in technology over the past two decades. But are these same organizations receiving the promised return on investment? The unfortunate answer is a resounding “no!” 

One of the biggest reasons for this failure is that most organizations have not committed to training their team members to use the tools provided or discover some of the newer features in core applications. For example, although almost all accounting and financial professionals use Excel daily, they do not know how to work with data models, create formulas based on dynamic arrays, utilize Flash Fill, or perform “what-if analysis” using Solver. Thus, tasks take longer than necessary, and results are often not as precise as needed. For 2021, commit to leverage the investment already made in core technologies such as Excel, Word, Outlook, PowerPoint, Adobe Acrobat DC, and Windows 10. Incredible improvements in accuracy and efficiency await!

Our Team Will Adopt New Technologies to Improve Productivity

Rounding out the list of five technology resolutions is adopting new technologies. Just as we will leverage existing technologies, we also resolve to adopt innovative technologies, where appropriate, to improve productivity. One example lies in Robotic Process Automation (RPA). RPA allows businesses to automate rote, repetitive tasks such as manual data entry. With tools such as Automation Anywhere, Microsoft Power Automate, and Blue Prism, you can build custom applications to automate virtually any repetitive task performed in your organization. 

But do not stop with RPA! Consider how you can use other technologies such as machine learning and artificial intelligence to improve productivity. For example, is there a role for artificial intelligence to help audit employee expense reports for errors, irregularities, and fraud? Tools available from companies such as AppZen can help you do precisely that, identifying out-of-policy spending and enhancing internal control at the same time. Once considered to be “bleeding-edge” technologies, these tools are now mainstream and offer new productivity plateaus.

Summary

Another New Year is upon us. With it, we will gladly say goodbye to what was a most tumultuous 2020. As we do, let us look forward to all that 2021 has to offer, including the opportunities to become more efficient, more effective, and more secure with technology. Resolve to improve your business with the five items outline above – collaboration, information security, equipping remote workers, leveraging existing technologies, and adopting new and transformative technologies. Next year, when you look back at the year that was 2021, you and your bottom line will be delighted you adopted these five technology resolutions for 2021. All the best to you and yours for a Happy Holiday season and a healthy and prosperous New Year!

Make 2021 your best year yet by becoming more knowledgeable, efficient, and secure when working with technology. You can do so by participating in the various learning opportunities we make available. To learn more, please click here or visit https://www.k2e.ca/training.html

Tommy Stephens

0 Comments
<<Previous

    Categories

    All
    Adobe
    Advisory
    AI
    Artificial Intelligence
    Automation
    Business Intelligence
    Cloud Computing
    Collaborate
    Collaboration
    COVID 19
    COVID-19
    CPA
    Cybersecurity
    Ecommerce
    Excel
    Forecast Sheet
    Google
    Grammarly
    Internal Priorities
    KPIs
    LET
    Management Reports
    Microsoft 365
    Microsoft Teams
    Office
    Office 365
    Online Shop
    Outsourcing
    PivotTables
    Power BI
    PowerPoint
    Productivity
    Quickbooks Online
    Ransomware
    Security
    Small Business Accounting Software
    Technology
    Tech Update
    Windows
    Windows 10
    Word
    Work At Home
    Zoho

    Authors


    Ward Blatch
    Ward provides consulting and training services as the Managing Director of K2E Canada Inc. He joined K2E Canada in 2005 and is responsible for the Canadian operations of this international consulting group, which provides professional development technology education for accountants across Canada and the US. Ward lives in rural Nova Scotia and can be reached at ward@k2e.ca.

    Tommy Stephens
    Tommy is one of the shareholders in K2 Enterprises, affiliating with the Firm in 2003 and joining as a shareholder in 2017. At K2, Tommy focuses on creating and delivering content and is responsible for many of the Firm's management and marketing functions. Tommy resides in the metro Atlanta area. You may reach him at tommy@k2e.com.

    Randy Johnson
    Randy is a nationally recognized educator, consultant, and writer with over 40 years experience in Strategic Technology Planning, Accounting Software Selection, Paperless, Systems and Network Integration, Business Continuity and Disaster Recovery Planning, Business Development and Management, Process Engineering and outsourced managed services. Randy can be reached at randy@k2e.com

    RSS Feed

Training & Education

Webinars
Upcoming Events
Road to Excellence Online Seminar
Seminars
Conferences
Instructors

Tech News

K2E Canada's Tech Update Newsletter

More

Privacy Policy
About
Contact

K2E Canada Inc.  |  591 Highway 221  |  North Kingston, NS Canada B0P 1R0  |  (902) 200-9207
© 2020 K2E Canada Inc. ALL RIGHTS RESERVED.