Creating a Consolidation Template and Two Keystroke Macros (Stored Instructions) Employers expect accounting graduates to be able to (1) create accounting models containing macros and (2)

use models created by others. Accordingly, this assignment gives you programming experience in

(1) creating a model and (2) creating two specific keystroke macros (stored instructions that can be automatically executed instantly at the touch of a key). These two macros are highly useful in reviewing a newly created model to detect bugs in programmed formulas.

You should use one of the leading spreadsheet programs such as EXCEL or Lotus 1-2-3. Refer to an EXCEL or Lotus 1-2-3 manual for particulars about creating and naming macros (so that you can execute your macros by pressing a letter of the alphabet after holding down either the Control key or the Alt key, depending on which spreadsheet program you use).
Required 1 Prepare a consolidation model (complete with formulas) for the trial balance consolidation worksheet shown in Appendix 2A. Use an appropriate format (such as the “Accounting” for¬
mat of EXCEL) with zero decimal places. You may abbreviate account descriptions and omit the vertical lines. To code your consolidation entries, program a three-character-wide column immediately to the right of the Debit column; do likewise for the Credit column. Program for¬
mulas in the Income Statement column as well as the Balance Sheet and Retained Earnings columns. (All amounts in the Debit and Credit columns must be “absolute” — no amounts can be shown in parentheses.)
2 Program a keystroke macro (a stored instruction) to convert your model from your, selected for¬
mat to a format that will display your formulas (Excel: Tools, Options, View Formulas, OK).
Include in this macro instructions to change column widths as necessary to fully display the for¬
mulas you programmed.
3 Program a macro to reverse the macro you programmed in requirement 2 (to go back to your selected format).
4 Program a logic formula (=IP function for EXCEL and @IF function for Lotus 1-2-3) for

(a) the cell containing the $142,000 balancing debit in the Income Statement Debit column and

(b) the cell to its immediate right. Thus your model must be able to accommodate a loss situation (in which you now balance in the Credit column instead of the Debit column. (To test this second logic formula, create a loss by decreasing the parent’s Sales account by $200,000 to see whether you are now balancing in the Credit column with a $58,000 amount.) To create your logic func¬
tion using EXCEL, use the Insert, Function [or the fx icon on the standard tool bar]. Single click on Logical (top of dialog box in Office 2000). Then double click on If (bottom portion of dia¬
log box in Office 2000). Then follow the prompts (be sure to press the OK button when you are done). Note that an IF logic function has three parts {commas separate the three parts in the formula): (1) an argument concerning a condition, (2) a command to insert a defined amount (or zero) if the condition is true, and (3) a command to insert a defined amount (or zero) if the condition is false. An example of an IF logic formula is as follows: IF(K40>J40, K40-J40,0).
5 Program either a logic function or a formula in the Retained Earnings column so that the $142,000 net income in the income statement is extended to the Retained Earnings column.
(This logic function or formula should also be able to accommodate a loss situation — a loss amount must appear in the Retained Earnings column in parentheses.) Accordingly, check out your solution as you did in requirement 4.
6 Printing instructions. First, print a copy of your model (in landscape layout — not picture lay¬
out) that does not display any formulas. Second, print a copy of your model (in landscape lay¬
out) that does display the formulas (first use the File, Page Setup, Sheets, place X in box com¬
mand, so that your Excel column letters and row numbers are printed [enables your formulas and logic functions to be verified]). Third, print out your recorded macro instructions in Visual Basic Language (use the Edit command).