Assume that you have just gone to work for Wipfli, Barber, & Zeitlow, a regional CPA firm with seven offices in your state. The company is interested in automating much of its documentation. The partner in charge of training asks you to prepare a spreadsheet template to be used by the firm for auditing marketable securities of its clients.

Required:

Prepare a spreadsheet template (spreadsheet with the logic built into it, so that all the staff auditors have to do is to input the current securities and the template will calculate expected values) to be used in the audit of marketable securities of clients.

In developing the template, assume that the clients invest in a wide range of marketable securities including common stocks, preferred stocks, certificates of deposits, and bonds, as well as noninterest or dividend instruments such as puts or calls. The template should be set up to accomplish the following as a minimum:

• Indicate the preparer of the worksheet.

• Calculate needed cost or market adjustments.

• Estimate expected income including dividend income, interest income, and gain or loss on disposal of securities.

• Project ending balances,

• Establish a standard electronic tick-mark legend.

The spreadsheet should be printed so that it shows the development of any macros and the logic built into it.