Budgeting basics: profit planning with spreadsheets.

Budgeting is a means of planning and evaluating. Making decisions based on careful planning is of strategic importance in all businesses, small and large, profit and nonprofit. Camps are no exception, and directors should be able to improve profitability by preparing a budget and using it to compare the expected outcomes of different courses of action.

Need for planning

Many small businesses either do not bother to budget, or they compile haphazard financial plans without adequate consideration of possible alternatives. For years, Ginger Lakes Camp had operated with the assumption that costs would be somewhat more than the previous summer. Tuition was raised every year based on the demand for the previous year's sessions, tuition at similar camps, the inflation level, and a gut feel for how parents would react. Little attempt had been made to determine what the increases would be for major cost categories, nor had an income goal been established.

The accreditation standards of the American Camping Association require camps to prepare an annual budget. In order to meet this standard for the triennial accreditation visit, the camp director had simply taken the previous year's income statement and made some changes. This approach defeats the intent of the standard, which is to encourage conscientious and systematic planning.

Developing the budget

Three basic steps that Ginger Lakes Camp followed in creating and using a budget are:

* listing fixed and variable costs

* computing a break-even point

* evaluating modifications to programs or facilities.

Listing fixed and variable costs

The previous year's results provide a logical starting point for the budgeting process. The behavior of costs is easiest to analyze if they are classified as fixed or variable. Fixed costs are constant for all enrollment levels within the relevant range. Examples of fixed costs are:

* rent, depreciation, and property tax

* staff salaries

* office and promotional expense.

In contrast, variable costs change with the number of campers enrolled. Typical variable costs include:

* food

* counselor salaries

* medical insurance.

Some costs are difficult to classify because they have both a fixed and variable component. Telephone bills are an example of such a mixed cost because a monthly service charge is combined with a per call charge based on usage. Although mixed costs can be analyzed into their fixed and variable components statistically, this level of precision is not usually necessary. Any costs of a mixed nature that do not change significantly with enrollment can be treated as fixed; those expected to vary substantially with enrollment can be classified as variable.

Computer spreadsheets provide a powerful tool for analyzing alternatives during the budgeting process. Figure 1 illustrates creation of a basic budget. In Figure 2, the budget is used to evaluate the impact of enrollment on profitability, and in Figures 3-5 it is used to evaluate modifications in programs or facilities.

Figure 1 illustrates a basic budget in which the costs are divided between the fixed and variable categories. For a business organization, pretax operating income (Figure 1, line 23) is the excess of tuition revenue over both fixed and variable costs. The income statement for the prior year (column B) is the basis for projecting budgeted amounts for the upcoming season. The camp director should consult with suppliers, assess current economic trends, and evaluate cost structures in order to predict costs and revenues for the upcoming year, based on last season's enrollment level (column C). In this example, expected cost increases lead to a budgeted reduction of $8,000 in pretax operating income despite the increase in tuition.

BASIC     A                                          B         C

1         GINGER LAKE CAMP
2         BUDGET ANALYSIS                          PRIOR
BUDGETED
3                                                   YEAR     AMOUNT
4         NUMBER OF CAMPERS                         100       100
5
6         TUITION REVENUE                       $350,000
$370,000
7
8         FIXED COSTS
9         Rent or depreciation & property tax     75,000
80,000
10        Staff salaries                          65,000
70,000
11        Office expense & promotion              20,000
25,000
12        Fixed program costs                     12,000
15,000
13        Other fixed costs                       25,000
25,000
14        TOTAL FIXED COSTS                      197,000
215,000
15
16        VARIABLE COSTS
17        Food                                    36,000
37,000
18        Counselors' salaries                    45,000
50,000
19        Insurance premiums - variable           16,000
17,000
20        Supplies                                15,000
16,000
21        Other variable costs                     8,000
10,000
22        TOTAL VARIABLE COSTS                   120,000
130,000
23        PRETAX OPERATING INCOME               $ 33,000    $
25,000
24        CAMPER CONTRIBUTION                    $ 2,300     $
2,400
25        CAMPERS TO BREAK EVEN                       86
90

Figure 1 - Basic Budget

The break-even point

Tuition less variable costs is the amount available to cover fixed costs and provide a profit. Camp directors may find it informative to know the enrollment level at which tuition revenues will be just sufficient to cover costs. At this break-even point, profit is zero. When enrollment is greater than the break-even point, each additional camper will contribute to profit to the extent that tuition exceeds variable costs. The concept of camper contribution is tuition minus variable cost stated on a per camper basis.

camper contribution = (total revenue total variable costs) / number of campers

break-even point = total fixed costs / camper contribution.

In Figure 1, four additional campers are needed to break-even based on the anticipated price changes. The following formulas entered in column B and copied to the respective row in column C will produce the results illustrated in Figure 1:

B14 @SUM(B9..B13) B22 @SUM(B17..B21) B23 +B6-B14-B22 B24 (+B6-B22)/B4 B25 +B14/B24

The impact of enrollment

The impact of enrollment on operating results can be seen when different revenue and cost outcomes are viewed in juxtaposition, as in Figure 2. Fixed costs are constant at different enrollment levels while tuition revenue and variable costs increase or decrease in proportion to the change in enrollment. Last year's enrollment level (column C) is used to compute the cost per camper for each variable cost category. For example,

food cost per camper = food cost / the number of campers ($37,000/100) = $370

This per camper cost can then be multiplied by the different enrollment figures to produce the expected food cost for a given number of campers:

food cost for 120 campers = food cost per camper ($370)

x number of campers (120) = $44,400.

The following formula entered into cell D 17 and copied to all the cells within the block D17..E21 will determine all the variable costs in this fashion:

+$C17/$C$4*D$4.

Because fixed costs remain unchanged at different enrollment levels, the break-even point is also the same.

[TABULAR DATA FOR FIGURE 2 OMITTED]

Once a spreadsheet is designed to calculate these amounts, the impact of alternate enrollment levels can be assessed merely by entering the new number.

Figure 2 illustrates that if only 80 campers were enrolled (column D), tuition would be insufficient to cover costs, and the camp would operate at a loss. For Ginger Lakes Camp, 90 campers must be enrolled to break even. At that level, tuition would be just sufficient to cover both fixed and variable costs. (Profit would be precisely zero at an enrollment between 89 and 90.) For each additional camper enrolled above 90, the difference between the tuition and the variable cost per camper will be profit. In Figure 2, this budgeted amount is $2,400 per camper. From a strictly financial point of view, this feature of the camp's cost structure prompts the director to increase enrollment. For Ginger Lakes, if enrollment increases from 100 to 120 campers, the bottom line would increase by $48,000. Naturally, financial measures are only one basis on which to plan enrollment. Quality of experience issues also need to be considered given the increased camp size.

With the flexible budget framework in place, a few keystrokes can illustrate how different assumptions affect results. The camp director can experiment with the impact of different tuition levels and alternate assumptions about cost fluctuations. Spreadsheets are ideally suited to this type of what-if analysis. Discovering how possible changes in revenues and costs will position the enterprise financially should lead to better planning and a better sense of how costs will affect the bottom line when they are actually incurred.

Modifying programs or facilities

Summer camp facilities and program activities evolve over time. Every year camp directors contemplate adding or eliminating activities and undertaking construction [TABULAR DATA FOR FIGURE 3 OMITTED] projects or major renovations. The financial effect can be evaluated by viewing the budget with and without the change in question.

Adding an activity

Adding a new activity will entail additional costs. Ginger Lakes Camp considered starting a kayaking program. The first year, the new activity is expected to require a $5,000 cash outlay to purchase kayaks (classified as a fixed supplies and program cost) plus $500 for additional pre-camp staff training (classified as staff expenses). Ginger Lakes did not expect the new program to affect enrollment, and they intended to report the cost of the kayaks as an expense in the year of purchase. Figure 3 (column D) shows that since the kayaking program is expected to produce additional costs but no additional revenue, Ginger Lakes' operating income would decrease $4,600 while the break-even point would increase from 90 to 92 campers. The director must decide whether a kayaking program would improve the quality of the camp experience enough to justify the additional expenditure. For long-range planning, the relationship between the quality of the current year's experience and the future years' enrollment is also an issue.

Eliminating an activity

Some changes in camp programs are expected to impact revenues as well as costs. Ginger Lakes Camp is considering eliminating its horseback riding program. Doing so would reduce fixed supplies and program costs by $6,000 (by eliminating horse leasing and feeding costs and reducing the cost of liability insurance) and reduce staff expenses by $2,000. However, some campers might go elsewhere if horseback riding were no longer available. If other campers could not be recruited to take the place of the lost campers, tuition revenue would be reduced. Recruitment costs (classified under office expense and promotion) are expected to increase by $400 in order to replace the lost campers.

Cost savings can be readily estimated from historical price information. The estimated reduction in enrollment due to termination of the riding program is more subjective. In part, the estimate is based on the number of campers active in the horseback riding program during the previous session, and the likelihood of replacing campers lost due to elimination of the program. The spreadsheet makes it easy to evaluate different estimated enrollment levels. Figure 4 shows how the loss of only three campers (column D) compares to the loss of six campers (column E). Because of the reduction in fixed costs, the break-even point is lower in both cases. The [TABULAR DATA FOR FIGURE 4 OMITTED] worst case scenario results in a $6,800 reduction in pre-tax income, while the loss of three campers would increase pre-tax income by $400.

Constructing new housing

When a camp is at maximum capacity because of limitations in physical facilities, an increase in enrollment necessitates construction of new housing. Wood-frame camp structures are relatively inexpensive, and the annual depreciation expense is usually small when compared to the tuition revenue generated by more campers. We assume that these additional campers do not extend the [TABULAR DATA FOR FIGURE 5 OMITTED] enrollment level beyond the range in which other fixed costs are truly fixed.

In Figure 2 it was assumed that the physical facility was large enough to accommodate 120 campers. In Figure 5, an alternate assumption is made that two cabins would have to be added to accommodate the 20 additional campers. Ginger Lakes planned to finance the new construction with a $60,000 cash outlay, and to depreciate the cost on a straight-line basis over 15 years. This cost appears in Figure 5 as a $4,000 increase in the rent, depreciation, and property tax category. Recruitment costs for the new campers are assumed to be insignificant. The construction project will increase pretax profits by $44,000, since the additional tuition more than offsets the higher depreciation cost. However, because the fixed costs increase, one more camper will be needed to break even. (The budget reflects the effect of the investment on operating income. Based on cash flows, the $60,000 investment can be evaluated based on the expected return on the investment or the payback period.)

Conclusion

Budgeting is a valuable strategic planning tool that summer camp directors should utilize. Although budgets can be developed with pencil and paper, basic spreadsheets facilitate the process. Popular spreadsheet programs available in both Windows and DOS versions include Quattro Pro, Microsoft Excel, and Lotus 1-2-3, and sell for between $250 and $300. Programs for Macintosh users include Excel, Lotus 1-2-3, Claris Works, and Microsoft Works. Mail order sources include Computer Discount Warehouse (800-859-4239), The PC Zone (800-258-2088), and DataCal (800-292-6143).

Once the basics of moving around the spreadsheet and entering formulas are understood, a budget can easily be created for your camp using the model illustrated. The cost categories should be tailored to fit those used by your camp. The resulting tool should improve your understanding of the interrelationships between the various costs and revenues that affect your business.

Joan Hollister, M.S., CMA and Sally M. Schultz, Ph.D., CMA are both with the Department of Business Administration at State University of New York at New Paltz.