-akes 2e Alpine Cupcakes, Inc. Audit Case Assignments Data Analytics Module Introduction: In the Data Analytics Module, instead of reviewing and evaluating audit workpapers as you have done in prior modules, you will do the audit work yourself. Novw, you are responsible for finding red flags in the data rather than looking for errors in another auditor's work. You need to pause after reading each queestion and use your critical thinking skills to plan an approach to answering the question. The purpose of this module is to provide "hands on" experience and practice in using analytics to complete common audit engagement tasks. You will download Excel files that hold a vast amount of company data. As you work through the questions, you will not only be exposed to using data analytics to answer common audit questions, but you will also have to think of questions your findings imply. After doing the analysis required, do you have additional questions to ask your manager or the firm? For the purpose of allowing you to perform data analysis using Excel files, we assume the casebook year 20X1 is 2021 and the casebook year 20X2 is 2022. Learning Objectives: This module enhances your understanding of the data analytics tools and procedures used in an audit. After performance of the module, you will be able to: 1. Use data analytics tools to complete analytical procedures within an audit. 2. Use critical thinking skills to solve problems and perform auditing procedures. 3. Perform risk assessment procedures using analytical tools. 4. Perform substantive audit procedures to evaluate transactions in the general ledger using analytical tools. 5. Perform substantive audit procedures to evaluate the cash processes and cash account transactions using analytical tools. 6. Perform substantive aud it procedures to evaluate accounts receivable transactions and account balances using analytical tools. Data Analytics Assignments: Before beginning the assignment, take some time to become familiar with typical data analytic tools. There are online tutorials available on this casebook's website. If you plan to use Excel for your analysis, become familiar with using formulas, vLookup and Pivot Tables. These tools are important to your efficient analysis of the data. Also, learn how to use some of the "short cut" keys (for example, copying down an entire page, moving to the top or bottom of a page, finding certain numbers on a page). You should not be looking at one line item at a time when you are performing the data analysis as there are too many transactions to manually check the data. Part 1: Risk Assessment Analytics In this part of your assignment, you will use the Data Analytics Risk Assessment Excel file. Please complete the following tasks using the Excel file: Q1. Analyze the risk associated with the cost of ingredients. Using the data provided in the Excel file, determine the cost of each type of cupcake. Present your work in a professional manner that is easy to understand. An example template is provided in the Excel file or you can create a better way to visually show the solution. Q2. Analyze the risk associated with the cost of ingredients. a. Create a visualization (i.e., chart, graph, table or some other visualization) to show how a 25% and 50% increase in the cost of sugar would affect the price of each cupcake. Case Assignment: pg. He he cenplmal dsplue aoa hal harty has evalva ouer hoe Rcarac and beck gre as fucoyaedhere s a Ha adinilya sh hahonond preassess acuosshatara larders k deess ard ctanges ferg thd Aave ale fer fot's aucld nst e beltre peaic a adid Alpine Cupcakes, Inc. Audit Case Assignments If the number of sales of cupcakes and their sales price remained the same, how would a 25% or 50% increase in the price of sugar affect the gross profit margin on cupcakes (cupcake revenue less cost of cupcakes) and the gross profit margin percent (gross profit margin/cupcake revenue)? Use the number of cupcakes sold in 2022. Q3. Analyze the risk associated with the cost of ingredients. Determine whether a possible change in the all sales of cupcakes remained the same, and the sales price remains the same. Use the number of c. Is there a price point past which you would say an increase in the price of sugar would become a risk to Alpine Cupcakes? price of sugar is the largest risk associated with the cost of sales for cupcakes. As in Question 2, assume cupcakes sold in 2022. Pick that have the largest effect on the cost of cupcakes. b. Show the effect of a 25% and 50% increase in the cost of these two products on gross profit of cupcakes and gross profit margin percent of cupcakes. Q4. Analyze the risk associated with customers. Use sal ices of ingredients to Alpine Cupcakes' future? a. Create a visualization (i.e., chart, graph, table or some other visualization) showing the risk C. How do you assess the verall risk in the rise in data from 2022 associated with customers. In other words, are there any customers that Alpine is particularly reliant upon so the loss of their business would constitute a risk to the continuation of Alpine's business? b. After seeing your visualization, how would you advise Alpine Cupcakes about vulnerabilities associated with customers? Q5. Analyze the risk associated with suppliers. Use data from 2022. Create a visualization (i.e., chart, graph, table or some other visualization) showing the risk a. associated with suppliers of ingredients. Who are Alpine Cupcakes' major suppliers? b. Do you feel there is risk associated with Alpine's suppliers? Are there suppliers upon which Alpine might be too reliant? After seeing your visualization, how would you advise Alpine Cupcakes when planning their purchases of ingredients going forward? Part 2: General Ledger Analytics In this part of your assignment, you will examine the Data Analytics General Ledger Excel file. Please complete the following tasks using the Excel file: Q1. Analyze unusual journal entries based on the time of day. a. Using the data provided in the Excel file, identify any entries made after 9 p.m. and before 6 a.m. in 2022. b. Are there any entries you want to discuss with management? If so, what questions do you have? Are there any additional audit procedures you think need to be performed? Q2. Evaluate unusual journal entries based upon the date transactions were entered into the general ledger. Alpine Cupcakes' official holidays are July 4th, New Year's Day, and Christmas Day. On those days, the business office is closed. The Alpine Storefront is open every day, so those entries will appear on weekends and holidays. The business office is open Monday through Friday, closed on Saturday nc Sunday lising tho dat a -akes 2e Alpine Cupcakes, Inc. Audit Case Assignments Data Analytics Module Introduction: In the Data Analytics Module, instead of reviewing and evaluating audit workpapers as you have done in prior modules, you will do the audit work yourself. Novw, you are responsible for finding red flags in the data rather than looking for errors in another auditor's work. You need to pause after reading each queestion and use your critical thinking skills to plan an approach to answering the question. The purpose of this module is to provide "hands on" experience and practice in using analytics to complete common audit engagement tasks. You will download Excel files that hold a vast amount of company data. As you work through the questions, you will not only be exposed to using data analytics to answer common audit questions, but you will also have to think of questions your findings imply. After doing the analysis required, do you have additional questions to ask your manager or the firm? For the purpose of allowing you to perform data analysis using Excel files, we assume the casebook year 20X1 is 2021 and the casebook year 20X2 is 2022. Learning Objectives: This module enhances your understanding of the data analytics tools and procedures used in an audit. After performance of the module, you will be able to: 1. Use data analytics tools to complete analytical procedures within an audit. 2. Use critical thinking skills to solve problems and perform auditing procedures. 3. Perform risk assessment procedures using analytical tools. 4. Perform substantive audit procedures to evaluate transactions in the general ledger using analytical tools. 5. Perform substantive audit procedures to evaluate the cash processes and cash account transactions using analytical tools. 6. Perform substantive aud it procedures to evaluate accounts receivable transactions and account balances using analytical tools. Data Analytics Assignments: Before beginning the assignment, take some time to become familiar with typical data analytic tools. There are online tutorials available on this casebook's website. If you plan to use Excel for your analysis, become familiar with using formulas, vLookup and Pivot Tables. These tools are important to your efficient analysis of the data. Also, learn how to use some of the "short cut" keys (for example, copying down an entire page, moving to the top or bottom of a page, finding certain numbers on a page). You should not be looking at one line item at a time when you are performing the data analysis as there are too many transactions to manually check the data. Part 1: Risk Assessment Analytics In this part of your assignment, you will use the Data Analytics Risk Assessment Excel file. Please complete the following tasks using the Excel file: Q1. Analyze the risk associated with the cost of ingredients. Using the data provided in the Excel file, determine the cost of each type of cupcake. Present your work in a professional manner that is easy to understand. An example template is provided in the Excel file or you can create a better way to visually show the solution. Q2. Analyze the risk associated with the cost of ingredients. a. Create a visualization (i.e., chart, graph, table or some other visualization) to show how a 25% and 50% increase in the cost of sugar would affect the price of each cupcake. Case Assignment: pg. He he cenplmal dsplue aoa hal harty has evalva ouer hoe Rcarac and beck gre as fucoyaedhere s a Ha adinilya sh hahonond preassess acuosshatara larders k deess ard ctanges ferg thd Aave ale fer fot's aucld nst e beltre peaic a adid Alpine Cupcakes, Inc. Audit Case Assignments If the number of sales of cupcakes and their sales price remained the same, how would a 25% or 50% increase in the price of sugar affect the gross profit margin on cupcakes (cupcake revenue less cost of cupcakes) and the gross profit margin percent (gross profit margin/cupcake revenue)? Use the number of cupcakes sold in 2022. Q3. Analyze the risk associated with the cost of ingredients. Determine whether a possible change in the all sales of cupcakes remained the same, and the sales price remains the same. Use the number of c. Is there a price point past which you would say an increase in the price of sugar would become a risk to Alpine Cupcakes? price of sugar is the largest risk associated with the cost of sales for cupcakes. As in Question 2, assume cupcakes sold in 2022. Pick that have the largest effect on the cost of cupcakes. b. Show the effect of a 25% and 50% increase in the cost of these two products on gross profit of cupcakes and gross profit margin percent of cupcakes. Q4. Analyze the risk associated with customers. Use sal ices of ingredients to Alpine Cupcakes' future? a. Create a visualization (i.e., chart, graph, table or some other visualization) showing the risk C. How do you assess the verall risk in the rise in data from 2022 associated with customers. In other words, are there any customers that Alpine is particularly reliant upon so the loss of their business would constitute a risk to the continuation of Alpine's business? b. After seeing your visualization, how would you advise Alpine Cupcakes about vulnerabilities associated with customers? Q5. Analyze the risk associated with suppliers. Use data from 2022. Create a visualization (i.e., chart, graph, table or some other visualization) showing the risk a. associated with suppliers of ingredients. Who are Alpine Cupcakes' major suppliers? b. Do you feel there is risk associated with Alpine's suppliers? Are there suppliers upon which Alpine might be too reliant? After seeing your visualization, how would you advise Alpine Cupcakes when planning their purchases of ingredients going forward? Part 2: General Ledger Analytics In this part of your assignment, you will examine the Data Analytics General Ledger Excel file. Please complete the following tasks using the Excel file: Q1. Analyze unusual journal entries based on the time of day. a. Using the data provided in the Excel file, identify any entries made after 9 p.m. and before 6 a.m. in 2022. b. Are there any entries you want to discuss with management? If so, what questions do you have? Are there any additional audit procedures you think need to be performed? Q2. Evaluate unusual journal entries based upon the date transactions were entered into the general ledger. Alpine Cupcakes' official holidays are July 4th, New Year's Day, and Christmas Day. On those days, the business office is closed. The Alpine Storefront is open every day, so those entries will appear on weekends and holidays. The business office is open Monday through Friday, closed on Saturday nc Sunday lising tho dat a