3 Questions for acct 305 and 400. Acct 400 Assignment
10-36 [LO 3] You are the auditor for Johnson Products, a medical supply company. The company ships wholesale products to clinics and hospitals and some retail products to drug stores.
When flowcharting the revenue cycle, you discover the following information:
All sales are made on open account via 1/10, net/30 credit terms, unless arrangements are okayed by credit manager approval. Credit terms were last evaluated 10 years ago.
Inventory is maintained in real-time via computer.
Official prices can be updated as needed by product managers or the sales vice president.
Regional managers can override official prices.
New customers require credit approval by the regional sales manager.
Once a line of credit is established, a monthly credit report is generated to compare customer credit lines with outstanding balances to identify instances where credit has been exceeded. In those cases, a halt is placed on further sales.
Required:
What are the internal control weaknesses you identify? What problems could these cause, and what suggestions would you make to correct them?
Acct 305 Assignment.
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:
• Initial sales of $5 million, with forecasted monthly growth of 1%
• 40% of each month’s sales for cash; 30% collected the following month; 20% collected 2 months later; 8% collected 3 months later; and 2% never collected
• Initial cash balance of $350,000
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%.
c. Add a scroll bar to your spreadsheet that will let you modify the amount of initial sales to vary from $4 million to $6 million in increments of $100,000.
d. Design appropriate data entry and processing controls to ensure spreadsheet accuracy.
13.6. Excel Project
a. Expand the cash flow budget you created in Problem 12.4 to include a row for expected cash outflows equal to 77% of the current month’s sales.
b. Also add a row to calculate the amount of cash that needs to be borrowed in order to maintain a minimum cash balance of $50,000 at the end of each month.
c. Add another row to show the cash inflow from borrowing.
d. Add another row to show the cumulative amount borrowed.
e. Add another row to show the amount of the loan that can be repaid, being sure to maintain a minimum ending balance of $50,000 each month.
f. Add appropriate data validation controls to ensure spreadsheet accuracy.
