Reunion Evolution: Books 1 and 2

This textbook is suitable as either a core text for using Excel Microsoft 365 in an introductory data analytics course or as a supplement to basic through intermediate accounting or finance courses. It may also be assigned to students who need exposure or a refresher on how to use the Excel Microsoft 365 application in general. Data Analytics Using Microsoft 365 Excel: With Accounting and Finance Datasets provides students with the opportunity to learn basic skills needed to execute many personal and professional activities using Excel Microsoft 365. This clear, easy-to-follow text explains how to analyze and make discoveries in datasets by treating data visualization skills as a vital component of the analytics process. It also prepares learners to master more advanced skills using Excel. Heavy emphasis is placed on how to efficiently navigate big datasets and use the keyboard to access commands during the design process. Extensive datasets included in the book are drawn from accounting and finance scenarios. They provide learners with a basis for conceptualizing and designing data analytics projects with the highest level of integrity. Author-narrated videos embedded throughout the digital reader explain concepts and operations, adding an engaging audio and visual richness to the written explanations and exercises. Brief Contents Contents About the Author Acknowledgments Dedication Preface Chapter 1: Introduction to Data Analytics and Microsoft Excel 365 1.1: A Working Model for Data Analytics What Is Data Design? The DATA Analytics Framework Conducting a Data Audit Establishing Data Internal Controls The Data Analytics Model Limitations of Data Analytics 1.2: A Review of Excel 365 Launching Microsoft Excel 365 The Excel Workbook Navigating Excel Worksheets The Excel Ribbon Quick Access Toolbar and Right-Click Menu The Backstage View Saving Workbooks (Save As) Saving and Sharing Workbooks in the Cloud Worksheet Headers and Footers Page Layout for Printing Worksheets Printing Worksheets Excel Help Keyboard Only Commands 1.3: Chapter Assignments and Tests Chapter 2: Essential Skills for Basic Data Analytics Projects 2.1: Entering and Managing Data Basic Data Audit Entering Data Cut, Copy, and Paste Auto Fill Moving Data Deleting Data and the Undo Command Editing Data Hiding Columns and Rows Inserting Columns and Rows Deleting Columns and Rows Adjusting Columns and Rows Inserting and Deleting Worksheets 2.2: Formatting Worksheets Wrap Text Center Alignment Merge Cells Entering Multiple Lines of Text Formatting Data and Cells Borders (Adding Lines to a Worksheet) Formatting Worksheet Tabs 2.3: Chapter Assignments and Tests Chapter 3: Data Analytics Techniques for Basic Projects 3.1: Introduction to Data Visualization The Treemap Chart Changing the Title of a Chart The Chart Sheet Sorting Data (Single-Level) Sorting Data (Multi-Level) 3.2: Introduction to PivotTables and PivotCharts Creating a PivotTable Sorting a PivotTable Formatting a PivotTable Creating a PivotChart Moving and Resizing Charts Adding a PivotChart Filter Basic Data Internal Controls 3.3: Chapter Assignments and Test Chapter 4: Using Mathematical Outputs for Analytics 4.1: Formulas Freeze Panes Cell References Relative References Formulas Paste Formulas Complex Formulas Auditing Formulas 4.2: Basic Statistics Functions SUM Function Absolute References AVERAGE Function MAX and MIN Functions COUNTA and COUNT Functions Conditional Formats for Data Visualization Conditional Formats for Data Internal Controls 4.3: Chapter Assignments and Test Chapter 5: Scenario Analytics 5.1: Loan and Lease Scenarios Loan Concepts and Terms PMT Function for Loans Referencing Data Between Worksheets Dates and EOMONTH (End of Month) Function The YEAR Function The TEXT Function Data Audit for Loan Amortization Schedule PMT Function for Leases Worksheet Protection for Data Internal Control 5.2: Investment Scenarios Time Value of Money Terms and Concepts The FV (Future Value) Function Evaluating Scenarios with Goal Seek The PV (Present Value) Function 5.3: Chapter Assignments and Test Chapter 6: Exception Analytics 6.1: Logical Functions Adding Capacity to Functions The Logical Test IF Function Nested IF Function The AND Function The OR Function 6.2: Lookup Functions VLOOKUP Function HLOOKUP Function Data Visualization for Exception Analytics Data Internal Controls Using the ISBLANK and IF Functions 6.3: Chapter Assignments and Test Chapter 7: Summarizing Exception Analytics 7.1: Statistical IF Functions The COUNTIF Function The AVERAGEIF Function The SUMIF Function 7.2: Statistical IFS Functions The COUNTIFS Function The AVERAGEIFS Function The SUMIFS Function The CONCATENATE Function 7.3: Chapter Assignments and Test Chapter 8: Working with Big Datasets Case: Investigating Fraud 8.1: Using the DATA Analytics Framework Case: Investigating Fraud Assessing the Case 8.2: Data Audit Using PivotTables Navigating Big Datasets with the Keyboard PivotTables for Basic Data Audits PivotTable Outputs PivotTable Filters PivotTable Calculated Fields 8.3: Analyzing the Data RIGHT Text Function WEEKNUM Function Using the IF Function to Eliminate Repeating Values The ABS (Absolute Value) Function The ISERROR Function Solving the Case 8.4: Summarizing Big Datasets in Custom Reports Excel Spell Check Adding Notes to Cell Locations Using Functions with Big Datasets Selecting Data with Statistical IF and IFS Functions Mixed References 8.5: Establishing Data Internal Controls READ ME Worksheet Setting Capacity Alerts Worksheet Protection 8.6: Chapter Assignments and Test Chapter 9: Charts for Presentations and Data Visualization 9.1: Chart Types Line Charts for Time Series Trends Adjusting the Y Axis Scale Line Charts for Trend Comparisons Pie Charts for Percent of Totals Stacked Column Chart for Percent of Total Trends Adding Series Lines and Annotations Column Charts for Frequency Distributions Frequency Comparison: Controlling Values Assigned to X and Y Axes 9.2: Formatting Enhancements for Charts Formatting the X and Y Axis Values Formatting the Chart Legends and Titles Adding X and Y Axis Titles Data Series Formats and Labels Visual Enhancements for the Plot and Chart Areas 9.3: Using Charts in PowerPoint and Word Creating a Chart Link Between Excel and PowerPoint Pasting a Chart Image into Word 9.4: The Scatter Chart and Motion Data Visualization Scatter Chart for Supply and Demand Changing the Scale of the X and Y Axes Adding a Trendline and Equation 9.5: Chapter Assignments and Test Chapter 10: Predictive Analytics Case: Can You Predict the Stock Market? 10.1: Assessing the Case Case: Can You Predict the Stock Market? Establish the DATA Analytics Framework 10.2: Preliminary Analysis: Examining Assumptions Adding Data Analysis Tools Data Design and Audit Descriptive Statistics Correlation Examining Correlation Through Data Visualization: The Combo Chart Formatting Bars for Negative Values on a Column Chart Identifying Exceptions 10.3: Solving the Case Empirical Probability Regression Interpreting a Regression Output Comments and Other Data Internal Controls 10.4: Case Wrap Up: Prediction versus Reality Appending Data Updating the Analysis Limitations of Predictive Analytics Epilogue: A New Paradigm? 10.5: Chapter Assignments and Tests Appendix A: Keyboard Only Commands from PC to Mac Appendix B: Visual Differences between PC and Mac B.1: Visual Differences on the Ribbon B.2: ToolTip and Dialog Box Differences Appendix C: Command and Feature Differences on a Mac C.1: Accessing Backstage Commands C.2: The Right-Click Menu C.3: Multi-Level Sorting C.4: Sorting Data in a PivotTable C.5: Charts: Assigning Data to X and Y Axes C.6: Conditional Formatting: New Formatting Rule Appendix D: Command and Feature Omissions on a Mac Index