Data Analytics 7-38 (OBJECTIVE 7-5) As the in-charge senior auditor on the audit engagement for JA Tire Manufacturing for the year ended December 31, 2019, you are responsible for perform- ing 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 com- mission 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 re- view sales information by sales division and by month to identify potential risk areas that might warrant further audit procedures related to sales. 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. Summary of Sales Orders x Division x Customer Sales Order Table 4 Summary of Sales Orders x Division x I Customer CUSTOMERS.TOTAL SALES DIVISIONS NE SE SW 2019-01 2019-02 2019-03 2019-04 2019-05 2019-06 2019-07 MW 17,138,716.76 14,760,223.29 18,665,225.52 16,390,985.05 19,109,105.33 15,283,426.38 17,457,301.95 19,375,718.55 18,457,941.76 18,054,205.87 18,952,135.38 22,099,690.60 3,543,838.00 219,288,514.44 20,767,531.56 32,057,895.55 34,041,115.71 44,770,652.43 27,541,424.47 29,829,170.08 34,906,950.78 36,528,669.04 29,098,767.29 29,005,275.58 50,065,592.65 50,373,054.49 9,195,832.57 428,181,932.20 MONTHS 13,788,945.54 16,287,252.42 22,578,477.32 17,756,238.99 20,798,629.62 17,527,471.04 22,000,455.02 16,881,111.69 20,624,892.33 22,849,663.00 16,243,116.66 19,801,689.03 3,186,504.05 230,324,446.71 TOTAL 74,037,052.23 89,552,800.11 91,029,930.09 99,518,702.25 88,544,981.12 82,928,910.09 97,938,071.19 93,757,317.11 85,377,252.90 93,192,632.47 100,522,182.44 110,904,367.11 18,733,994.65 1,126,038,193.76 22,341,858.37 26,447,428.85 15,745,111.54 20,600,825.78 21,095,821.70 20,288,842.59 23,573,363.44 20,971,817.83 17,195,651.52 23,283,488.02 15,261,337.75 18,629,932.99 2,807,820.03 248,243,300.41 2019-08 2019-09 2019-10 2019-11 2019-12 2020-01 TOTAL Summary of Sales Orders x Division x Customer Sales Order Table 4 Summary of Sales Orders x Division x Customer - Chart1 x c Summary of Sales Invoices x Division x Customer - Chart1 Summary of Sales Inv 55,000,000.00 50,000,000.00 45,000,000.00 40,000,000.00 35,000,000.00 30,000,000.00 25,000,000.00 20,000,000.00 15,000,000.00 MONTHS. 2019-01 MONTHS.2019-02 MONTHS. 2019-03 MONTHS.2019-04 MONTHS. 2019-05 MONTHS.2019-06 MONTHS. 2019-07 MONTHS.2019-08 MONTHS. 2019-09 MONTHS.2019-10 MONTHS. 2019-11 MONTHS.2019-12 SALES DIVISIONS.MW SALES DIVISIONS.NE SALES DIVISIONS.SE SALES DIVISIONS.SW SALES DIVISIONS MW NE SE SW 2019-01 2019-02 2019-03 2019-04 2019-05 2019-06 MONTHS 20,863,837.23 13,997,949.39 18,906,201.78 15,870,184.94 19,832,147.86 15,027,029.21 17,388,743.46 19,788,935.86 18,776,049.74 17,459,864.41 19,206,960.89 21,857,255.00 27,324,621.50 32,834,970.85 31,269,262.52 46,499,939.45 26,933,575.73 35,308,931.58 29,689,761.06 37,565,834.74 25,154,761.00 28,988, 181.68 51,702,711.96 55,066,349.61 15,790,867.18 17,797,883.46 20,466,018.78 19,877,186.14 20,158,700.31 16,455,170.39 22,449,425.44 16,517,270.24 21,444,283.37 20,904,370.98 18,042,172.53 18,633,592.43 27,225,594.36 23,533,787.68 16,789,197.93 20,572,500.55 20,947,099.93 22,743,562.58 24,485,986.03 17,299,426.68 18,596,137.87 23,452,034.07 15,122,570.12 17,974,431.25 2019-07 2019-08 2019-09 2019-10 2019-11 2019-12 Summary of Sales Orders x Division x Customer Sales Order Table d summary of Sales Orders x Division x Customer - Chart1 c Summary of Sales Invoices x Division x Customer - Chart1 x Summary of Sales Inv = 50,000,000.00 45,000,000.00 40,000,000.00 35,000,000.00 - 30,000,000.00 25,000,000.00 20,000,000.00 15,000,000.00 MONTHS. 2019-01 MONTHS.2019-02 MONTHS. 2019-03 MONTHS.2019-04 MONTHS. 2019-05 MONTHS.2019-06 MONTHS. 2019-07 MONTHS.2019-08 MONTHS. 2019-09 MONTHS.2019-10 MONTHS.2019-11 MONTHS.2019-12 SALES JS.MW SALES DIVISIONS.NE SALES DIVISIONS.SE SALES DIVISIONS.SW : SALES DIVISIONS MW NE SE SW 2019-01 2019-02 2019-03 2019-04 2019-05 2019-06 2019-07 MONTHS 17,138,716.76 14,760,223.29 18,665,225.52 16,390,985.05 19,109,105.33 15,283,426.38 17,457,301.95 19,375,718.55 18,457,941.76 18,054,205.87 18,952,135.38 22,099,690.60 20,767,531.56 32,057,895.55 34,041,115.71 44,770,652.43 27,541,424.47 29,829,170.08 34,906,950.78 36,528,669.04 29,098,767.29 29,005,275.58 50,065,592.65 50,373,054.49 13,788,945.54 16,287,252.42 22,578,477.32 17,756,238.99 20,798,629.62 17,527,471.04 22,000,455.02 16,881,111.69 20,624,892.33 22,849,663.00 16,243,116.66 19,801,689.03 22,341,858.37 26,447,428.85 15,745,111.54 20,600,825.78 21,095,821.70 20,288,842.59 23,573,363.44 20,971,817.83 17,195,651.52 23,283,488.02 15,261,337.75 18,629,932.99 2019-08 2019-09 2019-10 2019-11 2019-12 Customer CUSTOMERS.TOTAL SALES DIVISIONS SE SW 2019-01 2019-02 2019-03 2019-04 2019-05 2019-06 MW 17,138,716.76 14,760,223.29 18,665,225.52 16,390,985.05 19,109,105.33 15,283,426.38 17,457,301.95 19,375,718.55 18,457,941.76 18,054,205.87 18,952,135.38 22,099,690.60 3,543,838.00 219,288,514.44 2019-07 2019-08 NE 20,767,531.56 32,057,895.55 34,041,115.71 44,770,652.43 27,541,424.47 29,829,170.08 34,906,950.78 36,528,669.04 29,098,767.29 29,005,275.58 50,065,592.65 50,373,054.49 9,195,832.57 428,181,932.20 MONTHS 13,788,945.54 16,287,252.42 22,578,477.32 17,756,238.99 20,798,629.62 17,527,471.04 22,000,455.02 16,881,111.69 20,624,892.33 22,849,663.00 16,243,116.66 19,801,689.03 3,186,504.05 230,324,446.71 TOTAL 74,037,052.23 89,552,800.11 91,029,930.09 99,518,702.25 88,544,981.12 82,928,910.09 97,938,071.19 93,757,317.11 85,377,252.90 93,192,632.47 100,522,182.44 110,904,367.11 18,733,994.65 1,126,038,193.76 22,341,858.37 26,447,428.85 15,745,111.54 20,600,825.78 21,095,821.70 20,288,842.59 23,573,363.44 20,971,817.83 17,195,651.52 23,283,488.02 15,261,337.75 18,629,932.99 2,807,820.03 248,243,300.41 2019-09 2019-10 2019-11 2019-12 2020-01 TOTAL Data Analytics 7-38 (OBJECTIVE 7-5) As the in-charge senior auditor on the audit engagement for JA Tire Manufacturing for the year ended December 31, 2019, you are responsible for perform- ing 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 com- mission 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 re- view sales information by sales division and by month to identify potential risk areas that might warrant further audit procedures related to sales. 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. Summary of Sales Orders x Division x Customer Sales Order Table 4 Summary of Sales Orders x Division x I Customer CUSTOMERS.TOTAL SALES DIVISIONS NE SE SW 2019-01 2019-02 2019-03 2019-04 2019-05 2019-06 2019-07 MW 17,138,716.76 14,760,223.29 18,665,225.52 16,390,985.05 19,109,105.33 15,283,426.38 17,457,301.95 19,375,718.55 18,457,941.76 18,054,205.87 18,952,135.38 22,099,690.60 3,543,838.00 219,288,514.44 20,767,531.56 32,057,895.55 34,041,115.71 44,770,652.43 27,541,424.47 29,829,170.08 34,906,950.78 36,528,669.04 29,098,767.29 29,005,275.58 50,065,592.65 50,373,054.49 9,195,832.57 428,181,932.20 MONTHS 13,788,945.54 16,287,252.42 22,578,477.32 17,756,238.99 20,798,629.62 17,527,471.04 22,000,455.02 16,881,111.69 20,624,892.33 22,849,663.00 16,243,116.66 19,801,689.03 3,186,504.05 230,324,446.71 TOTAL 74,037,052.23 89,552,800.11 91,029,930.09 99,518,702.25 88,544,981.12 82,928,910.09 97,938,071.19 93,757,317.11 85,377,252.90 93,192,632.47 100,522,182.44 110,904,367.11 18,733,994.65 1,126,038,193.76 22,341,858.37 26,447,428.85 15,745,111.54 20,600,825.78 21,095,821.70 20,288,842.59 23,573,363.44 20,971,817.83 17,195,651.52 23,283,488.02 15,261,337.75 18,629,932.99 2,807,820.03 248,243,300.41 2019-08 2019-09 2019-10 2019-11 2019-12 2020-01 TOTAL Summary of Sales Orders x Division x Customer Sales Order Table 4 Summary of Sales Orders x Division x Customer - Chart1 x c Summary of Sales Invoices x Division x Customer - Chart1 Summary of Sales Inv 55,000,000.00 50,000,000.00 45,000,000.00 40,000,000.00 35,000,000.00 30,000,000.00 25,000,000.00 20,000,000.00 15,000,000.00 MONTHS. 2019-01 MONTHS.2019-02 MONTHS. 2019-03 MONTHS.2019-04 MONTHS. 2019-05 MONTHS.2019-06 MONTHS. 2019-07 MONTHS.2019-08 MONTHS. 2019-09 MONTHS.2019-10 MONTHS. 2019-11 MONTHS.2019-12 SALES DIVISIONS.MW SALES DIVISIONS.NE SALES DIVISIONS.SE SALES DIVISIONS.SW SALES DIVISIONS MW NE SE SW 2019-01 2019-02 2019-03 2019-04 2019-05 2019-06 MONTHS 20,863,837.23 13,997,949.39 18,906,201.78 15,870,184.94 19,832,147.86 15,027,029.21 17,388,743.46 19,788,935.86 18,776,049.74 17,459,864.41 19,206,960.89 21,857,255.00 27,324,621.50 32,834,970.85 31,269,262.52 46,499,939.45 26,933,575.73 35,308,931.58 29,689,761.06 37,565,834.74 25,154,761.00 28,988, 181.68 51,702,711.96 55,066,349.61 15,790,867.18 17,797,883.46 20,466,018.78 19,877,186.14 20,158,700.31 16,455,170.39 22,449,425.44 16,517,270.24 21,444,283.37 20,904,370.98 18,042,172.53 18,633,592.43 27,225,594.36 23,533,787.68 16,789,197.93 20,572,500.55 20,947,099.93 22,743,562.58 24,485,986.03 17,299,426.68 18,596,137.87 23,452,034.07 15,122,570.12 17,974,431.25 2019-07 2019-08 2019-09 2019-10 2019-11 2019-12 Summary of Sales Orders x Division x Customer Sales Order Table d summary of Sales Orders x Division x Customer - Chart1 c Summary of Sales Invoices x Division x Customer - Chart1 x Summary of Sales Inv = 50,000,000.00 45,000,000.00 40,000,000.00 35,000,000.00 - 30,000,000.00 25,000,000.00 20,000,000.00 15,000,000.00 MONTHS. 2019-01 MONTHS.2019-02 MONTHS. 2019-03 MONTHS.2019-04 MONTHS. 2019-05 MONTHS.2019-06 MONTHS. 2019-07 MONTHS.2019-08 MONTHS. 2019-09 MONTHS.2019-10 MONTHS.2019-11 MONTHS.2019-12 SALES JS.MW SALES DIVISIONS.NE SALES DIVISIONS.SE SALES DIVISIONS.SW : SALES DIVISIONS MW NE SE SW 2019-01 2019-02 2019-03 2019-04 2019-05 2019-06 2019-07 MONTHS 17,138,716.76 14,760,223.29 18,665,225.52 16,390,985.05 19,109,105.33 15,283,426.38 17,457,301.95 19,375,718.55 18,457,941.76 18,054,205.87 18,952,135.38 22,099,690.60 20,767,531.56 32,057,895.55 34,041,115.71 44,770,652.43 27,541,424.47 29,829,170.08 34,906,950.78 36,528,669.04 29,098,767.29 29,005,275.58 50,065,592.65 50,373,054.49 13,788,945.54 16,287,252.42 22,578,477.32 17,756,238.99 20,798,629.62 17,527,471.04 22,000,455.02 16,881,111.69 20,624,892.33 22,849,663.00 16,243,116.66 19,801,689.03 22,341,858.37 26,447,428.85 15,745,111.54 20,600,825.78 21,095,821.70 20,288,842.59 23,573,363.44 20,971,817.83 17,195,651.52 23,283,488.02 15,261,337.75 18,629,932.99 2019-08 2019-09 2019-10 2019-11 2019-12 Customer CUSTOMERS.TOTAL SALES DIVISIONS SE SW 2019-01 2019-02 2019-03 2019-04 2019-05 2019-06 MW 17,138,716.76 14,760,223.29 18,665,225.52 16,390,985.05 19,109,105.33 15,283,426.38 17,457,301.95 19,375,718.55 18,457,941.76 18,054,205.87 18,952,135.38 22,099,690.60 3,543,838.00 219,288,514.44 2019-07 2019-08 NE 20,767,531.56 32,057,895.55 34,041,115.71 44,770,652.43 27,541,424.47 29,829,170.08 34,906,950.78 36,528,669.04 29,098,767.29 29,005,275.58 50,065,592.65 50,373,054.49 9,195,832.57 428,181,932.20 MONTHS 13,788,945.54 16,287,252.42 22,578,477.32 17,756,238.99 20,798,629.62 17,527,471.04 22,000,455.02 16,881,111.69 20,624,892.33 22,849,663.00 16,243,116.66 19,801,689.03 3,186,504.05 230,324,446.71 TOTAL 74,037,052.23 89,552,800.11 91,029,930.09 99,518,702.25 88,544,981.12 82,928,910.09 97,938,071.19 93,757,317.11 85,377,252.90 93,192,632.47 100,522,182.44 110,904,367.11 18,733,994.65 1,126,038,193.76 22,341,858.37 26,447,428.85 15,745,111.54 20,600,825.78 21,095,821.70 20,288,842.59 23,573,363.44 20,971,817.83 17,195,651.52 23,283,488.02 15,261,337.75 18,629,932.99 2,807,820.03 248,243,300.41 2019-09 2019-10 2019-11 2019-12 2020-01 TOTAL
