Week 4 - Intro to AIS and Accounting Analytics Fall 2023 ACTG 335 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 Power Query/Get & Transform functions. Our objectives this week are to: Continue work on scenario planning in Excel Learn common frameworks used for effectively controlling business processes and operations, both in the physical realm and in the IT realm. Learn how Excel's built in Power Query feature can be used to transform or clean data, which often represents 30-80 percent of an analyst's job. Deliverables Submit your work to the Assignments area in Canvas: Files HW 4 Worksheet, renamed HW4_firstname_lastname Chapter6_Firstname_Lastname Chapter7_Firstname_Lastname PowerQuery I_Firstname_Lastname HW 4 Worksheet Chapter 6 DA Exercise 1.xlsx Data Analytics Project 5.09.xlsx Chapter 7 Skills Test.xlsx Data_Cleaning.xlsx LoanStats3c.xlsx/nPart 1: Excel textbook and concepts 1) Please continue to use Discord to discuss problems you encounter while working through the instructions. There were many great questions and helpful answers. As with last week, if the instructions aren't working on your system, explain what you tried and provide associated screenshots so you won't lose points. This week's lab has been tested in the virtual lab at vlab.pdx.edu. If you have any issues with the instructions, please try them in the vlab, and post any issues in Discord. Chapter 6 2) Chapter 6 covers logical functions including IF, AND, OR and nested IF, plus lookups and some conditional formatting functions. Skim the chapter and work through any parts you'd like to practice. 3) Complete the Data Analytics (Skills Review) Chapter 6 DA Exercise 1.xlsx at the end of the chapter. You can ignore step 20 if you'd like. Save your file as Chapter6_Firstname_Lastname. Take a screenshot showing the first several rows of columns J through P./nChapter 7 4) Chapter 7 covers formulas that include simple logical functions: COUNTIF, AVERAGEIF, and SUMIF, plus compound logical functions that can accommodate more than one criterion (e.g. major is accounting and GPA is >2.7). 5) Open the Data Analytics Project 5.09.xlsx file and work through all of Chapter 7. You won't be asked to turn in this spreadsheet or screenshots-it is intended for practice, so focus on learning rather than accuracy. 6) Open the Chapter 7 Skills Test.xlsx file. Complete the Chapter Skills Test at the end of the chapter. Save your file as Chapter7_Firstname_Lastname. 7) Take a screenshot showing your completed Summary sheet. In the table below, paste your formulas for the cells indicated. Cell Formula B3 C3 D3 BIO CIO DIO 2/nPart 2: AIS textbook and concepts Chapter 8 8) Read the section 8.1 What are ERP systems and watch the video in that section. Some companies use comprehensive ERPs and others use dedicated systems such as QuickBooks for accounting and SalesForce for customer relationship management (CRM)? What are the relative advantages of each approach? Chapter 9 9) Read the chapter and pay special attention to the discussion of the COSO framework and to the Seventeen Principles of Internal Control presented in Table 9.1. You don't need to watch the videos in this chapter. *If you're an accounting student, you'll spend a lot of time on COSO in your auditing course. These concepts apply to organizational control in general and some also apply to business processes in sales, purchasing, and manufacturing, which we'll study further in chapters 11-13. The following description is of a fictitious organization that, like Joe's Ristorante, has strengths but also control weaknesses. In my work in the microfinance industry and with nonprofits over many years, I encountered organizations similar to the fictitious one in this example. 10) Read the example below and think about its control strengths and weaknesses, relative to the COSO 2013 principles in Table 9.1. Transformation Bank Transformation bank is an NGO. Its mission is to alleviate poverty through microfinance. Donors, board members and employees believe in this mission. The bank loans money to microbusiness owners in developing countries. Most loans are in the $200 to $1000 range, and are used to invest in inventory or capital assets such as carts, bicycles, or small machines that can be used to generate income. In addition to loans, the bank offers provide business training and support to clients./nChapter 10 II) Read the chapter, paying special attention to the three application control categories and the three general control categories described in sections 10.2 and 10.3. You do not need to watch the videos in this chapter. 12) Read the EXP Fundamentals Corp. case, which is problem 27 in the End-of-Chapter Exercises. Answer the following questions rather than those in the text. According to Alice, the new CTO, what are the company's current problems? What are Alice's concerns associated with automating the manual system? For each of the six categories listed below, identify one or more risk EXP might face if it automates the system and specific controls that could be implemented to address those risks. You can use risks and controls from the case and from the chapter. Example risks Threat category Application controls Input Process Output General controls Access security Change Operational How risks can be controlled