Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more
Skip to content
Cover of Data Analytics Using Microsoft® Excel®: With Accounting and Finance Datasets v2.0
Published: 
November 2019
Page Count: 
482
ISBN (Digital): 
978-1-4533-9522-6

Data Analytics Using Microsoft® Excel®: With Accounting and Finance Datasets

Version 2.0
By Joseph M. Manzo

Included Supplements

Key Features

  • This version is updated to align with Excel 2016.
  • Early chapters explain how to conceptualize, design, and maintain the integrity of data analytics projects in any context, not just finance or accounting. Compatible with general technology courses that are not content specific.
  • Can be used stand alone or as a supplemental resource.
  • Datasets and projects are compatible with many accounting and finance courses, but can also support an interdisciplinary focus.
  • Clearly and simply walks students through core concepts. No prior accounting or finance coursework is required.

Educators

Request Examination Copy Read Online Adopt This Book Customize this Book

Students

Online Access Price:  $36.95 ($45.66 CAD) Color Printed Textbook with Online Access Price:  $63.95 ($77.41 CAD)
Buy This Book Redeem My Code

This textbook is suitable as either a core text for using Excel 2016 in an introductory data analytics course or as a supplement to many basic through intermediate accounting or finance courses. May also be assigned to students who need exposure or a refresher on how to use the Excel 2016 application in general.

Data Analytics Using Microsoft 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 2016. This clear, easy-to-follow text 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. 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.

Extensive datasets included in the book are drawn from accounting and finance scenarios and provide learners with a basis for conceptualizing and designing data analytics projects with the highest level of integrity. Data visualization skills are also introduced and reinforced throughout.

New in This Version

  • Updated to align with Excel 2016.
  • Expanded to nine chapters in length due to expanded coverage of data analytics and new datasets.
  • Over 18 new Excel skills and features covered.
  • Author-generated instructional videos have all been re-recorded from scratch with new screenshots and voice overs.
  • About the Author
  • Acknowledgments
  • Dedication
  • Preface

    • Chapter 1: Introduction to Data Analytics and Microsoft Excel 2016

  • 1.1 A Working Model for Data Analytics
  • 1.2 A Review of Excel 2016
  • 1.3 Chapter Assignments and Tests

    • Chapter 2: Essential Skills for Basic Data Analytics Projects

  • 2.1 Entering and Managing Data
  • 2.2 Formatting Worksheets
  • 2.3 Chapter Assignments and Tests

    • Chapter 3: Data Analytics Techniques for Basic Projects

  • 3.1 Introduction to Data Visualization
  • 3.2 Introduction to PivotTables and PivotCharts
  • 3.3 Chapter Assignments and Test

    • Chapter 4: Using Mathematical Outputs for Analytics

  • 4.1 Formulas
  • 4.2 Basic Statistics Functions
  • 4.3 Chapter Assignments and Test

    • Chapter 5: Scenario Analytics

  • 5.1 Loan and Lease Scenarios
  • 5.2 Investment Scenarios
  • 5.3 Chapter Assignments and Test

    • Chapter 6: Exception Analytics

  • 6.1 Logical Functions
  • 6.2 Lookup Functions
  • 6.3 Chapter Assignments and Test

    • Chapter 7: Summarizing Exception Analytics

  • 7.1 Statistical IF Functions
  • 7.2 Statistical IFS Functions
  • 7.3 Chapter Assignments and Test

    • Chapter 8: Working with Big Datasets Case: Investigating Fraud

  • 8.1 Using the DATA Analytics Framework
  • 8.2 Data Audit Using PivotTables
  • 8.3 Analyzing the Data
  • 8.4 Summarizing Big Datasets in Custom Reports
  • 8.5 Establishing Data Internal Controls
  • 8.6 Chapter Assignments and Test

    • Chapter 9: Charts for Presentations and Data Visualization

  • 9.1 Chart Types
  • 9.2 Formatting Enhancements for Charts
  • 9.3 Using Charts in PowerPoint and Word
  • 9.4 The Scatter Chart and Motion Data Visualization
  • 9.5 Chapter Assignments and Test
    • FlatWorld Homework System

    FlatWorld Homework

    FlatWorld Homework includes multi-format questions written specifically for your FlatWorld book, which you can access through our stand-alone interface or integrate with your learning management system.

    Request a demo

    In order to give you the best experience, it is important that we have accurate data about your correct school. If you are unable to find the correct school above, please enter the name and web address here so that we can add it to our system.



    Instructor’s Manual

    Instructor’s Manual

    The Instructor’s Manual guides you through the main concepts of each chapter and important elements such as learning objectives, key terms, and key takeaways. Can include answers to chapter exercises, group activity suggestions, and discussion questions.

    PowerPoint Lecture Notes

    PowerPoint Lecture Notes

    A PowerPoint presentation highlighting key learning objectives and the main concepts for each chapter are available for you to use in your classroom. You can either cut and paste sections or use the presentation as a whole.

    PowerPoint Lecture Notes

    Test Generator - powered by Cognero

    Test Generator - powered by Cognero

    FlatWorld has partnered with Cognero, a leading online assessment system, that allows you to create printable tests from FlatWorld provided content.

    Test Bank Files for Import to Learning Management Systems

    Test Bank Files for Import to Learning Management Systems

    For your convenience, we've packaged our test items for easy import into Learning Management Systems like Blackboard, Brightspace/D2L, Canvas, Moodle, or Respondus.

    Test Item File

    Test Item File

    Need assistance in supplementing your quizzes and tests? Our test-item files (in Word format) contain many multiple-choice, fill-in-the-blank, and short-answer questions.

    Other Supplements

    Other Supplements

    Solutions manuals, sample exams, video learning segments, workbooks, cases and lab manuals are just some of the extras our books will offer depending on the needs of the course. Click here to see what this textbook offers.

    At FlatWorld, we take pride in providing a range of high-quality supplements alongside our titles, to help instructors teach effectively. Supplements are available for instructors who have registered their adoption with us. If you need to review or preview something specific, please contact us.


    Sign up for FlatWorld

    Already registered? Sign in here

    Joseph M. Manzo Lehigh University

    Joseph M. Manzo (CMA and MBA Lehigh University) is Professor of Practice in Accounting at Lehigh University’s College of Business. He started teaching at Lehigh in fall 2002 where he developed and oversaw the Microsoft Excel Competency program for all first year business students. He went on to teach several courses in both the undergraduate and graduate programs in the College of Business and since 2010 has taught Introduction to Financial Accounting and Introduction to Managerial Accounting.  In addition to teaching, he is a former director of the Rauch Center for Business Communications and also former acting Associate Dean and Director of Undergraduate Programs for the College of Business. Prior to Lehigh, Professor Manzo worked in apparel manufacturing, retail, and wealth management industries. His industry practice had a heavy emphasis on data analytics and corporate strategic planning. He has written several textbooks addressing practical applications for Microsoft Office that teach students how to use such applications for professional and personal needs. 

    Additions & Errata

    1/27/20:

    Section 2.1: Added the Data Analytics Project 1 Excel file.

    8/14/20: 

    [Note: Applicable changes have been made in the Instructors Manual and the Chapter 8 PowerPoint files.]

    Section 2.1: Editing Data, Step 4, has been corrected to: Click cell B1.

    Section 2.2: Formatting Worksheet Tabs, Step 7, added a third sentence that reads: Click the Orange, Accent 2, Lighter 40% option from the color palette. 

    Section 2.2: Keyboard Only, Center, Left, and Right Alignment, the first sentence has been corrected to: Tap the ALT key on your keyboard followed by the H key.

    Section 2.3: Chapter Assignments and Tests, Data Analytics Skills Review Divisional Sales Report, Step 1, has been corrected to: Open the AnswerSheet worksheet in the Chapter 2 DA Exercise 2 workbook.

    Section 3.3: Chapter Assignments and Test, Data Analytics Skills Review Analyzing Retail Store Sales, the last value in step 19 now reads 55,400 rather than 54,500.

    Section 3.3: Chapter Assignments and Test, Data Analytics Skills Review Analyzing Retail Store Sales, Step 32, the cell range at the very end of the sentence has been corrected to A2:D36.

    Section 3.3: Chapter Assignments and Test, Data Analytics Skills Review Analyzing Stock Market Sectors, the very end of the first sentence of the second paragraph has been corrected to: Chapter 3 DA Exercise 2.

    Section 4.2: Absolute References, Step 5 has been corrected to: Copy cell B4. Step 8 has been corrected to: Tap the Enter key to close cell B5.  Step 9 has been corrected to: Activate cell B5 and tap the DELETE key to remove the formula creating the divide by zero error.

    Section 4.2: Paragraph between Figures 4.19 and 4.20 has been corrected to read: Figure 4.20 shows the output of the formula after pasting it with an absolute reference on cell A16. The formula bar shows how the formula appears in cell B15. Notice that cell A16 has not changed because of the absolute reference. However, relative referencing increased the row number for cell A4 to A15.

    Section 4.3: Chapter Assignments and Test, Data Analytics Skills Review Business Flex Budgets, Step 6, deleted the last sentence that read, “Activate cell G6”. 

    Section 5.1: PMT Function for Loans, add the following sentence to step 9:  If you are using Office 365, type “loan payments”. 

    Section 5.2: Time Value of Money Terms and Concepts, in the first paragraph the 5th sentence has been corrected to: Notice that the values in the Interest Paid column increase every year. 

    Section 5.2: The definition for the key term "401K" was corrected so that the last word of the definition is “free” rather than “fee”.

    Section 5.3: Chapter Assignments and Test, Data Analytics Skills Review Pro Forma Income Statement, Step 52, the second sentence has been corrected to: Click the Lease Data worksheet, click cell B4, and then tap the ENTER  key on your keyboard. 

    Section 5.3: Chapter Assignments and Test, Cash Outflow Scenarios, Step 49, added the following sentence to the end of this step: To complete the function,  type a closing parenthesis and tap the ENTER key on your keyboard.

    Section 6.2: Data Visualization for Exception Analytics, Step 4, corrected entire step to read: Locate the Type category in the lower right area of the New Formatting Rule dialog box (see Figure 6.33). Click the down arrow on the first dropdown box under this category. This is used to set the parameters that will assign a green circle to the value in the Percent Gain/Loss column.

    Section 6.2: Data Visualization for Exception Analytics, Step 8, changed first sentence to the following:  Click the down arrow on the second dropdown box below the Type category in the lower right area of the New Formatting Rule dialog box.

    Section 7.1: The SUMIF Function,Step 11, the first sentence corrected to: Highlight the range Q5:Q34. 

    Section 8.1: Case: Investigating Fraud, Figure 8.1, the green arrow direction above the "Cash Register" box has been corrected.

    Section 9.1: The definition for the key term "accounts receivable" was corrected to: An account used to track money that is owed to a company from its customers.

    Section 9.2: Visual Enhancements for the Plot and Chart Areas, Step 12, added the following sentence: If using Office 365, this is the Round option.

    9/4/20: 

    Section 3.3: Chapter Assignments and Test, Data Analytics Skills Review Analyzing Retail Store Sales, in the opening paragraph for this assignment, 54,000 was corrected to 55,400.

    12/4/20:

    [Note: Applicable changes have been made in the Instructors Manual and the Chapter 6 PowerPoint files.]

    Section 4.2: Sentences just after Figure 4.19 were corrected to read as follows: Figure 4.20 shows the output of the formula after pasting it with an absolute reference on cell A16. The formula bar shows how the formula appears in cell B15. Notice that cell A16 has not changed because of the absolute  reference. However, relative referencing increased the row number for cell A4 to A15.

    Section 6.2: VLOOKUP Function, Step 27, cell number was corrected to "B5". 

    Section 6.2: Table 6.4, "column 1" was corrected to "row 1" in last sentence of the Definition cell for Row_index_num.

    Section 6.2: The title for Figure 6.20 was corrected to read "VLOOKUP Function for the Description Column ".

    12/28/20:

    [Note: Applicable changes have been made in the Instructors Manual.]

    Section 2.3: Data Analytics (Skills Review) Exercise 2, Step 15, changed to “Row 7”.

    Section 7.2: The AVERAGEIFs Function, step 18 changed to “D8”.

    Section 7.2: The CONCATENATE Function, Step 3, “L2” changed to “L1”.

    Section 8.2: Navigating Big Data Sets with the Keyboard, Step 16 changed to “14 points”. Step 17 changed to “35 points”. Step 18 changed to “14 points”.

    Section 8.3: Solving the Case, Step 37, changed “C” to “D”.

    Back to top