Week 3 - Intro to AIS and Accounting Analytics Fall 2023 ACTG 407 Overview This lab has three parts: one using the Data Analytics Using Excel Microsoft 365 textbook, one using

the Accounting Information Systems textbook, and one using Excel's relational database and PowerPivot functions. Our objectives this week are to: Start thinking about spreadsheet design and principles of well-designed spreadsheets, particularly for financial scenario planning where input data can be changed. We'll also review database principles and learn how databases can be accommodated within Excel. Then we'll use PowerPivot to create Pivot Tables using data from multiple sheets within a workbook. More importantly, we'll learn how Pivot Tables can be created using data from one or more external sources, without loading that data into Excel. This enables analysts to create and automatically update Pivot Tables and other analyses using live data sets that are too large to store in an Excel file. Deliverables Submit your work to the Assignments area in Canvas: HW 3 Worksheet, renamed HW3_firstname_lastname • An Excel spreadsheet, renamed Spreadsheet3_firstname_lastname Files HW 3 Worksheet Data Analytics Project 3.xlsx Data Analytics Project 4.xlsx PFF Starter Database.xlsx PFF Starter Database.accdb/nInstructions The tables in this document are the same as those in the HW3 Worksheet. If you find it easier, you can fill in the tables here and then paste them into the worksheet when you're done with the assignment. You are allowed to discuss this assignment with classmates or even to work through the assignment together. However, you are not allowed to work on a single worksheet or Excel file-each student must complete their own version. Submitting two copies of a single file is not allowed. Part 1: Excel textbook and concepts Chapter 4 1) Skim Chapter 4 and read about or practice concepts and skills that are new or of interest to you. Chapter 5/n2) Chapter 5 covers a number of financial functions--some may be familiar to you. Using Data Analytics Project 3.xlsx and Data Analytics Project 4.xlsx, work through the entire chapter. Important: You will not be asked to provide any specific screenshots or numbers from within the chapter. Focus on learning or practicing skills rather than replicating the spreadsheets perfectly or getting the same answers as those shown in the text. *At the end of the chapter, you'll complete the Credit Card Amortization Schedule problem. If you'd like, you can try setting up and working through this problem first, and then reading the chapter to see how the author tackles similar problems. As you work through chapter 5, if you're already comfortable with some skills, you can skip them. You may have noticed that there are many versions of each practice spreadsheet, and the book tells you which one to use for each section. For example, if you skip the PMT Function for Loans section at the beginning of Chapter 5 and jump to Referencing Data Between Worksheets, you'll start with Data Analytics Project 3.01.xlsx. 3) As you work, make note of new formulas and pay attention to overall concept of scenario analytics. Much of analysts' time is spent doing scenario planning and what-if analysis. Pay attention to how the author sets up the problems and to the controls you are asked to include in your work. 4) The chapter highlights some foundational spreadsheet concepts that are particularly useful for shared and interactive spreadsheets. For example, the spreadsheets are set up such that data entry areas are clearly marked and separate from calculations. List and describe at least two other ways in which the spreadsheets are well-designed. This ICAEW post on 20 principles of good spreadsheet practice may be useful. (The PDF is available in Canvas.)/n5) The section at the end of the chapter called Worksheet Protection for Data Internal Control steps you through the process of protecting a worksheet such that only cells that require user input can be changed. The remainder of the worksheet, including all cells that contain formulas are protected from accidental or intentional modification or deletion. List the two main steps needed to protect a worksheet in this way. Don't give keystrokes, just give a short description you'd use if explaining to a coworker before showing them the keystrokes 6) Complete the Credit Card Amortization Schedule at the end of the chapter. You'll start with a blank spreadsheet for this assignment. Partial screenshots of the solutions provided by the textbook author are shown below. You do not need to design or organize your spreadsheet the same way. Please don't merge cells. You can create the same effect with color, spacing, and outside borders around selected groups of cells. It's good practice to make it obvious where the user should enter data, using color, spacing, boxes, etc. 2/nAmortization schedule: 2 Repayment in Years 3 Credit Card Balance Payment 5 Number 6 7 8 Pivot table: 1 2 2022 2023 3 A Statement Date 9/1/2020 10/1/2020 11/1/2020 12/14/2020 Row Labels 2020 2021 $ $ $ $ с D Credit Card Payment Analysis 14 Year Sum of Interest Payment 5,000 Amortization Schedule Month 2020 September 2020 October $ 2020 November $ 2020 Decembar € Sum of Principal Payment 266 $ 787 $ E 768 $ 746 S APR Monthly Payment 33 110 129 151 S 5,000.00 4,991.92 4,983.74 A 075 A Beginning Principal Monthly Interest Principal Balance Payment Payment Payment Count of Number of Payments G $74.74 $ 66.67 $74.74 $ 66.56 $74.74 $ 66.45 e BB 31 €74 74 4 12 $74.74 12 12 H 16% $8.08 $8.18 $8.29 o An Show the formulas you used in your analysis by clicking on the Formulas tab and selecting Show Formulas. Or better, use Ctrl + ~ to toggle formulas on and off. Take a screenshot showing the formulas you used in columns E through H in the Amortization Schedule above. (Your calculations may be in different columns.) Include the column headings and first two rows of formulas. Paste your screenshot here.