Ch. 12: The Revenue Cycle: Sales to Cash Collections
12.4 EXCEL PROJECT. (Hint: For help on steps b and c, see the article “Dial a
Forecast,” by James A. Weisel, in the December 2006 issue of the Journal of
Accountancy. The Journal of Accountancy is available in print or online at the
AICPA’s Web site: www.aicpa.org
Required:
a. Create a 12-month cash flow budget in Excel using the following assumptions:
b. Add a “spinner” to your spreadsheet that will enable you to easily change
forecasted monthly sales growth to range from 0.5% to 1.5% in increments of
0.1%.
A “spinner” is a tool that enables the user to easily alter the values of a variable by
clicking on the “spinner” rather than having to type in a new value. The spinner tool
c. Add a scroll bar to your spreadsheet that will let you modify the amount of initial
sales to vary from $4,000,000 to $6,000,000 in increments of $100,000.
A scroll bar is another spinner tool. The difference between a scroll bar and a spin button is that a
Step 3: As with the spin button, we have to link the scroll bar to the cell that will display the
Step 4: Now right-click on the scroll bar tool in cell F4, select properties, and enter the following
values:
d. Design appropriate data entry and processing controls to ensure
spreadsheet accuracy.
Chapter 10 describes the various data input validation controls that can be used. In this problem,
students should be instructed to set reasonable range checks on the allowable values for the
percentage of sales that are cash sales and what percentage of credit sales is never collected.
12.5 Excel Project. Accountants should help managers understand
trends in revenue cycle activities. One important issue concerns
granting credit to customers. Trends in bad debt expense (BDE)
to write-o$s (WO) provide insights into the accuracy of credit
granting policies. It is also important to monitor how long it will
take to write o$ the current balance in the allowance for
doubtful accounts (BADA Exhaustion Rate).
Required
a. Create a spreadsheet that contains the following data:
Year 2012 2013 2014 2015 2016
b. Create a two-dimensional columnar chart that displays the data values for each
variable for the five years 2012–2016.
c. Create a new chart that will display a two-dimensional columnar chart that shows a
rolling five-year window of the variables. Add the following data for 2017 and 2018
to your spreadsheet to demonstrate that the new chart shows only 2014–2018 values:
Year 2017 2018
BDE/WO 1.1 1.3
BADA/WO 1.3 1.1
BADA Exhaustion Rate (years) 1.5 1.6
(Hint: Read the article “Simplify Your Future with Rolling Charts,” by James A. Weisel in
the July 2012 issue of the Journal of Accountancy for an explanation of the how to create a
rolling chart (step c)—and take care to follow Excel’s rules for naming ranges. Read the
article “Assessing the Allowance for Doubtful Accounts: Using historical data to evaluate
the estimation process,” by Mark E. Riley and William R. Pasewark in the September 2009
issue of the Journal of Accountancy for an explanation of how the variables used in this
problem can help you evaluate a company’s process for estimating the allowance for
doubtful accounts. The Journal of Accountancy is available either in print or online at
www.aicpa.org).
The spreadsheet solution is available on website. Key steps include:
12.6 Create a questionnaire checklist that can be used to evaluate controls for each of the
four basic activities in the revenue cycle (sales order entry, shipping, billing, and cash
collections).
a. For each control issue, write a Yes/No question such that a “No” answer
represents a control weakness. For example, one question might be “Are
customer credit limits set and modified by a credit manager with no sales
responsibility?”
A wide variety of questions is possible. Below is a sample list:
Question Yes No
1. Is access to master data restricted?
2. Is the master data regularly reviewed and all changes investigated?
3. Is sensitive data encrypted while stored in the database?
4. Does a backup and disaster recovery plan exist?
5. Have backup procedures been tested within the past year?
6. Are appropriate data entry edit controls used?
7. Are digital signatures required for online orders?
8. Are physical counts of inventory taken regularly and used to adjust the
perpetual inventory records?
9. Are the credit approval and sales order entry tasks performed by
separate individuals?
10. Are picking list quantities compared to sales orders?
11. Is physical access to inventory controlled?
12. Are reports of open sales orders regularly created and reviewed?
13. Are shipping documents reconciled with sales orders?
14. Are the shipping and billing functions performed by different
individuals?
15. Are monthly statements mailed to customers?
16. Are the functions of processing customer payments and maintaining
accounts receivable performed by separate individuals?
17. Is the bank account reconciled by someone other than the person who
processes customer payments?
18. Are lockbox arrangements used?
19. Are customer credit limits set and modified by a credit manager with no
sales responsibility?
b. For each Yes/No question, write a brief explanation of why a “No” answer
represents a control weakness.
Question Reason a “No” answer represents a weakness
1 Unrestricted access to master files could facilitate fraud by allowing employees to change
account balances to conceal theft
2 Failure to investigate all changes to customer master data may allow fraud to occur because
unauthorized changes to credit limits may not be detected.
3 Failure to encrypt sensitive data can result in unauthorized disclosure of personal information
about customers
4 If a backup and disaster recovery plan does not exist, the organization may suffer loss of
important data.