COMPUTERIZING YOUR PROGRAM 
by Rick Curtis, Director, Outdoor Action Program, Princeton University

This material may be freely distributed and used by the anyone. However, if included in publications, written or electronic, attributions should be made to the author. 
Spreadsheets: Beyond Budget Planning

	Spreadsheets began as a basic ledger sheet using number rows and columns. You type text and numbers into the cells on the spreadsheet and perform calculations on the data. 

	This section of a sample budget illustrates how a spreadsheet works. Rows are indicated by numbers and columns by letters. A specific cell is the intersection of a row and a column such as A1. As you can see, both text and numbers can be typed into cells. For example in cell C6 the number $749.00 has been entered. Calculations are performed by entering formulas in cells. For example, cell C13 calculates the subtotal of the printing charges with the formula C6+C7+C8+C9+C10+C11 or a short cut formula SUM(C6:C11). The formula in cell D13 calculates the printing cost per participant by dividing the printing subtotal by the number of frosh participants through the formula C13/E1.

	A	B	C	D	E	
1	FT ‘93 Costs			Number of Frosh	440	
2						
3	ITEM	TYPE	COST 	COST/FROSH		
4						
5	PRINTING & MAILING		 			
6	Printing - first frosh letter - 1200	PO	749.00			
7	Mailing - first frosh letter	PO	619.13			
8	Printing - second frosh letter - 350	PO	250.00			
9	Mailing - second frosh letter	PO	40.00			
10	Other postage/shipping	Check	225.04			
11	Alumni Records	Cash	68.40			
12						
13		Subtotal	$1,951.57	$4.44		

	Today's spreadsheets allow you to do much more than just calculate this year’s budget. They can be used for any information that needs to be displayed in a table format. The key to using a spreadsheet effectively is expanding your idea of the types of information and/or calculations that can usefully be displayed in rows and columns, for example, using a spreadsheet as a database. Any information entered along one row of a spreadsheet can be treated as one record in a database program. In fact, most high end spreadsheets can import or export information to a format usable by database programs and vice versa. Spreadsheets also have charting capabilities that allow you to select rows and columns of numbers and plot a chart of the data. Here are some ideas of how you can make your spreadsheet program work for you.

1. As a Project Management Tool 

	In this case the spreadsheet has been set up as a project manager for planning a Frosh Wilderness Program. The range of cells from A3 through D10 is defined as a database with four basic columns, Starting Date, Ending Date, Function and Activity. You can tell the database to sort off of any of the first three columns or combinations of the three. In this case the data is sorted on column A in ascending date order. You could also sort it first on column C in ascending function order and second on column A and see all the leader tasks in chronological order, followed by all the mailing tasks, etc. For many projects this method may work well enough that you don’t need specific project management software.

	A	B	C	D	
1	START	END 	FUNCTION	SPECIFIC ACTIVITY (See Cell Notes)	
2					
3	Oct-1	Oct-30	Mailing	Redo OA Brochure for Admissions so it reflects dates for next FT	
4	Nov-15	Nov-30	Routes	Contact Catskills Rangers with dates and routes for next fall	
5	Jan-1	Jan-2	Transportation	Contact Starr Transit re: bus rental & prices, compare against Suburban	
6	Mar-1	Mar-7	Storage	Contact Building Services re: location for Early Arrivals storage, amounts,	
7	Mar-25	May-7	Leaders	Recruit Leaders - through meeting, OA News, Leader News, ads, explain need for people to have all requirements completed, explain new support random selection process,	
8	Mar-25	May-7	Leaders	All leaders must have Van Driver Training, send reminders out to everyone	
9	Mar-31	Mar-31	Workers	Contact Cindy Horr re: summer housing in Forbes for OA summer staff	
10	Apr-1	Apr-1	Mailing	Write & distribute information to Admissions Officers re FT	

	Another example of spreadsheets for logistical planning is this van logistics schedule.

	A	B	C	D			E	F	G	
1				OA FT '93 VAN SCHEDULE						
2	NUMBER	VAN	SIZE	DRIVERS	OUTFIT 	TRAILER	DEPART	BASE	RETURN	
3	VAN1	DG1	15	 	Roof Rack		Pines	Pines	 	
4	VAN2	Whig	15		 			SPARE		
5	VAN3	DG2	15	 	Bike Rack		 	Bike	Gap	
6	VAN4	NEW1	15	 	Roof Rack	PBC - 2"	Gap	Cats	Gap	
7	VAN5	NEW2	15	 	Roof Rack	PBC - 2"	Gap	Gap	Gap	
8	VAN6	NEW3	15	 	Roof Rack	OA - 2"	Gap	Gap	Gap	

2. As a Food Planning/Ordering Tool 

	For a large program with a standard food menu, you can set up a spreadsheet to automatically calculate your order based on the number of groups. In this case food item is entered in column A, the amount per group in column B, the unit size in column C, and the number of groups in column E. Column F calculates the total order for Grape Nuts by the formula B4*E4. You can simplify your spreadsheet by setting the number of groups to a constant. By entering a cell address into a new cell, the spreadsheet will you can reference the first cell. If you enter 50 in cell E1, and then enter the cell address E1 into cells E4 - E8, then all you have to do is change the number of groups in cell E1 and all cells referring to E1 will change automatically recalculating the entire spreadsheet. 

	A	B	C	D	E	F	G	
1	ITEM	AMOUNT	UNIT	ORDER SIZE	50	ORDER 		
2		 			GROUPS 	        		
3	BREAKFAST							
4	Grape Nuts   	2	boxes   	24 oz. box	50	100	boxes	
5	Granola	3	pounds	12/2#	50	150	pounds	
6	Bisquick	20	oz.	20 oz.	50	1000	oz.	
7	Instant Oatmeal	2.5	pounds	8/42 oz.	50	125	pounds	
8	Swiss Cereal	2	pounds		50	100	pounds	

3. As a Logistics Planning Tool 

	Below is a backpacking trip route in Pennsylvania. Using a spreadsheet you can lay out all of the routes in this area across the spreadsheet so that you can scan down to see each group’s activities for each day and scan across to see other groups. A simple formula such as B10+B18+B26+B34+B42+B49 will sum the mileage's for each day and give you a total trip mileage in cell B50. By setting up each group using the same spacing for each day (in this case 7 rows per day), you can easily establish common formulas. For example, the next group, BF27 would use columns C and D. The mileage calculation would use the same row numbers and substitute column D for column B. 

	A	B	
1	TRIP #	BF26	
2	Bus:	Bus 5	
3	Stop:	1st	
4	DEPART	Rt. 44 at Trout Run Rd. parking lot south of firetower	
5			
6	WED	From lot hike south and east on Black Forest trail (BF) to 	
7		Callahan Run, campsite #1	
8			
9			
10	Mileage:	3	
11	Water:	Spring at Trout Run Road camp, Callahan Run	
12	Campsite:	Camp #1	
13			
14	THURS	Hike north on BF past Naval Run to camp at Little	
15		Slate Run,  campsite #2 	
16	 	LONG DAY GET AN EARLY START	
17			
18	Mileage:	9	
19	Water:	Naval Run, Little Slate Run	
20	Campsite:	Camp #2	
21			
22	FRI	Hike north on BF then east and south to camp at Slate Run, camp #3	
23			
24			
25	Short Cut:	Take dirt road just beyond Manor Fork Rd., short cut east back to BF (cuts off 2 miles)	
26	Mileage:	7	
27	Water:	Forest Hollow, Slate Run	
28	Campsite:	Camp #3	
29			
30	SAT	Hike north on BF then west down Red Run, to just before Francis Road 	
31		to camp #4	
32			
33			
34	Mileage:	6.5	
35	Water:	Red Run, Campsite #4	
36	Campsite:	Camp #4	
37			
38	SUN	Hike west on BF then south, cross Rt. 44 camp along Line Branch near campsite 4.5	
39		NOT AT 4.5	
40			
41		BE CAREFUL CROSSING ROAD	
42	Mileage:	6.2	
43	Water:	Line Branch, NO WATER AT CAMP	
44	Campsite:	Line Branch near Camp #4.5	
45			
46	MON	Hike north on the BF to jct. Rt. 44 2.5 miles north of landing strip	
47		by 11:00 AM for pick-up	
48			
49	Mileage:	1	
50	Total Mileage:	32.7	
51			
52	RETURN	Rt. 44 parking lot 2.25 mi north of landing strip  	
53	Time:	11:00 AM	
54	Stop:	1st	
55	Bus:	Bus 5	

	The more important aspect of this spreadsheet is its cell linking functions. You can see below a sample of the departure section of the spreadsheet. In this case you can see the actual values for row 4 are direct cell links to the route plan from BF26. You can also see how setting a standard format makes your cell formulas much easier. This allows you collect all of the departure information in one area of the spreadsheet to make bus reservations. Any changes to the route need only to be made in one location and all linked cells will automatically be updated. For example, a change in the bus number for group BF26 only needs to be changed in cell B1. 

	M	N	O	P	
1					
2	VEHICLE	STOP	LOCATION	GROUP	
3					
4	Bus 5	1st	Rt. 44 parking lot south of firetower	BF26	
5	Bus 5	2nd	Rt. 44 parking lot north of firetower	BF27	
6	Bus 5	2nd	Rt. 44 parking lot north of firetower	BF28	
7	Bus 5	3rd	Rt. 44 parking lot 2.25 miles north of landing strip	BF29	

	M	N	O	P	
1					
2	VEHICLE	STOP	LOCATION	GROUP	
3					
4	=B2	=B3	=B4	=B1	
5	=D2	=D3	=D4	=D1	
6	=F2	=F3	=F4	=F1	
7	=H2	=H3	=H4	=H5	

	Similar linking can be done be done to compile campsite information, water availability, and other information for each group on a daily basis. 

	Q	R	
1		CAMPSITE SCHEDULE FOR BLACK FOREST GROUPS	
2			
3		WED	
4	BF26	Camp #1	
5	BF27	Camp #4.75	
6	BF28	Camp #2	
7	BF29	Camp #4.25	

	Q	R	
1		CAMPSITE SCHEDULE FOR BLACK FOREST GROUPS	
2			
3		=$A$6	
4	=B1	=B12	
5	=D1	=D12	
6	=F1	=F12	
7	=H1	=H12	

	You can take this a step further by linking cells from one spreadsheet to an entirely new spreadsheet. In this scenario, you can have the range of cells covering the departure information (M1 through P7 in the example above) from each different route spreadsheet linked into one spreadsheet consolidating departure information for all trips. Cell linking between spreadsheets can be set to update automatically so that any changes in the parent spreadsheet is made automatically the next time you open the child spreadsheet. This can be a great help with complicated logistics. You will need to change only the route itself and all other dependent information in other spreadsheets will be updated.    

4. As an Analysis Tool

	Remember that spreadsheets allow you to do both charting and statistical analysis of information. Here are a few examples some simple analytical uses of a spreadsheet.

Cost Comparisons

	A				B	C	D	
1	SLEEPING BAG COST COMPARISONS							
2				# OF BAGS				
3		BASE COST	COST/	20	28	30	35	
4	North Face	Cats Meow						
5	Reg	81	81	1620	2268	2430	2835	
6	Lg	85	 					
7	Average	81.00						
8								
9								
10	Caribou	Blue Moon	 		 			
11	Reg	83	75.6	1512	2116.8	2268	2646	
12	Lg	85	 	 	 	 	 	
13	Average	84.00						

Equipment Rental Fee versus Lifespan

	A	B	C	D	E	F	G	H	
1									
2									
3			Days of use/year						
4	Item	Initial Cost	5	10	15	20	25	30	
5	External frame backpack	$85 	$17.00 	$8.50 	$5.67 	$4.25 	$3.40 	$2.83 	
6	Three-season Sleeping Bag	$80 	$16.00 	$8.00 	$5.33 	$4.00 	$3.20 	$2.67 	
7	Two-person tent	$150 	$30.00 	$15.00 	$10.00 	$7.50 	$6.00 	$5.00 	
8									
9									
10		Base rental rate							
11									
12			Years of use						
13	Item	Initial Cost	1	2	3	4	5	6	
14	External frame backpack	$85 	90.1	95.2	100.3	105.4	110.5	115.6	
15	Three-season Sleeping Bag	$80 	84.8	89.6	94.4	99.2	104	108.8	
16	Two-person tent	$150 	159	168	177	186	195	204	
17									
18									
19		Increase in replacement cost per year of use assuming 6% inflation/year.							
20									
21			Years of use						
22	Item	Uses/year	1	2	3	4	5	6	
23	External frame backpack	5	$18.02 	$9.52	$6.69	$5.27	$4.42	$3.85	
24		10	$9.01 	$4.76	$3.34	$2.64	$2.21	$1.93	
25		15	$6.01 	$3.17	$2.23	$1.76	$1.47	$1.28	
26		20	$4.51 	$2.38	$1.67	$1.32	$1.11	$0.96	
27		25	$3.60 	$1.90	$1.34	$1.05	$0.88	$0.77 	
28		30	$3.00 	$1.59	$1.11	$0.88	$0.74	$0.64 	
29									
30									
31		Actual rental rate based on lifespan and replacement cost							

1	A	B	C	D	
2					
3			Days of use/year		
4	Item	Initial Cost	5	10	
5	External frame backpack	85	=$B$5/C4	=$B$5/D4	
6	Three-season Sleeping Bag	80	=$B$6/C4	=$B$6/D4	
7	Two-person tent	150	=$B$7/C4	=$B$7/D4	
8					
9					
10		Base rental rate			
11					
12			Years of use		
13	Item	Initial Cost	1	2	
14	External frame backpack	=$B$5	=$B$14+($B$14*(0.06*C13))	=$B$14+($B$14*(0.06*D13))	
15	Three-season Sleeping Bag	=$B$6	=$B$15+($B$15*(0.06*C13))	=$B$15+($B$15*(0.06*D13))	
16	Two-person tent	=$B$7	=$B$16+($B$16*(0.06*C13))	=$B$16+($B$16*(0.06*D13))	
17					
18					
19		Increase in replacement cost per year of use assuming 6% inflation/year.			
20					
21			Years of use		
22	Item	Uses/year	1	2	
23	External frame backpack	5	=C14/($B$23*C22)	=D14/($B$23*D22)	
24		10	=C14/($B$24*C22)	=D14/($B$24*D22)	
25		15	=C14/($B$25*C22)	=D14/($B$25*D22)	
26		20	=C14/($B$26*C22)	=D14/($B$26*D22)	
27		25	=C14/($B$27*C22)	=D14/($B$27*D22)	
28		30	=C14/($B$28*C22)	=D14/($B$28*D22)	
29					
30					
31		Actual rental rate based on lifespan and replacement cost			

Frosh Trip Participation

	A	B	C	D	E	
1	Year	Participants	Leaders	% of Class	% Increase From 
Previous Year	
2						
3	1974	100	15	8.7%	 	
4	1975	120	25	10.4%	20.0%	
5	1976	150	30	13.0%	25.0%	
6	1977	180	36	15.7%	20.0%	
7	1978	200	40	17.4%	11.1%	
8	1979	200	40	17.4%	0.0%	
9	1980	220	44	19.1%	10.0%	
10	1981	220	44	19.1%	0.0%	
11	1982	230	45	20.0%	4.5%	
12	1983	320	70	27.8%	39.1%	
13	1984	350	80	30.4%	9.4%	
14	1985	390	90	33.9%	11.4%	
15	1986	350	80	30.4%	-10.3%	
16	1987	290	80	25.2%	-17.1%	
17	1988	350	105	30.4%	20.7%	
18	1989	388	105	33.7%	10.9%	
19	1990	396	110	34.4%	2.1%	
20	1991	430	106	37.4%	8.6%	
21	1992	456	119	40.2%	6.0%	
22						
23	TOTAL =	5340	1264			

Frosh Trip Staff/Participant Calculations

	A	B	C	D	E	F	G	H	I	
1	OA Frosh Trip Participation Plan 									
2										
3	Critical Fields - E7 (Total Backpack Ldr), E8 (Total Bike Ldr), E17 (Total Backpack Frosh)									
4	 E21 (Total Bike Frosh)									
5	LEADERS	Primary	Co	Assist	Third	TOTAL				
6										
7	Backpack	37	20	35	12	47				
8	Bike	1		2	2	1				
9										
10				TOTAL TRIPS =		48				
11										
12										
13	These no. can support 		47	total trips backpacking (as long as Assist is > Primary).						
14										
15	47	Backpack trips can support a total of			564	persons				
16				-	96	Leaders (see V57)				
17				=	468	Frosh				
18										
19	1	Bike trips can support a total of			11	persons				
20				-	2	Leaders				
21				=	9	Frosh				
22										
23				TOTAL FROSH =	477					
24										
25	CANCELLATION RATE									
26	Year	Number	Total	Percent						
27	'85	25	390	6.0%						
28	'86	34	350	8.9%						
29	'87	19	290	6.1%						
30	'88	27	350	7.2%						
31	'89		388	 						
32	'90	36	396	8.3%						
33	‘91									
34	Average =	28.2		7.3%						
35										
36	Projected '92	26	430	6.0%						

Conclusion
	
	Setting up a good spreadsheet will take you some time. In the end, a the time you spend will allow you to use your computer for more than word processing.

Rick Curtis is the Director of the Outdoor Action Program at Princeton University. He has been computerizing the program for the past eight years.