please answer question 1,2,3,4,5

DA2 Risk For Students(2) (1) QSearch Sheet AutoSave FF Review View DComments Home Insert Draw Page Layout Formulas Data Share _ 4 AY Insert vA A General Calibri (Body) wraa Text 14 Deleie A = = Merge & Center Conditional Farmat Cell Find & Select Ideas Sensitivity Pastc % Sprt & -- X Farmat Formetting as Teble Styles Filten fxRisk Assessment Question 2 X A1 T A D E F H O T Risk Assessment: Question 2 2 Analyze the risk associated with the cost of ingredients a) Create a visua lization to show how a 25 % and 50 % increase in the cast 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 4 gross margin and gross margin percent c) Is there a price point past which you belicve an increase in the price of supgar would become a risk to Alpine Cupcakes? Show evidence too support your sconclusion. The example below shows one way you might do this. Look at the formulas in the dark bluc 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. Torar Orig. Difference w/price Vanilla Price change Key change Price Vanilla Cupcakes Butter Cream Frosting shortenin confec. %change (50 % increase-S baking 10 Ingredient 11 Unit measured as 12 Amount of unit used per 36 cupcalas 13 price/unit 14 Ingredient cost per /36 cupcakes 15 Costfcupcake New Price vanilla salt flour butter vanilla salt butten Sugar mil Item Price ees sugar powder pounds counds quart pounds 0.7500 0.9923 0.0049 00150 1.2375 $1.50 $0.50e $1.13 $0.5c $0.00 50.03 $0.01$0.00$0.02 0.01 doaen pounds ounas unds pounds Und $0.50 $0.50 galions 0.047 53.00 sugar 0.00co Confect. Sgar quart pounds 0.0195 9.481B 93.81 3.9500 S0.56 $0.56 1.0000 0.0225 0.0049 0.7500 0.5780 9.4818 $0.20 $40.00 $2.00 $2.00 055 $0.37 $93.81 50.30 $3.00 $40.00 $0.20 $2.00 $0.56 00000 $0.37 $0.05 $0.90 $1.50 $0.14 $9.79 $9.73 0.O049 $0.60 $0.00 $2.22 $0.27 50.04 $0.00 50.00 $0.01 $0.27 $0.06 50.03 $0.00 $0.06 50.00 16 17 a. Increase 50% increase 25 % vanilla cupcake in sugar in crease from 18 s0.01 to $0.03 butter cream frosting in confec. Sugar 19 increase from $0.06 to $0.12 incease from $0.01 to $0.02 INCAFASE from $0.06 to $0.09 20 21 22 23 24 25 26 27 Risk Assess Q3 Index for Worksheet Recipes Ingredient price list Customer List Sales Orders Vendor List Risk Assess Q1 Risk Assess 02 Risk Assess 04 Risk Assess Q5 Average: 5.031997763 Caunt: 126 Sum: 342.1758479 + 100 % DA2 Risk For Students(2) (1) AutoSave FF QSearch Sheet Review View DComments Home Insert Draw Page Layout Formulas Data Share _ ' . - X Insert A A Calibri (Body) wraa Text Currency 11 Delete Merge & Center I A Conditional Farmat Formetting as Teble Styles Cell Find & Select Ideas Sensitivity Peste Sort & Farmat Filten X fx(ROUND(G12/36,2) G13 A B C. D E E G K P Q. 1 Risk Assessment: Question 1 Analyze the risk associated with the cost of ingredients Using the data provided, determine the cost of each type of cupcake 2 3 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 4 visualization. It can also serve as a check to ensure that you are arriving at the correct cost when you create your cost analysis 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 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 Total Butter Cream Frosting baking confec 8Ingredient salt vanilla flour powder butter vanilla butter shortening pounds salt milk Sugar eges sugar 9Unit measured as dozen ounds gounds pounds pounds 10000 paunds pounds gallons 0,0473 auart pounds quart pounds 0,0195 10 Amount of unit used per 36 aupcakes 0.9923 0.7500 0.0049 0,0150 1.2375 0.0225 0.7500 0.6780 3.9600 0.0049 $3.00 11 price/unit 12 Ingredient cost per /36 cupcakes $1.50 $0.50 $0.20 $40.00 $3.00 $2.00 $40.00 $2.00 $0.30 0.55 $0.56 $0.20 $0.37290 $2.21760 0.00098 $0.14175$9.78508 $0.60000 $0.37125 50.05850 $1.12500 $0.49613 $2.00000 $0.90000 $0.00098 $1.50000 13 Cost /cupcake s0.00 $0.270 $0.02 $0.00 SD.00 $0.03 $0.01 $0.01 S0.06 $0.03 $0.04 $0.01 S0.06 $D.00 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Risk Assess Q2 Risk Assess Q3 Risk Assess Q4 Risk Assess Q5 Vendor List Index for Worksheet Recipes Ingredient price list Customer List Sales Orders Risk Assess Q1 118% DA2 Risk For Students(2) (1) QSearch Sheet AutoSave FF Formulas Review View DComments Home Insert Draw Page Layout Data Share - Insert Calibri (Body) vA A wraa Text 11 General Delete Merge & Center B IU A Conditional Farmat Cell Find & Select Idcas Sensitivity Peste Sort & 1-- X Format Formetting as Teble Styles Filten X D19 L C P F G H J N C s T 1 Risk Assessment: Question 3 Analyze the risk associated with the cost of ingredients 2 3Determine 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 s only on cost of cupcakes). Assume all 3 go up 25 % then 50 % at the same time cl 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 Hint: Agcod 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 13 14 15 16 17 18 19 20 21 22 23 28 25 26 27 28 25 30 31 32 33 34 Risk Assess Q4 Risk Assess Q2 Index for Worksheet Recipes Ingredient price list Customer List Sales Orders Vendor List Risk Assess Q1 Risk Assess Q3 Risk Assess Q5 + + 100 % DA2 Risk For Students(2) (1) AutoSave FF QSearch Sheet Review View DComments Home Insert Draw Page Layout Formulas Data Share _ 4 Insert vA A General Calibri (Body) wraa Text 14 Delete A Merge & Center Conditional Farmat Formetting as Teble Styles Cell Find & Select Ideas Sensitivity Pastc % Sprt & X Format Filten fx X Risk Assessment: Question 4 A1 T A G H N s T AA 1Risk Assessment: Question 4 2Analyze the risk associated with customers a) Create a visualization (i.., 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 3constitute 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 a bout vul ne rabilities associated with customers? 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 a mount sold? Some other number? Once you have assembled your data, experiment with visualization techniques, tables? Pivot tables? Charts? Graphs? or some other 6technique? 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 78 29 30 31 32 Risk Assess Q1 Risk Assess Q3 Sales Orders Index for Worksheet Recipes Ingredient price list Customer List Vendor List Risk Assess Q2 Risk Assess 04 Risk Assess Q5 Count: 5 + 100% DA2 Risk For Students (2) (1) AutoSave FF QSearch Sheet Review View DComments Home Insert Draw Page Layout Formulas Data Share _ Insert vA A General Calibri (Body) wraa Text 14 Delete A Merge & Center Conditional Farmat Formetting as Teble Styles Cell Find & Select Ideas Sensitivity Pastc % Sort & Format Filten fx X Risk Assessment: Question 5 Al C H J M N o T 7 1Risk 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 3ingredients. In otherwords, who are Alpine's major suppliers? b) Do you feel the re 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 visua lization about how they should plan their purchases of ingredients 4 gcing forward? 5Use data from 2022 Hint: You will need to use Vendor List and Ingredient Vendor I nvoices for this problem. Again combine with "vlookup" When your data is prepared, choose whatever visualization seems best for you. 10 11 12 13 14 15 16 17 18 15 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Risk Assess Q1 Risk Assess Q2 Risk Assess Q3 Risk Assess Q4 Index for Worksheet Recipes Ingredient price list Customer List Sales Orders Vendor List Risk Assess Q5 + Count: 6 100% 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. How do you assess the overall risk in the rise in prices of ingredients to Alpine Cupcakes' future? C Q4. Analyze the risk associated with customers. Use sales data from 2022. a. 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? After seeing your visualization, how would you advise Alpine Cupcakes about vulnerabilities associated with customers? b. 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 Alpin 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 Ansl sessmen of this module is to have you s. Through reviewing these documents, you w ry to assess the auditors' performance of the required proc r Alpine Cupcakes' overall financial statement risk and the account leve apers. The audit workpapers also include audit documentation on the auditors' mla s and overall risk assessment of inherent risk, control risk, and detection risk. Through this Du will gain a better understanding of the auditors considerations of client risk and how this 06. Prepare a memo to document your understanding of a assessment of Garcia and Foster's audit risk. Describe the speciie rs you believe these.factors relate to the Alpine Cupcakes sudit, and de likely to be affected by these risks. Describe how the audit teams d For example, the background information mentions that sugar prices present to Aipine Cupcakes? Which accounts would be affected ba b. Determine if the ratio Review the explanations of rat vou identify auditors. Dese t affects the audit. of their understanding of the client's environment, including its dinns Objectives: to enable you to understand the auditors' audit planning and risk assessment Cash Module nshve coverage of the cash account derstanding of impos ndule incluc the module, you will be able to: address these risks in the audit planr bridge Business Publisher rformed by: M 20/2/20x2 wiewed by: J 10/9/20x2 e Cambridge Business Publishers Alpine Cupcakes, Inc. Preliminary Analytical Procedures-Quarterly Income Statements Audit Year December 31, 20X2 Performed by: SDM 10/1/20x2 Reviewed by: TKJ 10/9/20x2 % Change 3 B.1.2 3 Months 3 Months Ended Ended -3.12 % 03/31/20X2 03/31/20X1 $ Change -11.77% % Change Expenses (continued) -0.01 % Car Maintenance and Fuel Expense 1 995.38 1,026.78 3.23% (31.40) -3.06% Repair Expense 372.50 381.25 (8.75) 3.20% Water Expense -2.30 % 378.75 373.75 -3.78% 5.00 Soda Machine Repair and CO2 Expense 1.34% 1,156.00 1,128.00 28.00 Credit Card Expense 2.69% 2.48% 878.31 897.89 Cooking Supplies Expense (19.58) -2.18 % 12,704.00 11,858.00 846.00 Banking Fees 7.13% 36.92% 445.00 445.00 Selling and Administrative Expenses 0.00 0.00% 288,978.68 298,028.45 Depreciation Expense: Equipment 3,627.00 4,152.00 (525.00) -12.64 % Depreciation Expense: Plant & Property Total Depreciation Expense 3,300.00 3,300.00 0.00 0.00% 0.49% 6,927.00 7,452.00 Total Expenses D.59% 63% 2 295,905.68 305,480.45 Earnings Before Income tax 00% v 62,493.45 54,963.92 Income Tax Expense 21,247.78 78% 18,683.66 2,564.12 Net Income 13.72% $83,741.23 82% $73,647.58 F 13% v Auditor Notes: The fluctuation is less than TM ($13,700) and less than a 10% change. 7% F-Footed % v 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 2 B.3.2: pg. 49 nt modute focuses or several inmportant assessment procedures, One af the module is to have you review and gain a better understanding of prefiminary analytica ugh reviewing these documents, you wili see real world audit workpapers and have the sess the auditors' performance of the required procedures. In addition, you will be able Cupcakes' overall financial statement risk and the account level risks as you review he audit workpapers also include auudit documentation on the auditors' materiality wverall 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 Bisk Assessment Module tind and proe discover with the memic Qs. Evaluate 1he preliminary analyt to B.3.3.) This question relates to Step a. Determine if the analunes of account fluc workpapers B.3.1 and B.3.2). Review the explanaions & auditors. Descritn any problems you identify Review the explanations of ratio fluctuations provided try the auieors mere 06 Prypare a memb to document your understanding of Alpine Cupcakes envieement nd s . Determine if the ratio analysis (workpaper 8.3.3) was approgranely serformed nd o you beleve these factors.nelate to the Alpine Cupcakes audit, and idenety which specfe m kely to be affected by these tisks, Describe how the audit teanms shouid adrens e om For example, the background information mentions that sugar prices may rice WWh rs present to Alpine Cupcakes? Which accounts would be affected by this risk? How he you identty, s the audit. sessnent af Garcia and Foster's audit risk. Describe the specific rs for igime Core res: -Ada is to enable you to understand the auditors audit planning and risk assessment the module, you will be able to: sasens Publishers of their understanding of the client's environment, inciuding its nsve coverage of the cash account, along with the rel nderstanding of important procedures ne dule includes consideration c d by: 20x2 y: /20x2 address these risks tn the audit plant Cash Module ninns Cambridge Business Publishers Performed by: Income Statements for the 3 Months Ended 3/31/20x2 and 3/31/20X1 (USD S) Reviewed by: Alpine Cupcakes, Inc. PBC nded X1 Audit Year December 31, 20x2 SDM 9/15/20x2 TKT 9/20/20x2 50.56 1.50 2.06 5.64 .55 B.3.2 3 Months Ended 3 Months Ended Earnings Before Income Taxes 03/31/20X2 Income Tax Expense 03/31/20X1 62,493.45 Net Income 54,963.92 .50 21,247.78 18.683.66 69 $41,245.67 Earnings per Share $36,280.26 37 o $0.82 $0.73 7 Client Supporting Document B.1.2 pg. 2 of 2 43 The risk assessment rmodule focuses on several important assessment procedurms ebjectives 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. ins to consider Alpine Cupcakes overalt financial statement risk and the acc the workpapers. The audit workpaners also include audit documentattion on calculations and overail risk assessment of inherent risk, control risk, and de module, you will gain a better understanding of thve auditors consideration- assessment affects the audit. Bisk Assessment Madll Introduction The ansl nt thir mde e n anable you to understand the auditors aud dule, vou wil be able to: eir understanding of the c Learning Objectives: Cambridge Business Publishers Performed by: SDM 10/a/20x2 Reviewed by: TKJ 10/9/20x2 eCambridge Busine Preliminary Analytical Procedures-Balance Sheets Audit Year December 31, 20X2 Alpine Cupcakes, Inc. B.1.1 As of As of 3/31/20X2 $ Change % Change 12/31/20X1 1-We discusse Accountine -6.39 % 2 (8,636.39) disbursem $135,135.15 $125,498.76 293,728.03 A0.33% 1 Current Assets 84,708.97 210,019.06 124,726.15 account to cash bala next year Cash: Storefront (293.72) (70,127.19) 775.00 -0.24 % / Cash: Corporate Accounts Cash: Payroli 123,432,43 -36.15 % 2 193,976.31 122,849.12 some del 29.76% Accounts Recelvable Office Supplies Cooking Supplies 2,604.00 2-We disc 2,379.00 22.58 % 1,064.00 4,713.00 noted th reducin additio does n 4,777.00 610.57 25,580.09 1,190.10 2.39% v 25,190.66 1,423.05 Inventory: Ingredients Inventory: Cake Boxes and Cupcake Cups Anventory: Beverages 1,232.95 103.60% 33.11 % v 1,079.50 3,260.80 3,340.30 3-We dis sight $701,204.66 $702,618.35 Total Current Assets Mour Long-Term Assets Equipment Accumulated Depreciation: Equipment Plant & Property Accumulated Depreciation: Plant & Proprty previ prod this 0.00 150,180.00 0.00% v 150,180.00 (78,828.00) 3,627.00 4.60% v (82,455.00) 330,000.00 0.00 0.00% 330,000.00 in t -4,17 % v 3,300.00 (82.500.00) (79,200.00) 125,000.00 4-Per 125,000.00 0.00 0.00% v 20 Land Th 440,225.00 $447,152.00 Total Long-Term assets 5-M $1,142,843.35 $1,148,356.66 Total Assets T Liabilities Accounts Payable $25,712.00 38,556.70 (12,844.70) -33.31% 3 Wage Taxes Payable 0.00 0.00 0.00 0.00% Corporate Income Tax Payable Dividends Payable Mortgage Payable 21,247.78 38,125.96 (16,878.18) 44.27% 4 0.00 13,125.00 (13,125.00) -100.00% 5 290,673.81 292,262.13 (1,588.32) -0.54% Notes Payable: Vehicles 22,555.53 24,878.31 (2,322.78) -9.34% v Total Liabilities 360,189.12 $406,948.10 Stockholders' Equity 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% 120,075.91 120,075.91 Retained Earnings 0.00 0.00% 612,578.32 571,332.65 $741,408.56 $1,142,843.35 $1,148,356.66 Total Stockholders' Equity 41,245.67 7.22% V 782,654.23 Total Liabilities and Stockholders' Equity .The fluctuation is less than TM ($13,700) and less than a 10% change. F-Footed Garcia and Foster Audit Workpaper B.3.1: pg. 1 of 2 46 to assess the auditols Apine Cupcakes' overall financial sta ers. The audit workpapers also include audit docu and overall risk assessment of inherent risk, control risk, and de will gain a better understanding of the auditors considerations of client i 06. Prepare a memo to docume assesiiment of Garcia and Foster's au you believe these factors.relate to the Alpins ikely to be affected by these risks. Describe how th For example, the background information mentions that s present to Alpine Cupcakes? Which accounts would be affected t you identify affects the audit f their understandine of the client's environment, including its tstions jectives nable you to understand the auditors' audit planning and risk assessment Cash Module module, you will be able to: ge Businesa Publishe acddress these risks in the audit pian? derstanding of impo ndule incl rformed by: M 10/1/20x2 wiewed by: 10/9/20X2 coverage of the cash accoun O Cambridge Business Publishers Alpine Cupcakes, Inc. Preliminary Analytical Procedures-Ratio Analysis Audit Year December 31, 20x2 Performed by: SDM 10/1/20x2 Reviewed by: TKJ 10/9/20x2 3 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 + Receivahles 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 Coverage Ratio: Long-Term Solvency (Abitity of Entity to Continue as a Going Concern) Max Debt to Assets (ST Deht + LT Deht) 13.76 Total Assets Debt to Equity= (ST Debt + LT Debt) + Stockholders' Equity 12.45 1-The current ratio and quick ratios increased significantly primarily due to an increase in 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 the timing of cash receipts and purchases. The company has not made any major purchases in the past year, but plans to make some purchases in 20x3. In addition, the current liabilities in 20X2 have decreased from 3/31/20X1 primarily due to the timing of paying accounts payable balances and the timing of purchases. 4.41 3.76 4.15 .71 48 02 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. 29 57 5 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. n -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. 4 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). 2 Garcia and Foster Audit Workpaper B.3.3: 51 proc opportunity to to consider Alpine Cupcakes the workpapers. The audit workpapers alse calculations and overal risk assessment of inherent r module, you will gain a better understanding of the auditors con assessment affects the audit, The anal nf thie mad ir tanable vou to understand the aud Cambridge Business Publishers Performed by: SDM 10/1/20x2 Reviewed by: TKJ 10/9/20X2 ndule, you will be able eir understandin Learning Objectives: Cambric Preliminary Analytical Procedures-Ratio Analysis Audit Year December 31, 20X2 Alpine Cupcakes, Inc. Company Ratios % Change 1 , H 60.1% 3/31/20X2 3/31/20x1 7.094 11.360 62.4% 1, 6.696 10,875 Current Ratio 33.9% 2, H 2.150 2,879 Quick Ratio* -25.3 % 2, 169.745 126.760 Receivables Turnover 1.4 % v , -1.3% V , u -3.2% , u 2.506 Days Outstanding in Receivables 2.540 145,654 inventory Turnover 143.710 Days of Inventory on Hand Gross Profit Percentage 4.865 4.710 13.1% 3, H 0.084 0.095 0.0% , Profit Margin 0.036 0.036 Return on Assets -11.7 % 4, 0.060 0.053 Return on Equity -17.0 % 5 0.274 0.330 Debt to Assets -25.9% 5, 0.54 0.400 0 Debt to Equity Industry Ratios March 20X1 March 20X2 Min x Min Avg Avg x 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 333.76 Inventory Turnover 1.62 0.24 3.95 1.51 0.20 Days of Inventory on Hand 4.15 441.54 92.29 1525.44 458.39 Gross Profit Percentage 87.88 1830.71 0.31 0.01 0.49 Profit Margin 0.33 0.10 0.48 0.02 -0.09 0.10 Return on Assets 0.11 -0.09 1.02 0.01 -0.03 Return on Equity 0.04 0.04 -0.02 0.04 0.29 -0.04 Debt to Assets 0.23 0.08 -0.16 0.21 Debt to Equity 0.67 0.00 0.74 0.19 0.00 0.26 -0.05 0.55 1.76 0.58 0.00 Auditor Notes: 3.99 ** Calculation includes current portion of mortqage 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 $8,337.88, respectively.On 3/31/20X1, the current portions of the mortgage and notes payables are $6,236.12 and s9,103.75, respectively. V-Fluctuation meets expectations of being less than a 10% change. -The 20X2 ratio is within the expected range (between the min and max) of the industry data. Garcla and Foster Audit Workpaper The risk assessmi objectives of this module is to have v procedures. Through reviewing these documents, opportunity to assess the auditors performance of the required pe to consider Alpine Cupcakes' overall financial statement risk and the accon the workpapers. The audit worknagers also include audit documentation calculations and overalt risk assessment of inherent risk, ontrol risk, and module, you will gain a better understanding of the auditors considerat assessment affects the audit. l you to understand the auditors Hule, you will be able to: eir understanding of th Learning Objectives: Cambridge Business Publishe Performed by: SDM 9/15/20x2. OCambridgs Bu Alpine Cupcakes, Inc. 7KJ 9/20/20x2 Income Statements for the 3 Months Ended 3/31/20x2 and 3/31/20X1 (USD S) Reviewed by: Inco Audit Year December 31, 20x2 PBC 8.3.2 PBC 3 Months Ended 03/31/20X1 3 Months Ended 03/31/20X2 $353,739.57 80,649.00 $434,388.57 66,736.39 $343,050.56 Revenue Sales Revenue: Corporate Accounts Sales Revenues: Storefront Total Sales Revenue Cost of Goods Sold: ingredients Cost of Gonds Sold: Boxes and Cupcake Cups Cost of Goods Sold: Beverages 91,411.50 $434,462.06 Earr 64,645.64 3,755.55 Ne 3,875.55 5,466.50 $76,078.44 $358,310.13 5,681.50 $74,082.69 $360,379.37 F: Total COGS Gross Profit Interest Revenue 65.00 89.00 $360,444.37 $358,399.13 Gross Profit Plus Interest Revenue Expenses Wage Expense Wage Tax Expense Medical Insurance Expense 217,791.00 216,719.00 18,077.94 17,971.24 17,100.00 8,100.00 1,035.00 1,035.00 Auto Insurance Expense 3,996.90 1,523.20 3,768.40 4,197.69 1,708.00 Interest Expense Electrical & Gas Service Expense 3,693.60 Liability Insurance Expense Telecommunications Expense Cell Phone Service Expense 472.50 462.00 912.00 933.00 Postage Expense Professional Servces Expense 139.50 135.90 2,070.00 2,122.50 Maintenance Expense 1,212.00 1,132.00 Office Supplies Expense Dry Cleaning Expense Storefront Paper Supplies Expense Rental Expense Waste Services Expense 8,050.00 7,342.00 398.75 416.15 778.25 869.00 4,752.00 4,752.00 150.00 150.00 Car Maintenance and Fuel Expense 995.38 1,026.78 Repair Expense Water Expense 372.50 381.25 378.75 Soda Machine Repair and CO2 Expense Credit Card Expense Cooking Supplies Expense 373.75 1,156.00 1,128.00 878.31 897.89 Banking Fees Selling and Administrative Expenses 12,704.00 11,858.00 445.00 445.00 Depreciation Expense: Equipment Depreciation Expense: Plant & Property $288,978.68 $298,028.45 3,627.00 4,152.00 Total Depreciation Expense Total Expenses 3,300.00 $6,927.00 $295,905.68 3,300.00 $7,452.00 $305,480.45 Client Supporting Document 42 B.1.2 pg. 1 of 2 essment module focuses on several Important as! of this module is to have vou review and gain a betrer under Through reviewing these documents, you will see real world audit worl wto assess the auditors performance of the required procedures. in addition, you W Alpine Cupcakes gverall financial statement risk and the accpunt level risks as you review Determine if the (workpapers B,3.1 and B. auditors. Describe any problema Determine if the ratio analysis (workpap eview the explanations of ratio fluctuations pro you identify. Ts to B.3.3.) pers. The audit workpapers also include audit documentation on the auditors' materiallty s and overall risk assessment of inherent risk, contral risk, and detection risk. Through this u will gain a better understanding of the auditors' considerations of client risk and how this assesiment of Garcia and Foster's audit risk. Describe the spectit, rks for agme a vou believe these factors.relate to the Alpine Cupcakes audit, and vdentt wih sem Wkely to be affected by these risks, Describe how the audit teams shols adres the For example, the background information mentions that sugar prices mary rise w present to Alpine Cupcakes? Which accounts would be affected by this risk o Q6. Prepare a memo to document your understanding of Alpine Cupcakes enee bjectives: is to enable you to understand the auditors audit planning and risk assessment affects the audit. he module, you will be able to: on of their understanding of the client's erivironment, including its derstanding of important procedum ndule includes consideran Cash Modufe address these risks in the audit plan? cOverage of the cash account, along with Cambridge Business Publishers Alpine Cupcakes, Inc. Balance Sheets as of 3/31/20x2 and 12/31/20x1 (USD $) Audit Year December 31, 20x2 Performed by: SDM 9/15/20x2 Reviewed by: TKJ 9/20/20x2 PBC B.3.1 As of Balance Sheet: As of 3/31/20x2 As of Current Assets 12/31/20X1 3/31/20X1 Cash: Storefront $125,498.76 Cash: Corporate Accounts. Cash: Payroll $135,135.15 $151,293.51 293,728.03 210,019.06 57,069.68 123,432.43 Accounts Receivable 124,726.15 122,931.81 122,849.12 Office Supplies 193,976.31 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 Inventory: Beverages 25,190.66 25,580.09 26,779.44 1,423.05 1,190.10 434.95 Total Current Assets 3,340.30 3,260.80 $701,204.66 2,348.50 $526,335.91 Long-term Assets $702,618.35 Equipment 150,180.00 Accumulated Depreciation: Equipment Plant & Praperty Accumulated Depreciation: Plant & Property 150,180.00 150,180.00 (82,455.00) (78,828.00) (66,372.00) 330,000.00 330,000.00 330,000.00 (82,500.00) 125,000.00 $440,225.00 (79,200.00) Land (69,300.00) 125,000.00 Total Long-term assets 125,000.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 Mortgage Payablele Notes Payable: Vehiclesb) Total Liabilities 0.00 13,125.00 0,00 290,673.81 292,262.13 296,909.93 22,555.53 24,878.31 31,659.28 $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 120,075.91 438,346.48 $741,408.56 B.2.1 $608,422.39 Additional Paid in Capital 120,075.91 120,075.91 Retained Earnings 612,578.32 571,332.65 Total Stockholders' Equity Total Liabilities and Stockholders' Equity $782,654.23 $1,142,843.35 $1,148,356.66 $995,843.91 (aCurrent portion $6,555.19 at 3/31/20X2 and $6,236.12 at 3/31/20X1 (bCurrent 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 41 DA2 Risk For Students(2) (1) QSearch Sheet AutoSave FF Review View DComments Home Insert Draw Page Layout Formulas Data Share _ 4 AY Insert vA A General Calibri (Body) wraa Text 14 Deleie A = = Merge & Center Conditional Farmat Cell Find & Select Ideas Sensitivity Pastc % Sprt & -- X Farmat Formetting as Teble Styles Filten fxRisk Assessment Question 2 X A1 T A D E F H O T Risk Assessment: Question 2 2 Analyze the risk associated with the cost of ingredients a) Create a visua lization to show how a 25 % and 50 % increase in the cast 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 4 gross margin and gross margin percent c) Is there a price point past which you belicve an increase in the price of supgar would become a risk to Alpine Cupcakes? Show evidence too support your sconclusion. The example below shows one way you might do this. Look at the formulas in the dark bluc 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. Torar Orig. Difference w/price Vanilla Price change Key change Price Vanilla Cupcakes Butter Cream Frosting shortenin confec. %change (50 % increase-S baking 10 Ingredient 11 Unit measured as 12 Amount of unit used per 36 cupcalas 13 price/unit 14 Ingredient cost per /36 cupcakes 15 Costfcupcake New Price vanilla salt flour butter vanilla salt butten Sugar mil Item Price ees sugar powder pounds counds quart pounds 0.7500 0.9923 0.0049 00150 1.2375 $1.50 $0.50e $1.13 $0.5c $0.00 50.03 $0.01$0.00$0.02 0.01 doaen pounds ounas unds pounds Und $0.50 $0.50 galions 0.047 53.00 sugar 0.00co Confect. Sgar quart pounds 0.0195 9.481B 93.81 3.9500 S0.56 $0.56 1.0000 0.0225 0.0049 0.7500 0.5780 9.4818 $0.20 $40.00 $2.00 $2.00 055 $0.37 $93.81 50.30 $3.00 $40.00 $0.20 $2.00 $0.56 00000 $0.37 $0.05 $0.90 $1.50 $0.14 $9.79 $9.73 0.O049 $0.60 $0.00 $2.22 $0.27 50.04 $0.00 50.00 $0.01 $0.27 $0.06 50.03 $0.00 $0.06 50.00 16 17 a. Increase 50% increase 25 % vanilla cupcake in sugar in crease from 18 s0.01 to $0.03 butter cream frosting in confec. Sugar 19 increase from $0.06 to $0.12 incease from $0.01 to $0.02 INCAFASE from $0.06 to $0.09 20 21 22 23 24 25 26 27 Risk Assess Q3 Index for Worksheet Recipes Ingredient price list Customer List Sales Orders Vendor List Risk Assess Q1 Risk Assess 02 Risk Assess 04 Risk Assess Q5 Average: 5.031997763 Caunt: 126 Sum: 342.1758479 + 100 % DA2 Risk For Students(2) (1) AutoSave FF QSearch Sheet Review View DComments Home Insert Draw Page Layout Formulas Data Share _ ' . - X Insert A A Calibri (Body) wraa Text Currency 11 Delete Merge & Center I A Conditional Farmat Formetting as Teble Styles Cell Find & Select Ideas Sensitivity Peste Sort & Farmat Filten X fx(ROUND(G12/36,2) G13 A B C. D E E G K P Q. 1 Risk Assessment: Question 1 Analyze the risk associated with the cost of ingredients Using the data provided, determine the cost of each type of cupcake 2 3 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 4 visualization. It can also serve as a check to ensure that you are arriving at the correct cost when you create your cost analysis 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 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 Total Butter Cream Frosting baking confec 8Ingredient salt vanilla flour powder butter vanilla butter shortening pounds salt milk Sugar eges sugar 9Unit measured as dozen ounds gounds pounds pounds 10000 paunds pounds gallons 0,0473 auart pounds quart pounds 0,0195 10 Amount of unit used per 36 aupcakes 0.9923 0.7500 0.0049 0,0150 1.2375 0.0225 0.7500 0.6780 3.9600 0.0049 $3.00 11 price/unit 12 Ingredient cost per /36 cupcakes $1.50 $0.50 $0.20 $40.00 $3.00 $2.00 $40.00 $2.00 $0.30 0.55 $0.56 $0.20 $0.37290 $2.21760 0.00098 $0.14175$9.78508 $0.60000 $0.37125 50.05850 $1.12500 $0.49613 $2.00000 $0.90000 $0.00098 $1.50000 13 Cost /cupcake s0.00 $0.270 $0.02 $0.00 SD.00 $0.03 $0.01 $0.01 S0.06 $0.03 $0.04 $0.01 S0.06 $D.00 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Risk Assess Q2 Risk Assess Q3 Risk Assess Q4 Risk Assess Q5 Vendor List Index for Worksheet Recipes Ingredient price list Customer List Sales Orders Risk Assess Q1 118% DA2 Risk For Students(2) (1) QSearch Sheet AutoSave FF Formulas Review View DComments Home Insert Draw Page Layout Data Share - Insert Calibri (Body) vA A wraa Text 11 General Delete Merge & Center B IU A Conditional Farmat Cell Find & Select Idcas Sensitivity Peste Sort & 1-- X Format Formetting as Teble Styles Filten X D19 L C P F G H J N C s T 1 Risk Assessment: Question 3 Analyze the risk associated with the cost of ingredients 2 3Determine 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 s only on cost of cupcakes). Assume all 3 go up 25 % then 50 % at the same time cl 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 Hint: Agcod 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 13 14 15 16 17 18 19 20 21 22 23 28 25 26 27 28 25 30 31 32 33 34 Risk Assess Q4 Risk Assess Q2 Index for Worksheet Recipes Ingredient price list Customer List Sales Orders Vendor List Risk Assess Q1 Risk Assess Q3 Risk Assess Q5 + + 100 % DA2 Risk For Students(2) (1) AutoSave FF QSearch Sheet Review View DComments Home Insert Draw Page Layout Formulas Data Share _ 4 Insert vA A General Calibri (Body) wraa Text 14 Delete A Merge & Center Conditional Farmat Formetting as Teble Styles Cell Find & Select Ideas Sensitivity Pastc % Sprt & X Format Filten fx X Risk Assessment: Question 4 A1 T A G H N s T AA 1Risk Assessment: Question 4 2Analyze the risk associated with customers a) Create a visualization (i.., 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 3constitute 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 a bout vul ne rabilities associated with customers? 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 a mount sold? Some other number? Once you have assembled your data, experiment with visualization techniques, tables? Pivot tables? Charts? Graphs? or some other 6technique? 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 78 29 30 31 32 Risk Assess Q1 Risk Assess Q3 Sales Orders Index for Worksheet Recipes Ingredient price list Customer List Vendor List Risk Assess Q2 Risk Assess 04 Risk Assess Q5 Count: 5 + 100% DA2 Risk For Students (2) (1) AutoSave FF QSearch Sheet Review View DComments Home Insert Draw Page Layout Formulas Data Share _ Insert vA A General Calibri (Body) wraa Text 14 Delete A Merge & Center Conditional Farmat Formetting as Teble Styles Cell Find & Select Ideas Sensitivity Pastc % Sort & Format Filten fx X Risk Assessment: Question 5 Al C H J M N o T 7 1Risk 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 3ingredients. In otherwords, who are Alpine's major suppliers? b) Do you feel the re 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 visua lization about how they should plan their purchases of ingredients 4 gcing forward? 5Use data from 2022 Hint: You will need to use Vendor List and Ingredient Vendor I nvoices for this problem. Again combine with "vlookup" When your data is prepared, choose whatever visualization seems best for you. 10 11 12 13 14 15 16 17 18 15 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Risk Assess Q1 Risk Assess Q2 Risk Assess Q3 Risk Assess Q4 Index for Worksheet Recipes Ingredient price list Customer List Sales Orders Vendor List Risk Assess Q5 + Count: 6 100% 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. How do you assess the overall risk in the rise in prices of ingredients to Alpine Cupcakes' future? C Q4. Analyze the risk associated with customers. Use sales data from 2022. a. 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? After seeing your visualization, how would you advise Alpine Cupcakes about vulnerabilities associated with customers? b. 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 Alpin 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 Ansl sessmen of this module is to have you s. Through reviewing these documents, you w ry to assess the auditors' performance of the required proc r Alpine Cupcakes' overall financial statement risk and the account leve apers. The audit workpapers also include audit documentation on the auditors' mla s and overall risk assessment of inherent risk, control risk, and detection risk. Through this Du will gain a better understanding of the auditors considerations of client risk and how this 06. Prepare a memo to document your understanding of a assessment of Garcia and Foster's audit risk. Describe the speciie rs you believe these.factors relate to the Alpine Cupcakes sudit, and de likely to be affected by these risks. Describe how the audit teams d For example, the background information mentions that sugar prices present to Aipine Cupcakes? Which accounts would be affected ba b. Determine if the ratio Review the explanations of rat vou identify auditors. Dese t affects the audit. of their understanding of the client's environment, including its dinns Objectives: to enable you to understand the auditors' audit planning and risk assessment Cash Module nshve coverage of the cash account derstanding of impos ndule incluc the module, you will be able to: address these risks in the audit planr bridge Business Publisher rformed by: M 20/2/20x2 wiewed by: J 10/9/20x2 e Cambridge Business Publishers Alpine Cupcakes, Inc. Preliminary Analytical Procedures-Quarterly Income Statements Audit Year December 31, 20X2 Performed by: SDM 10/1/20x2 Reviewed by: TKJ 10/9/20x2 % Change 3 B.1.2 3 Months 3 Months Ended Ended -3.12 % 03/31/20X2 03/31/20X1 $ Change -11.77% % Change Expenses (continued) -0.01 % Car Maintenance and Fuel Expense 1 995.38 1,026.78 3.23% (31.40) -3.06% Repair Expense 372.50 381.25 (8.75) 3.20% Water Expense -2.30 % 378.75 373.75 -3.78% 5.00 Soda Machine Repair and CO2 Expense 1.34% 1,156.00 1,128.00 28.00 Credit Card Expense 2.69% 2.48% 878.31 897.89 Cooking Supplies Expense (19.58) -2.18 % 12,704.00 11,858.00 846.00 Banking Fees 7.13% 36.92% 445.00 445.00 Selling and Administrative Expenses 0.00 0.00% 288,978.68 298,028.45 Depreciation Expense: Equipment 3,627.00 4,152.00 (525.00) -12.64 % Depreciation Expense: Plant & Property Total Depreciation Expense 3,300.00 3,300.00 0.00 0.00% 0.49% 6,927.00 7,452.00 Total Expenses D.59% 63% 2 295,905.68 305,480.45 Earnings Before Income tax 00% v 62,493.45 54,963.92 Income Tax Expense 21,247.78 78% 18,683.66 2,564.12 Net Income 13.72% $83,741.23 82% $73,647.58 F 13% v Auditor Notes: The fluctuation is less than TM ($13,700) and less than a 10% change. 7% F-Footed % v 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 2 B.3.2: pg. 49 nt modute focuses or several inmportant assessment procedures, One af the module is to have you review and gain a better understanding of prefiminary analytica ugh reviewing these documents, you wili see real world audit workpapers and have the sess the auditors' performance of the required procedures. In addition, you will be able Cupcakes' overall financial statement risk and the account level risks as you review he audit workpapers also include auudit documentation on the auditors' materiality wverall 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 Bisk Assessment Module tind and proe discover with the memic Qs. Evaluate 1he preliminary analyt to B.3.3.) This question relates to Step a. Determine if the analunes of account fluc workpapers B.3.1 and B.3.2). Review the explanaions & auditors. Descritn any problems you identify Review the explanations of ratio fluctuations provided try the auieors mere 06 Prypare a memb to document your understanding of Alpine Cupcakes envieement nd s . Determine if the ratio analysis (workpaper 8.3.3) was approgranely serformed nd o you beleve these factors.nelate to the Alpine Cupcakes audit, and idenety which specfe m kely to be affected by these tisks, Describe how the audit teanms shouid adrens e om For example, the background information mentions that sugar prices may rice WWh rs present to Alpine Cupcakes? Which accounts would be affected by this risk? How he you identty, s the audit. sessnent af Garcia and Foster's audit risk. Describe the specific rs for igime Core res: -Ada is to enable you to understand the auditors audit planning and risk assessment the module, you will be able to: sasens Publishers of their understanding of the client's environment, inciuding its nsve coverage of the cash account, along with the rel nderstanding of important procedures ne dule includes consideration c d by: 20x2 y: /20x2 address these risks tn the audit plant Cash Module ninns Cambridge Business Publishers Performed by: Income Statements for the 3 Months Ended 3/31/20x2 and 3/31/20X1 (USD S) Reviewed by: Alpine Cupcakes, Inc. PBC nded X1 Audit Year December 31, 20x2 SDM 9/15/20x2 TKT 9/20/20x2 50.56 1.50 2.06 5.64 .55 B.3.2 3 Months Ended 3 Months Ended Earnings Before Income Taxes 03/31/20X2 Income Tax Expense 03/31/20X1 62,493.45 Net Income 54,963.92 .50 21,247.78 18.683.66 69 $41,245.67 Earnings per Share $36,280.26 37 o $0.82 $0.73 7 Client Supporting Document B.1.2 pg. 2 of 2 43 The risk assessment rmodule focuses on several important assessment procedurms ebjectives 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. ins to consider Alpine Cupcakes overalt financial statement risk and the acc the workpapers. The audit workpaners also include audit documentattion on calculations and overail risk assessment of inherent risk, control risk, and de module, you will gain a better understanding of thve auditors consideration- assessment affects the audit. Bisk Assessment Madll Introduction The ansl nt thir mde e n anable you to understand the auditors aud dule, vou wil be able to: eir understanding of the c Learning Objectives: Cambridge Business Publishers Performed by: SDM 10/a/20x2 Reviewed by: TKJ 10/9/20x2 eCambridge Busine Preliminary Analytical Procedures-Balance Sheets Audit Year December 31, 20X2 Alpine Cupcakes, Inc. B.1.1 As of As of 3/31/20X2 $ Change % Change 12/31/20X1 1-We discusse Accountine -6.39 % 2 (8,636.39) disbursem $135,135.15 $125,498.76 293,728.03 A0.33% 1 Current Assets 84,708.97 210,019.06 124,726.15 account to cash bala next year Cash: Storefront (293.72) (70,127.19) 775.00 -0.24 % / Cash: Corporate Accounts Cash: Payroli 123,432,43 -36.15 % 2 193,976.31 122,849.12 some del 29.76% Accounts Recelvable Office Supplies Cooking Supplies 2,604.00 2-We disc 2,379.00 22.58 % 1,064.00 4,713.00 noted th reducin additio does n 4,777.00 610.57 25,580.09 1,190.10 2.39% v 25,190.66 1,423.05 Inventory: Ingredients Inventory: Cake Boxes and Cupcake Cups Anventory: Beverages 1,232.95 103.60% 33.11 % v 1,079.50 3,260.80 3,340.30 3-We dis sight $701,204.66 $702,618.35 Total Current Assets Mour Long-Term Assets Equipment Accumulated Depreciation: Equipment Plant & Property Accumulated Depreciation: Plant & Proprty previ prod this 0.00 150,180.00 0.00% v 150,180.00 (78,828.00) 3,627.00 4.60% v (82,455.00) 330,000.00 0.00 0.00% 330,000.00 in t -4,17 % v 3,300.00 (82.500.00) (79,200.00) 125,000.00 4-Per 125,000.00 0.00 0.00% v 20 Land Th 440,225.00 $447,152.00 Total Long-Term assets 5-M $1,142,843.35 $1,148,356.66 Total Assets T Liabilities Accounts Payable $25,712.00 38,556.70 (12,844.70) -33.31% 3 Wage Taxes Payable 0.00 0.00 0.00 0.00% Corporate Income Tax Payable Dividends Payable Mortgage Payable 21,247.78 38,125.96 (16,878.18) 44.27% 4 0.00 13,125.00 (13,125.00) -100.00% 5 290,673.81 292,262.13 (1,588.32) -0.54% Notes Payable: Vehicles 22,555.53 24,878.31 (2,322.78) -9.34% v Total Liabilities 360,189.12 $406,948.10 Stockholders' Equity 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% 120,075.91 120,075.91 Retained Earnings 0.00 0.00% 612,578.32 571,332.65 $741,408.56 $1,142,843.35 $1,148,356.66 Total Stockholders' Equity 41,245.67 7.22% V 782,654.23 Total Liabilities and Stockholders' Equity .The fluctuation is less than TM ($13,700) and less than a 10% change. F-Footed Garcia and Foster Audit Workpaper B.3.1: pg. 1 of 2 46 to assess the auditols Apine Cupcakes' overall financial sta ers. The audit workpapers also include audit docu and overall risk assessment of inherent risk, control risk, and de will gain a better understanding of the auditors considerations of client i 06. Prepare a memo to docume assesiiment of Garcia and Foster's au you believe these factors.relate to the Alpins ikely to be affected by these risks. Describe how th For example, the background information mentions that s present to Alpine Cupcakes? Which accounts would be affected t you identify affects the audit f their understandine of the client's environment, including its tstions jectives nable you to understand the auditors' audit planning and risk assessment Cash Module module, you will be able to: ge Businesa Publishe acddress these risks in the audit pian? derstanding of impo ndule incl rformed by: M 10/1/20x2 wiewed by: 10/9/20X2 coverage of the cash accoun O Cambridge Business Publishers Alpine Cupcakes, Inc. Preliminary Analytical Procedures-Ratio Analysis Audit Year December 31, 20x2 Performed by: SDM 10/1/20x2 Reviewed by: TKJ 10/9/20x2 3 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 + Receivahles 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 Coverage Ratio: Long-Term Solvency (Abitity of Entity to Continue as a Going Concern) Max Debt to Assets (ST Deht + LT Deht) 13.76 Total Assets Debt to Equity= (ST Debt + LT Debt) + Stockholders' Equity 12.45 1-The current ratio and quick ratios increased significantly primarily due to an increase in 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 the timing of cash receipts and purchases. The company has not made any major purchases in the past year, but plans to make some purchases in 20x3. In addition, the current liabilities in 20X2 have decreased from 3/31/20X1 primarily due to the timing of paying accounts payable balances and the timing of purchases. 4.41 3.76 4.15 .71 48 02 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. 29 57 5 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. n -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. 4 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). 2 Garcia and Foster Audit Workpaper B.3.3: 51 proc opportunity to to consider Alpine Cupcakes the workpapers. The audit workpapers alse calculations and overal risk assessment of inherent r module, you will gain a better understanding of the auditors con assessment affects the audit, The anal nf thie mad ir tanable vou to understand the aud Cambridge Business Publishers Performed by: SDM 10/1/20x2 Reviewed by: TKJ 10/9/20X2 ndule, you will be able eir understandin Learning Objectives: Cambric Preliminary Analytical Procedures-Ratio Analysis Audit Year December 31, 20X2 Alpine Cupcakes, Inc. Company Ratios % Change 1 , H 60.1% 3/31/20X2 3/31/20x1 7.094 11.360 62.4% 1, 6.696 10,875 Current Ratio 33.9% 2, H 2.150 2,879 Quick Ratio* -25.3 % 2, 169.745 126.760 Receivables Turnover 1.4 % v , -1.3% V , u -3.2% , u 2.506 Days Outstanding in Receivables 2.540 145,654 inventory Turnover 143.710 Days of Inventory on Hand Gross Profit Percentage 4.865 4.710 13.1% 3, H 0.084 0.095 0.0% , Profit Margin 0.036 0.036 Return on Assets -11.7 % 4, 0.060 0.053 Return on Equity -17.0 % 5 0.274 0.330 Debt to Assets -25.9% 5, 0.54 0.400 0 Debt to Equity Industry Ratios March 20X1 March 20X2 Min x Min Avg Avg x 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 333.76 Inventory Turnover 1.62 0.24 3.95 1.51 0.20 Days of Inventory on Hand 4.15 441.54 92.29 1525.44 458.39 Gross Profit Percentage 87.88 1830.71 0.31 0.01 0.49 Profit Margin 0.33 0.10 0.48 0.02 -0.09 0.10 Return on Assets 0.11 -0.09 1.02 0.01 -0.03 Return on Equity 0.04 0.04 -0.02 0.04 0.29 -0.04 Debt to Assets 0.23 0.08 -0.16 0.21 Debt to Equity 0.67 0.00 0.74 0.19 0.00 0.26 -0.05 0.55 1.76 0.58 0.00 Auditor Notes: 3.99 ** Calculation includes current portion of mortqage 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 $8,337.88, respectively.On 3/31/20X1, the current portions of the mortgage and notes payables are $6,236.12 and s9,103.75, respectively. V-Fluctuation meets expectations of being less than a 10% change. -The 20X2 ratio is within the expected range (between the min and max) of the industry data. Garcla and Foster Audit Workpaper The risk assessmi objectives of this module is to have v procedures. Through reviewing these documents, opportunity to assess the auditors performance of the required pe to consider Alpine Cupcakes' overall financial statement risk and the accon the workpapers. The audit worknagers also include audit documentation calculations and overalt risk assessment of inherent risk, ontrol risk, and module, you will gain a better understanding of the auditors considerat assessment affects the audit. l you to understand the auditors Hule, you will be able