Need help with this! Need to do the excel work on this

Analytical review Golden Big Burgers Part I: Background Golden Big Burgers (GB) is a hamburger food chain with 20 locations operating solely in the US. The menu offerings and store operations are largely patterned after McDonald's (MCD). GB was formed in 2005 and went public in 2009. GB's common shares are publicly traded under the ticker symbol GB and it has a December 31 year-end. All GB restaurants are based on a single-building model and have approximately the same square footage. Stores 1 through 4 are located in shopping centers near college campuses and are open 18 hours a day. Stores 5 through 20 are located on college campuses and are open 24 hours a day. GB has been a client of Best Audit Firm since it went public. Management is very control conscious and maintains a strong internal control environment. GB has received an unqualified opinion every year on its financial statements since its first audit in 2005. Assume that you are the audit senior. During a meeting with the controller to discuss operating results for the year, the controller provides you with the following summary of key financial information: Amounts in thousands 2012 2011 Sales $25,780 $23,409 $22,116 Less operating expenses $20,473 $18,462 $18,163 Operating margin $ 5,307 $ 4,947 $ 3,953 20.6% 21.1% 17.9% Operating margin percentage 2010 The controller informs you that, on an aggregated basis, GB had a very good year despite the contraction of the informal, eating-out segment of the US population brought on by the recession. The recession caused a softening of GB's sales growth in early 2012. However, GB reacted quickly and reduced its prices, which resulted in improved sales. Overall sales increased 10.1% in 2012. Additionally, operating margins were 20.6% in 2012 versus 21.1% in 2011. This slight decline in operating margins in 2012 was due to reduced pricing, but was partially offset by increased customer (guest) counts of 6.9%. Analytical review - case study part I 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. MM4155I1 The controller previously provided the audit team with disaggregated information by store for 2010, 2011 and 2012 that includes sales, expenses, operating margins and customer count by store. One of the audit staff performed limited testing on this information (see working paper A0 Financial and nonfinancial). After reviewing this information, you ask the controller whether there were any significant events or changes in the stores of which you should be aware. The controller informs you of the following: Store No. 2 was converted to a 24-hour store midyear. Store No. 15 had a fire the first week of January and was closed for two and a half months. This store was completely remodeled before it reopened; however, management did not expect that the remodel would have a notable impact on sales. Stores No. 19 and No. 20 were opened on May 1, 2012 and September 1, 2012, respectively. The chief operating officer retired in March 2012 and no successor has been identified. The controller also shares with you the following insights: The retail industry utilizes comparable sales and comparable customer counts as key performance indicators. Internally, management tracks GB's results against MCD's US operations. Because of GB's appeal to college students, its sales have grown approximately 1% faster than MCD's sales. Operating margins have been approximately 0.5% better than MCD's operating margins. This is largely due to GB's lower compensation structure, since it primarily employs part-time college students. GB's average growth in customer count visits has been in line with MCD's growth. GB's third quarter press release included a statement that 2012 sales were expected to grow by approximately 10%. Approximately 5% of this growth was due to the addition of two new stores in 2012. Despite increased pressure on pricing, total operating margin dollars were better than expected. Last year, the audit team determined the scope would be 1.5% of the operating margin ($74,000 in 2011). Differences outside of this dollar threshold of plus or minus a 5% difference from expectations were appropriately investigated. The audit manager has told you, after considering the quantitative and qualitative factors and the required degree of precision, a similar approach to determining acceptable differences from expectations is appropriate in 2012; therefore, the scope has been set at $80,000 (1.5% of $5.3 million), plus or minus a 5% difference from expectations. You review some of the audit work already completed with the audit manager. This work will impact your ability to rely on key financial (actual and budgeted sales and operating results) and nonfinancial (customer count) information by store provided in management's monthly operations report for purposes of performing your analytical review procedures. Together, you note that: Internal control testing included sales and operating expenses at the store level. No significant issues were noted as a result of this testing. The audit team evaluated the accuracy of the customer count information by tracing a representative sample of the customer count data to register receipts at selected stores as part of the internal control testing. No significant issues were noted as a result of this testing. While budget information is available, the completeness, accuracy and robustness of the process have not been tested and, therefore, this information should not be used for purposes of the analytical review. Before you begin your analytical procedures, you refresh your understanding of the objectives of analytical procedures by reading PCAOB Auditing Standard No.15, paragraph 21, which states: Analytical review - case study part I 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. MM4155I1 \"Analytical procedures consist of evaluations of financial information made by a study of plausible relationships among both financial and nonfinancial data. Analytical procedures also encompass the investigation of significant differences from expected amounts.\" Based on a recent seminar you attended, you are aware the PCAOB has identified several deficiencies in performing analytical procedures among auditors in general that you want to avoid. These include the failure to: Test the data used in analytical procedures Develop and document expectations Establish a threshold of differences requiring further investigation Follow up on unexpected differences You note that while the data available for your analytical procedures has already been tested, you will be responsible for the remaining procedures. Required: Read PCAOB Auditing Standard No. 12, Identifying and Assessing Risks of Material Misstatement, paragraphs 46 through 48. Performing Analytical Procedures 46. The auditor should perform analytical procedures that are designed to: a. Enhance the auditor's understanding of the client's business and the significant transactions and events that have occurred since the prior year end; and b. Identify areas that might represent specific risks relevant to the audit, including the existence of unusual transactions and events, and amounts, ratios, and trends that warrant investigation. 47. In applying analytical procedures as risk assessment procedures, the auditor should perform analytical procedures relating to revenue with the objective of identifying unusual or unexpected relationships involving revenue accounts that might indicate a material misstatement, including material misstatement due to fraud. Also, when the auditor has performed a review of interim financial information in accordance with AU sec. 722, he or she should take into account the analytical procedures applied in that review when designing and applying analytical procedures as risk assessment procedures. 48. When performing an analytical procedure, the auditor should use his or her understanding of the company to develop expectations about plausible relationships among the data to be used in the procedure.27/ When comparison of those expectations with relationships derived from recorded amounts yields unusual or unexpected results, the auditor should take into account those results in identifying the risks of material misstatement. Analytical review - case study part I 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. MM4155I1 Note: Analytical procedures performed as risk assessment procedures often use data that is preliminary or data that is aggregated at a high level, and, in those instances, such analytical procedures are not designed with the level of precision necessary for substantive analytical procedures. Read AU Section 329, Substantive Analytical Procedures, paragraphs .02, .05 and .17 through .22. .02 Analytical procedures are an important part of the audit process and consist of evaluations of financial information made by a study of plausible relationships among both financial and nonfinancial data. Analytical procedures range from simple comparisons to the use of complex models involving many relationships and elements of data. A basic premise underlying the application of analytical procedures is that plausible relationships among data may reasonably be expected to exist and continue in the absence of known conditions to the contrary. Particular conditions that can cause variations in these relationships include, for example, specific unusual transactions or events, accounting changes, business changes, random fluctuations, or misstatements. .05 Analytical procedures involve comparisons of recorded amounts, or ratios developed from recorded amounts, to expectations developed by the auditor. The auditor develops such expectations by identifying and using plausible relationships that are reasonably expected to exist based on the auditor's understanding of the client and of the industry in which the client operates. Following are examples of sources of information for developing expectations: a. Financial information for comparable prior period(s) giving consideration to known changes b. Anticipated resultsfor example, budgets, or forecasts including ` from interim or annual data c. Relationships among elements of financial information within the period d. Information regarding the industry in which the client operatesfor example, gross margin information e. Relationships of financial information with relevant nonfinancial information .17 The expectation should be precise enough to provide the desired level of assurance that differences that may be potential material misstatements, individually or when aggregated with other misstatements, would be identified for the auditor to investigate (see paragraph .20). As expectations become more precise, the range of expected differences becomes narrower and, accordingly, the likelihood increases that significant differences from the expectations are due to misstatements. The precision of the expectation depends on, among other things, the auditor's Analytical review - case study part I 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. MM4155I1 identification and consideration of factors that significantly affect the amount being audited and the level of detail of data used to develop the expectation. .22 When an analytical procedure is used as the principal substantive test of a significant financial statement assertion, the auditor should document all of the following: a. The expectation, where that expectation is not otherwise readily determinable from the documentation of the work performed, and factors considered in its development b. Results of the comparison of the expectation to the recorded amounts or ratios developed from recorded amounts c. Any additional auditing procedures performed in response to significant unexpected differences arising from the analytical procedure and the results of such additional procedures [Paragraph added, effective for audits of financial statements for periods beginning on or after May 15, 2002, by Statement on Auditing Standards No. 96.] Obtain a copy of McDonald's 2011 and 2012 annual reports. Review the management discussion and analysis section. Focus on comparable sales and guest counts for McDonald's US operations and company stores' operating margins for 2011 (pages 15-16) and 2012 (pages 18-19). Note that McDonald's only considers stores that have been open at least 13 months, including those temporarily closed (such as for remodels), in its comparable information statistics. - This link will take you to McDonald's annual reports: http://www.aboutmcdonalds.com/mcd/investors.html Analytical review - case study part I 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. MM4155I1 Develop and document your expectations regarding revenues for 2012 using the working papers that one of the audit staff has set up for you (Analytical_review_case_study_part_I.xls file). Use tick marks to describe any scope (you need to establish a threshold of differences from expectations requiring further follow-up), computations and explanations needed. Your analysis should include the development and documentation of analytics that consider plausible relationships between revenue with both financial and nonfinancial data. You should use formulas as appropriate to enhance the audit quality of your work. Your working papers should address the following: - Financial data: A1.1: Prepare an analysis of actual 2012 sales compared to expected sales: - At an aggregate level, develop expected sales based on an understanding of industry comparables. Document your work on working paper A6 Industry comparables. Identify if additional follow up is needed for differences from expectations that exceed your scope. - At a disaggregated level by store (document your work on working paper A2 Sales): Develop expected sales based on an understanding of industry comparables. Further modify these expected sales to reflect any company specific events. Identify if follow-up is needed for any stores where differences from expectations exceed your scope. A1.2: Prepare an analysis of the actual operating margin for 2012 compared to expected operating margins. - At an aggregate level, develop expected operating margins based on an understanding of industry comparables. Document your work on working paper A6 Industry comparables. Identify if additional follow up is needed for differences from expectations that exceed your scope. - At a disaggregated level by store (document your work on working paper A3 Operating margins): Develop expected operating margins based on an understanding of industry comparables. Further modify these expected operating margins to reflect any company-specific events. Identify if any follow-up is needed for any stores where differences from expectations exceed your scope. - Nonfinancial data: A1.3: Prepare an analysis of actual customer counts for 2012 compared to expected customer counts. - At an aggregate level, develop expectations based on an understanding of industry comparables. Document your work on working paper A6 Industry comparables. Analytical review - case study part I 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. MM4155I1 Identify if additional follow-up is needed for differences from expectations that exceed your scope. - At a disaggregated level by store (document your work on working paper A4 Customer count): Develop expectations based on an understanding of industry comparables. Further modify these expectations to reflect any company-specific events. Identify if any follow-up is needed for any stores where differences from expectations exceed your scope. A1.4: Prepare an analysis of the combination of financial and non-financial data for 2012 to expectations. - At a disaggregated level by store (document your work on working paper A5 Average sales per customer): Develop expectations based on an understanding of industry comparables. Further modify these expectations to reflect any company-specific events. Identify if any follow-up is needed for any stores where differences from expectations exceed your scope. A1.5: Produce a summary of the results of each analysis regarding items for follow up on working paper A1 Summary. A1.6 Cross-reference the numbers you use in your analysis to working paper A0 Financial and nonfinancial, as appropriate. Analytical review - case study part I 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. MM4155I1 WP No. A0 Prepared by_______ Reviewed by_______ Analytical review - case study working paper - Part I - Financial and nonfinancial 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. MM4155I1 Golden Big Burger financial and nonfinancial information Amounts in thousands GL C M PY 2012 unaudited Store location 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Total Sales $ 1,200 1,380 1,225 1,225 1,258 1,400 1,640 1,750 1,150 1,085 1,298 1,395 1,700 1,550 1,537 1,330 1,280 1,239 678 460 $ 25,780 Expenses $ 950 1,053 978 982 1,039 1,109 1,278 1,403 918 922 1,041 1,087 1,325 1,240 1,176 1,060 1,029 998 532 353 $ 20,473 Operating margin $ 250 327 247 243 219 291 362 347 232 163 257 308 375 310 361 270 251 241 146 107 $ 5,307 2011 actual Operating margin percentage 20.8% 23.7% 20.2% 19.8% 17.4% 20.8% 22.1% 19.8% 20.2% 15.0% 19.8% 22.1% 22.1% 20.0% 23.5% 20.3% 19.6% 19.5% 21.5% 23.3% 20.6% Customer count 126 136 129 129 146 146 165 186 122 124 141 140 171 163 153 140 135 129 70 49 2,700 Sales $ 1,150 1,100 1,170 1,175 1,200 1,341 1,475 1,675 1,100 1,180 1,240 1,260 1,512 1,480 1,680 1,270 1,220 1,181 $ 23,409 Expenses $ 918 875 934 925 957 1,067 1,149 1,334 875 919 997 1,020 1,150 1,151 1,327 1,012 932 920 $ 18,462 Operating margin $ 232 225 236 250 243 274 326 341 225 261 243 240 362 329 353 258 288 261 $ 4,947 2010 actual Operating margin percentage 20.2% 20.5% 20.2% 21.3% 20.3% 20.4% 22.1% 20.4% 20.5% 22.1% 19.6% 19.0% 23.9% 22.2% 21.0% 20.3% 23.6% 22.1% 21.1% Customer count 125 120 126 129 131 141 158 188 117 129 136 138 162 157 182 135 127 125 2,526 Sales $ 1,100 1,060 1,100 1,200 1,150 1,240 1,390 1,575 1,025 1,080 1,200 1,205 1,420 1,240 1,640 1,220 1,150 1,121 $ 22,116 Expenses $ 912 864 912 1,021 956 1,054 1,157 1,229 871 925 982 1,021 1,213 879 1,311 973 961 922 $ 18,163 Operating margin $ 188 196 188 179 194 186 233 346 154 155 218 184 207 361 329 247 189 199 $ 3,953 Operating margin Customer percentage count 17.1% 122 18.5% 117 17.1% 122 14.9% 136 16.9% 120 15.0% 134 16.8% 147 22.0% 177 15.0% 113 14.4% 118 18.2% 136 15.3% 132 14.6% 154 29.1% 159 20.1% 179 20.2% 133 16.4% 126 17.8% 120 17.9% 2,445 Tickmarks The schedule has been clerically tested for accuracy. PY Agreed to prior year workpapers. GL Agreed to the general ledger. C Calculated as operating margin over sales. M Agreed to internal management operations report. As part of the internal control testing at selected stores, the accuracy of the guest count information reported in management's operation report was evaluated by tracing the guest count data to register receipts. Based on this testing, the guest count data is believed to be reliable. WP No. A1 Prepared by_______ Analytical review - case study working paper - Part I - summary Reviewed by_______ 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. MM4155I1 Note: The follow-up summary is based on the results located on each corresponding tab in this workbook. Golden Big Burger follow-up summary Store location 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 A2 A3 A4 A5 Sales Operating margin Change in customer count Average sales per customer WP No. A2 Prepared by_______ Reviewed by_______ Analytical review - case study working paper - Part I - sales 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. MM4155I1 Golden Big Burger expected sales Notes 2011 actual sales Percentage Dollar difference in difference in expected sales expected sales based on based on industry industry comparables, comparables, adjusted for adjusted for company company events to events to actual actual sales in sales in 2012 2012 Dollar difference above scope? Yes or No Percentage difference above scope? Yes or No Follow-up required? Yes or No Tick mark Store location 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Total 2012 unaudited sales Expected 2012 sales based on industry comparables Expected 2012 adjusted for sales based on 2012 industry company comparables events Tick mark Results 18 hour 18 hour 18 hour 18 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour Tick marks Results If the results indicated an item is above scope in terms of dollar amount or percentage difference, from expectations, a "Yes" is indicated for further follow -up. Overall results are carried to the summary tab. WP No. A3 Prepared by_______ Reviewed by_______ Analytical review - case study working paper - Part I - operating margin 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. MM4155I1 Golden Big Burger expected operating margin Notes 2011 operating margin percentage Expected sales based on industry comparables, adjusted for 2012 company events Expected operating margin dollars in 2012 Actual unaudited operating margin dollars in 2012 Percentage difference in expected Dollar operating difference in margin actual percentage in unaudited 2012 2012 compared to operating actual Dollar Percentage margin to unaudited difference difference expected percentage in above scope? above scope? margin 2012 Yes or No Yes or No 18 hour 18 hour 18 hour 18 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour Tick marks Results If the results indicated an item is above scope in terms of dollar amount or percentage difference from expectations , a "Yes" is indicated for further follow -up. Overall results are carried to the summary tab. Follow-up required? Yes or No Tick mark Store location 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Total 2012 actual unaudited operating margin percentage Expected operating margin Expected percentage for operating 2012 based on margin industry percentage for comparable, 2012 based on adjusted for industry 2012 company comparable events Tick mark Results WP No. A4 Prepared by_______ Reviewed by_______ Analytical review - case study working paper - Part I - customer counts 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE SCORENo. No. MM4155I1 MM4155I1 Golden Big Burger expected customer count Notes 2012 customer count 2011 customer count Percentage Difference in difference in expected and actual versus Percentage actual expected difference customer customer above scope? count count Yes or No Tick mark Store location 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Total Expected customer count based on industry comparables Expected customer count based on industry comparables, adjusted for company events in 2012 Tick mark Results 18 hour 18 hour 18 hour 18 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour Tick marks Results If the results indicated an item is above scope in terms of percentage difference from expectations, a "Yes" is indicated for further follow -up. Overall results are carried to the summary tab. WP No. A5 Prepared by_______ Reviewed by_______ Analytical review - case study working paper - Part I - average sales per customer 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. MM4155I1 Golden Big Burger expected average sales per customer Notes Percentage difference in 2012 2012 actual average average sales sales per per customer to customer expected above average sales scope? Yes per customer or No Tick mark Store location 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 2012 average sales per customer Expected 2012 average sales per customer per industry comparable, adjusted for company specific events Tick mark Results 18 hour 18 hour 18 hour 18 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour 24 hour Tick marks Results If the results indicated an item is above scope in terms of the percentage difference,from expectations , a "Yes" is indicated for further follow -up. Overall results are carried to the summary tab. WP No. A6 Prepared by_______ Reviewed by_______ Analytical review - case study working paper - Part I - industry comparable 2013 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. MM4155I1 Golden Big Burger industry comparable with McDonald's Sales Same store sales Increase in same store from prior year Percentage increase in same-store sales MCD sales increase Difference Operating margins Operating margins MCD operating margins Difference Customer counts Percentage increase in same-store customer counts MCD percentage increase in same-store customer counts Difference Tick marks 2012 2011 2010