Page 4

Introduction:

Scouting, as a youth activity will sometimes require money to exchange hands from the youth’s family to the Scouting Section (Group, Area, Council or Store). Giving receipts as proof of the transaction is an appropriate business practice. It is worthwhile considering the families’ use of these receipts and therefore the format and information on the receipts.

Receipts are useful as input to a family budgeting exercise or as proof of expense in a family breakdown situation, but the largest use is, receipts used to develop tax deductions and tax credits Federally, Scouting has been deemed a physically active youth program by the Canada Revenue Agency (CRA) and Ontario has specifically included Scouting in its eligibility list. The possible tax use of receipts includes:

·  Child care deductions for camps that are longer than 5 days, such as summer camp programs or trips to Jamborees

·  Federal Children’s Fitness Tax Credit for Scouting registration and Scouting events lasting three of more days. Explanation of applicability is available at the
http://www.cra-arc.gc.ca/fitness/

·  Federal Children’s Arts Tax Credit for Scouting registration and Scouting events lasting three of more days. Explanation of applicability is available at the
http://www.cra-arc.gc.ca/tx/ndvdls/tpcs/ncm-tx/rtrn/cmpltng/ddctns/lns360-390/370/menu-eng.html

·  Ontario Children’s Activity Tax Credit for expenses similar to the Federal Children’s Fitness Tax Credit but more expenses are applicable, e.g. uniforms. Explanation of applicability is available at the http://www.fin.gov.on.ca/en/credit/catc/index.html

The non-tax based use and the Child care deduction use of receipts have ordinary content requirements. The other uses have similar special requirements with respect to information on the receipt. The receipt requirements are listed on the http://www.cra-arc.gc.ca/tx/ndvdls/tpcs/ncm-tx/rtrn/cmpltng/ddctns/lns360-390/365/rgnztns-eng.html => Federal site in the paragraph titled “Issuing Receipts”.

Care needs to be taken when creating the receipts to ensure that the amount listed as paid on the receipt is an amount actually paid by the person whom you specified on the receipt as payer. Possible situations leading to an errant receipt include:

·  For a youth who is a dependant of his or her parents, when Grandma pays, the receipts go to Grandma and are of no tax use to her.

·  For a youth whose family has decided to pay part of a Scouting cost with their allocation from a fundraising activity, the family’s receipt is not to include the amount from fundraising

·  For the tax credit applications, the amount actually paid and the eligible amount may be different and care needs to be taken to not include ineligible expenses as explained in
http://www.cra-arc.gc.ca/tx/ndvdls/tpcs/ncm-tx/rtrn/cmpltng/ddctns/lns360-390/370/rgnztns-eng.html =>this link which has a paragraph “Calculating the amount of eligible fitness expenses”.

Overview:

Two files provide the Receipt Maker facility. The first is an Excel workbook, into which all the raw receipt information goes for the receipts to be produced. The intent is to produce one receipt for each child within each family. Each row of the “Data Entry” worksheet corresponds to a line on a receipt. This facility gathers all expense items for a particular youth on a particular day together; the mail merge will print them on one receipt.

The Excel worksheets have been password protected with the password “p”. The reason for password protection is that the tab key will jump from cell to cell of the cells that should contain information you’ll be entering and will skip over the cells that contain formulas that shouldn’t be modified. This facility supports up to 998 rows of transaction data; more than that will require modification to the facility or splitting of the work. Some of the formula columns have been hidden to make the viewable one easier to read and complete.

The second file is the Word document that is the base of the mail merge. You can change this to meet your formatting desires but care should be taken around the programming that drags in the merge fields; it is complex and grumpy.

Once you have the base in the format you want, you should “open the data source” which really means connect this mail merge to the Excel file you’ve filled with good data. If you now do a mail merge to a new document you‘ll have an opportunity to preview then print your receipts.

This facility uses two more files that you download from MyScouts.ca: a membership report for your whole Group; and a batch detail report. The Receipt Maker assumes default names of “members.xls” and “batch.xls” respectively, but these names are selectable options.

Step By Step:

1.  Get the three files that comprise this facility

  1. Receipt Maker - Notes.doc (this file)
  2. Receipt Maker - Base.doc
  3. Receipt Maker - Data.xls

2.  From MyScouts.ca, download:

  1. A membership report for your whole Group, suggested name “Members.xls”.
  2. A batch detail report for your Group, suggested name “Batch.xls”.

3.  Gather and store data about transactions that are to be receipted.

  1. Open “Members.xls” (or whatever you called it). Leave it open until after you close “Receipt Maker - Data.xls”.
  2. Open “Batch.xls” (or whatever you called it). Leave it open until after you close “Receipt Maker - Data.xls”.
  3. Open “Receipt Maker - Data.xls”
  4. The first time and when changes are required follow these instructions:
  5. “Receipt Maker - Data.xls” has five worksheets. Go to the “Tables” worksheet (recent versions of Excel will have this as a red tab at the bottom)
  6. The “tab” key will jump from cell to cell for the cells so you can modify on this sheet.
  7. Set the name of your membership report sheet if it is different from the default. The format is
    [filename] worksheetname. The tab at the bottom of a worksheet displays its name.
  8. Set the name of your batch detail report worksheet if it is different from the default.
  9. Set the start and end dates for the batch detail you wish to include on the receipts. This facility over comes the current problem with MyScouts.ca Batch Detail Report in that it does not honour the date parameters on the “request a report” page. It always downloads everything. This means that if you are creating receipts for 2012, then you probably don’t want to consider a batch detail line item for January 15th, 2013.
  10. Save the workbook back into “Receipt Maker - Data.xls” (suggestion: save the original somewhere else before starting this process).
  11. To enter information about payers:
  12. Go to the “Payer Entry” worksheet (recent versions of Excel will have this as the yellow tab at the bottom).
  13. This is the place to enter information about payers: name; the order in which you want the receipts to print; the payer’s address if that is different from the participant address(es). You only need to enter a Payer’s information once in this list, no matter the number of participants for whom the payer has paid.
  14. If nothing is placed in the Payer Order column, the default print order is ascending by last name, then first name. By entering numbers in the Payer Order column you can affect the print order because the print order is then first sorted by payer order, then by last name then by first. For example, if you put
  15. 11 in the Payer Order column beside all the Beaver Leaders,
  16. 12 for all other payers of Beaver participants,
  17. 21 for all A Pack Cub Leaders,
  18. 22 for all other payers of A Pack Cub participants,
  19. 25 for all B Pack Cub Leaders,
  20. 26 for all other payers of B Pack Cub participants,
  21. 31 for all Troop Leaders,
  22. 32 for all other payers of Troop participants,
  23. Etc.

Then you would have receipts grouped by Section with Leaders on top and the rest below in last name order.

  1. If nothing is entered into any of the address columns, then the receipt will have the address of the associated with the participant. Participant addresses were downloaded in the Membership Report. If the payer has a different address from the participant, then enter that address here and it will appear on the receipts for this payer. This can be used in cases where the payer is a separately resident grandparent or non-custodial parent.
  2. Note that the tab key takes you from column to column and back to the start of the next line as you enter payer information
  3. A suggestion is to start by leaving the payer address info blank until while entering the transaction data on the “Data Entry worksheet you notice that it needs to be different from the participant’s address information.
  4. To enter a receipt transaction:
  5. Go to the “Data Entry” worksheet (recent versions of Excel will have this as the green tab at the bottom).
  6. On the next available row in the first column, enter the name of the person who paid the money associated with the transaction you are going to receipt. There is a dropdown list of all the payers you have entered. If your list of payers is not too long then you may wish to use that. If you type a payer’s name that has not been entered on the “Payer Entry” worksheet, then the system will warn you and you can correct spelling (if errant) or enter the payer info on the “Payer Entry” worksheet later. The name will remain highlighted in red until the “Payer Entry” worksheet is updated.
    If you complete the entry of the payer’s name (or any field) by pressing the tab key instead of the enter key, you will be taken to the next column into which you need to enter data.
  7. The participant’s MyScouts.ca membership number is entered next and is used to pull the member’s name, address and age from the membership report workbook.
  8. The next three columns are standard: date; description; amount of the transaction
  9. The Eligible Amount column records that part of what was paid which is applicable toward a Child Fitness / Children’s Arts Tax Credit.
  10. The Fundraised column records any fundraised money that was used to reduce the amount paid
  11. Repeat entering transaction, remembering to periodically save, until you have rows for all transactions for which payers should get a receipts.
  12. The final save before creating the receipts should include:
  13. First go to the “Table” worksheet and check for warnings;
  14. A batch warning means you have a batch detail line with no receipt being issued to someone for the participant’s registration. Go to the “Batch Detail” worksheet and look for a “FALSE” in the “Don't need one or Got a Receipt” column.
  15. If there are other warnings, they are about changes that have affected the internal workings of the Receipt Maker Facility. The changes to parts of the workbook that were protected. You will have to unprotect the sheet to fix things. Remember to re-protect the worksheets again.
  16. Go to the “Data” worksheet and check for reasonability of that data you see
  17. Save the workbook while viewing the “Data” sheet
  18. Close “Receipt Maker - Data.xls”

4.  Adjust receipt format.

  1. Open “Receipt Maker - Base.doc”
  2. The receipt will open, but immediately, you’ll be prompted as to whether or not you wish to run an SQL command from a file that likely does not exist on your machine. Press the “no” button
  3. Edit the Group name and address placing your top of page info on both the receipts. Remove any images that you don’t want and replace them with your Group’s images. Be careful of the wrapping and in front/behind order options that you choose.
  4. The Group Rep line at the bottom of the receipts will also need to be set to your name and title as the person producing the receipts.
  5. Save and close “Receipt Maker - Base.doc”.

5.  Initiate the mail merge

  1. Open “Members.xls” (or whatever you called it). Leave it open until after you close “Receipt Maker - Data.xls”.
  2. Open “Batch.xls” (or whatever you called it). Leave it open until after you close “Receipt Maker - Data.xls”.
  3. Open “Receipt Maker - Base.doc”
  4. The receipt will open, but immediately, you’ll be prompted as to whether or not you wish to run an SQL command from a file that likely does not exist on your machine. Press the “no” button
  5. For later versions of Word (i.e. 2007 +) make sure that the “Confirm file format conversion on open” option is checked.
  6. Do this by selecting “File”, then “Options”, then “Advanced”. Scroll to the bottom of the advanced options. Under the title “General” there is a check box and line “Confirm file format conversion on open”. Check the box.
  7. Go to Word’s mail merge interface and select from where the data is to come. Your experience will resemble or be a mash-up of Word 97 or Word 2010
  8. For Word 97 select “Mail merge” from the “Tools” dropdown menu this will start the wizard. Under”2 Data Source” press “Get Data” and the “Open Data Source”.
  9. For Word 2010 select the “Mailings” ribbon and from that select the “Select Recipients”. That will present a dropdown window from which you can select “use existing list”
  10. This will present a file selection pop-up window. Find and select “Receipt Maker - Data.xls”
  11. The next pop-up will be the “Conform Data Source” window that we enabled with the setting of the option above. Along with an OLE and ODBC option there will be a DDE option. Select the DDE option.
  12. Next is a pop-up window to select the cells to be used. Select “entire worksheet”
  13. With the pop-ups finally stopped, you’ll see the receipts…they might look a little messy.
  14. Mail merge has the capability to display dummy placeholder data or preview results of your merging data. The Word document will look better previewing results.
  15. For Office 97, find the mail merge tool bar and press the icon that looks something like «abc»
  16. For Office 2010, from the “Mailings” ribbon select “Preview Results”. You should now be looking at a page of receipts formatted with the first few entries from “Receipt Maker - Data.xls”. From the “preview Results” part of the ribbon you’ll notice that you can move forward and see receipts from further down in “Receipt Maker - Data.xls”.
  17. Select the receipt data and drop the blank extra receipts
  18. For Office 97, from the wizard popup, under “3 Merge the data with the document” press “Query Options”.
  19. For Office 2010, this is done by selecting “Edit Recipient List” from the “Mailings” ribbon. A popup window will appear with the data from some of your rows and in the lower part of the window will be a link to “Filter”; select that link.
  20. A “Query Options” popup will appear with a “Filter Records” tab. From the "Field:" window dropdown select "Payer". From the comparison window dropdown select "is not blank". Press "OK" Press “OK” on the “Query Options” popup. You can also filter by Section so that you can do a mail merge for each of your Sections
  21. Now do the actual merge
  22. For Office 2010 the merge is done by selecting from the “Mailings” ribbon the “Finish & Merge” button which will drop down a list of options. “Edit individual Documents” will produce a new Word document with all the receipts in it.
  23. For Office 97, there is a mail merge wizard

6.  Reset “Confirm file format conversion on open” check box if you think it’s appropriate. Save all, print, close all.