Teaching Financial Ratio Analysis with XBRL and Excel Page 116 - Developments in Business Simulation and Experiential Learning, Volume 44, 2017 ABSTRACT Both professional accountants and accounting academics are interested in developing higher-order skills in accounting graduates. Accounting graduates not only need to know the dense body of detailed knowledge required to practice as an accountant, but are also expected to have critical thinking, problem solving and other higher-order analytical skills. Experiential learning curriculum enables instructors to engage students and develop these skills by using rich, real-world, complex scenarios. This paper presents a curriculum unit designed to be delivered to accounting majors in an undergraduate accounting information course. In this curriculum unit, students use XBRL data and Google Sheets to aggregate real-time corporate financial data and generate financial ratios. The ratios are then analyzed to make judgements regarding the financial health of the business organizations. INTRODUCTION Financial ratio analysis is a key tool used by accounting, auditing, and finance professionals to assess the financial health of a business organization, the reasonableness of reported financial results, and to predict future performance. Analytical procedures, including ratio analysis, are recognized as an essential component of both planning an audit and carrying out substantive testing and AU Section 329.02 states, “A basic premise underlying the application of analytical procedures is that plausible relationships among data may reasonably be expected to exist and continue in the absence of known conditions to the contrary.” Knowledge of financial statement analysis using ratios is a component of several professional certifications, including the CPA (Certified Public Accountant), CMA (Certified Management Accountant) and CFA (Certified Financial Analyst) certifications. In undergraduate accounting programs, the topic of financial ratios is often not given high priority. Coverage is typically fractured and spread throughout the required financial accounting courses as an afterthought. Many programs offer an elective course in financial statement analysis toward the end of the required sequence of accounting courses, which is the case in the undergraduate accounting major at the College of Business Administration of the University of Pittsburgh. In addition, students are not required to take this course and often do not take it. Publishers of introductory financial accounting textbooks generally have two approaches to the presentation of financial ratios. In the first approach, the ratios are scattered through the text material and are introduced in chapters covering the related accounts. This means that ratios may not be grouped by type or purpose (for example, liquidity ratios versus profitability ratios) but are included simply because the chapter in which these ratios are presented discuss those accounts. For example, Financial Accounting Fundamentals (Wild, 2016) introduces both net profit margin and the current ratio at the end of the third chapter focusing on adjusting journal entries while Financial Accounting (Libby et al., 2017) introduces net profit margin in the third chapter focused on the income statement, and the current ratio in the second chapter focused on the balance sheet, but then introduces ROA (return on assets) and gross profit percentage in the fifth chapter focused on communicating accounting information. Both of these introductory texts tie the fractured ratio presentations together in a later chapter on financial statement analysis at the end of the book, but instructors rarely have time in a typical undergraduate semester to cover that chapter material as a unit. As an alternative, the second approach is to present all ratios in a cohesive chapter toward the beginning of the book, such as in Financial Accounting (Weil et al., 2014) which devotes chapter 7 to this topic. Then, as the text progresses, the ratios are used in applied cases and discussion questions embedded in the later chapters. But this early introduction of ratios is often challenging for new undergraduate accounting students and is better suited to a graduate level accounting course in an MBA program. The Accounting Education Change Commission (1990a, 1990b), Albrecht and Sack (2000) and the American Institute of Certified Public Accountants Core Competency Framework (1999) all stated that accounting graduates should be lifelong learners with a skill set ranging from competency in discipline specific accounting knowledge to higher-order skills of critical reasoning and problem solving. The development of course content which enhances higher-order skills generally involves increasing ambiguity, complexity, and realism in assignments. Hamilton and Klebba (2011) describe an approach for course design which focuses on these characteristics and they argue that experiential learning represents a viable way to improve critical thinking skills since higher-order thinking skills are enhanced when students are asked to apply, analyze, evaluate, and create (Krathwohl, 2002). Analytical procedures are of increasing importance in auditing, and we believe the use of financial statement ratios to perform financial statement analysis should be given more focus in our undergraduate curriculum. Similar to most undergraduate accounting programs, we require that accounting majors take a course on accounting information systems after they have taken the introductory accounting courses in the undergraduate core curriculum. Most students enrolled in accounting information systems are concurrently enrolled in Teaching Financial Ratio Analysis using XBRL Elise Boyas University of Pittsburgh eaboyas@katz.pitt.edu Ryan Teeter University of Pittsburgh ryan.teeter@pitt.edu mailto:eaboyas@katz.pitt.edu mailto:ryan.teeter@pitt.edu Page 117 - Developments in Business Simulation and Experiential Learning, Volume 44, 2017 either the first or second intermediate accounting course, so they already understand the nature of financial statements and accounts. The content of the accounting information systems course can get stale quickly and must be periodically updated to ensure that the curriculum is current and relevant to practice. As a result, we determined that a hands-on experiential unit on financial statement ratios fits well in our accounting information systems course and satisfies multiple learning objectives. In addition to presenting financial ratios, accounting information systems courses can also provide students with an explanation of XBRL (eXtensible Business Reporting Language) but generally fail to provide concrete examples of how it works and the benefits of access to computer-readable financial data. For this reason, financial ratio analysis is enhanced through the use of XBRL data, enabling students to dynamically select publicly-listed companies and various financial accounts, represented by XBRL tags, to provide a concrete example of the benefits of XBRL for financial statement users. The remainder of this paper describes a unit we have developed that is currently in use in our undergraduate accounting information systems course. It requires students to access a financial ratios template in Google Sheets that is connected to company XBRL data using a publicly-available add-in. The students manipulate the data in the spreadsheet to perform financial ratio analysis, and then interpret the results. This unit achieves multiple curriculum goals. First, it teaches students how to access XBRL data and use Google Sheets to manipulate the data. Second, it requires students who have some knowledge of financial accounting information to create and interpret financial ratios in an exercise using real world data. It also allows students to identify limitations in XBRL and apply judgment in selecting appropriate XBRL tags (accounts) for their analysis. OVERVIEW: CLASSROOM EXERCISE In this exercise, we demonstrate to students the value of XBRL in data analysis and generate financial ratios to compare the financial performance of several companies. This exercise is designed to be completed within one 75-minute class period, although students may also work on this outside of class. Pre- exercise instruction relates to the mechanics of and meaning behind the financial ratios. The goals of this in-class exercise are to: 1. Analyze financial changes over a three-year period for a single company. 2. Compare financial metrics across competing companies within one industry for a single period. 3. Identify potential flaws or shortcomings in the data. 4. Understand how XBRL facilitates complex financial analyses. Before the in-class exercise, students learn about the use of financial ratios, including indicators of liquidity, financing (leverage), activity, and profitability. These common ratios are consistent with those found in typical intermediate accounting textbooks, for example, the Risk Analysis presentation in Chapter 3 and the Profitability Analysis presentation in Chapter 5 of Intermediate Accounting (Spiceland et al., 2016). We discuss the drivers behind the calculated values (for example, higher leveraged companies will have higher interest expense and consequently lower times interest earned) as well as what the ratios might indicate (for example, return on assets will be much higher for an older company with older assets recorded at historic costs than for a newer company with newer assets recorded at amounts closer to market value). We also identify the advantages and disadvantages of making comparisons over time and across companies (for example, unusual and transitory income statement items will skew results, or large companies are not always comparable with each other because they are organized differently or use different accounting methods). Students are given a list of “comparable” companies across different industries, included in Table 1 in the next section of the paper. Students must select one of the industries for the analysis and choose one company for primary analysis within that industry. They must calculate 14 financial ratios for this company for the past three years and calculate the same ratios for the remaining two companies in their industry for the most recent year. When reviewing the requirements of the exercise, we discuss how tedious it would be to collect the data needed to calculate financial ratios for the selected companies if we had to manually review each company’s financial statements and extract the data (via copy-and-paste) into a spreadsheet. Fortunately, students have two key resources which streamline this task: (1) spreadsheet templates and (2) XBRL data. Since these financial ratios are commonly used, there are a number of input-ready templates in the public domain with formulas pre-calculated. We use an available template, but instructors can easily alter this exercise to require students to create their own spreadsheet. Regarding the financial data, students learn that public companies have been required to use XBRL in SEC filings since 2011, and that XBRL is used worldwide. XBRL is a system which tags financial data that is presented in a computer-readable format. By identifying the appropriate XBRL tags, (for example, ProfitLoss is net income or AssetsCurrent is current assets), we can generate meaningful analyses by simply linking to and searching the XBRL data. Students are also made aware of the challenges when using XBRL data. Since many filers outsource XBRL tagging (for example, to RR Donnelley and Fujitsu), these providers often choose conflicting or uncomparable tags. For example, for the balance sheet account “Cash and Cash Equivalents”, one company may use the tag CashAndCashEquivalents while a different company may use CashAndCashEquivalentsAtCarryingValue. These are completely different XBRL tags representing the same financial statement data and a spreadsheet formula may not load both values if we explicitly choose only one. To mitigate this, some software will use “normalized” accounts which essentially group similar items under a common tag, as shown in this exercise. Students are made aware, however, that some values may not load into the spreadsheet, and this will distort the financial ratio analysis. At this point we are ready to begin the in-class lab. Students have responded positively to this lab. At the conclusion of the Fall 2016 semester, 52 students were asked a general question about the usefulness of the lab in understanding XBRL (1= Not Useful, 5 = Very Useful) and the average response was 3.94/5. In future semesters, the authors will use pre and post assessment tools to provide assurance of student learning and to identify areas of potential improvement for future semesters. The following section of the paper presents the exercise given to students. This exercise is designed to be completed by students on a laptop or in a computer lab using a standard web browser. It also works on mobile devices, such an iPad or Android phone using the Google Sheets app and a Google Account. No additional tools or accounts are necessary. For Page 118 - Developments in Business Simulation and Experiential Learning, Volume 44, 2017 extensions of this exercise, see the last section of the paper. XBRL AND FINANCIAL STATEMENT RATIO EXERCISE INSTRUCTIONS Financial ratios can more easily be calculated using spreadsheets and XBRL. In this lab, you will 1) select an industry to analyze, 2) create a copy of a spreadsheet template, 3) input ticker symbols from three U.S. public companies, and 4) calculate financial ratios and make observations about the state of the companies using these financial ratios. PART 1: SELECT AN INDUSTRY Below is a list of 15 Fortune 100 companies in five different industries. Each of these companies has attributes and strategies that are similar to and different from their competitors. Choose one industry to analyze.  Retail: Walmar t (WMT), Target (TGT), and Kroger (KR)  Technology: Microsoft (MSFT), Apple (AAPL), and Facebook (FB)  Pharmaceutical: Johnson & Johnson (JNJ), Merck (MRK), and Bristol-Myers Squibb (BMY)  Finance: Citigroup (C), Wells-Fargo (WFC), and JP Morgan Chase (JPM)  Energy: Exxon-Mobil (XOM), Chevron (CVS), and Phillips 66 (PSX) PART 2: CREAT A COPY OF A SPREADSHEET TEMPLATE Do this: 1. Open a web browser and go to drive.google.com. 2. If you haven’t done so already, sign into your Google account. FIGURE 1 FINANCIAL RATIOS WITH XBRL TABLE 1 INPUT TICKER SYMBOLS Retail Technology Pharmaceutical Finance Energy WMT MSFT JNJ C XOM TGT AAPL MRK WFC CVS KR FB BMY JPM PSX https://drive.google.com/ Page 119 - Developments in Business Simulation and Experiential Learning, Volume 44, 2017 3. Go to http://tinyurl.com/xbrlratios. You will see a spreadsheet similar to Figure 1. 4. Click File > Make a copy… as shown in Figure 1 below. 5. Rename your spreadsheet, if desired, and click OK to save a copy to your Drive. A new tab will open with your copy of the spreadsheet. You may now edit the values and formulas. PART 3: INPUT TICKER SYMBOLS Refer to Table 1 below for your industry’s ticker symbols. 1. In the “Main Company Ticker” field, input the ticker of the company you would like to focus your analysis on and press Enter. In a moment, the value on the spreadsheet will change to Loading… and then show your company’s financial figures. 2. In the “Most Recent Year” field, enter the most recent reporting year. It may be the current year or the previous year. 3. In the “Period” field, enter either FY for fiscal year or Q1 for 1st quarter, etc. 4. In the “Round to” field, choose the rounding amount. 1,000 will round to thousands of dollars; 1,000,000 will round to millions of dollars. 5. In the “Comparable 1 Ticker” field, input the ticker of a second company you would like to compare with your first company. 6. In the “Comparable 2 Ticker” field, input the ticker of a third company you would like to compare with your first company. PART 4: ANALYZE THE FINANCIAL RATIOS First, review the Facts sheet to determine whether there are any values missing for the companies you are analyzing. Describe what impact (if any) the missing data has on the ratios. Once you have determined whether any data is missing, you have a chance to find some interesting trends and comparisons in the data. You will click through the sheets at the bottom to review the ratios. To aid in this analysis, the template also includes SparkLines that provide a mini-graph to help you quickly visualize any significant values or trends. Review the 14 financial ratios and make some conclusions or judgements about the values, trends, or comparisons with the other companies. For example, if one company has a significantly higher debt-to-equity ratio than the other two, what might be driving this? Has the company you are analyzing seen any major changes in their ratios in the past three years? Which of the three companies is most liquid in the most current year? How has your company managed short-term liabilities over the last three years? Make sure you analyze liquidity, profitability, financing (leverage) and activity for your company. Make sure you consider the DuPont framework to interpret the results and make sure you make a judgement about your company’s financial position based upon the data. EXPANDING THE EXERCISE ADDITIONAL COMPANIES The add-in for this spreadsheet is provided by FinDynamics and provides access to data related to Fortune 100 companies. Instructors and students wishing to choose additional companies or make comparisons with smaller companies, must register for a free academic account. Go to https://findynamics.com/register/ and check the Apply for Academic License button. CUSTOM SPREADSHEETS You can make any Google Sheet XBRL enabled by adding a script to the page. 1. Go to https://findynamics.com/gsheets/ixbrlanalyst.gs. 2. Select the script and copy it to your clipboard. 3. Open your Google Sheet. 4. Go to Tools > Script Editor… 5. Go to the end of the script and paste the script you copied above in step 2. 6. Save the script and close the browser tab to return to your Google Sheet. 7. Refresh the page. You will now see a new menu called iXBRLAnalyst. 8. [Optional] To access additional companies, connect your FinDynamics account a. Go to iXBRLAnalyst > Connect. b. Enter your FinDynamics username and password when prompted. 9. You will now be able to load all company figures. The basic formula to access XBRL data is: =XBRLFact (ticker, xbrltag, year, period). Set up your sheet to include the company, accounts, year, and reporting period. Then use relative references to load the relevant information. CONCLUSION This paper describes a rich, complex curriculum unit designed to be delivered to accounting majors in an accounting information course. It focuses on the generation and interpretation of financial ratios to evaluate the financial health of business organizations and requires students to access real time corporate financial data to calculate and interpret financial ratios while simultaneously introducing students to XBRL data and developing their spreadsheet skills. Anecdotal and overall evidence from student reaction to this unit has been good. Students are very positive when they have learned material that they deem to be useful and relevant and this unit delivers on those measures. It is also a creative way to integrate curriculum content across courses. http://tinyurl.com/xbrlratios https://findynamics.com/register/ https://findynamics.com/gsheets/ixbrlanalyst.gs Page 120 - Developments in Business Simulation and Experiential Learning, Volume 44, 2017 Accounting Education Change Commission (AECC). (1990a). Issues statement number one: AECC urges priority for teaching in higher education. Sarasota, FL: American Accounting Association. Accounting Education Change Commission (AECC). (1990b). Position statement number one: Objectives of education for accountants. Sarasota. FL: American Accounting Association. Albrecht, W.S., & Sack, R.J. (2000). Accounting education: Charting the course through a perilous future. Sarasota, FL.: American Accounting Association. American Institute of Certified Public Accountants (AICPA). (1989). AU329: Analytical procedures. New York, NY: AICPA. American Institute of Certified Public Accountants (AICPA). (1999). Core competency framework for entry into the accounting profession. AICPA. http://www.aicpa.org/ interestareas/accountingeducation/resources/pages/ corecompetency.aspx (accessed October 16, 2016). Hamilton, J. G., & Klebba, J.M. (2011). Experiential learning: a course design process for critical thinking. American Journal of Business Education 4, no: 12, 1-12. Krathwohl, D. R. (2002). A revision of bloom's taxonomy: an overview. Theory into Practice 41, no. 4, 212-218. Libby, R. L., Libby, P. A., & Hodge, F. (2017). Financial Accounting. New York, NY: McGraw-Hill Education. Spiceland, D., Sepe, J., Nelson, M., & Thomas, W. (2016). Intermediate Accounting. New York, NY: McGraw-Hill Education. Weil, R. L., Schipper, K., & Francis, J. (2014). Financial Accounting. Mason, OH: South-Western Cengage Learning. Wild, J.J. (2016). Financial Accounting Fundamentals. New York, NY: Mc-Graw Hill Education. REFERENCES http://www.aicpa.org/edu/corecomp.htm http://www.aicpa.org/interestareas/accountingeducation/resources/pages/corecompetency.aspx http://www.aicpa.org/interestareas/accountingeducation/resources/pages/corecompetency.aspx http://www.aicpa.org/interestareas/accountingeducation/resources/pages/corecompetency.aspx