help me answer q1, 2, 3, 4, 5

the audit of Alpine Cupcakes, Inc. tasks Audit Case Assignments ng standards ble Introduction: Data Analytics Module e accounts In the Data Analytics Module, instead of reviewing and evaluating audit workpapers as you have done in nesses for prior modules, you will do the audit work yourself. Now, you are responsible for finding red flags in the data m identified rather than looking for errors in another auditor's work. You need to pause after reading each question and use your critical thinking skills to plan an approach to answering the question. R.2.1 to The purpose of this module is to provide "hands on" experience and practice in using analytics to complete letail the common audit engagement tasks. You will download Excel files that hold a vast amount of company data. ttention As you work through the questions, you will not only be exposed to using data analytics to answer common 55 and 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 year 20X2 is 2022. you to perform data analysis using Excel files, we assume the casebook year 20X1 is 2021 and the casebook lem and Learning Objectives: ion? 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: valuate 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 n and tools . 5. Perform substantive audit procedures to evaluate the cash processes and cash account transactions n? using analytical tools. 6. Perform substantive audit procedures to evaluate accounts receivable transactions and account ster. balances using analytical tools. Data Analytics Assignments: off 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.of this module is to have you s. Through reviewing these documents, you wi auditors. Descry y to assess the auditors' performance of the required proced Determine if the ratio a Alpine Cupcakes' overall financial statement risk and the account lev b. Review the explanations of rat apers. The audit workpapers also include audit documentation on the auditors' mid s and overall risk assessment of inherent risk, control risk, and detection risk. Through this u will gain a better understanding of the auditors' considerations of client risk and how this Q6. Prepare a memo to document your understanding of assessment of Garcia and Foster's audit risk. Describe the specific you identify. you believe these factors relate to the Alpine Cupcakes audit, and likely to be affected by these risks. Describe how the audit team For example, the background information mentions that sugar price t affects the audit . .- be to enable you to understand the auditors' audit planning and risk assessment present to Alpine Cupcakes? Which accounts would be affected by address these risks in the audit plan? Cash Module bjectives: of their understanding of the client's environment, including its the module, you will be able to: insive coverage of the cash account derstanding of imp ridge Business Publishers Icsolutions. module inclu rformed by: M 10/1/20X2 Cambridge Business Publishers viewed by: 10/9/20X2 Alpine Cupcakes, Inc. Performed by: Preliminary Analytical Procedures-Quarterly Income Statements SDM 10/1/20X2 Audit Year December 31, 20X2 Reviewed by: TKJ 10/9/20X2 % Change B.1.2 Months 3 Months Ended Ended -3.12% 03/31/20X2 03/31/20X1 $ Change % Change -11.77% Expenses (continued) -0.01% Car Maintenance and Fuel Expense 995.38 1,026.78 (31.40) -3.06% 3.23% Repair Expense 372.50 381.25 (8.75) -2.30% 3.20% Water Expense 378.75 373.75 5.00 1.34% -3.78% Soda Machine Repair and CO2 Expense 1,156.00 1,128.00 28.00 2.48% 2.69% Credit Card Expense 878.31 897.89 (19.58) -2.18% Cooking Supplies Expense 12,704.00 11,858.00 846.00 7.13% 36.92% Banking Fees 445.00 445.00 0.00 0.00% Selling and Administrative Expenses 288,978.68 298,028.45 Depreciation Expense: Equipment 3,627.00 4,152.00 (525.00) -12.64% Depreciation Expense: Plant & Property 3,300.00 3,300.00 0.00 0.00% 0.49% Total Depreciation Expense 6,927.00 7,452.00 Total Expenses 295,905.68 305,480.45 1.59% 63% 2 Earnings Before Income tax 62,493.45 54,963.92 00% Income Tax Expense 21,247.78 18,683.66 2,564.12 13.72% 78% Net Income $83,741.23 $73,647.58 32% 13% Auditor Notes: 7% - The fluctuation is less than TM ($13,700) and less than a 10% change. F -Footed 1 - The Company has seen decreases in both their corporate and storefront sales. The Company has experienced a decrease in birthday party and holiday orders leading to the decline in storefront sales. Most customers order these items a week before the event and pick them up in the store. The decrease in corporate accounts revenue is primarily due to two customers, Luigi's Bistro and Steinberg Delis. The Company expects corporate sales to pick up through the rest of the year. 2 - We discussed the change in the medical insurance expense account with Lindsay Mckenna. Lindsay said the increase in medical insurance expense from prior year is due to accidentally paying $3,000 more on each check that was written to Blue Cross Blue Shield in Q1 of 20X1. The Company received a refund of $9,000 from the medical insurance company in April 20X1 due to the mistakes in payments. Garcia and Foster Audit Workpaper B.3.2: pg 49Risk Assessment Module find and pro t module focuses on several important assessment procedures. One of the discover with the memo, module is to have you review and gain a better understanding of preliminary analytic Q5. Evaluate the preliminary analyt to B.3.3.) This question relates to Step gh reviewing these documents, you will see real world audit workpapers and have the . Determine if the analyses of account flu (workpapers B.3.1 and B.3.2). Review the explan ess the auditors' performance of the required procedures. In addition, you will be able auditors. Describe any problems you identify. Cupcakes' overall financial statement risk and the account level risks as you review petermine if the ratio analysis (workpaper B.3.5) was appropriately performed and er he audit workpapers also include audit documentation on the auditors' materiality Review the explanations of ratio fluctuations provided by the auditors Describe any of verall risk assessment of inherent risk, control risk, and detection risk. Through this gain a better understanding of the auditors' considerations of client risk and how this 06. Prepare a memo to document your understanding online Cupcakes fro. you isment of Garcia and Foster's audit risk. Describe the specific riks for Alpine Curran you identify . you believe these factors relate to the Alpine Cupcakes audit, and idente Alpi toely to be affected by these risks. Describe how the quora teams should address magill the audit. For example, the background information mentions that cage prices may rise, Vest -dula is to enable you to understand the auditors' audit planning and risk assessment present to Alpine Cupcakes? Which accounts would be affected by this risk? How es. of their understanding of the client's environment, including its the module, you will be able to: address these risks in the audit plan ? Cash Module iness Publishers "ansive coverage of the cash account, along with the Imidations. understanding of important procedures undule includes consideration by: 20X2 Cambridge Business Publishers by: /20X2 Alpine Cupcakes, Inc. Performed by: SDM 9/15 / 20X 2 PBC Income Statements for the 3 Months Ended 3/31/20X2 and 3/31/20X1 (USD $) Audit Year December 31, 20X2 Reviewed by: inded TKJ 9/20/20X2 X1 3 50.56 B.3.2 1.50 3 Months Ended 3 Months Ended 2.06 Earnings Before Income Taxes 03/31/20X2 03/31/20X1 .64 Income Tax Expense 62,493.45 54,963.92 .55 Net Income 21,247.78 18,683.66 50 $41,245.67 $36,280.26 69 Earnings per Share $0.82 $0.73 Client Supporting Document B.1.2. pg 2 of 2 43control option command Alpine Cupcakes, Inc. Audit Case Assignments b. 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. 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? Q3. Analyze the risk associated with the cost of ingredients. Determine whether a possible change in the price of sugar is the largest risk associated with the cost of sales for cupcakes. As in Question 2, assume all sales of cupcakes remained the same, and the sales price remains the same. Use the number of cupcakes sold in 2022. a. Pick 3 ingredients 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. c. How do you assess the overall risk in the rise in prices of ingredients to Alpine Cupcakes' future? Q4. Analyze the risk associated with customers. Use sales data from 2022. Create a visualization (i.e., chart, graph, table or some other visualization) showing the risk 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. a. Create a visualization (i.e., chart, graph, table or some other visualization) showing the risk 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 Alpi 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 you ment, you will examRisk Assessment M Introduction: The risk assessment module focuses on several important assessment procedu objectives of this module is to have you review and gain a better understanding procedures. Through reviewing these documents, you will see real world audit opportunity to assess the auditors' performance of the required procedures. to consider Alpine Cupcakes' overall financial statement risk and the account the workpapers. The audit workpapers also include audit documentation on calculations and overall risk assessment of inherent risk, control risk, and module, you will gain a better understanding of the auditors' consideration assessment affects the audit. Learning Objectives: The meal of this module is in enable you to understand the auditors' au odule, you will be able to: Cambridge Business Publishers Seir understanding of the Performed by: SDM 10/1/20X2 Alpine Cupcakes, Inc. Reviewed by: Cambridge Busin Preliminary Analytical Procedures-Balance Sheets Audit Year December 31, 20X2 TKJ 10/9/20X2 B. 1.1 As of As of 12/31/20X1 $ Change % Change 3/31/20X2 1 - We discuss (8,636.39) Accountin Current Assets $125,498.76 $135,135.15 -6.39% 1 disbursem Cash: Storefront 293,728.03 210,019.06 84,708.97 40.33% 1 account t Cash: Corporate Accounts 123,432.43 124,726.15 (293.72) -0.24% v cash bala Cash: Payroll 122,849.12 193,976.31 (70,127.19) -36.15% 2 next year Accounts Receivable 2,379.00 2,604.00 775.00 29.76% v some de Office Supplies 4,777.00 4,713.00 1,064.00 22.58% 2 - We disc 610.57 noted t Cooking Supplies 25,580.09 2.39% reducin Inventory: Ingredients 25,190.66 Inventory: Cake Boxes and Cupcake Cups 1,423.05 1,190.10 1,232.95 103.60% addition Inventory: Beverages 3 , 340.30 3,260.80 1,079.50 33.11% does Total Current Assets $702,618.35 $701,204.66 3 - We dis slight Long-Term Assets Mou Equipment 150,180.00 150,180.00 0.00 0.00% previ Accumulated Depreciation: Equipment (82, 455.00) (78,828.00) 3,627.00 4.60% prod 330,000.00 330,000.00 this Plant & Property 0.00 0.00% in t Accumulated Depreciation: Plant & Proprty (82,500.00) (79,200.00) 3,300.00 4.17% 4 -Pe Land 125,000.00 125,000.00 0.00 0.00% Total Long-Term assets 440,225.00 $447,152.00 Total Assets $1,142,843.35 $1,148,356.66 Liabilities Accounts Payable $25,712.00 38,556.70 (12,844.70) Wage Taxes Payable 0.00 -33.31% 3 0.00 0.00 Corporate Income Tax Payable 21,247.78 0.00% 38,125.96 Dividends Payable (16,878.18) 0.0 13,125.00 -44.27% 4 Mortgage Payable (13,125.00) 290,673.81 292,262.13 -100.00% 5 Notes Payable: Vehicles 22,555.53 (1,588.32) -0.54% Total Liabilities 24,878.31 360,189.12 (2,322.78) -9.34% Stockholders' Equity $406,948.10 Common Stock, Par value $1.00; Authorized 1,000,000; Issued and outstanding 50,000 shares Additional Paid in Capital 50,000.00 50,000.00 0.00 0.00% Retained Earnings 120,075.91 120,075.91 0.00 0.00% Total Stockholders' Equity 612,578.32 571,332.65 41,245.67 Total Liabilities and Stockholders' Equity 782,654.23 $741,408.56 7.22% $1,142,843.35 $1,148,356.66 F - Footed V . The fluctuation is less than TM ($13,700) and less than a 10% change. Garcia and Foster Audit Workpaper 46 B.3.1: pg. 1 of 2Alpine Cupcakes' overall financial st ers. The audit workpapers also include audit docu you identify. and overall risk assessment of inherent risk, control risk, and de will gain a better understanding of the auditors' considerations of client Q6. Prepare a memo to document assessment of Garcia and Foster's a you believe these factors relate to the Alpin likely to be affected by these risks, Describe how the For example, the background information mentions that My affects the audit. able you to understand the auditors' audit planning and risk assessment present to Alpine Cupcakes? Which accounts would be affecton jectives: of their understanding of the client's environment, including its address these risks in the audit plan? Cash Module module, you will be able to: "elve coverage of the cash accout waterstanding of imp undule incl rformed by: landations. M 10/1/20X2 viewed by: Cambridge Business Publishers 7 10/9/20X2 Alpine Cupcakes, Inc. Performed by: Preliminary Analytical Procedures-Ratio Analysis SDM 10/1/20X2 Audit Year December 31, 20X2 Reviewed by TKJ 10/9/20X2 Auditor Notes (continued) Ratio Calculations Short-term (ST) Liquidity Ratios: Ability to Meet ST Obligations Current Ratio = Current Assets + Current Liabilities Quick Ratio = (Current Assets - Inventories) + Current Liabilities Activity Ratios: How Effectively Assets Are Managed Receivables Turnover = Credit Sales + Receivables Days Receivables Outstanding = 365 + Receivables Turnover Inventory Turnover = COGS = Inventory Days Inventory on Hand = 365 + Inventory Turnover Profitability Ratios Gross Profit Percentage = (Sales - COGS) + Sales Profit Margin = Net Income : Net Sales Return on Assets = Net Income + Total Assets Return on Equity = Net Income + Total Stockholder's Equity Max Coverage Ratio: Long-Term Solvency (Ability of Entity to Continue as a Going Concern) 13.76 Debt to Assets = (ST Debt + LT Debt) + Total Assets 12.45 Debt to Equity = (ST Debt + LT Debt) + Stockholders' Equity 4.41 1- The current ratio and quick ratios increased significantly primarily due to an increase in 3.76 corporate cash of 236,658 (293,728 - 57,070) [B.1.1] from 3/31/20X1 to 3/31/20X2. As noted in our balance sheet analysis (B.2.1), Miguel Lopez says cash fluctuates drastically depending on 4.15 the timing of cash receipts and purchases. The company has not made any major purchases in 0.71 the past year, but plans to make some purchases in 20x3. In addition, the current liabilities in .48 20x2 have decreased from 3/31/20X1 primarily due to the timing of paying accounts payable balances and the timing of purchases. 02 29 2- The Company is collecting its receivables more quickly than it has in prior years. Per our discussion with Lisa Thomas, the Company is taking additional steps to have customers pay in a more timely manner by improving customer relationships and calling customers weekly when the customers have past due balances. 3 - The Company's profit margin has increased since prior year due to increases in storefront sales and decreases in the Company's selling and administrative expenses. Per discussion with Miguel Lopez, the Company has seen great growth and has performed well in relation to the industry competitors, largely because the Company has built its reputation for quality and has maintained good relationships with vendors to keep costs down. 4 - The company's ROE is significantly lower than prior year. We have requested a time to meet with Miguel Lopez to discuss the difference in this ratio in comparison to our expectations. 5 - The company's debt to assets and debt to equity ratios have decreased from prior year due to the decrease in the long-term liabilities, along with the decreases in current accounts payable (due to timing of purchases and payments of liabilities). Garcia and Foster Audit Workpaper B.3.3: 51The risk objectives of this me procedures. Through reviewing the opportunity to assess the auditors' performan to consider Alpine Cupcakes' overall financial statement ri the workpapers. The audit workpapers also include audit document calculations and overall risk assessment of inherent risk, control risk, an module, you will gain a better understanding of the auditors' considerat assessment affects the audit. The goal of this module is to enable you to understand the auditors' Learning Objectives: - module, you will be able to their understanding of t Cambridge Business Publishers Performed by: SDM 10/1/20X2 Cambridge B Alpine Cupcakes, Inc. Reviewed by: Preliminary Analytical Procedures-Quarterly Income Statements TKJ 10/9/20X2 Audit Year December 31, 20X2 B.1.2 3 Months 3 Months Ended Ended 03/31/20X2 03/31/20X1 $ Change % Change $353,739.57 $343,050.56 (10,689.01) -3.12% Revenue Sales Revenue: Corporate Accounts 80,649.00 91,411.50 (10,762.50) -11.77% Expens Sales Revenues: Storefront 434,388.57 434,462.06 (73.49) -0.01% Car Total Sales Revenue 66,736.39 64,645.64 2,090.75 3.23% Rep Cost of Goods Sold: Ingredients 3,875.55 3, 755.55 120.00 3.20% Wa Cost of Goods Sold: Boxes and Cupcake Cups 5,466.50 5,681.50 (215.00) -3.78% So Cost of Goods Sold: Beverages 74,082.69 1,995.75 Total COGS 76,078.44 2.69% Gross Profit 358,310.13 360,379.37 Interest Revenue 89.00 65.00 24.00 36.92% Gross Profit Plus Interest Revenue 358,399.13 360,444.37 Sell Expenses Wage Expense 216,719.00 217,791.00 (1,072.00) -0.49% Wage Tax Expense 17,971.24 18,077.94 (106.70) -0.59% Medical Insurance Expense 8,100.00 17,100.00 (9,000.00) -52.63% 2 Auto Insurance Expense 1,035.00 1,035.00 0.00 0.00% Interest Expense 3,996.90 4,197.69 (200.79) -4.78% Electrical & Gas Service Expense 1,523.20 1,708.00 (184.80) Liability Insurance Expense -10.82% 3,768.40 3,693.60 74.80 Telecommunications Expense 2.03% 472.50 462.00 Cell Phone Service Expense 10.50 912.00 2.27% 933.00 Postage Expense (21.00) 139.50 -2.25% Professional Services Expense 135.90 3.60 Maintenance Expense 2,070.00 2.65% 2,122.50 Office Supplies Expense 1,212.00 (52.50) -2.47% 1,132.00 80.00 Dry Cleaning Expense 8,050.00 7,342.00 7.07% 708.00 398.75 416.15 9.64% Storefront Paper Supplies Expense (17.40) Rental Expense 778.25 -4.18% 869.00 4,752.00 (90.75) 4,752.00 -10.44% Waste Services Expense 150.00 0.00 150.00 0.00% 0.00 0.00% Garcia and Foster Audit Workpaperopportunity to as to consider Alpine Cupcakes the workpapers. The audit workpapers als calculations and overall risk assessment of inherent fish module, you will gain a better understanding of the auditors' con assessment affects the audit. adule, you will be able The anal of this module is to enable you to understand the au Learning Objectives: heir understandin Cambridge Business Publishers Performed by: SDM 10/1/20X2 Cambri Alpine Cupcakes, Inc. Reviewed by: Preliminary Analytical Procedures-Ratio Analysis TKJ 10/9/20X2 Audit Year December 31, 20X2 Company Ratios 3/31/20X2 3/31/20X1 % Change 11.360 7.094 60.1% 1, H Current Ratio * * 10.875 6.696 52.4% 1, H Quick Ratio * * 2.150 33.9% 2, H 2.879 126.760 169.745 -25.3% Receivables Turnover Days Outstanding in Receivables 2.540 2.506 1.4% V, Inventory Turnover 143.710 145.654 -1.3% V, H Days of Inventory on Hand 4.710 4.865 -3.2% V, u Gross Profit Percentage 0.095 0.084 13.1% 3, H Profit Margin 0.036 0.036 0.0% V, H Return on Assets Return on Equity 0.053 0.060 -11.7% 4, H Debt to Assets 0.274 0.330 -17.0% 5, H Debt to Equity 0.400 0.540 -25.9% 5, H Industry Ratios March 20X2 March 20X1 Avg Min Max Avg Min Max Current Ratio 5.55 1.05 15.30 5.70 2.02 13.76 Quick Ratio 4.50 0.77 14.00 4.54 1.06 12.45 Receivables Turnover 2.97 1.28 5.68 2.66 1.09 4.41 Days Outstanding in Receivables 150.08 64.30 285.43 159.54 82.83 Inventory Turnover 333.76 1.62 0.24 3.95 1.51 Days of Inventory on Hand 0.20 441.54 4.15 92.29 1525.44 Gross Profit Percentage 458.39 0.31 87.88 0.01 1830.71 Profit Margin 0.49 0.33 0.02 0.10 0.10 0.48 Return on Assets -0.09 0.11 0.01 -0.09 0.04 1.02 Return on Equity -0.03 0.04 0.04 -0.04 -0.02 Debt to Assets 0.23 0.29 0.08 Debt to Equity 0.21 0.00 - 0.16 0.74 0.67 0.26 0.19 -0.05 0.00 1.76 0.58 0.55 Auditor Notes: 0.00 3.99 ** Calculation includes current portion of mortgage payable and notes payable based on client's amortization schedule. On 3/31/20X2, the current portions of the mortgage and notes payables are $6,555.19 and $6,236.12 and $9,103.75, respectively. $8,337.88, respectively. On 3/31/20X1, the current portions of the mortgage and notes payables are V - Fluctuation meets expectations of being less than a 10% change. M - The 20X2 ratio is within the expected range (between the min and max) of the industry data. Garcia and Foster Audit WorkpaperThe risk assessm objectives of this module is to have yo procedures. Through reviewing these documents, you opportunity to assess the auditors' performance of the required p to consider Alpine Cupcakes' overall financial statement risk and the acco the workpapers. The audit workpapers also include audit documentation calculations and overall risk assessment of inherent risk, control risk, an module, you will gain a better understanding of the auditors' considera assessment affects the audit. Learning Objectives: cable you to understand the auditors ... . dule, you will be able to Cambridge Business Publishers eir understanding of Performed by SDM 9/15/20X2 Cambridge Bu Alpine Cupcakes, Inc. Income Statements for the 3 Months Ended 3/31/20X2 and 3/31/20X1 (USD >) | Reviewed by: TKJ 9/20/20X2 Audit Year December 31, 20X2 Inc PBC B.3.2 PBC 3 Months Ended 3 Months Ended 03/31/20X2 03/31/20X1 $353,739.57 $343,050.56 Revenue 80,649.00 91,411.50 Sales Revenue: Corporate Accounts $434,388.57 $434,462.06 Sales Revenues: Storefront Ear 64,645.64 Total Sales Revenue 66,736.39 Cost of Goods Sold: Ingredients 3,875.55 3,755.55 Cost of Goods Sold: Boxes and Cupcake Cups 5 ,466.50 5,681.50 Cost of Goods Sold: Beverages $76,078.44 $74,082.69 Total COGS $358,310.13 $360,379.37 Gross Profit 89.00 65.00 Interest Revenue $358,399.13 $360,444.37 Gross Profit Plus Interest Revenue Expenses 216,719.00 217,791.00 Wage Expense 17,971.24 18,077.94 Wage Tax Expense Medical Insurance Expense 8,100.00 17,100.00 Auto Insurance Expense 1,035.00 1,035.00 Interest Expense 3,996.90 4,197.69 Electrical & Gas Service Expense 1,523.20 1,708.00 Liability Insurance Expense 3,768.40 3, 693.60 Telecommunications Expense 472.50 462.00 Cell Phone Service Expense 912.00 933.00 Postage Expense 139.50 135.90 Professional Services Expense 2,070.00 2,122.50 Maintenance Expense 1,212.00 1,132.00 Office Supplies Expense 8,050.00 7,342.00 Dry Cleaning Expense 398.75 416.15 Storefront Paper Supplies Expense 778.25 869.00 Rental Expense 4,752.00 Waste Services Expense 4,752.00 150.00 Car Maintenance and Fuel Expense 150.00 Repair Expense 995.38 1,026.78 Water Expense 372.50 381.25 Soda Machine Repair and CO2 Expense 378.75 373.75 Credit Card Expense 1,156.00 1,128.00 Cooking Supplies Expense 878.31 897.89 Banking Fees 12,704.00 11,858.00 Selling and Administrative Expenses 445.00 445.00 Depreciation Expense: Equipment $288,978.68 $298,028.45 Depreciation Expense: Plant & Property 3,627.00 4,152.00 Total Depreciation Expense 3,300.00 Total Expenses $6,927.00 3,300.00 $295,905.68 $7,452.00 Client Supporting Document $305,480.45 42 B.1.2: pg. 1 of 2essment module focuses on several important as f this module is to have you review and gain a better under to B.3.3.) Determine if the Through reviewing these documents, you will see real world audit work (workpapers B.3.1 and B to assess the auditors' performance of the required procedures. In addition, you w auditors. Describe any problems Alpine Cupcakes' overall financial statement risk and the account level risks as you review b. Determine if the ratio analysis (workpap pers. The audit workpapers also include audit documentation on the auditors' materiality Review the explanations of ratio fluctuations and overall risk assessment of inherent risk, control risk, and detection risk. Through this aspare a memo to document your understandthe the colleges environment u will gain a better understanding of the auditors' considerations of client risk and how this you isment of Garcia and Foster's audit risk. Describe the specmic risks for Alpine you identify. You believe these factors relate to the Alpine Cupcakes aller, and identify which , likely to be affected by these risks. Describe how the con reams should addem M For example, the background information mentions that calder prices may ness affects the audit. bjectives: dude is to enable you to understand the auditors' audit planning and risk assessment present to Alpine Cupcakes? Which accounts would be affected by this risk? u Cash Module on of their understanding of the client's environment, including its . he module, you will be able to: address these risks in the audit plan? "ive coverage of the cash account, along with -lerstanding of important proced module includes considera Cambridge Business Publishers Alpine Cupcakes, Inc. Performed by: SDM 9/15/20X2 PBC Balance Sheets as of 3/31/20X2 and 12/31/20X1 (USD $) Audit Year December 31, 20X2 Reviewed by: TKJ 9/20/20X2 B.3.1 Balance Sheet: As of As of Current Assets 3/31/20X2 12/31/20X1 As of 3/31/20X1 Cash: Storefront Cash: Corporate Accounts $125,498.76 $135,135.15 Cash: Payroll 293,728.03 $151,293.51 210,019.06 57,069.68 Accounts Receivable 123,432.43 124,726.15 122,849.12 193,976.31 122,931.81 Office Supplies 159,537.02 Cooking Supplies 2,379.00 2,604.00 2,133.00 Inventory: Ingredients 4,777.00 4,713.00 3,808.00 Inventory: Cake Boxes and Cupcake Cups 25,190.66 25,580.09 26,779.44 Inventory: Beverages 1,423.05 1,190.10 434.95 Total Current Assets 3 ,340.30 3,260.80 2,348.50 Long-term Assets $702,618.35 $701,204.66 $526,335.91 Equipment Accumulated Depreciation: Equipment 150,180.00 150,180.00 150,180.00 Plant & Property (82, 455.00) (78,828.00) (66,372.00) 330,000.00 Accumulated Depreciation: Plant & Property 330,000.00 330,000.00 Land (82,500.00) (79,200.00) (69,300.00) 125,000.00 125,000.00 Total Long-term assets 125,000.00 $440,225.00 $447,152.00 Total Assets $469,508.00 $1,142,843.35 $1,148,356.66 $995,843.91 Liabilities Accounts Payable 25,712.00 38,556.70 40,168.65 Wage Taxes Payable 0.00 0.00 0.00 Corporate Income Tax Payable 21,247.78 38,125.96 18,683.66 Dividends Payable 0.00 13,125.00 0.00 Mortgage Payable(a) 290, 673.81 292,262.13 296,909.93 Notes Payable: Vehicles(b) 22,555.53 24,878.31 31,659.28 Total Liabilities $360,189.12 $406,948.10 $387,421.52 Stockholders' Equity Common Stock, Par value $1.00; Authorized 1,000,000 shares; Issued and outstanding 50,000 shares 50,000.00 50,000.00 50,000.00 Additional Paid in Capital 120,075.91 120,075.91 120,075.91 Retained Earnings 612,578.32 571,332.65 438,346.48 Total Stockholders' Equity $782,654.23 $741,408.56 B.2.1 $608,422.39 Total Liabilities and Stockholders' Equity $1,142,843.35 $1,148,356.66 $995,843.91 (Current portion $6,555.19 at 3/31/20X2 and $6,236.12 at 3/31/20X1 ()Current portion $8,337.88 at 3/31/20X2 and $9,103.75 at 3/31/20X1 Client Supporting Document B.1.1: pg. 1 of 1 41AutoSave OFF DA2 Risk_For Students(2) (1) Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) v 14 A A Wrap Text v General Insert v Ex Delete v Ex AP- O. Paste BIUV VV Av E Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Ideas Formatting as Table Styles Format v X v Sensitivity Filter Select A1 X V fx Risk Assessment : Question 2 D E F G H 1 J K L M N 0 P Q R S U W Risk Assessment : Question 2 2 Analyze the risk associated with the cost of ingredients 3 a) Create a visualization to show how a 25% and 50% increase in the cost of sugar would affect the price of each cupcake. b) If the number of sales of cupcakes and their sales price ($2.4/cupcake) 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). For this exercise, use the number of cupcakes sold in 2022. Also, assume we are looking at only the cost of ingredients when computing the gross margin and gross margin percent. c) Is there a price point past which you believe an increase in the price of sugar would become a risk to Alpine Cupcakes? Show evidence to support your 5 conclusion. The example below shows one way you might do this. Look at the formulas in the dark blue boxes, by changing the number in the green box of the "Price Change Key," it changes the price of sugar in the visualization box as well as the price of the cupcake. Try it. In the green box, put 6 .25 for a 25% increase in the price of sugar. Hint: Often when doing analysis in excel, you need to set up a visualization where you can change a number in a "key" to see an effect elsewhere on a 7 sheet. This exercise gives you an opportunity to practice this skill by referencing numbers in a different cell. 8 Total w/price Orig. 9 Vanilla Vanilla Cupcakes Butter Cream Frosting change Price Difference Price change Key baking shortenin confec change (50% 10 Ingredient eggs sugar vanilla flour powder butter vanilla butter Sugar milk Item Price increase =.5) New Price 11 Unit measured as dozen pounds pounds quart pounds pounds pounds quart pounds pounds pounds pounds gallons sugar $0.50 $0.50 2 Amount of unit used per 36 cupcakes 0.7500 0.9923 0.0049 0.0150 1.2375 0.0195 1.0000 0.0225 0.0049 0.7500 0.6780 3.9600 0.0473 9.4818 9.4818 0.0000 Confect. Sugar $0.56 $0.56 13 price/unit $1.50 $0.50 $0.20 $40.00 $0.30 $3.00 $2.00 $40.0 $0.20 $2.00 0.55 $0.56 $3.00 $93.8 93.81 0.000 14 Ingredient cost per /36 cupcakes $1.13 $0.50 $0.00 $0.60 $0.37 0.06 2.0 $0.90 0.00 $1.50 $0.37 $2.22 $0.14 $9.79 $9.79 0.0049 15 Cost / cupcake $0.03 $0.01 $0.00 $0.021 $0.01 $0.00 $0.06 $0.03 $0.00 $0.04 $0.01 $0.06 $0.00 $0.27 $0.27 $0.00 16 7 a. increase 50% increase 25% vanilla cupcake in sugar in crease from 18 $0.01 to $0.03 incease from $0.01 to $0.02 butter cream frosting in confec Sugar 19 increase from $0.06 to $0.12 INCREASE from $0.06 to $0.09 20 21 22 23 24 25 26 27 Index for Worksheet Recipes Ingredient price list Customer List Sales Orders Vendor List Risk Assess Q1 Risk Assess Q2 Risk Assess Q3 Risk Assess Q4 Risk Assess Q5 + Average: 5.031997763 Count: 126 Sum: 342.1758479 + 100%AutoSave O OFF 6 . DA2 Risk For Students(2) (1) Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) v 11 A Wrap Text v Currency Insert v Ex Delete v Ex AP- O. Paste BIUV V M Av E Merge & Center v $ ~ % " Conditional Format Cell Sort & Formatting as Table Styles Format X v Filter Find & Ideas Sensitivity Select G13 4 X V fx =(ROUND(G12/36,2)) B C D E F G H I K L M N O P Q R 1 Risk Assessment: Question 1 2 Analyze the risk associated with the cost of ingredients 3 Using the data provided, determine the cost of each type of cupcake Presentation matters when showing the results of data analytics, because you are trying to take many numbers and present them in a way that someone unfamiliar with your computations can quickly understand your results. An example of one type of presentation for the cost of cupcakes is shown below. You can use this example as a template, or come up with a better way to present your work. If you click on the yellow cells below, you can see the formulas used in this particular visualization. It can also serve as a check to ensure that you are arriving at the correct cost when you create your cost analysis. 5 6 Hint: There are many ways to set up a visualization, pick a way that makes sense for you. You can copy and paste the recipes or make reference in the cells below to the original numbers on the data pages. 7 Vanilla Vanilla Cupcakes Butter Cream Frosting Total baking confec 8 Ingredient egg sugar sal vanilla flour powder butter vanilla butter shortening Sugar sal milk 9 Unit measured as dozen pounds pounds quart pounds pounds pounds quart pounds pounds pounds pounds gallons 10 Amount of unit used per 36 cupcakes 0.7500 0.9923 0.0049 0.0150 1.2375 0.0195 1.0000 .0225 0.750 0.6780 3.9600 0.0049 0.0473 11 price/unit $1.50 $0.50 $0.20 $40.00 $0.30 $3.00 2.00 $40.0 $2.00 0.55 0.56 $0.20 $3.00 2 Ingredient cost per /36 cupcakes $1.12500 $0.49613 $0.00098 $0.60000 $0.37125 $0.05850 $2.00000 $0.90000 $1.50000 $0.37290 $2.21760 $0.00098 $0.14175 8508 13 Cost / cupcak 0.0 $0.01 $0.00 $0.02 $0.01 $0.00 50.06 $0.03 $0.04 50.01 $0.06 $0.00 $0.00 $0.270 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Index for Worksheet Recipes Ingredient price list Customer List Sales Orders Vendor List Risk Assess Q1 Risk Assess Q2 Risk Assess Q3 Risk Assess Q4 Risk Assess Q5 + + 118%AutoSave O OFF 6 . DA2 Risk For Students(2) (1) Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) v 11 A A Wrap Text v General Insert v Ex Delete v Ex AP- O. Paste BIU V VVAv E Merge & Center v $ ~ % " Conditional Format Cell Format v Sort & X v Filter Find & Ideas Sensitivity Formatting as Table Styles Select D19 X V fx B C D E F G H I J K L M N 0 P Q R S T Risk Assessment: Question 3 2 Analyze the risk associated with the cost of ingredients 3 Determine whether a possible change in the price of sugar is the largest risk associated with the cost of sales for cupcakes. 4 a) Pick 3 ingredients the have the largest effect on costs of cupcakes b) Show the effect of a 25% and 50% increase in the cost of these three ingredients on gross profit and gross profit percent (based only on cost of cupcakes). Assume all 3 go up 25% then 50% at the same time. c) Thought question: How do you assess the overall risk in the rise in prices of ingredients to Alpine Cupcakes future? As in question 2, assume all sales of cupcakes remained the same, and the sales price ($2.40 per cupcake) remains the same. Use the number of cupcakes sold in 2022. 8 Hint: A good place to start is to look back at your answer to the cost per cupcake. Are there any ingredients that appear frequently, that have a high price? 10 11 12 14 15 18 19 20 28 29 30 32 33 34 Index for Worksheet Recipes Ingredient price list Customer List Sales Orders Vendor List Risk Assess Q1 Risk Assess Q2 Risk Assess Q3 Risk Assess Q4 Risk Assess Q5 + + 100%AutoSave OFF DA2 Risk_For Students(2) (1) Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) v 14 A Wrap Text v General Insert v Ex Delete v Ex AP- O. Paste BIU V V V A v E Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Ideas Sensitivity Formatting as Table Styles Format v X v Filter Select A1 X V fx Risk Assessment: Question 4 A B G H 1 J K L M N 0 P Q R S T U V W X Y Z AA Risk Assessment: Question 4 2 Analyze the risk associated with customers a) Create a visualization (i.e., chart, graph, table or some other visualization) showing the risk associated with customers. In otherwords, are there any customers that Alpine is particularly reliant upon so the loss of their business would 3 constitute a risk to the continuation of Alpine's Business? Use Sales data from 2022. 4 b) How would you advise Alpine Cupcakes after seeing your visualization about vulnerabilities associated with customers? 5 Hint: Use data from "Sales Orders" worksheet and the "Customer List." You might need to use VLOOKUP to combine the data. Ask if it is better to use number of cupcakes sold? Dollar amount sold? Some other number? Once you have assembled your data, experiment with visualization techniques, tables? Pivot tables? Charts? Graphs? or some other 6 technique? 10 31 32 Index for Worksheet Recipes Ingredient price list Customer List Sales Orders Vendor List Risk Assess Q1 Risk Assess Q2 Risk Assess Q3 Risk Assess Q4 Risk Assess Q5 + Count: 5 + 100%AutoSave OFF DA2 Risk_For Students(2) (1) Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) v 14 A A Wrap Text v General Insert v Ex Delete v Ex AP - O. Paste BI UV V V A v E Merge & Center v $ ~ % " Conditional Format Cell Formatting as Table Styles Format v X v Sort & Find & Ideas Sensitivity Filter Select A1 X V fx Risk Assessment: Question 5 A G H I J K L M N 0 P Q R S T U V W X Y Risk Assessment: Question 5 2 Analyze the risk associated with suppliers a) Create a visualization (i.e., chart, graph, table or some other visualization showing the risk associated with suppliers of ingredients. In otherwords, who are Alpine's major suppliers? b) Do you feel there is risk associated with Alpine's suppliers? Are there suppliers upon which Alpine might be too reliant. How would you advise Alpine Cupcakes after seeing your visualization about how they should plan their purchases of ingredients going forward? 5 Use data from 2022 6 Hint: You will need to use Vendor List and Ingredient Vendor Invoices for this problem. Again combine with "Vlookup" When 7 your data is prepared, choose whatever visualization seems best for you. 6 00 10 16 28 29 30 34 35 Index for Worksheet Recipes Ingredient price list Customer List Sales Orders Vendor List Risk Assess Q1 Risk Assess Q2 Risk Assess Q3 Risk Assess Q4 Risk Assess Q5 + Count: 6 + 100%