Flowcharting made simple.

Spreadsheet tools do the job.

EXECUTIVE SUMMARY

* AS CPAs ADVANCE INTO HIGHER managerial positions, taking on more strategic responsibilities, many find that flowcharts enable them to better communicate their complex plans.

* ALTHOUGH SUPERB COMPUTER flowchart software applications are on the market, it's possible to use the tools built into Excel to create professional-looking graphics to illustrate complex accounting systems, organization charts and data-flow diagrams.

* BOTH WORD AND POWERPOINT contain similar flowchart features, but Excel has two unique advantages that make it a better choice:

* The Excel work area is virtually unlimited, allowing users to create large flowcharts not hemmed in by margins or page length.

* For charts that require some computing steps, Excel can do the calculations automatically.

* WHEN WRITING DESCRIPTIONS, remember that a flowchart symbol already communicates a message to the reader; for example, an arrow indicates a direction of movement and a diamond is universally understood to be a decision point. So your description should clarify this message--not be redundant.

* A CONNECTOR IS MORE THAN simply a line. If you move the flowchart symbols it is connected to, the connector adapts in slope and size to maintain the connection.

As CPAs advance into higher managerial positions and take on more strategic responsibilities, many find that flowcharts and similar graphic representations enhance their efforts and enable them to better communicate complex plans. Although you can buy superb flowchart software, it's possible to use the tools built into Excel to create professional-looking graphics to illustrate complex accounting systems, organization charts and data-flow diagrams. (For more on graphic software applications, see "Picture That," JofA, Feb.00, page 43.)

Both Word and PowerPoint contain similar built-in flowchart features, but Excel has two unique advantages that make it a better choice. First, the Excel work area is virtually unlimited, allowing the creation of large flowcharts not hemmed in by margins or page length--until, of course, you print them. Second, for charts that require some computing steps, Excel can do the calculations automatically and place the results in the flowchart.

CREATE THE STRUCTURE

The first step in creating a flowchart is to build a basic structure by determining the people involved (if it's an organization chart) or the functions and steps to be represented (if it's a data-flow diagram). The two-part flowchart in exhibit 1, page 78, is a typical sales-order-entry chart. It contains two functions, or in this case, column headings: the role of the computer and the role of the sales representative in entering the sales order.

[Exhibit 1 ILLUSTRATION OMITTED]

To access Excel's flowchart symbols, go to the Drawing toolbar (see exhibit 2, page 79). If it's not already visible, go to the toolbar and select View, Toolbars, Drawing. When the Drawing toolbar appears on the bottom of the screen, click on AutoShapes to display its menu of categories (see exhibit 3, page 79). Select Flowchart to display its 28 symbols (see exhibit 4, page 79). Pointing your cursor to any symbol generates a yellow ScreenTip window containing a brief description of the symbol.

[Exhibits 2-4 ILLUSTRATION OMITTED]

When you click on a desired symbol, the menu disappears, revealing the crosshair (+) mouse pointer. Move the crosshair to where you want to place the symbol; when you release the button the symbol will appear. If you double-click on the symbol, a Format AutoShape screen appears (see exhibit 5, below) that lets you format many of its properties: color, relative size and line thickness. Experiment with the various choices to view the effects you can create.

[Exhibit 5 ILLUSTRATION OMITTED]

A single click on the symbol will evoke eight small boxes, called handles, that surround the object (see exhibit 6, page 80). Regardless of the shape of the object, the handles will form a rectangle surrounding it. Their appearance indicates that you can type in a symbol description and resize the object. An easy way to resize an object, other than evoking Format AutoShapes, is to click on any handle and drag it to the desired size. Side handles modify the object's width and the top and bottom handles adjust the height. Any one of the four corner handles changes both dimensions simultaneously.

[Exhibit 6 ILLUSTRATION OMITTED]

To enter a comment inside the symbol, simply begin typing. The text will be formatted automatically to the computer's default font type, size and alignment. You can adjust the formatting by double-clicking on the symbol. When writing descriptions, remember that a flowchart symbol already communicates a message to the reader; for example, an arrow indicates a direction of movement and a diamond is universally understood to be a decision point. Your description should clarify this message--not repeat a message of the symbol. Thus, it should focus on the nature of the decision--some required action or an explanation of the action, for example.

When you finish entering text or resizing the object, click outside the object and the handles will disappear. To go back for more adjustments, just click on the symbol.

CONNECTING SYMBOLS

Once you have drawn two or more symbols, you're ready to connect them with lines and arrows. Flowchart lines show the flow of data or control between a source and destination symbol, and arrows show the direction of the flow. To see them, click on AutoShapes and then on Connectors, and the nine choices will appear (see exhibit 7, at left).

[Exhibit 7 ILLUSTRATION OMITTED]

Notice the connector variations: number of arrows (none to two), number of segments (one and two) and the line style (straight, elbow and curved). As with the Flowchart menu, the Connectors menu disappears when you select a symbol and that also produces the crosshair mouse pointer. Move the crosshair inside the source symbol and the crosshair changes to a square as four small blue handles, called connector sites, surround the symbol (see exhibit 8, page 83).

[Exhibit 8 ILLUSTRATION OMITTED]

Click and drag the connector to the destination symbol until its connector sites appear and release the mouse button. At that moment the connector's handles will change to red, signifying that the connector is "locked" to the two objects (see exhibit 9, page 83).

[Exhibit 9 ILLUSTRATION OMITTED]

Click anywhere off the connector to complete the process. If a green handle appears (see exhibit 10, page 83), you missed aligning the connector with the symbol's connection sites and you need to repeat the process.

[Exhibit 10 ILLUSTRATION OMITTED]

A connector is more than simply a line. If the connected flowchart symbols are moved, the connector adapts in slope and size to maintain the connection. For more elaborate changes, the connecting point of any connector can be moved to another object.

Don't be put off by the process: It's actually very intuitive, sounding more complicated than it really is. You're sure to get the hang of it after a few tries.

ADD AN ANNOTATION 

Sometimes the few words of text within a flowchart symbol aren't sufficient to explain a flowchart process. In that case you can add an explanation inside a callout symbol. To do that, go back to AutoShapes and click on Callouts. After writing the explanation inside the callout, drag the sharp end of the pointer to the symbol that needs further explanation (see exhibit 11, below).

[Exhibit 11 ILLUSTRATION OMITTED]

Unlike connectors, however, callouts don't adjust when the related object is moved or resized. To resize a callout, click on the line portion of the symbol. Three yellow triangles, called adjustment handles, appear to let you reposition the line's midpoint and the two endpoints.

MOVE MULTIPLE OBJECTS

As a practical matter, the initial placement of a flowchart symbol is seldom its final position. You often add more symbols as you build the chart, and that may require further adjustments. There are two ways to move multiple flowchart symbols. The simplest involves clicking on each flowchart symbol and callout while holding down the Shift key. You do not have to click on connectors because they move automatically with the connected symbols. The handles for each object will appear, signifying that they can be moved or resized. Then, by clicking inside any of the highlighted symbols, you can drag all the symbols in unison, maintaining their relative positions.

Once a section of a flowchart is complete, the symbols can be grouped to form a single object. To do that, hold down the Shift key, click on each symbol or callout to be grouped and select Draw and then Group on the Drawing toolbar. The handles surrounding each object are replaced with a single set of handles surrounding the grouped area (see exhibit 12, page 84). You can now move the group as a single object. However, be wary of resizing the grouped object because that can cause format anomalies. If any object within the group requires editing, select the Draw and Ungroup tools to restore handles to the individual objects in the group.

[Exhibit 12 ILLUSTRATION OMITTED]

FUNCTION DIVIDER LINES

Many accounting-related flowcharts are for processes that illustrate a segregation of duties. In exhibit 1, for example, the "duties" are divided between actions taken by the computer and those taken by the sales representatives. Often it's easier to build the flowchart by placing the separate duties in different spreadsheet columns--say, one in column A and the other in column B. Formatting that physical separation with a column divider makes it easier for the audience to visualize the segregation of duties. To draw a function divider line between columns, click on the column heading and select the Right Border tool on the Formatting toolbar. Note, however, that any object that overlaps a column divider will be resized if the column width is subsequently changed.

PRINTING ADVICE

Having virtually unlimited drawing space is useful--until it comes time to print the flowchart. For convenience, try to limit a chart to a single printed page; that can be done even if you use more space to create it. Here's how: Once the design is done, you can force the entire worksheet to print on a single page; of course, everything will appear smaller so it will fit in that limited space. To do that, select File on the toolbar and Page Setup. Then click the radio button next to the Fit to: option in the Scaling section of the dialog box.

If a flowchart is too large for a single page and you don't want the image reduced, divide the flowchart into separate pages and use an off-page connector symbol as shown in exhibit 13, above, to tell the viewer that the chart flows to the next page. Look at the bottom of exhibit 1 and see how it's used.

[Exhibit 13 ILLUSTRATION OMITTED]

CREATE LINKS

Flowcharts can contain hyperlinks, too. Although hyperlinks are most often associated with linking to an Internet page, they also can be used to link parts of worksheets. In exhibit 1, a hyperlink attached to the off-page connector symbol will, when clicked, advance the view to a separate worksheet.

To create a hyperlink, click on the object to display its handles, click on Insert Hyperlink on the Standard toolbar to display the Insert Hyperlink dialog box (see exhibit 14, page 84), select the Place in This Document option and click on the desired worksheet.

[Exhibit 14 ILLUSTRATION OMITTED]

To make a viewer aware of the hyperlink, add a ScreenTip at the hyperlink location by clicking on the ScreenTip button in the upper right-hand corner and typing something like Hyperlinks to S-2 Flowchart (see exhibit 15, at right). The ScreenTip appears when the mouse pointer is positioned on the object. Once a hyperlink is created, a single mouse click will execute the hyperlink command. If you wish to amend the symbol, you must use a right mouse click.

[Exhibit 15 ILLUSTRATION OMITTED]

COMPUTE SYMBOL DESCRIPTIONS

Not only can information in a flowchart be linked to another data source but also when that information is altered, the linked flowchart data will change accordingly. Excel can perform that change on text, on a number or even on a spreadsheet-calculated cell. If the information is text or a number, Excel will simply carry it from the source file and copy it in the flowchart. If the information is a number that's calculated by some other Excel file, it will be copied to the flowchart after it's computed.

To illustrate such a link, look at exhibit 16, page 88. The upper portion is a spreadsheet file that lists the senior officers of the Paradiso Corporation. On the bottom is an organization chart prepared in Excel with each block linked to the appropriate row in the senior officers' spreadsheet list.

[Exhibit 16 ILLUSTRATION OMITTED]

Now if Anthony Peters, the president, retires and Mark Lehman succeeds him with the new title CEO and chairman, and you make those changes in the spreadsheet (see exhibit 17, page 88), they will be reflected immediately in the organization chart.

[Exhibit 17 ILLUSTRATION OMITTED]

To create the link between the positions spreadsheet and the organization chart, place the executive's name in column A and position in column B. Then, using Excel's concatenate function and the following formula, we can link the two pieces of information and display them in cell C2, which is hyperlinked to the flowchart: =A2&", "&B2.

To insert a computed description in a flowchart symbol, create or edit the symbol to display its handles. Click once on the formula bar, and then click on the cell containing the computed description. Excel inserts a formula on the formula bar that references the cell. The computed description on the positions worksheet now serves as the source for any change in the organization charts.

OTHER TIPS

* Align symbols. Display the symbol handles and use the arrow keys to nudge the object into its correct position. To align several symbols at one time, select each one by depressing the Shift key and clicking on each object, then select one of the align tools (left, center, right) from the Draw and Align or Distribute tools on the Drawing toolbar.

* Customize your toolbars. If you create flowcharts often, consider creating a customized toolbar containing your favorite tools (see "Spreadsheet Skills," JofA, Oct.98, p. 39).

* Erase gridlines. To hide the gridlines, click on the Select All button in the upper-left corner of the worksheet (above row 1 and to the left of column A) and select the white Fill Color tool from the Formatting toolbar.

Armed with a knowledge of Excel's flowcharting tools, you are now ready to draw a variety of flowcharts. You'll find the tools are easy to use and will enable you to quickly create highly professional flowcharts.

An Invitation

If you have a special how-to technology topic you would like the JofA to consider for inclusion in this series, or an application shortcut you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin. His e-mail address is zarowin@ mindspring.com.

MARK W. LEHMAN, CPA, PhD, is an assistant professor of accounting at Mississippi State University at Mississippi State. His email address is [e-mail].