This guide covers leading-edge topics in managerial accounting and finance. It's packed with useful tips and practical guidance controllers and financial managers can apply immediately. You'll also gain insight into hot topics such as:
* Power Pivot
* Integrated Reporting
* Technology trends
In addition, this guide includes a case study covering three chapters using Excel tools, working capital trends and technology changes.
Jim Lindell, CPA, CGMA, MBA, is the president of Thorsten Consulting Group Inc., a Wisconsin-based provider of strategic and financial consulting, professional speaking, training and executive coaching. He has an extensive background in senior management, including positions as chief financial officer, corporate controller and corporate assistant controller. Jim has worked with a variety of industries, including manufacturing health care, not for profit, distribution and food processing.
Power BI, Pivot and Excel Tips and Techniques for the Controller
- Recognize Power BI Desktop as a resource to move into data analytics.
- Identify dataset resources available to the controller from which to better understand their industry.
- Recognize the value of the Power Pivot feature in Excel.
This chapter will focus on Power BI, Get and Transform (Power Query) feature in Excel using a case study with implications for chapters 2, 5, and 7. This chapter will also demonstrate the Microsoft Office Excel Power Pivot tool to improve efficiency and embed more knowledge in the worksheet. The ultimate goal is to improve overall productivity within the accounting department. Adaptation of the tools in this chapter should help to lighten the workload, convey more information, and make the accounting department more efficient. These tools are not meant to be the solution to all problems, but with practice, they will make the job easier. It should also be strongly emphasized that the nature of accounting is quickly changing. The accountants who process transactions, prepare budgets, and prepare reports are being replaced with systems and artificial intelligence (AI) tools. To remain relevant, accountants must learn new tools and develop skill sets that complement the future organization - not the past.
As in previous yearly updates, we will focus on increasing productivity and efficiency in your organization by improving the use of Excel spreadsheets. Many of us are too busy to take the time to research and practice the tricks and advanced skills that could improve our day-to-day effectiveness. This section will highlight the tools and techniques that can save time for you and your staff.
Lean and your skill set
Before our examination of software tools to help improve our daily efficiency, let us briefly examine why becoming more proficient is in our best interest. If you have not had the opportunity to study lean concepts and principles, it is well worth your time. Lean management principles come from the Toyota production system management theories. In its simplest form, lean management theory has two central themes:
- The first theme is that your organization will only produce products and services based on what the customer values. If the customer does not value it, your organization should not create it.
- The second theme is that your organization will be relentless in the identification and elimination of wasteful processes and procedures. It is this theme that drives our investigation of new techniques and new software to make our accounting departments more effective and efficient.
All accounting departments waste time and, for this reason, waste money by being poorly trained. Accounting departments create spreadsheets that are unnecessary, enter data multiple times, and automate processes that should be components of their formal accounting system.
As we consider making ourselves and our departments more efficient in the sense of lean processes, let's consider time management.
- Where does your staff waste time?
- If you could change one corporate activity that is inefficient, what would it be?
Following is an adaptation of Stephen Covey's Time Management Matrix. All the tips and techniques presented should help us eliminate tasks that are not important and are not urgent. Also, the tips and techniques will help reduce the time required to perform tasks that fall into the other categories, thereby increasing the overall productivity of an individual or department.
Time management matrix
Adapted from First Things First by Stephen Covey:
The proper use of tools such as Excel can immediately help us deal with the urgent and important topics. Time deadlines are associated with financial report preparation, review, and reporting. Any methods or techniques used to speed up the preparation of reports or monitoring of financial information will help meet time-critical deadlines. Also, the accounting staff spend an inordinate amount of time in meetings. Tools that help us address the urgent and important criterion in Quadrant I are tools that help us:
- better manage meetings,
- aggregate information from meetings, and
- discharge the responsibilities and assignments generated by meetings.
Also, the not urgent and important items in Quadrant II can be addressed by the use of different software techniques and tools such as Excel or Microsoft Office OneNote.
Note that in Quadrant IV, items that are not urgent and not important include "time wasters." Redundant keypunching and data entry, unnecessary spreadsheets, and poor technique in spreadsheet creation are all examples of activities that waste time. If we can find ways to eliminate tasks - by increasing our skill set - we will create additional time for ourselves and our staff within Quadrant IV.
The remainder of this chapter will be laid out in the following order: a case study that weaves together Excel Get and Transform, Power Pivot, big data, and working capital into one mosaic, followed by tips and techniques that were shared for the first time in the 2018 update.
- In Covey's First Things First time management matrix, time wasters are in which quadrant?
Excel techniques for the 2020 annual update edition
The Excel examples in this chapter refer to Excel 2010, Excel 2016, or Excel 2019. Some interesting tools and features have been added to Excel. As a regular practice, someone on the accounting staff should review the new versions of Excel when they come out and determine whether any new features will be worth the price of an upgrade for your organization.
Power BI - The next step up for controllers
In the 2019 update, we began coverage of Power Pivot in Excel. We used Power Pivot to access over 47,000,000 rows of information from SEC data to analyze working capital by industry and company. In the second half of this chapter, the reader can review using Power Pivot for this aforementioned exercise.
In this 2020 update, we are going to expand beyond Power Pivot and consider Power BI. The purpose of looking at Power BI is not so much to make the reader an expert in its usage, but to raise familiarity so that the reader upon return to their office will begin to experiment with Power BI on their own.
In the 1960s and seventies, our work was done on green bar paper with the use of mechanical pencils, scotch tape and 10 key calculators. Excel was not the first spreadsheet that we used but it quickly came to garner the largest market share of the spreadsheet industry. We learned how to automate Excel through the use of macros and connecting to various databases in and out of the organization. However, the main result of Excel was to make the accountant quicker, more efficient, and better able to present financial information. As we have progressed into the area of big data and data analytics, all of these prior processes, which are still important, become less essential to the work that we do. Now we find ourselves in a different crossroad. Accountants must move beyond the aggregation process in traditional reporting and become comfortable with accessing, aggregating, reporting, charting, and most importantly interpreting and recommending both tactical and strategic actions. The answers and insights are in the data. We must learn how to access them. The goal of this first part of chapter 2 is simply to get you to experiment with Power BI once you get back to your office.
One of the advantages of using Power BI is that you can download Power BI Desktop version for free. Power BI is available in the desktop version, and a professional version. The links for the download are in the footnotes.
Microsoft Power BI1
Microsoft Power BI Desktop is a companion desktop application to Power BI.
With Power BI Desktop, you can
- Get data.
- The Power BI Desktop makes discovering data easy. You can import data from a wide variety of data sources. After you connect to a data source, you can shape the data to match your analysis and reporting needs.
- Create relationships and enrich your data model with new measures and data formats.
- When you import two or more tables, oftentimes you'll need to create relationships between those tables. The Power BI Desktop includes the Manage Relationships dialog and the Relationships view, where you can use Autodetect to let the Power BI Desktop find and create any relationships, or you can create them yourself. You can also very easily create your own measures and calculations or customize data formats and categories to enrich your data for additional insights.
- Create reports.
- The Power BI Desktop includes the Report View. Select the fields you want, add filters, choose from dozens of visualizations, format your reports with custom colors, gradients and several other options. The Report View gives you the same great report and visualizations tools just like when creating a report on Power BI.com.
- Save your reports.
- With the Power BI Desktop, you can save your work as a Power BI Desktop file. Power BI...