b. Calculate the difference between your expectation and the client's recorded amount as a percentage using the formula (expected value-recorded amount)/expected value. 7-37 (Objective 7-5 ) Data Analytics As the in-charge senior auditor on the audit engagement for JA Tire Manufacturing for the year ended December 31, 2023, you are responsible for performing risk assessment procedures related to the sales cycle. JA Tire has four sales divisions within the U.S. and sells primarily to large tire companies with regional warehouses that subsequently distribute to local retailers. Based on some of the risk assessment procedures already performed, you identified risks related to the fact that salespersons receive a commission on sales to distributors and the commission is calculated on a monthly basis. Your manager has asked you to perform analytical procedures as a part of audit planning to review sales information by sales division and by month to identify potential risk areas that might warrant further audit procedures related to sales. Required 1. Visit the textbook website at pearsonhighered.com/arens to download the file "JATireSales.xIs" provided to your audit firm by the company. This file contains sales transaction information for the year ended December 31, 2023. If you have not already done so, read the JA Tire Manufacturing system description provided on the first tab of the Excel file before attempting this assignment to familiarize yourself with the sales process and the relevant worksheets and terminology. Using the "Invoices" tab for sales information (an invoice is generated to bill for the goods when they are shipped) and the "Sales Order" tab for sales division information in the Excel spreadsheet, perform the procedures in requirements 2 through 4 using either Excel, Tableau, or audit software such as ACL or IDEA. 2. Summarize sales by month and sales division using a pivot table in Excel (hint: put divisions in columns, months in rows) or using a graph or chart in Tableau. If you are using Excel to summarize, you will first need to add a column to the Excel worksheet and extract the "month" portion of the shipping date in order to summarize by month. If you are using Excel, use the "graph" feature to graph the pivot table data. Identify any divisions or months that you believe should be examined further, and document your rationale. 3. For any divisions identified in step 2 for follow-up, summarize sales by customer and month using a pivot table in Excel or graph or chart in Tableau. You can use a "filter" in Excel or Tableau to limit the analysis to one or more divisions. Identify any months that you want to examine further, and document your rationale 4. Summarize your findings in a brief memo to the manager and include the charts or graphs generated in steps 2 and 3 above as support for your findings. Discuss your recommendations for performing further audit procedures.Calibri (Body) |11 A" A B I U ab D v d. Y Av Aa =| Wrap Merge v General v P1 V X V Fx D E F G H M O P O R S B C Ship to Address Ship to Address Ship to Address Ship to Ship to Address Zip Customer Customer Purchase 1 Order Number Product Number Price Order Quantity Order Amount Sales Order Date Number Street City Address State Code Sales Division Voided Sales Order Number Number 00000245 1024 00000926524 ASALL1002 $ 52.42 613 $ 346,653.46 1/1/2023 5432 Eastman Ave Scottsdale AZ 35259 SW 700000246 1017 9563135 TRUALL1001 $ 99.70 4901 $ 488,629.70 1/1/2023 54497 Washington Atlanta GA 30301 SE IA C N 700000247 1022 85643120 LIGALL1003 $ 80.44 1291 $ 103,848.04 1/1/2023 34816 Jefferson Ave Santa Maria CA 93455 SW 700000248 1024 00000926542 TRUALL1001 $100.32 3865 $ 387,736.80 1/1/2023 5432 Eastman Ave Scottsdale AZ 85259 SW 14534 NE 700000249 1027 15489000264 TRUALL1001 $100.43 19802 $ 1,988,714.86 1/1/2023 511 Patrick Rd Pittsford NY NV 89002 SW 700000250 1023 002356872 PASALL1002 50.51 12260 $ 619,252.60 1/1/2023 94911 Swede Ave Henderson AZ 85259 SW 8 700000251 1024 00000926560 PASWIN1006 56.22 6259 $ 351,880.98 1/1/2023 5432 Eastman Ave Scottsdale 700000252 1018 70465244 LIGALL1003 78.57 1057 $ 83,048.49 1/1/2023 34461 Meadowbrook dr Montgomery AL 36043 SE 700000253 1013 100000002524 TRUALL1001 $100.59 747 $ 75,140.73 1/1/2023 12765 Concord Madison WI 53558 MW 10 11 700000254 1013 100000002526 PASALL1002 $ 50.75 1773 $ 89,979.75 1/1/2023 12765 Concord Madison WI 53558 MW 12 700000255 1022 85643280 PASALL1002 $ 51.49 1712 $ 88,150.88 1/2/2023 34816 Jefferson Ave Santa Maria CA 93455 SW 13 700000256 1021 AS168953180 LIGALL1003 $ 79.15 4194 $ 331,955.10 1/2/2023 10969 Dartmouth Dr Fresno CA 93710 SW 14 700000257 1023 0023568128 PASALL1002 $ 50.58 9546 $ 482,836.68 1/2/2023 94911 Swede Ave Henderson NV 89002 SW 15 700000258 1014 8462001251 PASALL1002 $ 49.05 4470 $ 219,253.50 1/2/2023 58918 Elm Ct Royal Oak MI 48067 MW 16 700000259 1017 95631126 TRUALL1001 $ 99.46 5069 $ 504,162.74 1/2/2023 54497 Washington Atlanta GA 30301 SE 17 700000260 1017 95631133 PASALL100 $ 49.42 9400 $ 464,548.00 1/2/2023 54497 Washington Atlanta GA 30301 SE 18 700000261 1021 AS168953240 TRUALL1001 $100.73 3559 $ 358,498.07 1/2/2023 10969 Dartmouth Dr Fresno CA 93710 SW 32746 NE 19 700000262 1012 DB148168 PASWIN1006 55.15 2596 $ 143,169.40 1/2/2023 5467 Wendy Lane Lake Mary FL 20 1021 AS168953264 TRUALL1001 $ 98.80 3539 $ 349,653.20 1/2/2023 10969 Dartmouth Dr Fresno CA 93710 SW 700000263 OH 45839 MW 21 700000264 1015 125115 PASALL1002 $ 49.70 425 $ 21,122.50 1/3/2023 3089 Sugnet Findlay 22 700000265 1015 125120 PASALL1002 $ 51.65 333 $ 17,199.45 1/3/2023 3089 Sugnet Findlay OH 45839 MW 23 700000266 1024 000009265150 LIGALL1003 $ 81.01 4028 $ 326,308.28 1/3/2023 5432 Eastman Ave Scottsdale AZ 85259 SW OH 43054 MW 24 700000267 1016 8500023156 PASWIN1006 $ 55.20 6562 $ 362,222.40 1/3/2023 68446 Wheeler Columbus 25 700000268 1013 100000002554 TRUALL1001 $ 99.45 731 $ 72,697.95 1/3/2023 12765 Concord Madison WI 53558 MW 26 700000269 1022 85643560 LIGWIN1007 $ 86.35 887 $ 76,592.45 1/3/2023 34816 Jefferson Ave Santa Maria CA 93455 SW 27 700000270 1023 0023568232 PASWIN1006 $ 54.84 9362 $ 513,412.08 1/3/2023 94911 Swede Ave Henderson NV 89002 SW MI 49504 MW 28 700000271 1011 1025FG150 LIGALL1003 $ 79.12 1352 $ 106,970.24 1/3/2023 6212 Lexington Ct Grand Rapids AL 36043 SE 29 700000272 1018 704652124 TRUALL1001 $ 99.54 889 $ 88,491.06 1/3/2023 34461 Meadowbrook dr Montgomery WI 53558 MW 30 700000273 1013 100000002564 PASALL1002 $ 50.23 1877 $ 94,281.71 1/3/2023 12765 Concord Madison TRUWIN1005 $110.43 174 $ 19,214.82 1/3/2023 3089 Sugnet Findlay OH 45839 MW 31 700000274 1015 125165 MI 48067 MW 32 700000275 1014 84620012102 PASALL1002 $ 49.82 4202 $ 209,343.64 1/4/2023 58918 Elm Ct Royal OakAction Required: Ve. subscription Shared drives - GOO... Addons Store mazon.com ebay Walmart.com f Facebook You Tube P Priceline.com B. Booking.com Sign U.. ADP P ParkChicago Fleet -. Elevating the Educa.. Tracke Addons Store Search for tools, help, and more (Alt + Q) addons.securebrowser.com taset_18th 1 - original Cov Comments " Catch up File Home Insert Share Page Layout Formulas Data Review View Help Draw Calibri (Body) |11 v A" A" B I U ab D v dov Av Aa =v Wrap Merge v General v Q9 B C D E G H K M N P R S 1 Invoice Number Shipment Date Invoice Date Payment Due Date Customer Number Product Number Quantity Price Amount Bill of Lading Number Sales Order Number 135693 1/6/2023 1/7/2023 2/6/2023 1024 PASALL1002 6613 $ 52.42 $ 346,653.46 90395402854 700000245 CO N 135694 1/6/2023 1/7/2023 2/6/2023 1017 TRUALL1001 4901 $ 99.70 $ 488,629.70 90395402855 700000246 135695 1/6/2023 1/7/2023 2/6/2023 1022 LIGALL1003 1291 $ 80.44 $ 103,848.04 90395402856 700000247 135696 1/6/2023 1/7/2023 2/6/2023 1024 TRUALL1001 3865 $100.32 $ 387,736.80 90395402857 700000248 135697 1/6/2023 1/7/2023 2/6/2023 1027 TRUALL1001 19802 $100.43 $ 1,988,714.86 90395402858 700000249 135698 1/6/2023 1/7/2023 2/6/2023 1023 PASALL1002 12260 $ 50.51 $ 619,252.60 90395402859 700000250 135699 1/6/2023 1/7/2023 2/6/2023 1024 PASWIN1006 6259 $ 56.22 $ 351,880.98 90395402860 700000251 135700 1/6/2023 1/7/2023 2/6/2023 1018 LIGALL1003 1057 $ 78.57 $ 83,048.49 90395402861 700000252 10 135701 1/6/2023 1/7/2023 2/6/2023 1013 TRUALL1001 747 $100.59 75,140.73 90395402862 700000253 11 135702 1/6/2023 1/7/2023 2/6/2023 1013 PASALL1002 1773 $ 50.75 $ 89,979.75 90395402863 700000254 12 135703 1/7/2023 1/8/2023 2/7/2023 1022 PASALL1002 1712 $ 51.49 $ 88,150.88 90395402864 700000255 13 135704 1/7/2023 1/8/2023 2/7/2023 1021 LIGALL1003 4194 $ 79.15 $ 331,955.10 90395402865 700000256 14 135705 1/7/2023 1/8/2023 2/7/2023 1023 PASALL1002 9546 $ 50.58 $ 482,836.68 90395402866 700000257 15 135706 1/7/2023 1/7/2023 2/6/2023 1014 PASALL1002 4470 $ 49.05 $ 219,253.50 90395402867 700000258 16 135707 1/7/2023 1/8/2023 2/7/2023 1017 TRUALL1001 5069 $ 99.46 $ 504,162.74 90395402868 700000259 17 135708 1/8/2023 1/9/2023 2/8/2023 1017 PASALL1002 9400 $ 49.42 $ 464,548.00 90395402869 700000260 18 135709 1/7/2023 1/8/2023 2/7/2023 1021 TRUALL1001 3559 $100.73 358,498.07 90395402870 700000261 19 135710 1/7/2023 1/7/2023 2/6/2023 1012 PASWIN1006 2596 $ 55.15 $ 143,169.40 90395402871 700000262 20 135711 1/7/2023 1/8/2023 2/7/2023 1021 TRUALL1001 3539 $ 98.80 $ 349,653.20 90395402872 700000263 21 135712 1/8/2023 1/9/2023 2/8/2023 1015 PASALL1002 425 $ 49.70 $ 21,122.50 90395402873 700000264 22 135713 1/8/2023 1/10/2023 2/9/2023 1015 PASALL1002 333 $ 51.65 $ 17,199.45 90395402874 700000265 23 135714 1/8/2023 1/9/2023 2/8/2023 1024 LIGALL1003 4028 $ 81.01 $ 326,308.28 90395402875 700000266 24 135715 1/8/2023 1/9/2023 2/8/2023 1016 PASWIN1006 6562 $ 55.20 $ 362,222.40 90395402876 700000267 25 135716 1/8/2023 1/9/2023 2/8/2023 1013 TRUALL1001 731 $ 99.45 $ 72,697.95 90395402877 700000268 26 135717 1/8/2023 1/9/2023 2/8/2023 1022 LIGWIN1007 887 $ 86.35 $ 76,592.45 90395402878 700000269 27 135718 1/8/2023 1/9/2023 2/8/2023 1023 PASWIN1006 9362 $ 54.84 $ 513,412.08 90395402879 700000270 28 135719 1/8/2023 1/8/2023 2/7/2023 1011 LIGALL1003 1352 $ 79.12 $ 106,970.24 90395402880 700000271 29 135720 1/8/2023 1/8/2023 2/7/2023 1018 TRUALL1001 889 $ 99.54 $ 88,491.06 90395402881 700000272 30 135721 1/8/2023 1/8/2023 2/7/2023 1013 PASALL1002 1877 $ 50.23 $ 94,281.71 90395402882 700000273 31 135722 1/8/2023 1/9/2023 2/8/2023 1015 TRUWIN1005 174 $110.43 $ 19,214.82 90395402883 700000274 32 135723 1/9/2023 1/9/2023 2/8/2023 1014 PASALL1002 4202 $ 49.82 $ 209,343.64 90395402884 700000275 33 135724 1/9/2023 1/10/2023 2/9/2023 1013 LIGALL1004 1020 $ 85.76 $ 87,475.20 90395402885 700000276 Sales System Description Sales Order Bill of Lading Invoice Cash Receipts Customer List Product List +eBay K Walmart.com f Facebook YouTube P Priceline.com B. Booking.com .. . My tasks - Sign u.. M Action Required: Ve... ( subscription ? ADP P ParkChicago Fleet -... Elevating the Educa.. Shared drives - Goo... [ Tracker Addons Store a Amazon.com X JA Tires Dataset_18th 1 - original Cov Search for tools, help, and more (Alt + Q) Comments " Catch up Editing v File Home Insert Share Page Layout Formulas Data Review View Help Draw v Calibri (Body) v 11 A" A" B I U ab D v dov Av Aa EVER Wrap Merge General G35 X V fx M N O P Q R S B D G H Delivery Delivery Address Billing Address Billing Address Billing Billing Address Billing Address Sales 1 Customer Delivery Address Delivery Address Delivery Address State Zip Code Number Stree Address City State Zip Code Division Number Customer Name Number Stree Address City 49504 7158 Winter Ave Royal Oak MI 48073 MW 2 1011 Bob's Tire Shack 6212 Lexington C Grand Rapids MI Woodward Ave Orlando -L 32789 NE 32746 4184 3 1012 Budget Wheels 5467 Wendy Lane Lake Mary FL 12765 Concord Madison WI 53558 1679 Sugnet St. Milwaukee WI 53712 MW 4 1013 Tire Center MW Michael's Car Care Center 58918 Elm Ct Royal Oak MI 48067 8079 Division Ave. Royal Oak MI 48067 5 1014 OH 45839 MW 6 1015 Best Year Tires 3089 Sugnet Findlay OH 45839 990 Wealthy Findlay MW Big O Wheels 68446 Wheeler Columbus OH 43054 6483 Butterworth St. Columbus OH 43054 7 1016 GA 30301 3176 Valley Ave. Atlanta GA 30301 SE 8 1017 Joe's Tires & Auto Service 54497 Washington Atlanta 34461 Meadowbrook dr Montgomery AL 36043 1440 Fulton W Montgomery AL 36101 SE 9 1018 Wheel Kingdom 33018 519 Veto St. NW Miami FL 33127 SE 79661 Miami FL 10 1019 Chris' Tire & wheel St Andrews 45462 Ashman Columbus GA 31907 853 Park St. Columbus GA 31997 SE 11 1020 Georgia Tire Company SW Mr. Wheels Auto Service Center 10969 Dartmouth Dr Fresno CA 93710 7621 Sibley St. E Fresno CA 93721 12 1021 SW 34816 Jefferson Ave Santa Maria CA 93455 954 Monroe Ave Santa Maria CA 9345 13 1022 Green Tire Store Swede Ave Henderson NV 89002 5039 Ottawa Ave Henderson NV 89044 SW 14 1023 Cool Boys 94911 Eastman Ave Scottsdale AZ 85259 6736 9th St. Scottsdale AZ 85267 SW 15 1024 Midtown Tire 5432 58961 Orchard Dr Newark NJ 07105 7977 Richmond St. NW Newark NJ 07198 NE 16 1025 Wheels Plus 06023 452 Emerson Ave Middletown CN 06459 NE 17 1026 Lake View Tire & Auto Service 42798 Main St. Middletown CN NY 14534 NE 18 1027 New York Tire Depot 511 Patrick Rd Pittsford NY 14534 245 Derby Dr. Pittsford 19 20 21 22 23 24 25 26 27X JA Tires Dataset_18th 1 - original C ~ Search for tools, help, and more (Alt + Q) File Home Insert Share Page Layout Formulas Data Review View Help Draw Calibri (Body) v 11 v AABIUab DY dov AVAa E v Wrap Merge v General v J36 X V fx A B C D E F G H J K L M N O 1 Product Number Product Description Weight Weight Measure Volume Volume Measure 2 TRUALL1001 Truck All Weather Tire 10 lb 4 FT^3 3 PASALL1002 Passenger All Weather Tire 6 1b 2 FT^3 4 LIGALL1003 Light SUV All Weather Tire 8 lb 3 FT^3 5 LIGALL1004 All Terrain SUV 8 lb 3 FT^3 6 TRUWIN1005 Truck Winter Tire 10 lb 4 FT^3 7 PASWIN1006 Passenger Winter Tire 6 1b 2 FT^3 8 LIGWIN1007 Light SUV Winter Tire 3 lb 3 FT^3 9 MOTOCY1008 Motorcyle Tires 4 lb 2 FT^3 10 TRUSUM1009 Truck Summer Tire 10 lb 4 FT13 11 PASSUM1010 Passenger Summer Tire 6 1b 2 FT^3 12 LIGSUM1011 Light SUV Summer Tire 8 lb 3 FT^3 13 14 15 16 17JA Tires Dataset_18th 1 - original v O Search for tools, help, and more File Home Insert Share Page Layout Formulas Data Review \\View Help Draw 0O~ & Calibri(Body) ~| 11 ~ A" A B 1 U g D v v &~ A | =~ = Wrap & Merge v | Date v B g 08 13975 v fe A B C D E F G H | J K 3975 40006618 019 2/2/2024 2/2/2024 161,438.77 39664 | ! 3976 40006619 013 2/3/2024 2/3/2024 & 92,026.25 29665 3977 40006620 1023 2/2/2024 2/1/2024 $ 533,346.02 139666 3978 40006621 017 2/2/2024 2/2/2024 477,493.38 39667 3979 40006622 026 2/2/2024 2/1/2024 537,047.90 29662 3980 40006623 1016 2/2/2024 2/2/2024 $ 397,089.82 139669 3981 40006624 023 2/2/2024 2/2/2024 494,761.88 39670 3982 40006625 026 2/3/2024 2/3/2024 607,992.25 29671 3983 40006626 1023 2/2/2024 2/2/2024 440,378.20 139672 3984 40006627 013 2/2/2024 2/1/2024 76,327.40 29673 3985 40006628 014 2/2/2024 2/2/2024 191,427.84 29674 3986 40006629 1013 2/2/2024 2/2/2024 83,878.55 139675 3987 40006630 026 2/3/2024 2/3/2024 513,429.84 29676 3988 40006631 015 2/3/2024 2/3/2024 & 17,727.24 29677 3989 40006632 013 2/3/2024 2/3/2024 5 53,546.00 39678 3990 40006633 013 2/2/2024 2/2/2024 89,121.90 19679 3991 40006634 026 2/3/2024 2/3/2024 532,371.40 29620 3992 40006635 013 2/4/2024 2/4/2024 5 75,022.98 396581 3993 40006636 019 2/3/2024 2/3/2024 199,459.49 19682 3994 40006637 023 2/3/2024 2/3/2024 455,416.92 29623 3995 40006638 017 2/3/2024 2/3/2024 & 486,045.94 39634 39965 40006639 015 2/3/2024 2/3/2024 16,411.50 19685 3997 40006640 026 2/3/2024 2/3/2024 628,720.65 29635 3998 40006641 013 2/4/2024 2/4/2024 87,532.66 39687 3999 40006642 015 2/4/2024 2/3/2024 21,145.20 19688 4000 40006643 019 2/4/2024 2/4/2024 154,795.91 29629 4001 40006644 019 2/4/2024 2/4/2024 166,491.69 39650 4002 40006645 026 2/4/2024 2/4/2024 & 427,719.87 29691 4003 40006646 1027 2/3/2024 2/3/2024 $ 1,939,373.48 139692 4004 40006647 018 2/2/2024 2/2/2024 91,802.70 39693 4005 40006648 014 2/4/2024 2/4/2024 & 240,253.52 29694 4006 40006649 1013 2/4/2024 2/4/2024 $ 88,570.20 139695 4007 40006650 014 2/4/2024 2/4/2024 23531424 39696 A = Sales System Description Sales Order Bill of Lading Invoice Cash Receipts Customer List ~ Product List + 'Workbook Statistics H