As part of the annual audit of Coliseum Entertainments 20X5 financial statements, the engagement audit partner has encouraged you a third-year associate auditor to develop a substantive analytical procedure to test Coliseums 20X5 Ticket Sales Revenue account for the Reel Wheel attraction. After carefully reviewing the appropriate auditing standards, you approach the appropriate personnel at Coliseum and request the data needed to create your data analytic.

Appendix A of this case contains a copy of the requested data related to ticket sales revenue generated by the Reel Wheel during 20X5. Use Excel to create analytics showing your solutions to the following three questions. Use separate tabs to show your work in analyzing each question.

2.1 In order to better understand whether the number of total passengers in 20X5 (as reported by Coliseum) is reasonable, develop a quick estimate of the maximum number of passengers possible during 20X5. In other words, how many passengers could ride on the Reel Wheel if every passenger car was filled to capacity for the entire year? In addition to documenting maximum capacity, show how many passengers would have experienced the Reel Wheel if it ran at 75%, 50%, and 25% of maximum capacity. Refer to the client data you requested, and document the percentage of total capacity that the clients reported number represents. Based upon your capacity calculations, does the number of reported passengers seem reasonable?

2.2 Develop an independent estimate of Ticket Sales Revenue using just the highly aggregated data available in the case (e.g., Total Passengers, Average Ticket Price). Evaluate this independent estimate by comparing it to the client's reported account balance. Determine if the reported balance is within a tolerable range (i.e., the clients reported amount plus or minus the tolerable error specified in the case) of your independent estimate using this aggregated data approach.

2.3 Using all of the disaggregated data provided by the client, develop an audit work paper to calculate the most precise estimate you can for Ticket Sales Revenue in 20X5 at the Reel Wheel. Compare this revised estimate using disaggregated data with the clients reported account balance and determine (1) whether or not the clients amount appears to be materially misstated and (2) what, if any, additional audit work is required to test the account balance.

20X5 Unaudited Account Balance (Ticket Sales Revenue) Notes - Where noted, data is verified at multiple sources (e.g., PBC, RW). - The Reel Wheel sells some tickets to wholesalers - such as third-party travel websites - if booked as part of a travel package that includes a stay at a Coliseum hotel. - Although not specifically listed on the RW website, prices for Las Vegas locals (Photo ID required) are 40% off of Regular Adult (Day, Night) prices. No summer discount for locals. Coliseum Entertainment Substantive Analytical Procedure - Ticket Sales Revenue: The Reel Wheel Year-end Audit: December 31, 20X5 20X5 Ticket Sales Mix (\%) for "The Reel Wheel" Tickmark Legend PBC Provided by client (Coliseum Entertainment) 20X5 Unaudited Account Balance (Ticket Sales Revenue) Notes - Where noted, data is verified at multiple sources (e.g., PBC, RW). - The Reel Wheel sells some tickets to wholesalers - such as third-party travel websites - if booked as part of a travel package that includes a stay at a Coliseum hotel. - Although not specifically listed on the RW website, prices for Las Vegas locals (Photo ID required) are 40% off of Regular Adult (Day, Night) prices. No summer discount for locals. Coliseum Entertainment Substantive Analytical Procedure - Ticket Sales Revenue: The Reel Wheel Year-end Audit: December 31, 20X5 20X5 Ticket Sales Mix (\%) for "The Reel Wheel" Tickmark Legend PBC Provided by client (Coliseum Entertainment)