Using the Spreadsheet Template

Creating a good template for financial planning is an investment on the co-op's part -- a darned good one.

Computer spreadsheets make the task of financial planning infinitely easier, They allow you to write and link formulas, to test myriad assumptions, and to play the What If? game. A well written template is integrated throughout and allows you to see the results of changes at the push of a button. It's a fabulous tool to use in developing exactly the right recipe for your co-op's success.

The "key" is to write it correctly. Creating a good template for financial planning is an investment on the co-op's part. And a darned good one. The structure can be used again and again, year after year. You renew it by substituting new assumptions for the old, easily updating and modifying within the original skeletal form.

The most commonly used financial planning template is the annual operating budget, and we'll assume that model here. The principles of 'how to" write a template hold up under other scenarios as well.

Building a budget template

To be complete, a co-op's financial plan generates a projection of an income statement, cash flow, and balance sheet. To facilitate comparisons to actual performance, budget line items should parallel your chart of accounts, and the periods should correspond with the reporting periods used in preparing periodic financial statements.

It's essential to document your assumptions. By making them clear and accessible, you are making them easy to change when playing What If? and when you retrieve the structure in future years. Documenting your assumptions also makes it easier for those people not intimate with the preparation of the template to see the foundations on which it rests. Helpful schedules of assumptions include sales detail, margins, purchases, capital expenditures, debt service, and labor.

For the template to have continuing viability, it must be flexible. Let's assume that rent is now $1,000 per month and will increase 10 percent in the seventh month. Rather than entering the value 100 into six cells and 1100 into the other six, one enters 1000 into a column for base assumptions and enters 10% into a column for change assumptions.

The template designer then writes formulas that reference the assumptions in order to derive monthly values. However, if the co-op is negotiating a new lease, it's simple to see the results of higher and lower percentage increases simply by changing 10% to 12% or 6%. Consider, also, that next year one need only enter a new base rent and a new change assumption in order to complete this line.

For the template to have integrity, the components must be linked. The sales detail flows into the income statement, the income statement affects cash flow, and all link to the balance sheet. When one component is changed, ripples are felt throughout and these changes flow through smoothly, accurately, and in balance.

When structuring the template, consider the factors that impact the line item being projected. Is it variable and therefore stated as a percent of sales (e.g., discounts)? Is it fixed, and therefore the same each month (basic telephone bill)? Is it fixed but has seasonal variation (heat)? What internal or external forces may cause it to behave differently this year than in the past?

Example: department sales

Sales serves as a good example. First, we want the analysis to be by department and generally flowing from last year's performance. Next, list the factors that may affect performance and assign values to them, stated as a percent of change. Note that the formula is constructed so that as assumptions are changed, the computed result automatically changes:

                                                            Jan.                Feb.                March

Prior year sales, PRODUCE    10,000                9,000                9,500

Inflation                                                   3.0%              3.0%                3.0%

Install 12' case, 2/97                          0.0%              2.0%                  6.0%

Increased advertising                      0.0%              0.0%                2.0%

Hire new produce mgr., 2/97        -4.0%             -1.0%               2.0%

Compute sum of factors:                -1.0%                4.0%            13.0%

Compute current year sales: BASE x (1 + sum of factors)

Projected sales, current year        9,900                9,360            10,735

Now step back and apply the critical eye to the resulting change. Is this realistic? Adequate? Modify the assumptions until a satisfactory result is achieved. Sometimes we "know" that we're going to get a 10 percent increase in produce sales. The exercise is then to work backwards, identifying and assigning values to the activities that produce this "known" result.

Explicitly stating assumptions is also important because things will not be exactly as planned. You monitor variances throughout the year, looking at and critiquing the underlying assumptions. You update your model, learning from experience and analysis what was right and where you were way wrong.

It is really best if financial planning is guided by an established strategic plan. With that direction, management's charge is to find the best operating strategies to realize the desired strategic results. A well designed template encourages exploration and enhances management's creativity. Their attention to options exposes various contingencies for which they are now better prepared.

But please be careful. After all, these are projections. They can be made to say anything. There's a very real risk that while playing and working with the template one becomes wedded to a certain result and forces the figures to support it. "I'll just nudge sales up another 2 percent...increase gross margin by 0.5 percent...shave 1 percent off labor..." Remember, you are making financial commitments in your plan, and there are real world effects from the decisions made in it. Ultimately, management proposes and the board adopts a plan that stretches the co-op and asks it to fulfill its potential.

It takes an investment of time (and therefore money) to produce a financial planning template. Once in hand, however, it is a wonderful tool, fun to work with and illuminating. So get Excel or Lotus 123 or Quatro Pro and get busy writing a template that will shed a lot of light on your co-op's possible futures. There's plenty of help available from your accountant, from other co-ops, from consultants, and probably from among your staff and members, to get this up and running for you.