Instructions for Excel Itineration Report Note: This document is best viewed using the non-paginated view in your word processor. For instance, in Word use the normal view in the bottom left hand corner of the screen. In some of the screen shots you may see reference to the word Deputation or Deputational, please note that this word has now been changed to the word Itineration. A. First Time Setup. Double click on the IR0000.xls to open it. To keep things organized you may want to create a Finances folder under your My Documents folder and save IR0000.xls in it. Click on the IR tab at the bottom left of the screen if it is not already selected:

Double click on cell 3A and change “Joe Missionary” to your name and “Country” to your country:

Now scroll down to the second page and fill in cell H60 and H64 (highlighted in red below) with your monthly pledge required and total cash to raise, respectively. Remember that if your budged changes later you will need to reflect these changes here:

Now save these changes to IR0000.xls by selecting Save under the file menu:

B. Beginning of each IR setup. Double click on the IR0000.xls to open it. Now select Save As… from the File menu:

Save it with a name where the first two digits might represent the year and the second two the month. For instance, for January 2004 it would look like this:

Hit the Save button. Now double click on cell A3 and change the xx/xx to the month and year. For instance for January 2004 it would look like:

Now go down to the second page on the IR sheet and fill in cells H61 and H66 with the pledge to date, per you records and the present cash balance (highlighted in red in the screen shot below):

Now go down and fill in cell E70 with the number of churches this month, including district/sectional meetings. Also, fill in cell D71 with the number of conventions you had and double click on cell E71 and change the 0 to the number of services in those conventions and E72 for the number of churches that participated in those conventions. Now in cell D74 put in the number of itineration receipts that are attached, an in cell F74 the number of the first receipt and in cell H74 the number of the last receipt you are turning in:

Now in cell H78 put in the amount you are requesting for an advance for household furniture, furnishings, and appliances, if any. In cell H80 put in the amount you are asking for freight, shipping, customs, duties and related expenses. In cell H83 put in the amount you are asking for

as an advance for other field expenses including travel, rent and utilities deposits, language school, etc.

In cells B85 to I88 you may type any comments you might have. You will notice that the lines to not automatically wrap around. You will need to go to the next line to fill in more comments:

C. Inputting receipts. Select the Receipts worksheet tab towards the bottom of the screen:

You will notice the following list that serves as a guide for classifying receipts (do not delete or change this list):

The line number is what you will use to input a receipt that fits into the category of the description above. For instance if you stayed in a Holiday Inn in Corsicana Texas while ministering at First Assembly of God in the same city on January 23rd and paid $67.00, you would put the receipt in beginning at cell A23 as follows:

If the date is for the current year, you do not need to put the year in, it will automatically populate the year. You also do not need to put the dollar sign in the amount column or if there are no cents, the zeros. I use the row number as the receipt number and put this on the receipt and circle it so that it is easier to see. In the case above it would be 23 and in the case below it would be 24. Also, write on the receipt the description in English if it is in another language and the amount in dollars if it is in another currency. Only input receipts for expenses that occurred during the current month you are inputting. If you find a receipt from a previous month that you forgot to put in write an explanation on it explaining why this happened. Let’s say that the next receipt you are going to put in was for a special personal offering of $45.67 given to you by Betsy McGuire of Fort Worth, Texas on February 2, 2004. You would put this receipt in when you input the itineration receipts, which will be described later, but you would also put it in here as an expense, since you kept the offering. It would look something like this:

The #34567543 is the itineration receipt number. Continue inputting your receipts, one on each line, until they are all in. D. Inputting mileage log. Select the Mileage Log worksheet tab towards the bottom of the screen:

If you own a vehicle and are using it for itineration, input the date (if it is in the current year you do not need to input the year, it will automatically be put in when you tab to the next field), beginning and ending mileage, and place/reason for each work trip taken. The total and total mileage will automatically be calculated. Here is an example with two trips entered:

You will notice in the above example that the second entry is too big for the place/reason. To expand a column, place the mouse pointer between the column header after the column. The pointer will turn in to a line with an arrow going left and right. When this happens, click and drag to the right to expand the column. In the example above that would be between the D and E column. Here is the end result:

If you need more lines to input information, either in the mileage log or in the district report, which will be discussed later, click on the row header and then drag down for the number of rows you need to insert. The following is an example for inserting 4 rows:

Now right click in the highlighted area and select Insert from the menu:

This will leave you with the following result:

Now select the blank row above what you just inserted by clicking on the row header (in this case row 19) and left click in what you selected and select copy from the drop down menu:

Now select the inserted rows again, right click in the highlighted area and select paste:

The end result will look something like this with the new rows inserted:

E. Inputting the District Report. Select the District Report worksheet tab towards the bottom of the screen:

Fill in the Mail To sections in cells A4 to C5 for your District Missions Director. Fill in the From sections with your name and field in cells D4 to E5.

If you stay mainly in one or two districts you may want to input address info here in initial setup. This will keep you from having to put this in every month. Also, if you scroll down, you will notice that there is another District Report form below the first one. This can be used if you itinerate in a second district during the same month.

Now input your itineration receipts for the month. Remember, that if you don’t put in the year under the date column it will put in the current year for you automatically. Also, if you need more room in a column or more rows, you can expand a column or add rows using the directions given in the last section. The Total Offerings and Contributions to District Missions Office will update automatically. Remember that the receipts for different districts need to be put on separate District Reports. Here is a sample with two offerings put in:

To change the percentage given to the District Missions Office, first double click on cell C33 and change the text, say 2% to 5%. Next double click on E33 and change the formula from =E32*0.02 to =E32*0.05, or whatever percentage your district uses. Next you can put in whatever remarks you would like to send to the District Missions Office in cells B35 to E40:

You will notice that the remainder of the sheet updates automatically from the information you put on the IR worksheet. The exception to the is the Approved Project which will have to be put in manually. Here is a sample:

If you need more Monthly District Reports you can make as many copies as you like. Let’s look at an example for making a third copy. The same principles, adjusted for the new location, would need to be applied to each copy. First, select cell A53, scroll down, hold down the shift key and select cell E103. This will select the second copy of the Monthly District Report. Now right click somewhere in the selected area:

Now select cell A105, right click and select Paste from the menu:

This will paste a third copy of the Monthly District Report below the second copy. Now fill in cells A108 to C109 and cells E108 to D111 with the district and your information. Now find the cell where the total is being calculated for this report, in this case E137:

Remember this cell number and click on the IR tab then scroll to cell H41 and double click on it and add “+'District Report'!E137” (without the quotes) to the end of the formula in cell H41:

Repeat this process if you itinerated in more than 3 districts in a month (once for each additional district).

F. Creating Itemized List Select the Receipts tab:

Now select the receipts you put in by clicking the cell in the top left hand corner, scrolling down to the end of the receipts and while holding down the shift click on the cell in the bottom right hand corner. In the example below that was A23 and D30. Now right click in the selected area and select copy:

Now select the Itemized List tab:

Then right click on cell A32 and select Paste from the drop down menu:

Now with the receipts still selected, select Sort from the Data menu:

Select the following (make sure header row is selected):

This will sort the receipts by the line number column. Now with the list of receipts still selected select Subtotals… from the Data menu:

Click “yes” on the following:

Now select the following:

The result will look something like the following:

You will notice a Recpt. Column, here is where you put in the receipt numbers you circled on the receipts for all receipts over $75 and others (such as hotel and airline receipts). Don’t forget to send in copies of these receipts with your report.:

G. Calculating IR Select the calculation tab and then click on the red exclamation button on the Pivot Table menu. This will update all the calculations on the IR:

If you do not see the Pivot Table menu select (check it) on the Toolbars submenu under the View menu:

H. Printing the IR Select the IR tab:

Then click on cell A1 the scroll down, hold down the shift key and click on cell J46. This will select the first page of the IR. Now select Print from the File menu:

Then select the following, your setup will be different, but the main thing is to have Selection selected under the Print What section. You may want to print two copies, one to send in and one to file:

Now turn the page over and put it back in your printer to print on the reverse side and click on cell A47 then scroll down and hold down the shift key and click on cell I109. Now repeat the process above to print the selection. DON’T FORGET TO SIGN THE COPY YOU ARE SENDING IN. Now select the Mileage Log tab:

If you are using a regular vehicle that you own to itinerate in click on cell A1 then scroll down and hold down the shift key and click on cell E30 (or the bottom right hand corner of the log if you have added lines). Then repeat the process above to print the selection. If you are using a car with travel trailer or camper unit repeat the paragraph above only substitute cell G1 for cell A1 and cell K30 for cell E30. If you are using an STL vehicle to itinerate in (only missionaries stationed in U.S.A.) repeat the paragraph above only substitute cell M1 for cell A1 and cell Q30 for cell E30. If you are using an STL vehicle for other business miles (only missionaries stationed in U.S.A.) repeat the paragraph above only substitute cell S1 for cell A1 and cell W30 for cell E30.

Next, print the World Missions Monthly District Report by selecting the District Report tab:

Click on cell A1 and then scroll down and then hold down the shift key and click on cell E51. Follow the instructions above for printing the selection. You may want to print 3 copies of this, one for AGWM, one to send in to the district (with a check for the tithe calculated on the form) and one for your files. If you itinerated in more than one district during the month repeat the process above for each World Missions Monthly District Report you filled out only substitute the selection (i.e., for the second one select cells A53 to E103, etc.). Next, print out the Itemized List and Description by selecting the Itemized List tab:

Click on cell A1 and then scroll down and then hold down the shift key and click on the cell in the bottom right hand corner of the list of receipts. Follow the instructions above for printing the selections. You may want to use these totals to compare what you have on the IR and then use a calculator to double check the calculations on the IR. Mail the IR with a check for the amount found in cell I52 on the IR and the itineration receipts and the copies of the receipts for expenses over $75 (as well as others for airlines and motels) and a copy of the World Missions Monthly District Report and the Mileage Log and the Itemized List to AGWM.