Instructions For Downloading and
Using The Coin Box Spreadsheet

Print this file out now, and refer to it for the following steps.

A sample Excel Spreadsheet (TrainingSpreadsheet.xls) is provided to make it easier for your Club’s Foundation Committee Chair to track Coin Box donations to RI’s Annual Programs Fund and to report to members how much they have contributed to date. If the spreadsheet format provided doesn’t meet your needs, please feel free to modify it as desired – it’s only a suggested starting point and is neither copyrighted nor protected. However, Centralia Rotary Club would like to know about changes or improvements that you make that may be of interest also to other clubs.

To use the spreadsheet, a working copy of Microsoft Excel or Microsoft Works must be installed on the Windows computer you will be using. If you are using the Microsoft Works spreadsheet, you will still be able to download the Excel spreadsheet and then save it as a Microsoft Works spreadsheet (TrainingSpreadsheet.xlr) using the directions below. However, you may need to adjust the width of columns containing dates. All other instructions below for an Excel spreadsheet will work with a Works spreadsheet. To make reports of donations made by an individual, Microsoft Word or other word processor that can import .doc files must be installed.

To Download The Sample Spreadsheet to Your Computer

1.The sample spreadsheet and reporting forms can be used for training new Foundation
Committee Members without disturbing your actual donation data. Three files will be downloaded and saved to a 3-1/2” floppy disk for your training use: “TrainingSpreadsheet.xls”, this document “Instructions.doc”, and “StatementOfContributions.doc”.

2.Insert a formatted non-copy-protected 3-1/2” floppy disk in Drive A.

3.One at a time, Copy or Paste the following links into your browser and then press Enter.

4.In the File Download window each time after you press Enter in Step 3 above, click on Save.
Then in the Save As window, move up the directory levels until you reach My Computer.
Then click on “3-1/2 floppy (A:)” and click on Save.

5.Create a “Coin Box Training” folder and a separate “Coin Box Donations” folder on your hard drive. The Coin Box Training folder will be used exclusively to train Foundation Committee members in the use of the Coin Box Spreadsheet, and the Coin Box Donations folder will be used exclusively to track and manage actual Coin Box donations received from club members.

6.Copy all three files on the floppy disk in Drive A to the Coin Box Training folder.

7.Remove the floppy disk from Drive A and set the write-protect tab so that it cannot be overwritten. Then store the floppy disk in a safe place so you can easily restore the practice files in the Coin Box Training folder if they become corrupted.

8.View the list of files in the Coin Box Training folder and double-click on the file “TrainingSpreadsheet.xls”. The sample spreadsheet should open in either a Microsoft Excel or Microsoft Works spreadsheet. If it does not, open your spreadsheet program (either Excel or Works) and click on File – Open – (Coin Box Training folder) – TrainingSpreadsheet.xls.

9.If you’re using Microsoft Works, save the open spreadsheet as a Works spreadsheet. Click on File – Save As, enter the file name “TrainingSpreadsheet,” make sure that the extension .xlr is selected, and then Click OK or SAVE. Then click Open, make sure you can see both spreadsheet files (TrainingSpreadsheet.xls and TrainingSpreadsheet.xlr). If you can, highlight TrainingSpreadsheet.xls and press Delete. Verify you want to delete the .xls record, and then press Cancel to return to the spreadsheet. The original Excel .xls spreadsheet has now been replaced in the Coin Box Training folder on your hard drive with the Works .xlr spreadsheet.

To Tailor the Spreadsheet to Your Club

1.On the open sample spreadsheet, click on cell E3 and enter the name of your Rotary Club where “Centralia Rotary Club” appears just above the column headings (A, B, C, etc), and then click on the green check mark.

2.Scroll up and down in the spreadsheet and notice that the first eight Rows remain fixed. This will allow you to always see the column entry headings when you enter or modify data.

3.Notice also that the donation data is entered sequentially by date. This will allow you to maintain a current running inventory of all donations made.

4.Additional columns may be inserted as desired to meet the specific needs of your Club; however, for training purposes, we’ll use the format initially provided from the floppy disk.

To Enter New Coin Box Donations

1.Let’s assume that Ed Eason makes another Coin Box donation of $34.79 on September 24, 2005. Scroll to the bottom of the data list and on Row 33 enter Eason in Column B, Sep 24 2005 in Column C, and 34.79 in Column D.

2.Note that only the last names of individuals are recorded. This is for sorting purposes that will be described later. If there are two Easons in the club (Ed and Jane), enter their names as “Eason, Ed” and “Eason, Jane” or as “Eason, E” and “Eason, J”. Again, this is for sorting purposes described later.

3. If donations are made to the Annual Programs fund by means other than Coin Boxes, enter the amount donated in the appropriate column.

To Save The Sample Spreadsheet

1.With the spreadsheet opened, click on File – Save.

To View Donations For An Individual

1.Let’s view all the APF donations made by Mary Nelson. Go to the end of all records in the database (Column H, Row 33). Click on this cell and hold the mouse button down while dragging the cursor up to the beginning of the first data Row entered (Column B, Row 9), and release the mouse button. NOTE: It’s VERY important that you select the ENTIRE database range, even if there are empty cells. It’s also VERY important that you do NOT select any Rows above Row 9. The entire database is now highlighted.

2.Click on Data – Sort. In the “My list has” area, make sure the “Header Row” radio button is selected.

3.In the “Sort by” drop-down box, select “Name” and in the first “Then by” drop-down box, select “Rec’d”. Click OK. The spreadsheet is now sorted alphabetically according to the last name. Click any single cell to deselect the entire database. Scroll to the bottom of the sorted database, and you will see the five donation records made by Mary Nelson.

To Calculate Donations For An Individual

1.Ensure that the ∑ symbol is showing in the upper formatting toolbars. If it is not, click on View – Toolbars – Standard.

2.Click on any open cell outside the database range (i.e., to the right of Column H or below Row 33). Then click on the ∑ symbol in the upper toolbar.

3.Highlight only the cells in the database that you want to add up -- in this case we want to add up only the donations made with Coin Boxes, so highlight only cells D29 – D33. Click Enter, and the sum 132.62 appears in the selected cell.

4.If you want to add up all her Coin Box donations during the 2004-2005 Rotary year, click on an empty cell outside the database range, press Delete just to make sure that the cell is empty, click on the ∑ symbol in the upper toolbar, highlight the range of donations between July 1, 2004 and June 30, 2005 (D29 – D32), press Enter, and 96.90 appears in the cell.

5.If you want to add up all her donations during the current calendar year (2005), click on an empty cell outside the database range, press Delete, click on the ∑ symbol in the upper toolbar, highlight only those donations made in 2005 (D29 – G33), press Enter, and 1132.62 appears in the cell.

To Report Donations Made To An Individual

1.Go to the Coin Box Training folder, view the list of files, and double-click on the file “StatementOfContribution.doc.” The Microsoft Word document will open in most word processing applications such as Microsoft Word, Microsoft Works, WordPerfect, etc. The Centralia Rotary Club letterhead object at the top of the page is a jpeg file scanned from a sheet of our letterhead stationery, and can be replaced with a scanned jpeg of your own letterhead.

2.The wording and formatting of the Statement of Contribution is that used by the Centralia Rotary Club, but you are free to adapt this to your own requirements. The blanks can easily be filled in by sorting and viewing the database information as described above.

3.Use of this or a similar format allows the Club Foundation Committee Chair to let each person know their status of donations as they progress toward their next Paul Harris level, and also allows the Chair to prepare donation summaries that are specific for a calendar year (IRS deduction statements) or for a Rotary year (EREY statements and status) as required.

To Prepare a Blank Spreadsheet For Use By Your Club

1.Copy the three files in the Coin Box Training folder to the Coin Box Donations folder.

2.In the copy of the spreadsheet in the Coin Box Donations folder, delete ONLY the training data. Highlight cells B9 – H33, then press Delete. You are now ready to enter data for actual donations as they are received from Club members.

3.Enjoy!

Comments and suggestions for improvement of the Coin Box spreadsheet are welcomed! Send them to:

Centralia Rotary Club

Coin Box Project

PO Box 183

Centralia, WA 98531

1