CIS 123 Final Project

This project involves the use and integration of four Microsoft Office applications (Word, Excel, PowerPoint, and Access). The basic concept is that you must prepare the annual report for a small charitable foundation and use it to prepare a presentation explaining what the foundation has done over the past year and what it expects to be able to do for the next year. You must also create a small database which contains donor information including transaction data, and use that data to create some queries and reports. That information will be used to feed into a Word document using mail-merge so individualized thank you letters can be generated.

The order in which you complete the various parts of this project is important. The PowerPoint presentation relies on the Excel spreadsheet. The Word document relies on the Access database. The requirements are presented in an order that works. Please let me know if you find any errors so I can correct them.

The final project rubric is available at the end of this document. Throughout the document are links to samples of what things should look like so you can see if you are on the right track. It is expected that you will ask questions to complete this assignment. The discussion forum would be a great place for such questions. I will also answer questions via email and face-to-face if you prefer to actually meet in person.

• Use Word, Excel, Access, and PowerPoint to create related documents
• Copy charts between applications and keep them modifiable
• Use Word and Access to perform a mail merge (form letters)
• Redirect printed output to a virtual (XPS) printer

1. Create a new blank desktop database and name it: intasst.accdb
2. Make sure you remember where on your computer you have created the database so you can retrieve it easily later.
3. Required tables (detailed later):
   1. Donors: keeps track of donor information
   2. Donations: keeps track of donation transactions
4. Required relationship:
   The two tables must be related by DonorID with ALL three referential integrity options selected.
5. Required queries (detailed later):
   1. DonationsByDonor
   2. YearSummaryByDonor
   3. TotalDonations
6. Required report: DonationsByDonor
7. Examples
   1. Donors table
   2. Donations table
   3. Table relationship
   4. DonationsByDonor query
   5. TotalDonations query
   6. YearSummaryByDonor query
   7. DonationsByDonor report

Create a new table named Donors using table design view. Note: To make a field required, you must set the "required" property for that field to Yes. Most of the fields in this table should be set to "required", but not the Title or AddressLine2 fields. If either of those two fields is set to required, then you will not be able to properly enter the Donor data. This table needs nine fields:

1. DonorID: a long integer field used as the primary key (make this a required field) [Note: A long integer is one type of Number field.]
2. Title: a short text field used to store a donor's title (eg. Mr., Ms., Dr.) (set size to 10 characters)
3. LastName: a short text field used to store a donor's last name (set size to 20 characters, make this a required field)
4. FirstName: a short text field used to store a donor's first name (set size to 20 characters, make this a required field)
5. AddressLine1: a short text field for the donor's street address (set size to 40 characters, make this a required field)
6. AddressLine2: a short text field for additional address information (set size to 40 characters)
7. City: a short text field for the city the donor lives in (set size to 20 characters, make this a required field)
8. State: a short text field for the state the donor lives in (set size to 15 characters, make this a required field)
9. Zip: the donor's zip code; use a long integer data type (make this a required field) [Note: A long integer is one type of Number field.]

Create a new table named Donations using table design view. Note: To make a field required, you must set the "required" property for that field to Yes. All of the fields in this table should be set to "required". This table needs four fields:

1. TransactionID: a long integer field used as the primary key (make this a required field) [Note: A long integer is one type of Number field.]
2. DonorID: a long integer used as a foreign key into the Donors table (make this a required field) [Notes: A long integer is one type of Number field. Nothing special has to be done to create a foreign key - it just tells you that it will later be related to the primary key in another table.]
3. DDate: the date the donation was made; use the date/time data type (make this a required field)
4. Amount: the amount of the donation; use the currency data type (make this a required field)

Append the donor information to the Donors table using a text import (External Data tab) in Access. The data is available in the text file: DonorsSp18.txt. The file is comma-delimited text. In case you are interested, that data looks like something like this:

DonorID	Title	LastName	FirstName	AddressLine1	AddressLine1	City	State	Zip
1	Ms.	Oswald	Clara	115 E. 34th St.		New York	NY	10016
2	Dr.	Williams	Rory	450 Lexington		New York	NY	10017
3	Mrs.	Pond	Amy	365 W. 125th St.		New York	NY	10027
4	Dr.	Jones	Martha	Empire State Building	350 5th Ave.	New York	NY	10118
5		Noble	Donna	Woolworth Building	233 Broadway	New York	NY	10279
6	Ms.	Song	River	United Nations Station	405 E 42nd St.	New York	NY	10017
7	Capt.	Harkness	Jack	Chrysler Building	405 Lexington Ave.	New York	NY	10174
8		Tyler	Rose	Rockefeller Center	610 5th Ave.	New York	NY	10020

Here is an example of the Donors table

Append the donation information to the Donation table using a text import (External data tab) in Access. The data is available in the text file: DonationsSp18.txt. The file is comma-delimited text. In case you are interested, that data looks like something like this:

TransactionID	DonorID	Date	Amount
1	5	8/3/2017	$137.35
2	2	8/7/2017	$250.00
3	4	8/13/2017	$1200.00
4	1	9/1/2017	$190.00
5	6	9/5/2017	$123.45
6	2	9/15/2017	$200.00
7	8	9/15/2017	$400.00
8	3	9/17/2017	$350.00
9	7	9/22/2017	$50.00
10	4	9/28/2017	$25.00
11	4	10/9/2017	$175.00
12	1	10/9/2017	$210.00
13	2	10/15/2017	$800.00
14	7	10/23/2017	$97.82

Here is an example of the Donations table

Create a query named DonationsByDonor using design view which meets these requirements:

1. Include the fields FirstName, LastName, DDate, and Amount in that order.
2. Sort the records in ascending order by LastName and then DDate. The sort must be specified in design view.
3. DonationsByDonor query example

Create a query named TotalDonations using design view which meets these requirements:

1. Include the Amount field from the Donations table.
2. Only show the sum of the Amount field (this uses an aggregate function).
3. TotalDonations query example

Create a query named YearSummaryByDonor using design view which meets these requirements:

1. Include the DonorID, Amount, Amount, Title, LastName, FirstName, AddressLine1, AddressLine2, City, State, and Zip fields in that order. [Note: The Amount field is included twice because you are going to get both a count and a sum for that field.]
2. Use the Totals row and aggregate functions to show the Count of the Amount, and the Sum of the Amount fields in the query. All the other fields should remain "Group By" in the Totals row.
3. YearSummaryByDonor query example

A starting Excel workbook is available to help get you started. It originally contains one worksheet which is partially completed. You will have to complete that worksheet with a formatting change, entering a note, and adding formulas. You will also be adding a couple of charts to the workbook. When you finish, there should be three worksheets - the completed original worksheet and two worksheets with charts on them.

The comma format is used for the currency numbers. In the comma format, a 0 shows up as a dash. When you see dashes where numbers should be on the sample output, those are actually 0s that just look like dashes because of the comma format.

1. Samples
   1. 2017 Annual Report
   2. Revenue chart
   3. Giving chart
2. Download the starting Excel workbook and save it with the name: intasst.xlsx
3. Worksheet 1: 2017 Annual Report
   1. Rename the tab to: 2017AnnualReport
   2. You should only be using three columns on this worksheet. The first column has line item descriptions. The second column has amounts and formulas. The third column is for miscellaneous notes.
   3. Section 1: Revenues
      1. Trust fund income: 4,400.00 (already supplied)
      2. Donations: 4,208.62 (already supplied)
      3. Total: The sum of the two revenue sources above (this cell must be a formula).
   4. Section 2: Overhead expenditures
      1. Office staff: 0 (already supplied)
      2. Include a note in third column for Office staff noting that the staff is all volunteer. See the example output for wording.
      3. Widen the third column just enough to make sure the office staff note fits within that column.
      4. Office rent: 2,600.00 (already supplied)
      5. Copying: 175.65 (already supplied)
      6. Mailing: 122.15 (already supplied)
      7. Supplies: 197.50 (already supplied)
      8. Total: The sum of the overhead expenditure items (this cell must be a formula).
   5. Section 3: Charitable expenditures
      1. Anderson Hospice: 2,750.00 (already supplied)
      2. St. Mary's Soup Kitchen: 1,500.00 (already supplied)
      3. AARF Animal Shelter: 400.00 (already supplied)
      4. ABE Methodist Food Pantry: 350.00 (already supplied)
      5. Meals on Wheels: 550.00 (already supplied)
      6. First United Special Olympics: 2,600.00 (already supplied)
      7. St. Mary's Special Ed. Class: 450.00 (already supplied)
      8. Good Sam Hospital Radiology: 1,500.00 (already supplied)
      9. Total: The sum of the charitable expenditures (this cell must be a formula).
   6. Section 4: Summary section
      1. Total revenue: Obtain this from the total cell from the Revenues section (this cell must be a formula)
      2. Total overhead: Obtain this from the total cell from the Overhead Expenditures section (this cell must be a formula)
      3. Total charitable expenses: Obtain from from the total cell in the Charitable Expenditures section (this cell must be a formula)
      4. Amount returned to trust fund: Obtain by subtracting the overhead and expenditures from total revenue (this cell must be a formula).
      5. Balance: Obtain this by subtracting total overhead, total charitable expenditures, and the amount returned to the trust fund from the total revenue (this cell must be a formula, and it should come out to be 0, which will display as a dash when using the comma style format)
   7. Section 5: Short note explaining trust fund finances
      1. Format this note in a single cell that has word wrap on and have the three cells from the three columns the note spans merged together. The text should be left aligned (not centered). The row should be made taller so that the entire note displays properly.
   8. Section 6: Assumptions for 2018
      1. Increase in trust fund revenue: 2,000.00 (already supplied)
      2. Increase in donations: 12% (already supplied)
      3. Increase in overhead: 4.5% (already supplied)
   9. Section 7: 2018 projected budget
      1. Revenue from trust fund: Calculate this from the trust fund income in the Revenues section and the expected increase in the 2018 assumptions section (this cell must be a formula).
      2. Revenue from donations: Calculate this from the donation revenue figure in the Revenues section and the expected increase in the 2018 assumptions section (this cell must be a formula).
      3. Overhead expenditures: Calculate this from the overhead total in the Overhead expenditures section and the expected increase in the 2018 assumptions section (this cell must be a formula).
      4. Total available for charity: Calculate this by adding the projected revenues and subtracting the projected overhead (this cell must be a formula)
   10. Example of finished 2017 Annual Report
4. Chart 1: 2017 Revenue Chart
   1. Create a new 2-D pie chart based on the names and amounts of the two items in the Revenues section.
   2. Cut the chart from the current worksheet and paste the chart on a new worksheet.
   3. Rename the tab on the new worksheet to: 2017RevenueChart
   4. This chart must be based on cells on the 2017 annual report worksheet and not a copy of those cells. You should include the cells for both the amounts and descriptions of the two revenue sources. Be careful when you cut and paste the chart to make sure the chart you end up with is a live chart that changes as the data on the 2017 Annual Report worksheet changes and that it is not a picture of that chart (which quite a few people incorrectly do when copying/cutting and pasting in Excel).
   5. The chart legend should be deleted.
   6. The chart title should be bold and state: 2017 Revenue
   7. The data labels on the chart should include the category names, values, and percentages.
   8. Example of finished Revenue chart
5. Chart 2: 2017 Charitable Expenditures
   1. Create a new 2-D bar chart based on the names and amounts of the eight items in the Charitable Expenditures section.
   2. Cut the chart from the current worksheet and paste the chart on a new worksheet.
   3. Rename the tab on the new worksheet to: 2017ExpendituresChart
   4. This chart must be based on cells on the 2017 annual report worksheet and not a copy of those cells. You should include the cells for both the amounts and descriptions of the two revenue sources. Be careful when you cut and paste the chart to make sure the chart you end up with is a live chart that changes as the data on the 2017 Annual Report worksheet changes and that it is not a picture of that chart (which quite a few people incorrectly do when copying/cutting and pasting in Excel).
   5. The chart title should be bold and state: 2017 Charitable Expenditures
   6. Example of finished Giving chart

1. The Word document will function as a mail merge template based on data from the Access database. Samples:
   1. Letter before merge (PDF)   Letter before merge (XPS)
   2. Letters after merge (PDF)   Letters after merge (XPS)
2. Create a new Word document named: intasst.docx
3. The document should be two pages long. The first page is a thank you letter. The second page is a chart. Sample text for the letter is available at DonationLetterText.txt. The letter thanks the donor for his/her support and assures them that their money has been used wisely. You will have to format the text in the Word document to make it look better. Note that the sample text has many words speed incorrectly. Those must be fixed.
4. The main paragraphs in the body of the letter (this does not include the address block, greeting line, signature block, or chart) should be full justified, double-spaced, and have a first-line indent of .5 inches. The text should not have any left indent other than the first line of a paragraph, and the indent must not be done using space characters or tabs.
5. The parts of the letter that are bracketed by ** are fields that will have to be filled in using mail merge fields, so you will need to replace those from the sample letter text. Typing in the ** will NOT create the fields you want. It must be done using mail merge and inserting fields. Once the fields are inserted into the letter, but before the merge is done, the ** will look like « and ».
6. The address block should be single spaced.
7. The address block and greeting should have no indentation.
8. The part that has the instructor name in the signature block should be replaced with your name. The signature block should be shifted to the right as seen on the sample document. The signature block should not be shifted using tabs or spaces, but by changing the left indentation for that section to 3.5 inches. After your name, insert a page break.
9. On the new page, change the paragraph formatting back to no indentation.
10. On the new page, the letter should contain the 2017 Charitable Expenditures chart from the Excel spreadsheet. You can copy the chart in the Excel worksheet and then paste it on the Word document, but make sure you do NOT paste it as an image. [Choose the paste option that says: Use destination theme and embed workbook] You must still be able to select the chart and modify it. Make sure the chart makes sense and has all the needed labeling. If it needs to be corrected, go back to the original spreadsheet and fix it.
11. Change the bar chart inside the Word document into a column chart and resize it to make it wider (and make it look similar to the example).
12. Use mail merge to create the form letters:
    1. Base your mail merge on the YearSummaryByDonor query from your database intasst.accdb.
    2. The donor's name and address should appear as an address block at the beginning of the letter.
    3. A personalized greeting should start the letter.
    4. The first paragraph should include the total number of donations and total amount the donor gave last year.
    5. If the count of the donations is greater than one, then the letter should say "donations", otherwise it should say "donation". This can be done while you are in mailmerge by selecting the word "donation" in the letter. Then choose "Rules" from the "Mailings" tab. Choose an "If/then" rule. Have it insert "donation" if CountOfAmount is 1, otherwise have it insert "donations".
    6. You want the amount field to display two decimal places, but this is not Word's default behavior. To get the display correct, right click on the SumOfAmount field before the letters are merged. Select "Toggle Field Codes" from that context menu. The field should change from "«SumOfAmount»" to "{ MERGEFIELD "SumOfAmount" }". Modify that to include formatting information by changing it to "{ MERGEFIELD "SumOfAmount" \# ,#.00 }". Then right click on the field again and choose "Toggle Field Codes" to return it to normal. Now the fields should be ready for the mail merge.
13. You are going to have to print out the merged letters as an XPS file. Printing to an XPS file is included in recent versions of Word. Please note that you just specify the XPS Printer when you go to print and it will automatically create an XPS file for you. If you find yourself checking a box that says "Print to File", then you are doing it incorrectly. This is done just like printing except for the printer you are printing to. A dialog box will open up and ask for the name of the file to save it to. When you print the merged letters, name the file intasst.xps. Include this file as part of your assignment submission in Desire2Learn. Check the XPS document to make sure all the merged letters are included.
14. You must also include the original Word document with the mailmerge fields in your dropbox submission. Once you are finished with the mailmerge and printing to the XPS file, then you should be able to toggle between the original document and the mailmerge preview by clicking on the Preview icon on the Mailings tab of the ribbon in Word.

A sample of the PowerPoint presentation (PDF)   sample of the PowerPoint presentation (XPS).

The PowerPoint part of the project is meant to be used as graphic support for an imaginary presentation of the GC Foundation's 2017 Annual Report. The information comes from the Excel spreadsheet of the annual report, but you can type it in manually since there isn't that much data to present. The charts for the presentation must be at least copied and pasted from the spreadsheet, however. The presentation requires seven slides:

1. Introduction stating what the presentation is (name of organization, subtitle, due date)
2. 2017 revenues presented in a table with three rows and three columns. The font in the total row should be made bold and italic.
3. 2017 overhead expenditures presented in a table with six rows and three columns. The font in the total row should be made bold and italic.
4. 2017 charitable expenditures presented in a table with nine rows and three columns. The font in the total row should be made bold and italic.
5. Chart of 2017 revenues by category copied from the spreadsheet. Modify the chart formatting if needed to display information well.
6. Chart of 2017 charitable expenditures from the spreadsheet.
7. Change the charitable expenditures chart into a column chart within the presentation. It should remain a bar chart in the spreadsheet. Modify the chart formatting if needed to display the information well.
8. A final credits slide should include your name, the course number, and the date.

Use a consistent MS Office theme throughout the presentation. Have slide numbers and the organization name automatically display as a header/footer on each slide except for the first slide. Make sure the presentation has a professional look throughout. Spelling, grammar, and accuracy will be checked.

The three slides that display data in a tabular form MUST use tables and not lists to display the data. The numbers must be right aligned using an alignment setting for that column of cells and not using tabs or spaces. Make sure the font used in the tables is large enough to read easily and make the totals bold and italic. The numbers in the tables are expected to be typed in, not linked in any way from the Excel workbook. The samples use a third (middle) column to hold dollar signs in a neat column where desired. The samples had the numbers typed in as ordinary text, including the commas - so they aren't actually numbers any more. The tables should not have any visible formatting for rows, columns, or borders. See the examples to see how the tables should look.

• Example of finished presentation (PDF)   Example of finished presentation (XPS)
• Example of tables with the borders turned on to demonstrate the table layout: PDF version   XPS version

You have a couple of choices when submitting your assignment to Desire2Learn. You may submit many different files or you may submit one joined and compressed file. Submitting several files just requires you to upload all of your files before you submit your assignment. If you wish to submit everything as one file, then use Windows Explorer or My Computer to view the directory where your files are located, highlight all of the files you wish to include (Ctrl-clicking is useful for selected non-contiguous files), and then right click to get a context menu. On the context menu, select Send To..., and then Compressed (zipped) folder. That should create a file with a .zip extension. You may rename it if you wish. You can also double-click on it to view the contents. If you do this, then you should only have to submit this one file.

The following is a list of the files that must be included in your submission:

1. intasst.accdb
2. intasst.xlsx
3. intasst.docx
4. intasst.xps
5. intasst.pptx

Points	Based on
Access (29 points)
4	Donors table creation (fields, properties)
2	Donors table import of data
3	Donations table creation (fields, properties)
1	Donations table import of data
2	Table relationship
4	DonationsByDonor query
4	YearSummaryByDonor query
4	TotalDonations query
5	DonationsByDonor report
Excel (34 points)
1	Worksheet 1: Worksheet tab renamed to 2017AnnualReport
2	Worksheet 1: Revenues total formula
1	Worksheet 1: Office staff note done and matches example
1	Worksheet 1: Third column widened just enough to contain office staff note.
2	Worksheet 1: Overhead expenditures formula
2	Worksheet 1: Charitable expenditures formula
1	Worksheet 1: Summary section total revenue formula
1	Worksheet 1: Summary section total overhead formula
1	Worksheet 1: Summary section total charitable expenditures formula
1	Worksheet 1: Summary section amount returned to trust fund formula
1	Worksheet 1: Summary section balance formula
1	Worksheet 1: Trust fund note cells merged across three columns, left aligned.
1	Worksheet 1: Trust fund note formatting has word wrap turned on.
1	Worksheet 1: Trust fund note row made taller to fit entire note.
1	Worksheet 1: 2018 projected budget section formula for revenue from trust fund (this cell must be a formula)
2	Worksheet 1: 2018 projected budget section formula for revenue from donations (this cell must be a formula)
2	Worksheet 1: 2018 projected budget section formula for overhead (this cell must be a formula)
2	Worksheet 1: 2018 projected budget section formula for total available for charity (this cell must be a formula)
1	Chart 1: 2017 revenue chart created properly
1	Chart 1: 2017 revenue chart on new worksheet named 2017RevenueChart
1	Chart 1: 2017 revenue chart is live chart and not picture
1	Chart 1: 2017 revenue chart has bold title stating: 2017 Revenue
1	Chart 1: 2017 revenue chart has data labels formatted correctly
1	Chart 1: 2017 revenue chart has legend deleted
1	Chart 2: 2017 charitable expenditures chart created properly
1	Chart 2: 2017 charitable expenditures chart on new worksheet named 2017ExpendituresChart
1	Chart 2: 2017 charitable expenditures chart is live chart and not picture
1	Chart 2: 2017 charitable expenditures chart has bold title stating: 2017 Charitable Expenditures
Word (24 points)
3	Document has no spelling or grammar errors.
1	Document body paragraphs are double spaced.
1	Document body paragraphs are full justified.
1	Document body paragraphs have .5 inch first line indent.
1	Address block and greeting line have no indentation.
1	Signature block has 3.5 inch left indentation.
1	Hard page break is inserted at end of signature block.
1	Document includes functioning chart from Excel.
2	Chart is changed to column chart.
1	Chart is resized to better fit the page width.
4	All four fields are correctly included from database for the mail merge.
2	Two decimal positions display in the amount field when the letters are merged.
2	The word donation is properly made plural (donations) when the number of donations is not equal to 1.
3	a single XPS including ALL of the mail-merged letters has been submitted
PowerPoint (13 points)
1	Presentation uses a consistent theme.
1	Title slide fits requirements.
1	Footers with the name of the charity and slide number are automatically included on all slides except the first.
3	Tables are used to present data on the three slides that contain columnar data.
3	The data in tables is presented neatly in table columns, aligned properly with names left-aligned and numbers right-aligned without using spaces or tabs for alignment.
2	The two charts are included properly in the presentation (and not just as pictures).
1	The charitable expenditures chart has been changed to be a column chart.
1	The last slide (credits) includes name, date, and course number.
Overall
-?	Spelling and grammar errors will be deducted from total project points.