Excel Across the Curriculum Moderator & Panelist: Dr. Kimberly

Excel Across the Curriculum Moderator & Panelist: Dr. Kimberly Swanson Church - University of Missouri Kansas City Panelist: Dr. Jennifer Riley – University of Nebraska Omaha Dr. Paul Goodchild – University of Central Missouri Julie Kline – University of Missouri Kansas City Angela King – Johnson County Community College March 4, 2016

Motivation for taking on this project Extensive use of Excel in Accounting Profession Department Employer initiative to complete Excel skills assessment perceptions of Excel skills for new hires Survey – Given to business professionals in the KC area during an Excel conference Onset of Big Data requires an advanced Excel skillset Like other technology skills Excel is a large/diverse skillset that requires repetition for learning/recall.

Why not just cover the Excel skills in AIS? Technology is pervasive in accounting, where does it make sense to partition the technology skills necessary for our students’ success? ERP/Databases Data analytics diagramming tools project management tools audit support tools general ledger Excel Etc

Excel Skills Approach Across the Curriculum Published resources for purchase/download with Excel included Update your own assignment/activity to include basic Excel skills Create an Excel skills lesson within a project Critical thinking Excel skills within an exercise/project What can you do?

Published: Systems Understanding Aid, 8th Edition Manual full accounting cycle (Paper-based system ) and Excel-based accounting cycle Faculty Quote (from their website) “There is no other tool out there that allows accounting students to work through the entire accounting cycle, from transaction analysis through closing entries. It does an excellent job of helping students understand this process.“ Also offer same company and project in General Ledger software packages

Published: Systems Understanding Aid, 8th Edition WAREN SPORTS SUPPLY BALANCE SHEET AT DECEMBER 31, 2013 AND 2012 ASSETS 2013 2012 CURRENT ASSETS Cash Accounts receivable - net Inventory Marketable securities Total current assets 109,962.33 44,013.24 196,461.00 24,000.00 374,436.57 11,025.19 7,814.19 101,681.00 0.00 120,520.38 FIXED ASSETS - Net of accumulated depreciation 215,921.00 239,105.50 590,357.57 359,625.88 Total assets LIABILITIES & STOCKHOLDERS' EQUITY CURRENT LIABILITIES Accounts payable Payroll taxes withheld and payable Federal income taxes payable Interest payable Total current liabilities 8,767.15 11,279.35 4,062.45 52,293.66 197.26 65,320.52 3,284.54 29,797.00 0.00 44,360.89 NOTE PAYABLE Total liabilities 80,000.00 145,320.52 0.00 44,360.89 STOCKHOLDERS' EQUITY Common stock Retained earnings Total stockholders' equity 225,000.00 220,037.05 445,037.05 225,000.00 90,264.99 315,264.99 590,357.57 359,625.88 Total liabilities and stockholders' equity

Update: Documentation Assignment

Create: Using Excel to Create Source Documents Step 1: Excel tutorial using updated version of Taylor and Pennington (2009). Excel 2007: A tutorial of basic skills for accounting and business majors. C3 volume 4. Step 2: Excel assignment Unformatted data Create worksheets Create usable source documents w/ internal controls Data validation, named ranges, drop lists Vlookup, hlookup

Critical Thinking: Business Intelligence with Pivot Tables Step 1: A tutorial using Kovar, Kovar, Vogt (2009) Pivot Table Toolkit: Materials for Integrating Pivot Tables in the Accounting Curriculum. C3, Volume 4 Step 2: Pivot Table Assignment Unformatted data Develop reasonable business questions Answer questions with pivot tables and pivot charts

Excel Skills in Managerial Accounting Courses Published: Publisher provided materials Update: your own assignment/exercise to include basic Excel skills Create: Critical an Excel skills lesson within an exercise/activity thinking: larger set of Excel skills within an exercise/project

Published: Managerial Accounting, Garrison et al. Preformatted Excel worksheets for specific chapter questions. Narrated slides to complete Excel file New for version 14 is a Word file that provides formula details

Published: Understanding Corporate Annual Reports, Pasewark Preformatted Excel worksheets for ratio analysis. Example Excel file Workbook style text and Advanced Analysis Word file.

Published: Bloomberg Terminal U.S. and International news Downloadable (company) data into Excel Corporate and Industry Research

Create: Journal Entries, T-accounts, & Statements Blank Excel worksheets: small group projects

Update: Budget Project

Critical thinking: Financial: Data Analysis Project

Critical thinking: Cost: Budget Project

Excel Skills in Financial & Audit Accounting Courses Published: Publisher provided materials Update: your own assignment/exercise to include basic Excel skills Create: Critical an Excel skills lesson within an exercise/activity thinking: larger set of Excel skills within an exercise/project

Published: Auditing & Assurance Services, Messier et al.

Update: Financial statements

Create/Critical Thinking: Financial Statement Project

Create/Critical Thinking: Financial Statement Project, cont

Excel Skills in Specialized Accounting Courses Published: Publisher provided materials Update: your own assignment/exercise to include basic Excel skills Create: Critical an Excel skills lesson within an exercise/activity thinking: larger set of Excel skills within an exercise/project

Update: Computerized Accounting Problems – Required 3 hour course in JCCC’s A.A.S. Accounting degree - Created 12 years ago due to advisory board feedback - Offered via hybrid, face-to-face and online - Accounting problems solved in Excel

Published: Textbook Lesson #4 – Loan and Bond Amortization Points 1 Using Excel & Access For Accounting 15 2 Using Excel & Access For Accounting Using Excel & Access For Accounting Read Chapter 5. Complete ‘Chapter 5 Assignments’ #1 & #2 for Coast Jewelers. p.113114 Complete Chapter 5 Case Problem #1 – Kelly’s Boutique. p.114-115 Complete Model Building Problem 13 (M13) through the chart. (Bond Disc & Prem Amort Schedule) p. 168 3 15 10

Create: - First two weeks, learn Excel basics - Different weekly topics: - Financial Statement Preparation and Analysis Depreciation Schedules Bond and Loan Amortization Schedules Budgeting - Predicting Costs CVP Analysis Capital Budgeting Analysis Pivot Tables

Critical thinking: - What If Analysis - Pivot Tables – Given 300 rows of data with 15 columns, the student must determine how to set-up the pivot table to achieve the desired result. - Microsoft Access – Queries & Reports

Conclusion – Excel Across the Curriculum Basic Skills Basic Mathematical Function (count, sum, average, max, min, rounding.) Identify accepte d Excel skills for your students Find and/or Replace (using a dialog box to locate cells) Filter, Sort, Subtotal data (feature to rearrange or extract data in cells) Intermediate Skills Text to Columns (separate contents of a cell using the data menu, transposing column to row) Conditional Formatting (using a dialog box to self-create a formula rule for formatting of specific cells, often used when buit-in formatting isn't enough, for example fuzzy dupes) Importing/Exporting data (import data from another source) Advanced Skills Setup and Use Excel Macros (self-created functions to perform specific tasks) Vlookup/Hlookup ( vlookup (Cell reference, array, column index, [True/False]) Pivot Table (data summarization tool for large amount of data)

Conclusion – Excel Across the Curriculum Basic Skills Basic Mathematical Function (count, sum, average, max, min, rounding.) Determin e Excel skills coverage in your course Find and/or Replace (using a dialog box to locate cells) Filter, Sort, Subtotal data (feature to rearrange or extract data in cells) Intermediate Skills Text to Columns (separate contents of a cell using the data menu, transposing column to row) Conditional Formatting (using a dialog box to self-create a formula rule for formatting of specific cells, often used when buit-in formatting isn't enough, for example fuzzy dupes) Importing/Exporting data (import data from another source) Advanced Skills Setup and Use Excel Macros (self-created functions to perform specific tasks) Vlookup/Hlookup ( vlookup (Cell reference, array, column index, [True/False]) Pivot Table (data summarization tool for large amount of data)

Conclusion – Excel Across the Curriculum Map Excel skills list to your accounting curriculum Basic Skills Basic Mathematical Function (count, sum, average, max, min, rounding.) Cell Formatting (change display within a cell: floating point, currency, text, bold, underline, highlight, date.) Use of dynamic cells (avoiding the use of static solutions (etc.) in place of calculations/formulas to reuse worksheets) Print formatting (fit to page, page breaks, etc) Text Manipulation (Left, Right, Concatenate, etc) BSA Course Coverage Systems Audit x X x x x x X

Conclusion – Excel Across the Curriculum Identify the gaps in Excel skills for your curriculum What do we do here? Basic Skills Basic Mathematical Function (count, sum, average, max, min, rounding.) Keyboard Shortcuts (Ctrl C to copy/ Ctrl V to paste) Pasting Special Types (using a dialog box to paste complex items using specific attributes or mathematical functions) Cell Formatting (change display within a cell: floating point, currency, text, bold, underline, highlight, date.) Use of dynamic cells (avoiding the use of static solutions (etc.) in place of calculations/formulas to reuse worksheets) BSA Course Coverage Financial Systems Audit Managerial X X X x X X X x x Is once X Tax X

Conclusion – Excel Across the Curriculum More questions for our panelist?

Systems Understanding Aid, 8th Edition By Arens and Ward Armond Dalton publisher ISBN# 978–0–912503–38–7 Suggested Retail Price to Students: 64.50 http://www.armonddalton.com/publications/systems-understanding-aid-2/

SIMnet for Office 2013 http://successinhighered.com/cit/simnet/ Videos and interactive “Guide Me” pages to allow students to study MS Office skills on any devices. CONTENT LISTING - SIMnet Online for Office 2013 includes the following modules: Microsoft Office Suite Microsoft Outlook Computer Concepts Windows 8, 7, Vista & XP Internet Explorer 9, 8 & 7 contact your McGraw-Hill representative.

Excel-Based Decisions in Managerial Accounting (2014) By Stephenson & Porter Armond Dalton publisher ISBN# 978-0-912503-49-3 Suggested Retail Price to Students: 69.00 http://www.armonddalton.com/publications/excel-based-decisions-in-managerial-accou nting/

Computerized Auditing Using ACL Data Analytics 3rd Edition (2013) By Arens, Elder and Borsum ISBN# 978–0–912503–43–1 Suggested Retail Price to Students: 87.00 http://www.armonddalton.com/publications/computer ized-auditing-using-acl-data-analytics/ ACL is suitable for financial auditing by CPA firms, internal auditors, or government auditors, or for operational auditing by internal or other auditors.

Managerial Accounting v 14e By Garrison, Noreen and Brewer McGraw-Hill publisher ISBN: 0078111005 Copyright year: 2012 Text information http:// highered.mheducation.com/sites/0078111005/infor mation center view0/index.html

Understanding Corporate Annual Reports v7 By William R. Pasewark McGraw-Hill publisher ISBN: 0073526932 Copyright year: 2009 Text information http:// highered.mheducation.com/sites/0073526932/infor mation center view0/index.html

Bloomberg Terminal By Bloomberg L.P. The Bloomberg Professional service brings together real-time data on every market, unparalleled news and research, powerful analytics, communications tools and world-class execution capabilities — in one fully integrated solution. http://www.bloomberg.com/professional/products-solutions/