As the staff auditor on the audit engagement for JA Tires Manufacturing for the year ended December 31, 2023, you are responsible for auditing the payroll and personnel cycle. You are currently testing sales commission expense for the year and accrued sales commissions payable as of fiscal year end. JA Tires has four sales divisions within the U.S. and sells primarily to large tire companies with regional warehouses that subsequently distribute to local retailers. Sales commissions are paid to the sales division managers at a rate of 0.05% of sales in dollars (0.0005 times sales). Commissions are earned based on total sales for the month but are paid to the division manager in the following month. The audit in-charge is auditing the sales and collection cycle and is testing sales revenue as a part of that cycle. Your plan for testing commission expense and accrued commission is to use the JA Tires sales data to develop an estimate of each amount. 

1. Visit pearsonhighered.com/arens to download the file “JATireSales.xls” 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 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. Develop and document an estimate of sales commission expense for the fiscal year in total and separately for each division.

3. Develop and document an estimate of accrued sales commissions payable as of the end of the fiscal year in total and by sales division. It is helpful to summarize estimated sales commissions by month and sales division first. 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.

4. If the in-charge auditor identifies any proposed audit adjustments in sales revenue, how will this impact your analyses and estimates?