Question
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
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) Account Data Point Unit Label Tickmark WP Reference Ticket Sales Revenue (unaudited) $73,468,219.00 dollars from Trial Balance Allocated tolerable difference 675,000.00 dollars AA.09 20X5 Summary Information & Data for "The Reel Wheel" Description Tickmark Data Point Unit Label WP Reference Observation Wheel Height Number of Passenger Cars Interior Space per Car Maximum Capacity per Car Time Required for Car Revolution Hours of Operation (Open) Hours of Operation (Close) Description (20X5 calendar year) 550 feet PBC, RW , RW 28 cars 225 PBC, RW square feet PBC, RW PBC, RW PBC, RW , RW 40 passengers minutes 30 11:00am 2:00am Tickmark Data Point Unit Label WP Reference Days in Operation Total Passengers Total Children Passengers 361 days from A.02 A.02, A.03 3,058,452 250,389 passengers passengers Description (20x5 calendar year) Tickmark Data Point Unit Label WP Reference Regular Ticket Price (Child; Age 0-12) Regular Ticket Price - Day (Adult; Age 13+) Regular Ticket Price - Night (Adult; Age 13+) Summer Ticket Price Day (Adult; Age 13+) Summer Ticket Price - Night (Adult; Age 13+) $ dollars RW 24.95 dollars RW 34.95 RW dollars 19.95 RW dollars 29.95 dollars RW 12.95 WholesaleTicket Price dollars 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. Workpaper (WP) ID Coliseum Entertainment A.02 Substantive Analytical Procedure Ticket Sales Revenue: The Reel Wheel Year-end Audit: December 31, 20x5 Date: Prepared By: 20X5 Ticket Sales Data for "The Reel Wheel" [Provided by Client] Total Passengers Unit Label Tickmark WP Reference January 245,163 people February 228,455 ople March 261,996 people Q1 20X5 735,614 people April 255,898 people May 270,268 eople 256,758 June people Q2 20X5 782,924 people July 270,000 ople August 260,209 people September 256,207 people Q3 20X5 786,416 people October 273,523 eople November 246,589 people December 233,385 ople 753,497 ople Q4 20X5 20x5 Total 3,058,451 PBC people to A.01 Tickmark Legend Provided by client (Coliseum Entertainment) A.03 Colisum Entertainment Workpaper (WP) ID: Substantive Analytical Procedure Ticket Sales Revenue: The Reel Wheel Date: Prepared By: Year-end Audit: December 31, 20X5 20X5 Ticket Sales Mix (%) for "The Reel Wheel" [Provided by Client] Tickmark Children (0 12) Adults (13+) Locals Wholesale Night Night Night Night Day Day Day Day 4.5% 5.2% January February March 4.2% 3.3% 22.4% 43.8% 8.7% 7.9% 41.6% 5.0% 8.5% 4.7% 3.1% 22.8% 4.9% 9.4% 5.8% 4.1% 3.3% 23.1% 43.6% 4.0% 7.8% 8.3% 4.5% 5.3% 100.0% Q1 4.3% 3.2% 22.8% 43.0% 8.6% 8.2% April ay 5.1% 4.3% 3.8% 23.9% 41.2% 4.7% 9.0% 8.0% 4.6% 3.0% 24.1% 44.2% 4.3% 8.9% 4.2% 6.7% June 4.9% 4.3% 23.6% 44.7% 4.6% 9.3% 3.7% 4.9% 43.4% 4.5% 6.5% Q2 4.6% 3.7% 23.9% 9.1% 4.3% 100.0% July 5.2% 4.9% 4.0% 22.2% 41.2% 10.4% 4.9% 7.2% August September Q3 5.9% 4.9% 4.1% 21.6% 43.3% 4.8% 11.0% 4.4% 22.9% 22.2% 42.6% 5.3% 4.1% 3.4% 4.6% 10.4% 6.7% 10.6 42.4% 4.6% 3.8% 4.9% 4.9% 6.6% 100.0% October 4.5% 43.2% 3.7% 23.1% 4.1% 9.7% 4.9% 6.8% November 4.5% 5.0% 4.1% 23.7% 42.9% 4.0% 8.8% 7.0% December 23.0% 23.3% 42.5% 5.4% 4.3% 4.1% 4.2% 8.4% 8.1% 42.9% 5.1% 100.0% Q4 4.4% 4.0% 4.1% 9.0% 7.3% Tickmark Legend Provided by client (Coliseum Entertainment) 20X5 Unaudited Account Balance (Ticket Sales Revenue) Account Data Point Unit Label Tickmark WP Reference Ticket Sales Revenue (unaudited) $73,468,219.00 dollars from Trial Balance Allocated tolerable difference 675,000.00 dollars AA.09 20X5 Summary Information & Data for "The Reel Wheel" Description Tickmark Data Point Unit Label WP Reference Observation Wheel Height Number of Passenger Cars Interior Space per Car Maximum Capacity per Car Time Required for Car Revolution Hours of Operation (Open) Hours of Operation (Close) Description (20X5 calendar year) 550 feet PBC, RW , RW 28 cars 225 PBC, RW square feet PBC, RW PBC, RW PBC, RW , RW 40 passengers minutes 30 11:00am 2:00am Tickmark Data Point Unit Label WP Reference Days in Operation Total Passengers Total Children Passengers 361 days from A.02 A.02, A.03 3,058,452 250,389 passengers passengers Description (20x5 calendar year) Tickmark Data Point Unit Label WP Reference Regular Ticket Price (Child; Age 0-12) Regular Ticket Price - Day (Adult; Age 13+) Regular Ticket Price - Night (Adult; Age 13+) Summer Ticket Price Day (Adult; Age 13+) Summer Ticket Price - Night (Adult; Age 13+) $ dollars RW 24.95 dollars RW 34.95 RW dollars 19.95 RW dollars 29.95 dollars RW 12.95 WholesaleTicket Price dollars 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. Workpaper (WP) ID Coliseum Entertainment A.02 Substantive Analytical Procedure Ticket Sales Revenue: The Reel Wheel Year-end Audit: December 31, 20x5 Date: Prepared By: 20X5 Ticket Sales Data for "The Reel Wheel" [Provided by Client] Total Passengers Unit Label Tickmark WP Reference January 245,163 people February 228,455 ople March 261,996 people Q1 20X5 735,614 people April 255,898 people May 270,268 eople 256,758 June people Q2 20X5 782,924 people July 270,000 ople August 260,209 people September 256,207 people Q3 20X5 786,416 people October 273,523 eople November 246,589 people December 233,385 ople 753,497 ople Q4 20X5 20x5 Total 3,058,451 PBC people to A.01 Tickmark Legend Provided by client (Coliseum Entertainment) A.03 Colisum Entertainment Workpaper (WP) ID: Substantive Analytical Procedure Ticket Sales Revenue: The Reel Wheel Date: Prepared By: Year-end Audit: December 31, 20X5 20X5 Ticket Sales Mix (%) for "The Reel Wheel" [Provided by Client] Tickmark Children (0 12) Adults (13+) Locals Wholesale Night Night Night Night Day Day Day Day 4.5% 5.2% January February March 4.2% 3.3% 22.4% 43.8% 8.7% 7.9% 41.6% 5.0% 8.5% 4.7% 3.1% 22.8% 4.9% 9.4% 5.8% 4.1% 3.3% 23.1% 43.6% 4.0% 7.8% 8.3% 4.5% 5.3% 100.0% Q1 4.3% 3.2% 22.8% 43.0% 8.6% 8.2% April ay 5.1% 4.3% 3.8% 23.9% 41.2% 4.7% 9.0% 8.0% 4.6% 3.0% 24.1% 44.2% 4.3% 8.9% 4.2% 6.7% June 4.9% 4.3% 23.6% 44.7% 4.6% 9.3% 3.7% 4.9% 43.4% 4.5% 6.5% Q2 4.6% 3.7% 23.9% 9.1% 4.3% 100.0% July 5.2% 4.9% 4.0% 22.2% 41.2% 10.4% 4.9% 7.2% August September Q3 5.9% 4.9% 4.1% 21.6% 43.3% 4.8% 11.0% 4.4% 22.9% 22.2% 42.6% 5.3% 4.1% 3.4% 4.6% 10.4% 6.7% 10.6 42.4% 4.6% 3.8% 4.9% 4.9% 6.6% 100.0% October 4.5% 43.2% 3.7% 23.1% 4.1% 9.7% 4.9% 6.8% November 4.5% 5.0% 4.1% 23.7% 42.9% 4.0% 8.8% 7.0% December 23.0% 23.3% 42.5% 5.4% 4.3% 4.1% 4.2% 8.4% 8.1% 42.9% 5.1% 100.0% Q4 4.4% 4.0% 4.1% 9.0% 7.3% Tickmark Legend Provided by client (Coliseum Entertainment)Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started