The case of the boundary hoppers

Learning the Spreadsheet

1. 
   Titling. Launch Microsoft Excel. There is a cross in the upper left-hand corner (cell 1A). Move the cursor over the “B” in the word processing toolbar and left-click. This will put whatever you type in bold face print. Type “Five Year Income History Worksheet” and hit the tab key to enter.
   

2. 
   Saving. To save the document, strike the Ctrl key and the “S” simultaneously. In the “File name” box, type “Income History.” Hit “Enter” to save.
   

3. 
   Reposition the cursor in cell 2A and select the cell. Type “Ordinary (Restricted) Income.” Hit tab key to enter.
   

4. 
   Type Face. Position the cursor in cell 3A . Left click on mouse. With cursor, strike the U in the word processing toolbar. This will underline whatever you type. Then type “Account Number” and hit the tab key.
   

5. 
   Resizing the Cells. Position the cursor immediately above cell 1A, where there is a letter A in a grey square. Move the cross to the line between the A cell and the B cell. The cross becomes a vertical line with horizontal arrows. Drag the cursor to the right so that the words “Account Number” fit within cell 3A.
   

6. 
   Select cell 3B. Type the word “Description” and hit the tab key. Type 1997 and hit the tab. Type 1998 and hit the tab. Continue until you have typed the year 2001. Finally, type the word “Average.” Then hit the return key.
   

7. 
   Underlining a Row. Select cell 3A. Drag the cursor to the right until you have selected all the cells from 3A to 3H. Then position the cursor over the U in the word processing toolbar and left-click it. This underlines the words and numbers in row 3.
   

8. 
   Italicizing. Position the cursor at cell 4B and select that cell. Then move the cursor to the italicized I in the word processing toolbar and left-click it. That will italicize whatever you type. Then type “Collection” and hit “Enter.”
   

Entering and Manipulating Data

9. 
   In cell 5A, type 470216. Hit tab. This identifies the line item. Then type the word “Envelopes.” Hit tab. Type 500,000 and tab, type 505,000 and tab, type 510,000 and tab, type 515,000 and tab, type 520,000 and tab, and type 525,000 and tab. This gives you five years’ history of the income from envelopes.
   

10. 
    Using a Function to Average. Position the cursor in cell 5H and left-click. Type in the equal sign (=). Then move the cursor to the “Paste Function” sign (fx) in the standard toolbar and left-click. In the “Paste Function” dialogue box, select “average” and left-click or click “OK.” Then, in the “average” dialogue box, click OK. This will give you the average of cells 5C to 5G.
    

11. 
    Applying a Function to a Row of Cells. Select cell 5H, the cell that represents the average of the numbers in cells 5C to 5G. Position the cursor over the lower right corner of the cell (where the “fill handle” is). Drag the fill handle down from cell 5H to cell 10H. Each and every cell fills with the average of cells C to G at the left.
    

Calculate a Linear Trendline in a Chart

PS 583, Parish Administration

(Taken from Microsoft Office Online)

Before you can calculate a trendline, you need to have your data in a chart. For this purpose, open Excel Spreadsheet Chart A, “Income History.” Your task is to project your income history one year forward.

1. 
   Highlight the data that you want to include in the chart (in this example, the contents of cells C47 through G47).
   
2. 
   On the Insert menu, click Chart.
   
3. 
   On the Standard Types tab, under Chart type, click Line.
   
4. 
   Click Finish.
   
5. 
   To remove the legend and make more room for your chart, click Legend on the Chart toolbar.
   

Now, calculate a trendline:

1. 
   In the chart, click on the line, so that each point in the line is illuminated.
   
2. 
   On the Chart menu, click Add Trendline.
   
3. 
   In the Add Trendline dialog box, click the Type tab.
   
4. 
   Under Trend/Regression type, click Linear. This creates a straight trendline.
   
5. 
   Click the Options tab.
   
6. 
   In the Add Trendline box, under Forecast, type 4.
   
7. 
   Select the Display R-squared value on chart check box.
   
8. 
   Click OK.
   

The R-squared value, which tells you how closely your trendline follows your data, appears on the chart. As you can see by the chart, the trendline doesn't follow the data very closely because the sales rise rapidly in the first few years and then start to level out. A straight-line (linear) trendline that includes the rapid-growth early years may overestimate future sales. You can compensate by asking Excel to plot a logarithmic trendline.

Add a logarithmic trendline

Logarithmic trendlines are generally good for data that rises quickly and then starts to level off. You can add a logarithmic trendline and compare it to the linear trendline.

1. 
   Click the trendline on the chart so that the various data points are illuminated.
   
2. 
   On the Chart menu, click Add Trendline.
   
3. 
   In the Add Trendline dialog box, click the Type tab.
   
4. 
   Under Trend/Regression type, click Logarithmic. This creates a curved trendline.
   
5. 
   Click the Options tab.
   
6. 
   In the Add Trendline box, under Forecast, type 4.
   
7. 
   Select the Display R-squared value on chart check box.
   
8. 
   Click OK. Compare the linear trend to the logarithmic trend.
   
9. 
   Parish Budgeting: Two Case Studies
   

Case 1: Dispute over Budgeting

The finance council at St. Philip Neri Church is studying how to implement a new diocesan-mandated system of categorizing expenses in the parish budget. The new system uses categories different from what the parish has used in the past. The new system distinguishes between “operating expenses” (necessary for the day-to-day administration of the parish, including supplies and equipment) and “discretionary expenses” (including programs, ministries, and events).

Parish staff members dislike the change because they will have to re-do their budget projections. One businessman on the pastoral council has complained to the pastor, Father Nguyen. The businessman says that, by introducing the category of “discretionary expenses,” the finance council is trying to identify areas for budget cutbacks. “The financial tail,” he says, “is wagging the pastoral dog.”

Father Nguyen will meet tonight with the pastoral council to discuss the new budgeting system. What should he say?

Case 2: The “Science” of Budgeting

A new subdivision of over 100 condos has opened within the boundaries of St. Bridget’s Church. The parish census committee has reported to Father Ferré and the pastoral council that Mass attendance will grow by ten percent in one year. The council members are delighted. They recommend that the expected rise in parish income should be spent on a new religious education classroom. The DRE and the business manager have already begun to make inquiries about the cost of portables.

The finance council, however, is putting on the brakes. It has maintained a detailed record of parish income for the past ten years. From this record, the committee has projected a five percent income growth. To whom should Father Ferré listen – to the parish pastoral council or to the parish finance council?