CSUN A Step By Step Guide to Auditing Accounts Receivable Excel Spreadsheet

Southern Industries: A Realistic Simulation of Substantive Testing for Accounts Receivable
 
A STEP-BY-STEP GUIDE TO AUDITING ACCOUNTS RECEIVABLE
This guide was prepared by your senior, Bruce Allen, and is intended to walk you through the performance of your substantive testing procedures. If you need clarification on what an auditing term means as you read this guide, please consult the glossary in the appendix at the end of this document. All words included in the glossary have been italicized.
Workpaper Documentation Instructions
During your review of the workbook, you should notice that your senior has already completed some portions of the workpapers, represented in RED font in the workbook. Your senior’s work will serve as a guide for how to document your work, so make sure to review the work performed by your senior, including the meaning of all tick marks. Use the tick marks included in the “Tick Mark Legend” tab and the other workpapers in the Excel file to assist you in documenting your work. Tick mark references for all tabs can be found in the “Tick Mark Legend” tab. Tick mark references in the Excel workbook can be copied and pasted to the respective workpapers. Tick marks may be used once, more than once, or not at all. Always check tick marks after utilizing the copy and paste function to ensure they have copied correctly.
To document your work, you will insert tick marks and explanations in the TAN-shaded cells, which may or may not require your input. Note that all input into these tan cells should be in GREEN, bolded font to assist your professor in identifying your input in the workbook.
 
 
Step-by-step Guide to the Accounts Receivable Simulation
The following section will provide a step-by-step guide on how to complete all the steps outlined in your Accounts Receivable (AR) Audit Work Program (AR AWP). Be sure to have the “AR AWP” tab open before beginning any work steps. This tab contains all the substantive testing procedures you are required to perform during the current year audit. You will notice that your senior has already completed the workpaper (W/P) referencing section (Column H) of the “AR AWP” tab (except the cutoff procedures), leaving you to sign off and date the work procedures you successfully complete.
Begin each step by first selecting all assertions being tested from the drop down list to indicate the assertions being tested by that particular work step. Next, perform the work step specified in the “AR AWP” using the detailed instructions included in this guide below. Once you have completed the audit work step, you will be required to provide a testing conclusion in the shaded cells of the “AR AWP” (Column F) and add a checkmark in the follow up section (Column G) if you think additional follow up is required based on your testing conclusions. If you feel additional follow up is required, you must also document a description of the follow up you think the audit team should perform. Finally, once you have competed all aspects of the work step, sign and date the “AR AWP” (Columns D & E) to indicate your completion of this testing procedure. You can use the work step completed by your senior (Step 2d) as an example of how to document your work.
During the simulation, you will be required to make several judgments. Depending on the judgments you make, you may or may not be required to post adjusting journal entries (AJE). If you decide to make an adjustment, you must document your proposed adjustment and rationale on the “Summary of Audit Differences (SAD)” tab. You will notice on this tab that your senior has provided a template of how to document your work. Select the appropriate accounts to debit and credit from the drop-down menus and enter the dollar balance of the adjustment. Next, you will be required to tie out and post the Adjusting Journal Entry (AJE) from the “SAD” tab to the “Trial Balance (TB)” tab, specifically the AJE columns. This ensures that all adjustments are correctly reflected on the year-end financial statements. Finally, once you have made your final determination of all audit adjustments, you will need to ensure they are properly reflected in the allowance for doubtful accounts (ADA) calculation, “ADA Calculation (A-2).”
Keep in mind that you will need to recall the information from the Case Narrative when making judgments during the simulation. In addition, you will want to review all management inquiries for important information related to your testing. You can find this information on the “Management Inquiries (A-3)” tab. Let’s begin by performing our first set of audit procedures (Steps 1a–1c).
Step 1a
This step requires you to test the mathematical accuracy of “AR Aging (A-1).” Begin this step by first selecting the assertion(s) being tested from the drop-down menu in Column B of the “AR AWP.” While it may seem counterproductive to test for mathematical errors in Excel, several issues can arise in the real world that lead to computational errors in electronic documents. For this reason, an auditor always reviews all mathematical computations. Conduct this procedure by recalculating all mathematical equations, footing, and crossfooting in “AR Aging (A-1).” You will need to select the appropriate tick mark from the “Tick Mark Legend” tab and paste it in the cells on the “AR Aging (A-1)” tab (see blue arrows) to note that you performed this procedure. A tick mark next to each value is not necessary; you may use a power tick mark instead (use the shape function to draw lines in Excel). This completes the testing of this work step.
Step 1b
This step requires you to reconcile the AR balances between the “AR Aging (A-1)” and “Trial Balance (TB).” Begin this step by first selecting the assertion(s) being tested from the drop-down menu in Column B of the “AR AWP.” The purpose of this procedure is ensuring that the AR balance recorded in the financial statements is not missing any valid customer balances. The total from “AR Aging (A-1)” should agree to the total amount of AR recorded in the “Trial Balance (TB).” To document this, the auditor places a tick mark next to the total AR balance on the “AR Aging (A-1)” and a corresponding tick mark on the “Trial Balance (TB).” Document your work by placing a “TB” tick mark next to the balance on the “AR Aging (A-1)” and an “A-1” tick mark next to the balance in the “Trial Balance (TB).” If there are any material differences (i.e., reconciling items), the auditor must investigate to determine that all items are properly included in the AR balance. This concept of tying out balances between different workpapers will be used regularly throughout this simulation. This completes the testing of this work step.
Step 1c
This step requires you to investigate the returned AR confirmations sent to customers. Begin this step by first selecting the assertion(s) being tested from the drop-down menu in Column B of the “AR AWP”. Next, go to the “AR Aging (A-1)” tab, where the customers selected for AR confirmation testing are shaded in green. Note that the audit team confirms customer balances in total rather than individual invoice amounts. The audit team has already prepared, sent, and received all the confirmation requests. The returned customer confirmations are included in tab “Confirmation Support (A-1a).”
Review the confirmations to ensure they were properly completed and investigate any issues that may arise from comments left by customers. Indicate the status of returned confirmation requests with the appropriate tick mark from the “Tick Mark Legend” on “AR Aging (A-1).” Different tick marks are provided based on whether the confirmation was returned or not returned. A guide explaining the appropriate tick marks for confirmation testing is provided in “AR Aging (A-1).”
If the confirmation has not been returned, you are required to perform alternative procedures. Your senior has already obtained subsequent receipts of payment made by the customer for any unconfirmed balances to assist you in conducting alternative procedures. For any unreturned confirmation requests, you are required to review the subsequent receipts located in the “Subsequent Receipts (A-1b)” tab to determine whether customers have paid their AR balance. Based on your findings, copy and paste the appropriate tick mark in the “AR Aging (A-1).” This completes the testing of this work step.
Step 2a
This step requires you to test the inputs to the clients in “AR Aging (A-1).” Begin this step by first selecting the assertion(s) being tested from the drop-down menu in Column B of the “AR AWP”. Now open the “AR Aging (A-1)” tab. The columns (i.e., buckets) in AR Aging indicate how long each customer’s balance has been outstanding. This information is useful in evaluating the likelihood of customers paying, as the longer a customer balance is overdue, the less likely the customer is to pay. Before relying on this schedule, auditors will perform what is known as “bucket testing” to ensure that invoices (bills sent to customers) included in AR Aging are being aged accurately.
Your senior has selected 10 customer invoices for bucket testing (blue-shaded cells) from “AR Aging (A-1).” The invoices for these customers can be found on the “Bucket Testing Invoices (A-1c)” tab. Invoices were selected for testing from each bucket, with a high concentration of those in the 91 and over bucket. To perform this audit procedure, examine the invoices included on tab “Bucket Testing Invoices (A-1c).” Compare the due date noted on the invoice to the year-end date of December 31, 2018 to ensure that each invoice is categorized in the proper aging column in “AR Aging (A-1).” For example, if an invoice due date was December 31, 2018, this would fall into the “current” bucket, or if an invoice due date was October 15, 2018, this would fall into the “61–90” bucket.
Review the invoices to ensure they were properly aged. Use the appropriate tick mark from the “Tick Mark Legend” to document the results of your bucket testing on the “AR Aging (A-1).” A guide explaining the appropriate tick marks for bucket testing has been provided in “AR Aging (A-1).” This completes the testing of this work step.
Step 2b
This step requires you to recalculate the client’s ADA balance based on the policy obtained from management. Begin this step by first selecting the assertion(s) being tested from the drop-down menu in Column B of “AR AWP.” Companies often estimate their ADA based on a percentage of overdue customer balances. Your job is to determine whether the method applied by management is reasonable. Begin by first reviewing the “ADA Policy (A-2a)” provided by management. Evaluate it to determine whether the method and percentages of uncollectible accounts applied by management are reasonable. Next, use the policy to recalculate the allowance on workpaper “ADA Calculation (A-2).” You are given the AR bucket balance prior to any audit adjustments. Before recalculating the ADA balance, you need to consider how any material errors noted during your audit testing affect the ending balances in each bucket. If a material error(s) does affect a customer bucket, make an adjustment in the “Adjustments” row to properly reflect the correct adjusted AR bucket balance. Now that you have determined the proper ending balance in each bucket, the next step is recalculating the ADA balance. Enter the percentages from the “ADA Policy (A-2a)” into the “% Uncollectible” row, and Excel will automatically calculate the portion of each bucket that is expected to be uncollectible. The final step is comparing your recalculation of the ADA reserve to the amount recorded by the client. Reconcile the recorded ADA balance to the “Trial Balance (TB)” by adding the appropriate tick mark. The “Potential Audit Adjustment” row is used for assessing the difference between your recalculation and the client recorded balance, which can be used to determine whether any adjustment to the ADA balance is necessary. This completes the testing of this work step.
Step 2c
This step requires you to review management’s responses to inquiries sent by your senior for balances 90 days overdue. These balances are indicated by a tick mark (α) above the 90-day column in “AR Aging (A-1).” Begin this step by first selecting the assertion(s) being tested from the drop-down menu in column B of the “AR AWP.” Auditors will often inquire about why management has not yet written off balances that are 90 days overdue, as the risk of an account being uncollectible rises the longer it is outstanding. Review management’s responses on tab “Management Inquiries (A-3)” and make your own determination as to whether any accounts should be written off by management. This completes the testing of this work step.
Step 3
This step requires you to review management inquiries to determine whether the company has any factored or pledged receivables as of the balance sheet date. Begin this step by first selecting the assertion(s) being tested from the drop-down menu in column B of the “AR AWP.” Review management’s responses to inquiries sent by your senior regarding any pledged or factored receivables on the “Management Inquiries (A-3)” tab. This completes the testing of this work step.
Step 4
This step in the simulation involves testing the management assertion cutoff. Note that, for this test, the assertion in Column B of the “AR AWP” is already selected for you. Unlike the other procedures in the “AR AWP,” you should use your judgment to select which procedure to perform to properly test the cutoff assertion. Depending on which test you select, you will also need to fill out the workpaper references column in the “AR AWP.” Cutoff tests are designed to identify potential misstatements of revenue by ensuring transaction and account balances are recorded in the correct accounting period. Begin by reviewing the four potential testing options for testing the cutoff. Select the procedure you think best tests the cutoff assertion. The Excel workbook will automatically take you to the Excel tabs that contain the audit evidence required to perform the test you selected. Skip to the section of the guide below to read the instructions on how to complete the alternative you selected.
Cutoff Alternative 1: Compute the accounts receivable turnover ratio and days sales outstanding. If you selected this procedure, you opted to perform analytical procedures to test the cutoff for the client sales transactions. Ratios assist auditors in determining the rate of collections for a company, which can also be useful in evaluating the cutoff assertion. These ratios can be compared with industry averages to determine any unlikely fluctuations. The senior has already performed research on the industry standards in the wholesaler industry and provided you with the following averages: AR turnover = 12.4 and days sales outstanding (DSO) = 29.4.
Alternative 1 requires you to compute the AR turnover ratio and DSO from the balances included in the “Trial Balance (TB)” and “Balance Sheet (BS).” The formula required for computing AR turnover is “Net Credit Sales ÷ Average AR.” The formula required for computing DSO is “365 / AR Turnover.” Assume all sales are credit sales for determining net credit sales. You should use the “Financial Statements” column (Column T) from the “Trial Balance (TB)” and the prior year receivable balance from the “Balance Sheet (BS)” in your calculations. You should use the equation functions in Excel to assist you in your computations. Assess the ratios for the company against industry averages and document your conclusions. This completes the testing of this work step.
Cutoff Alternative 2: Review significant year-end sales contracts. Auditors want to inquire about any significant year-end contracts to determine whether any benefits or obligations should be recognized during the year. To perform this step, review the “Management Inquiries (A-3)” tab to determine whether the company entered into any significant sales contracts near year end. If so, review these contracts to ensure proper accounting treatment and document your findings. This completes the testing of this work step.
Cutoff Alternative 3: Select a sample of sales transactions and compare details to shipping documents and invoices. If you selected this alternative, you elected to perform inspection of documents to test the cutoff assertion. You will notice that your senior has already selected a sample of revenue transactions from three days before and after year-end for you on the “Cutoff Workpaper (A-4)” tab. Supporting documentation for your test sample has been provided on the “Invoices (A-4a)” and “Shipping Documents (A-4b)” tabs. Begin by reconciling the quantities sold on the invoice to the shipping documents to ensure you have the correct source documents for the sales transactions selected. Next, inspect the shipping terms and pick-up dates on the shipping documents for each sales transaction to ensure all transactions are recorded in the correct accounting period (recall that revenue should be recognized based on the shipping terms specified for the sale). This completes the testing of this work step.
Step 5
The final step in this simulation requires you to complete a partial financial statement tie out. Begin this step by first selecting the assertion(s) being tested from the drop-down menu in Column B of the “AR AWP.” Next, you will be required to input the financial statement balances from the “Trial Balance (TB)” located in Column T (which have been automatically updated with any audit adjustments) to the FY 2018 balances of AR and ADA on the “Balance Sheet (BS)” and to Sales Revenue and Bad Debt Expense on the “Income Statement (IS).” Once you have input the balances, you are required to tie out these balances on the “Balance Sheet (BS)”, “Income Statement (IS)”, and “Trial Balance (TB)” with the corresponding tab references. This completes the testing of this work step.
Congratulations, you have now completed substantive testing of the client’s AR account balances!
 
 
Deliverable Submission and Conclusion
Prior to submitting your workpapers, conduct a thorough review of all tabs in the workbook to ensure all “AR AWP” steps have been completed. Make sure that you have saved the Excel workbook as a different file name, as follows “AR AWP – Last Name,” for example, “AR AWP – Edmonds.” Upload this file in accordance with the instructions provided by your professor.
 
 
APPENDIX
Case Glossary
Accuracy: Amounts and other data relating to recorded transactions and events have been recorded appropriately.
AR aging: A listing of individual customers’ accounts classified by the number of days subsequent to billing, that is, by age. A preliminary step in estimating the collectability of accounts receivable.
Audit work program: Required for all audits to document the work performed within the audit plan by the auditor in forming conclusions on the financial statements.
Completeness: All transactions and events that should have been recorded have indeed been recorded.
Crossfooting: Adding all the numbers in a single row.
Cutoff: Transactions and events have been recorded in the correct accounting period.
Existence: Assets, liabilities, and equity interests exist.
Factoring accounts receivable: Selling the rights to collect the accounts to a third party, known as a “factor.”
Footing: Adding all the numbers in a single column.
Negative confirmation: A type of confirmation where the customer only responds if disagreeing with the balance listed on the confirmation.
Pledged receivables: AR balances that have been used as collateral on a loan.
Positive confirmation: A type of confirmation where the customer either agrees or disagrees with the balance listed on the confirmation and a response from the customer is required.
Power tick mark: One tick mark and a line to include all numbers that were verified. In the Excel worksheet, click the “Insert” tab on the command ribbon and then click the “Shapes” button in the Illustrations group to open the gallery of thumbnails of shape images. Click on the first line image and click in the worksheet where you wish to start the line. Drag the cursor as far as you want the line to extend on the worksheet and release the mouse to set the line. You may edit the line with the tools on the Format tab in the Drawing Tools ribbon.
Recalculation: Checking the mathematical accuracy of documents or records. Recalculation can be performed through the use of information technology, for example, by obtaining an electronic file from the entity and using computer-aided audit tools to check the accuracy of the summarization of the file.
Rights and obligations: The entity holds or controls the rights to assets, and liabilities are the obligations of the entity.
Risk of material misstatements (RMM): The risk that the financial statements of an organization have been misstated to a material degree.
Tick mark: A symbol used by the auditor in workpapers to indicate a specific step in the work performed. Whenever tick marks are used, they should be accompanied by a legend explaining their meaning.
Tie Out: Process of matching figures on the company’s financial statements to workpapers where the auditor has tested the account balances.
Tolerable misstatement: An estimate of materiality for the specific audit test.
Trial balance: A bookkeeping worksheet in which the balances of all ledgers are compiled into debit and credit columns. A company prepares a trial balance periodically, usually at the end of every reporting period. The general purpose of producing a trial balance is ensuring the debits equal credits in a company’s bookkeeping system.
Valuation: Assets, liabilities, and equity balances have been valued appropriately.
Workpapers: Papers that document the evidence gathered by auditors to show the work they have done, methods and procedures they have followed, and conclusions they have developed in an audit of financial statements or other type of engagement.
I think you should work on excel and complete that one by instruction and I upload that professor gave to us.
How can I upload excel because the format professor gave us is XLSM but here just I can upload PDF, Screenshot/ Image, XSLS, Doc. Would you please say to me because I need excel fotmat to submit. This one that I uploaded is excel by PDF format.Copy of The Case – AR AWP – Student Version_FINAL