Reference no: EM132222496

Capstone Exercise 1 -

As a travel editor for a regional magazine, you are responsible for the preparation of material related to destinations and special events. Your staff often prepares material on specific areas, and you edit and combine the submissions into attractive documents for publication. In this case, you collect information to spotlight a couple of destinations in the Southeast. A deadline is looming so you use Word to produce an attractive article.

Spelling, Margins, Watermarks, and Editing

Nothing detracts from content more than spelling and grammatical errors. Since this article is destined for print, it must be error free. You check it for errors and manually proofread it. Additionally, you find it necessary to adjust margins to improve readability and you edit the content. Finally, a watermark indicates that this is not the final version.

a. Open w01c1Travel and save it as w01c1Travel_LastFirst.

b. Position the insertion point at the end of the document. On a new line, insert text from w01c1Texas.

c. Use Word's Spelling & Grammar checker to identify any spelling or word usage errors. All names are spelled correctly, so ignore any errors related to a name. Select the word quaint in the third paragraph on the first page. Use the thesaurus to identify synonyms. Insert the word charminginstead. Close the Thesaurus pane. Proofread the document to ensure that there are no other errors.

d. Change the left and right margins to 1".

e. Insert a diagonal watermark that displays Draft. Color the watermark blue and leave semitransparency on.

f. Display nonprinting characters. Remove the hard return at the end of the third paragraph on the first page (ending in guest rooms). Insert a space. Change the number 200 in the sixth paragraph on the first page to 300. Change the word offer in the last paragraph on the first page to feature. Change the word it's in the last paragraph on the second page to it is. The fourth paragraph on the first page incorrectly describes The Cloister as The Cloisters. Edit the paragraph to remove the s from the end of the resort name each place it occurs in the paragraph.

Headers, Footers, Symbols, and Features That Improve Readability

You number the pages and include a descriptive header. You also ensure that each destination appears on its own page (or set of pages). Finally, you use several symbols to improve readability and to ensure proper credit.

a. Create a page number footer, centered at the bottom of each page. Create a header with Southeastern Living on the left. On a second line in the header, insert the document file name.

b. Edit the header to include a copyright symbol before the words Southeastern Living.

c. At a couple of locations throughout the document, a hyphenated phrase is incorrectly separated at the end of a line. Replace the dash separating those divided words with a nonbreaking hyphen. Similarly, replace the space after the first name Addison with a nonbreaking space so that the full name is not divided.

d. Insert a page break before the fifth paragraph on the first page.

e. View the document as Multiple Pages to get a feel for the flow of text. Because the text is a bit too high on the page, change the top margin to 2". Return the view to 100%. Save the document.

Set Properties and Finalize Document

As the document nears completion, you adjust document properties to include yourself as the author and to apply descriptive keywords. You also save the document in a format that ensures others will be able to read it. Finally you ensure compatibility with earlier Word versions.

a. Save the document in Rich Text Format as w01c1Travel_LastFirst. Save the document again as a Word Document with the same file name. Replace the existing file and agree to the upgrade.

b. Save the document. Run the Compatibility Checker for all previous Word versions. Inspect the document and remove all document properties and personal information.

c. Add Travel, Texas, and Georgia to the Keywords field in the document properties. List your first name and last name as the Author.

d. Preview the document.

e. Save and close the file. Based on your instructor's directions, submit w01c1Travel_LastFirst (the RTF document) and w01c1Travel_LastFirst (the Word document).

Capstone Exercise 2 -

This semester you are enrolled in a personal finance course at your local university. One of the assignments is to write a research paper about investing. You conducted research on the various types of investing instruments, and wrote a final draft of the report. Now your research paper requires formatting to enhance readability and important information; and you will use skills from this chapter to format multiple levels of headings, arrange and space text, and insert graphics.

Applying Styles

This document is ready for enhancements, and the Styles feature is a good tool that enables you to add them quickly and easily.

a. Open w02c1Finance and save it as w02c1Finance_LastFirst.

b. Press Ctrl+Home. Create a paragraph style named Title_Page_1 with these formats: 22-pt font size and Dark Blue, Text 2, Darker 50% font color. Ensure that this style is applied to the first line of the document, Personal Finance:.

c. Select the second line, Understanding the Investment Instruments. Change the font size to 16 and apply a font color of Dark Blue, Text 2, Darker 50%.

d. Click the line following Updated by: and type your first and last names. Change the capitalization for your name to uppercase.

e. Select the remainder of the text in the document that follows your name, starting with Personal Finance. Justify the alignment of all paragraphs and change line spacing to 1.15. Place the insertion point on the left side of the title Personal Finance (below your name) and insert a page break (not a section break).

f. Apply Heading 1 style to Personal Finance at the top of page 2. Apply Heading 2 style to paragraph headings, including Introduction, Equity Stocks, Bonds, Mutual Funds, U.S. Treasury Bills, Fixed Deposits, Sources, and Conclusion.

g. Modify the Heading 2 style to use Dark Red font color.

Formatting the Paragraphs

Next, you will apply paragraph formatting to the document. These format options will further increase the readability and attractiveness of your document.

a. Apply a bulleted list format for the six-item list in the third paragraph of the Introduction section. Use the symbol of a diamond.

b. Select the second body paragraph in the Introduction section, which begins with The best time to prepare for your retirement, and apply these formats: 0.6" left and right indents, 6 pt spacing after the paragraph, boxed 1 1/2 pt border using the color Dark Blue, Text 2, Darker 25%, and the shading color Dark Blue, Text 2, Lighter 80%.

c. Apply the numbered list format (1., 2., 3.) to the three types of bonds in the Bonds section.

d. Select the three quotes by Warren Buffet and the Source: paragraph in the Equity Stocks section and display them in two columns with a line between the columns.

e. Insert the hyperlinks for all the five sources listed in the Sources section.

Inserting Graphics

To put the finishing touches on your document, you will add graphics that enhance the explanations given in some paragraphs.

a. Insert the picture file w02c1Bull at the beginning of the line that contains The major stock market in the Equity Stocks section. Change the height of the picture to 3". Change text wrapping to Top and Bottom. Center the graphic horizontally. Apply the Rounded Diagonal Corner, White picture style. Position the picture so that it appears below the Equity Stocks heading.

b. Insert the picture file w02c1Bear at the beginning of the line that begins with If you want something a little less risky than stocks in the Bonds section. Change the height of the picture to 3". Ensure that text wrapping is Top and Bottom, position the picture so it appears immediately above the line beginning U.S. Treasury bills and government. Apply Offset Center Shadow Picture Effect (second row, second column under Outer) to the graphic. Position the picture so that it appears below the Bonds heading.

c. Spell check and review the entire document-no author names are misspelled.

d. Display the document in Outline view. Collapse all paragraphs so only lines formatted as Heading 1 or Heading 2 display. Move the Sources section to below the Conclusion section. Close Outline view.

e. Save and close the file. Based on your instructor's directions, submit w02c1Finance_LastFirst.

Capstone Exercise 3 -

Your family owns Baker Home Store, a local home improvement business. As the general manager, you are involved in all facets of the business, from working with suppliers to ensuring customer satisfaction. Often, customers are interested in making improvements to homes that are being sold. You are currently preparing a brief guide providing tips on improving a house or getting it ready to sell. The basic outline of the document is ready. You review the document and add a couple of informative tables before the item is ready to send to your valued customers. Upon completing your edits, you use mail merge to distribute the document.

Create and Enhance Tables

You know that information presented in a tabular format is often more easily understood, especially where numeric data is involved. As you review the document draft, you see a couple of places where a table might be useful. You create and format two tables related to home improvement.

a. Open w03c1Home and save it as w03c1Home_LastFirst.

b. Move to the end of the document and convert the text beginning with Under $100 and ending with Update maintenance to a table.

c. Insert a row above row 1. Type Home Improvement Tips in the first cell of the new row. Merge all cells in the row. Bold and center the text in row 1. Change the font size of the text in row 1 to 18 pt. Bold and center the text in row 2.

d. AutoFit the table contents. Center the table horizontally on the page.

e. Shade row 1 with Blue, Accent 1, Lighter 40%. Shade row 2 with Blue, Accent 1, Lighter 80%. Apply a Blue, Accent 1, Darker 25%, ½ pt size, double line border around the outside of the table.

f. Add a hard return after the first complete sentence at the top of page 2 (An energy efficient home is more valuable and is much more marketable in the long run.). Insert a 7 × 6 table. Complete the table as follows.

g. Delete the first column (Months). Insert a row above row 1, merge cells in the row, and type Return on investment estimates for household energy efficiency improvements. (Do not type the period.) Change the table style to Grid Table 2 - Accent 3 (row 2, column 4 under Grid Tables).

h. Insert a row between rows 5 and 6 (Attic insulation and Duct sealing). Type the following data in the new row.

Wrap water pipes 48% 60 $0.15 $4.80 $10

i. Remove bold formatting from rows 3 through 8 in column 1. Ensure that text in rows 1 and 2 is bold. Center the table horizontally on the page. Insert a row after the last row and type Total in the first cell of the new row. In the fifth column on the last row, enter a formula to sum the annual savings. The total should be formatted as currency. Change the annual savings for the high efficiency showerhead to $35. Update the total to reflect the new value.

j. Insert a column at the right of the Cost per unit column. Type Estimated cost increase as the new column heading. In rows 3-8 of the new column, include a formula that increases the cost per unit by 3%. The formula should multiply the cost per unit by .03 and then add the result back to cost per unit.

For example, the Estimated cost increase formula for the High efficiency showerhead is =F₃*.03+F₃. All results should be formatted at currency.

k. Check spelling and correct any mistakes. Create a caption to be shown below the table. The caption should be Table 1: Return on investment for energy improvements.

l. Modify the Caption style to include centered, bold and italicized font with a font color of Blue, Accent 1, Darker 50%. Save the document.

Use Mail Merge to Create Personalized Letters

You prepare a letter to current customers, thanking them for their business and including the collection of home improvement tips. You use Mail Merge to create personalized letters.

m. Move to the top of the document and insert a page break. Place the insertion point at the top of the new page. Insert text from w03c1Thanks. Show nonprinting characters if they are not already displayed.

n. Conduct a mail merge, using Sheet1 of w03c1Customers as the data source for addresses. Filter the data source so that only records with a zip of 65757 are included.

o. Insert an address block below the date, using default settings. Following the space after the word Dear (immediately before the colon), insert the title, followed by a space and the last name. Include your name in the closing instead of Student Name. Preview the letters and adjust the spacing of the address so that it displays as a single-spaced unit with no paragraph spacing.

p. Complete the merge of all records, producing a document containing two letters (four pages each).

q. Save the merged document as w03c1HomeMerge_LastFirst and close the file. Save and close w03c1Home_LastFirst. Based on your instructor's directions, submit w03c1Home_Merge_LastFirst.

Capstone Exercise 4 -

You are a division manager for a regional hearing-aid company in Cheyenne, Wyoming. Your sales managers travel frequently to some of the offices in the western region. You need to create a travel expense report for your managers to use to record their budgeted and actual expenses for their travel reports. The draft report contains a title, input areas, and a detailed expense area.

Format the Title and Complete the Input Areas

Your first tasks are to format the title and complete the input area. The input area contains two sections: Standard Inputs that are identical for all travelers and Traveler Inputs that the traveler enters based on his or her trip.

a. Open e01c1Travel and save it as e01c1Travel_LastFirst.

b. Merge and center the title over the range A1:E1 and set the row height for the first row to 40.

c. Apply the Input cell style to the ranges B3:B6, E3:E4, and E6:E7, and then apply the Calculation cell style to cell E5. Part of the borders are removed when you apply these styles.

d. Select the ranges A3:B6 and D3:E7. Apply Thick Outside Borders.

e. Enter 6/1/2018 in cell E3 for the departure date, 6/5/2018 in cell E4 for the return date, 149 in cell E6for the hotel rate per night, and 18% in cell E7 for the hotel tax rate.

f. Enter a formula in cell E5 to calculate the number of days between the return date and the departure date.

Insert Formulas

The Detailed Expenses section contains the amount budgeted for the trip, the actual expenses reported by the traveler, percentage of the budget spent on each item, and the amount the actual expense went over or under budget. You will insert formulas for this section. Some budgeted amounts are calculated based on the inputs. Other budgeted amounts, such as airfare, are estimates.

a. Enter the amount budgeted for Mileage to/from Airport in cell B12. The amount is based on the mileage rate and roundtrip to the airport from the Standard Inputs section.

b. Enter the amount budgeted for Airport Parking in cell B13. This amount is based on the airport parking daily rate and the number of total days traveling (the number of nights + 1) to include both the departure and return dates. For example, if a person departs on June 1 and returns on June 5, the total number of nights at a hotel is 4, but the total number of days the vehicle is parked at the airport is 5.

c. Enter the amount budgeted for Hotel Accommodations in cell B16. This amount is based on the number of nights, the hotel rate, and the hotel tax rate.

d. Enter the amount budgeted for Meals in cell B17. This amount is based on the daily meal allowance and the total travel days (# of hotel nights + 1).

e. Enter the % of Budget in cell D12. This percentage indicates the percentage of actual expenses to budgeted expenses. Copy the formula to the range D13:D18.

f. Enter the difference between the actual and budgeted expenses in cell E12. Copy the formula to the range E13:E18. If the actual expenses exceeded the budgeted expenses, the result should be positive. If the actual expenses were less than the budgeted expense, the result should be negative, indicating under budget.

Add Rows, Indent Labels, and Move Data

The Detailed Expenses section includes a heading Travel to/from Destination. You want to include two more headings to organize the expenses. Then you will indent the items within each category. Furthermore, you want the monetary columns together, so you will insert cells and move the Over or Under column to the right of the Actual column.

a. Insert a new row 15. Type Destination Expenses in cell A15. Bold the label.

b. Insert a new row 19. Type Other in cell A19. Bold the label.

c. Indent twice the labels in the ranges A12:A14, A16:A18, and A20.

d. Select the range D10:D21 and insert cells to shift the selected cells to the right.

e. Cut the range F10:F21 and paste it in the range D10:D21 to move the Over or Under data in the new cells you inserted.

Format the Detailed Expenses Section

You are ready to format the values to improve readability. You will apply Accounting Number Format to the monetary values on the first and total rows, Comma Style to the monetary values in the middle rows, and Percent Style for the percentages.

a. Apply Accounting Number Format to the ranges B12:D12 and B21:D21.

b. Apply Comma Style to the range B13:D20.

c. Apply Percent Style with one decimal place to the range E12:E20.

d. Underline the range: B20:D20. Do not use the border feature.

e. Apply the cell style Bad to cell D21 because the traveler went over budget.

f. Select the range A10:E21 and apply Thick Outside Borders.

g. Select the range A10:E10, apply Blue-Gray, Text 2, Lighter 80% fill color, apply Center alignment, and apply Wrap Text.

Manage the Workbook

You will apply page setup options, insert a footer, and, then duplicate the Expenses statement worksheet.

a. Spell-check the workbook and make appropriate corrections.

b. Set a 1.5″ top margin and select the margin setting to center the data horizontally on the page.

c. Insert a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side.

d. Copy the Expenses worksheet, move the new worksheet to the end, and rename it Formulas.

e. Display the cell formulas on the Formulas worksheet, change to landscape orientation, and adjust column widths. Use the Page Setup dialog box or the Page Layout tab to print gridlines and row and column headings.

f. Save and close the file. Based on your instructor's directions, submit e01c1Travel_LastFirst.

Capstone Exercise 5 -

You are an account manager for Inland Jewelers, a regional company that makes custom class rings for graduating seniors. Your supervisor requested a workbook to report on new accounts created on payment plans. The report should provide details on total costs to the student as well as payment information. Each ring financed has a base price that can fluctuate based on ring personalization.

Insert Current Date

You open the starting workbook you previously created, and insert the current date and time.

a. Open the e02c1ClassRing workbook, and then save it as e02c1ClassRing_LastFirst.

b. Insert a function in cell B2 to display the current date and format as a Long Date.

c. Set column B's width to Autofit.

Calculate Cost

You are ready to calculate the cost of each class ring ordered. The rings are priced based on their base metal as displayed in the range A15:B19.

a. Insert a lookup function in cell C5 to display the ring cost for the first student.

b. Copy the function from cell C5 down through C11 to complete column C.

c. Apply Accounting Number Format to column C.

Determine the Total Due

You will calculate the total due for each student's order. The total is the base price of the ring plus an additional charge for personalization if applicable.

a. Insert an IF function in cell E5 to calculate the total due. If the student has chosen to personalize the ring, there is an additional charge of 5% located in cell B21 that must be applied; if not, the student pays only the base price. Use appropriate relative and absolute cell references.

b. Copy the function from cell E5 down through E11 to complete column E.

c. Apply Accounting Number Format to column E.

Calculate the Monthly Payment

Your next step is to calculate the periodic payment for each student's account. The payments are based on the years financed in column F and the annual interest rate in cell B22. All accounts are paid on a monthly basis.

a. Insert the function in cell G5 to calculate the first student's monthly payment, using appropriate relative and absolute cell references.

b. Copy the formula down the column.

c. Apply Accounting Number Format to column G.

Finalize the Workbook

You perform some basic statistical calculations and finalize the workbook with formatting and page setup options.

a. Calculate totals in cells C12, E12, and G12.

b. Apply Accounting Number Format to the cells C12, E12, and G12.

c. Set 0.3″ left and right margins and ensure that the page prints on only one page.

d. Insert a footer with your name on the left side, the sheet name in the center, and the file name on the right side.

e. Save and close the workbook. Based on your instructor's directions, submit e02c1ClassRing_LastFirst.

Capstone Exercise 6 -

You are an analyst for the airline industry. You created a workbook that lists overall airline arrival statistics for several years. In particular, you listed the percentage and number of on-time arrivals, late arrivals, canceled flights, and diverted flights based on information provided by the Bureau of Transportation Statistics. You want to create charts and insert sparklines that show the trends to discuss with airline and airport managers.

Insert and Format Sparklines

The first dataset shows the percentages. You want to insert sparklines that show the trends in the five-year data. The sparklines will help show any trends in on-time arrivals compared to late arrivals, canceled flights, and diverted flights.

a. Open the e03c1Arrivals workbook and save it as e03c1Arrivals_LastFirst.

b. Insert Line sparklines in the range G4:G7, using the data for the five years.

c. Display the high and low points for the sparklines.

d. Change the high point marker color to Green.

Create a Pie Chart

You want to focus on the arrival percentages for 2014. Creating a pie chart will help people visualize the breakdown of all operations for that year. After you create the chart, you will move it to its own chart sheet and edit the chart title to reflect 2014 flight arrivals.

a. Select the range A4:A7 and the range F4:F7.

b. Create a pie chart and move it to a chart sheet named Pie Chart.

c. Change the chart title to 2014 Flight Arrivals.

Add and Format Chart Elements

You want to format the chart by applying a different chart style and positioning the legend above the plot area. Furthermore, you need to add data labels so that you will know the percentages for the arrival categories. Finally, you want to emphasize the canceled flights in Dark Red and explode the late arrival pie slice.

a. Apply the Style 12 chart style to the pie chart.

b. Format the chart title with Blue font color.

c. Position the legend between the chart title and the plot area.

d. Add data labels to the Best Fit position and display.

e. Apply bold to the data labels and change the font size to 12.

f. Format the Canceled data point with Dark Red fill color and format the Late Arrival data point in Green.

g. Explode the Late Arrival data point by 5%.

Create and Size a Column Chart

To provide a different perspective, you will create a clustered column chart using the actual number of flights. The Total Operations row indicates the total number of reported (scheduled) flights. After creating the chart, you will position and size the chart below the source rows.

a. Create a clustered column chart using the range A10:F15 in the Arrivals sheet.

b. Edit the chart title: On-Time and Late Flight Arrivals.

c. Position the clustered column chart so that the top-left corner is in cell A20.

d. Change the width to 5.75″ and the height to 3.5″.

Format the Column Chart

Now that you have created the column chart, you realize that some data seems irrelevant. You will filter out the unneeded data, format the value axis to remove digits, insert a vertical axis title, apply a color change, and format the chart area.

a. Apply chart filters to remove the canceled, diverted, and total operations data.

b. Select the value axis, set 500000 for the Major unit, display the axis units in Millions, select category Number format with 1 decimal place.

c. Add a primary vertical axis title Number of Flights.

d. Apply the Color 2 chart color to the chart.

e. Apply the Light Gradient - Accent 3 fill to the chart area.

Finalizing the Workbook

You want to prepare the workbook in case someone wants to print the data and charts. The margins and scaling have already been set. You just need to insert a footer.

a. Create a footer on each worksheet with your name, the sheet name code, and the file name code.

b. Save and close the file. Based on your instructor's direction, submit e03c1Arrivals_LastFirst.

Capstone Exercise 7 -

You are employed as a technical supervisor at a chain of book-stores. One of the store managers has expressed confusion about Access. You have offered to train her on the basics of Access. To avoid mistakes in the main database, you will save the file with a new name. You will then train her on the basics of the database system, including making data modifications, sorting and filtering, adding a table using an application part, and creating a backup.

Modify Data in a Table

You will open an original database file and save the database with a new name. You will then demonstrate adding, updating, and deleting information.

a. Open the a01c1Books file and save the database as a01c1Books_LastFirst.

b. Open the Publishers table in Datasheet view. Notice that some of the publisher city and state information is missing. Update the database with the information below and close the table.

c. Change the PubCity for Pearson to Hoboken.

d. Close the Publishers table.

e. Open the Author table in Datasheet view.

f. Navigate to the last record (Author ID of XXXX01) and replace YourFirstName with your first name and YourLastName with your last name. Close the table.

g. Open the Author table again and notice the changes you made have been stored.

h. Click the plus sign next to your name. Notice the book Social Media: A Student's View is listed. Close the table again.

i. Open the Books table in Datasheet view. Notice the book with ISBN 9780809400775 (American Cooking: The Northwest) has no items in stock. Delete this record.

j. Close the table.

Sort a Table and Apply a Selection Filter

You will sort the publisher's table by name and then apply a filter to display only publishers located in New York.

a. Open the Publishers table in Datasheet view. Notice Time Life appears after Triumph Books. This is because the table is sorted by the PubID field.

b. Click in any record in the PubName field and sort the field in ascending order.

c. Apply a Selection filter to display only publishers with a PubCity equal to New York.

d. Close the table and save the changes.

Use Filter By Form

You will obtain a list of all books with more than 50 units in stock. This will help the management decide on what books to put on sale. You will use Filter By Form to accomplish this. You will also demonstrate how filters are saved.

a. Open the Books table in Datasheet view.

b. Use Filter By Form to display books with more than 50 units in stock. Save and close the table.

c. Open the Books table in Datasheet view. Click Toggle Filter in the Sort & Filter group to demonstrate that the filter is saved.

Back Up a Database and Add an Application Part

You will demonstrate adding an application part to the manager to show how tables are created. You will first back the database up to reinforce the importance of backing up the data.

a. Create a backup copy of your database, accepting the default file name.

b. Add a Comments application part, selecting the option One 'Books' to many 'Comments'. Select the Title field for the Field from Books and Sort Ascending for Sort this field. Name the lookup column Book.

c. Open the Comments table in Datasheet view. Add a new comment. Select Social Media: A Student's View for the Book. Use the current date and add A fun and insightful book! for the Comment field.

d. Close the database and exit Access. Based on your instructor's directions, submit the following: a01c1Books_LastFirst and a01c1Books_LastFirst_CurrentDate

Capstone Exercise 8 -

The Morris Arboretum in Chestnut Hill, Pennsylvania tracks donors in Excel. They also use Excel to store a list of plants in stock. As donors contribute funds to the Arboretum, they can elect to receive a plant gift from the Arboretum. These plants are both rare plants and hard-to-find old favorites, and they are part of the annual appeal and membership drive to benefit the Arboretum's programs. The organization has grown, and the files are too large and inefficient to handle in Excel. You will begin by importing the files from Excel into a new Access database. Then you will create a table to track donations, create a relationship between the two tables, and create some baseline queries.

Create a New Database

You will examine the data in the Excel worksheets to determine which fields will become the primary keys in each table and which fields will become the foreign keys.

a. Open the a02c1Donors Excel workbook, examine the data, and close the workbook.

b. Open the a02c1Plants Excel workbook, examine the data, and close the workbook.

c. Create a new, blank database named a02c1Arbor_LastFirst. Close the new blank table created automatically by Access without saving it.

Import Data from Excel

You will import two Excel workbooks into the database.

a. Click the External Data tab and click Excel in the Import & Link group.

b. Navigate to and select the a02c1Donors workbook to be imported.

c. Select the First Row Contains Column Headings option.

d. Set the DonorID field Indexed option to Yes (No Duplicates).

e. Choose DonorID as the primary key when prompted and accept the table name Donors.

f. Import the a02c1Plants workbook, set the ID field as the primary key, and then change the indexing option to Yes (No Duplicates).

g. Accept the table name Plants.

h. Change the ID field name in the Plants table to PlantID.

i. Open each table in Datasheet view to examine the data. Close the tables.

Create a New Table

You will create a new table to track the donations as they are received from the donors.

a. You will create a new table in Design view and save the table as Donations.

b. Add the following fields in Design view and set the properties as specified:

• Add the primary key field as DonationID with the Number Data Type and a field size of Long Integer.
• Add DonorID (a foreign key) with the Number Data Type and a field size of Long Integer.
• Add PlantID (a foreign key) as a Number and a field size of Long Integer.
• Add DateOfDonation as a Date/Time field.
• Add AmountOfDonation as a Currency field.

c. Switch to Datasheet view, and save the table when prompted. You will enter data into the table in a later step. Close the table.

Create Relationships

You will create the relationships between the tables using the Relationships window.

a. Open the Donors table in Design view and change the Field Size property for DonorID to Long Integerso it matches the Field Size property of DonorID in the Donations table. Save and close the table.

b. Open the Plants table in Design view and change the Field Size property for PlantID to Long Integerso it matches the Field Size property for PlantID in the Donations table. Save and close the table.

c. Identify the primary key fields in the Donors table and the Plants table and join them with their foreign key counterparts in the related Donations table. Enforce referential integrity and cascade and update related fields. Save and close the Relationships window.

Add Sample Data to the Donations Table

You will add 10 records to the Donations table.

a. Add the following records to the Donations table:

b. Sort the Donations table by the AmountOfDonation field in descending order. Close the table.

Use the Query Wizard

You will create a query of all donations greater than $100 in the Donations table.

a. Add the DonorID and AmountOfDonation fields from Donations (in that order).

b. Save the query as Donations Over 100.

c. Add criteria to include only donations of more than $100.

d. Sort the query results in ascending order by AmountOfDonation.

e. Run the query.

f. Save and close the query.

Create a Query in Design View

You will create a query that identifies donors and donations.

a. Create a query that identifies the people who made a donation after April 1, 2018. This list will be given to the Arboretum staff so they can notify the donors that a plant is ready for pickup. The query should list the date of the donation, donor's full name (LastName, FirstName), phone number, the amount of the donation, and name of the plant they want (in that order). Add the tables and fields necessary to produce the query.

b. Sort the query by date of donation in descending order, then by donor last name in ascending order.

c. Run, close, and save the query as Plant Pickup List.

Copy and Modify a Query in Design View

You will copy a query and modify it to add and sort by a different field.

a. Copy the Plant Pickup List query and paste it using ENewsletter as the query name.

b. Open the ENewsletter query in Design view and delete the DateofDonation column.

c. Add the ENewsletter field to the first column of the design grid and set it to sort in ascending order, so that the query sorts first by ENewsletter and then by LastName.

d. Run, save, and close the query. Close the database and exit Access. Based on your instructor's directions, submit a02c1Arbor_LastFirst.

Capstone Exercise 9 -

Northwind Traders, an international gourmet food distributor, hired a new CEO. She asked for your assistance in providing summaries of data that took place before she started with the company. To help her with her strategic planning, you will create queries to perform data analysis. Based on your meeting, you plan on creating four queries. One query will find orders with major delays. Another query will summarize the cost impact of customer discounts. A third query will be used to help evaluate financing. The final query will calculate the total sales by country.

Database File Setup

You will open the Northwind Traders food database, use Save As to make a copy of the database, and then use the new database to complete this capstone exercise. You will add yourself to the employee database.

a. Locate and open a03c1Food and save the database as a03c1Food_LastFirst.

b. Open the Employees table. Add yourself as an employee. Fill in all information, with the hire date as the current date. Set your Title to Technical Aide, extension to 1144, and the Reports To field to Buchanan, Steven. Leave the Photo and Notes fields blank.

c. Close the Employees table.

Shipping Efficiency Query

You will create a query to calculate the number of days between the date an order was placed and the date the order was shipped for each order. The result of your work will be a list of orders that took more than 30 days to ship. The salespeople will be required to review the records and report the source of the delay for each order. The CEO feels there may be issues with one of the shipping companies, and would like data to back that up.

a. Create a query using Query Design. From the Customers table, include the fields CompanyName, ContactName, ContactTitle, and Phone. From the Orders table, include the fields OrderID, OrderDate, and ShippedDate.

b. Run the query and examine the records. Save the query as Shipping Efficiency.

c. Add a calculated field named DaysToShip to calculate the number of days taken to fill each order. (Hint: The expression will include the OrderDate and the ShippedDate; the results will not contain negative numbers.)

d. Run the query and examine the results. Does the data in the DaysToShip field look accurate? Save the query.

e. Add criteria to limit the query results to include only orders that took more than 30 days to ship.

f. Add the Quantity field from the Order Details table and the ProductName field from the Products table to the query. Sort the query by ascending OrderID. When the sales reps contact these customers, these two fields will provide useful information about the orders.

g. Add the caption Days to Ship to the DaysToShip field. Switch to Datasheet view to view the results. Adjust column widths as necessary.

h. Save and close the query.

Order Summary Query

The CEO is considering the financial impact of discounts. She asked for a query showing the employee name, number of orders they have taken, and the total discount amount they have given customers. She hopes to see if there is a correlation between the discount offered and the number of sales.

a. Create a query using Query Design and add the Orders, Order Details, Products, and Customers tables. Add the fields OrderID and OrderDate from the Orders table. Set both fields' Total row to Group By.

b. Add a calculated field in the third column. Name the field ExtendedAmount. This field should multiply the quantity ordered (from the Order Details table) by the unit price for that item (from the Products table). This will calculate the total amount for each order. Format the calculated field as Currency and change the caption to Total Dollars. Change the Total row to Sum.

c. Add a calculated field in the fourth column. Name the field DiscountAmount. The field should multiply the quantity ordered, the unit price for that item, and the discount field (from the Customers table). This will calculate the total discount for each order. Format the calculated field as Currencyand add a caption of Discount Amt. Change the Total row to Sum.

d. Run the query. Examine the results. Most customers should have a discount of 10% of the total dollars, but some customers will have no discount. Save the query as Order Summary. Return to Design view.

e. Add criteria to the OrderDate field so only orders made between 1/1/2016 and 12/31/2016 are displayed. Change the Total row to Where. This expression will display only orders that were placed in 2016.

f. Run the query and view the results. Adjust column widths as necessary. Save and close the query.

Order Financing Query

The CEO would like the salespeople to discuss financing with customers. In order to do so, she would like you to create a query showing the impact on price for prior orders. This way, the reps can give customers a comparison with an order they have already placed. For the moment, she is considering a 5% interest rate, paid over 12 months. She would like you to leave the results as negative numbers.

a. Create a copy of the Order Summary query named Order Financing.

b. Open the Order Financing query in Design view and remove the DiscountAmount field.

c. Add a new field using the Expression Builder named SamplePayment. Insert the Pmt function with the following parameters:

• Use .05/12 for the rate argument (5% interest, paid monthly).
• Use the number 12 for the num_periods argument (12 months).
• Use the calculated field [ExtendedAmount] for the present_value.
• Use the value 0 for both future_value and type.

d. Change the Total row to Expression for the SamplePayment field.

e. Change the Format for the SamplePayment field to Currency.

f. Run the query and examine the results. Adjust column widths as necessary. The results appear as negative numbers, as requested. Save and close the query.

Order Summary by Country Query

The company is planning on opening up some shipping centers internationally. The previous CEO had been considering Brazil, Denmark, and Germany as potential shipping center locations, but he was working from older data. You will provide a list of total shipment value by country for the year before the current CEO started to best inform her decision making.

a. Create a copy of the Order Summary query named Order Summary by Country.

b. Open the query in Design view. Replace the OrderID field with the Country field from the Customers table.

c. Run the query and examine the summary records; there should be 21 countries listed.

d. Switch to Design view and change the sort order so that the country with the highest ExtendedAmount is first and the country with the lowest ExtendedAmount is last.

e. Run the query and verify the results. Note the ExtendedAmount field has a caption of Total Dollars, so this is the field the query will be sorted by.

f. Save and close the query.

g. Close the database and exit Access. Based on your instructor's directions, submit a03c1Food_LastFirst.

Capstone Exercise 10 -

Want'n Waffles is a small, successful mobile food business. The company was started by two culinary arts students and their families as a way to finance the students' college education. A year later they own three food trucks that sell breakfast waffles, waffle sandwiches, and dessert waffles. Street-food lovers line up around the block when the food trucks park in their neighborhood. The truck locations are advertised via Twitter and on Facebook so waffle lovers can follow the trucks from place to place. The business has increased its revenue and profits and the owners are looking to expand their operation by offering franchises in other college cities. They need to prepare a presentation for an important meeting with financiers.

Create a Title Slide

You add your name to the title slide, apply a theme, and create a slide for the Want'n Waffles mission statement.

a. Open p01c1Waffles and save it as p01c1Waffles_LastFirst.

b. Create a Notes and Handouts header with your name and a footer with your instructor's name and your class name. Include the date and time, updated automatically. Apply to all.

c. Replace YOUR NAME in the subtitle placeholder on Slide 1 with your name.

d. Apply the Retrospect theme.

Add Content

You add the information about your business as content slides.

a. Create a new slide after Slide 1 using the Section Header layout. Type the following in the title placeholder: Want'n Waffles provides gourmet quality food prepared on the spot in a clean mobile truck.

b. Double-click the border of the title placeholder, then change the font size to 60 pt and apply Italic.

c. Add the following speaker note to Slide 3: We can sell inexpensive breakfasts, lunches, and desserts because our overhead is low. We don't have to pay for a "brick and mortar" restaurant with all of the expenses of a building. Because we don't have to pay for servers, prices stay down while sales increase. Our trucks are a favorite with employees because they get quick service, excellent food, and the convenience of a location close to them. We are mobile so we can change location as needed to increase sales. Best of all, our food is FUN!

Create Tables

You create a table to show the increase in sales from last year to this year and a table showing a few of your waffle specialties.

a. Create a table of four columns and seven rows in the content placeholder on Slide 4. Type the data from the table below in your table.

b. Apply the Medium Style 2 - Accent 2 Table Style to the table.

c. Format the table text font to 18 pt. Center align the column headings and right align all numbers.

d. Add a new slide after Slide 4 that uses the Comparison layout. Type Want'n Waffle Specialties as the title of the slide, use Luncheon Waffles as the heading for the left column, and type Dessert Waffles as the heading for the right column. Type the data from Table 2 below in your table and apply the same formatting to this table that you applied in Step c.

e. View the slide show in Slide Sorter view.

f. Move Slide 5 (A Natural Franchise) so that it becomes Slide 3.

g. Note that Slide 2 includes the mission statement as the introduction slide, Slides 3 through 7 cover the key points of the presentation and include supporting data, and Slide 8 uses a plan for the future as the conclusion (summary) slide.

Add an Online Picture and Animate Content

You want to include a picture of a waffle creation to inspire interest in the franchise. To emphasize the profits the business has realized, you add animations. To help the audience absorb the next steps on the summary slide, you animate the text.

a. Display Slide 3. Use the content placeholder on the right side to open Online Pictures. Use waffles as your search keyword in the search box. Locate an image of a waffle and insert it in the placeholder.

b. Use the same online picture of a waffle on the last slide of your slide show. Position the image in the lower-right portion of your slide, and size it appropriately.

c. Select the Our first year was profitable box on Slide 5 and apply the Fly In entrance animation.

d. Select the Our second year was significantly better box and apply the Fly In entrance animation. Change the Start option to After Previous.

e. Apply the Fly In entrance animation to the text content placeholder on Slide 8.

f. Check the spelling in the slide show, and review the presentation for any other errors. Fix anything you think is necessary.

Navigate and Print

You proofread the presentation in Slide Show view and check the animations. You notice an error on a slide and correct it. When all errors have been corrected, you print a handout with four slides per page.

a. Start the slide show and navigate through the presentation, experimenting with various navigation methods.

b. Note the parallel construction error on Slide 4. The third bulleted point, Profits are increasing, does not start with an active verb as the other bulleted points do.

c. Annotate the conclusion slide, The Next Steps, by underlining detailed financial proposal and circling two and ten with a red pen.

d. Exit the presentation and keep the annotations.

e. Use the Slides pane in Normal view to navigate to Slide 4. Modify the third bulleted point as follows: Increase profits.

f. Print a handout with four slides, horizontal per page if directed to print by your instructor.

g. Save the file as a presentation and as a show. Close the file. Based on your instructor's directions, submit the following:

p01c1Waffles_LastFirst

p01c1Waffles_LastFirst.ppsx

Capstone Exercise 11 -

You are developing a report for your sociology class about the roles of women in the science, technology, engineering, and mathematics (STEM) fields. After doing some research, you begin to see that throughout history, women have had very few opportunities in these areas for historical and societal reasons. You want to demonstrate to your classmates the key and increasingly important role women have played in STEM advances. You will use your PowerPoint presentation to inform them of some key contributors in the STEM areas. In this capstone project, you concentrate on developing the content of the presentation.

Design Template

You download a template to create the basic design and structure for your presentation, save the presentation, and create the title slide.

a. Create a new presentation using one of the available templates. Search for the template using the search term Technology and locate and download the Technology at work design slides template.

b. Save the presentation as p02c1Women_LastFirst.

c. Type Women in STEM as the title on the title slide.

d. Type the subtitle Science, Technology, Engineering, and Mathematics and change the font size to 20.

e. Insert 6 blank new slides.

f. Create a handout header with your name and a handout footer with your instructor's name and your class. Include the current date. Apply to all slides.

Outline and Modifications

Based on the storyboard you created after researching women in STEM on the Internet, you type the outline of your presentation. As you create the outline, you also modify the outline structure.

a. Open Outline view.

b. Type Name 3 women in STEM as the title for Slide 2.

c. Type each of the following as Level 1 bullets for Slide 2: My biology teacher, My computer applications teacher, My math teacher.

d. Type Think on a bigger scale as the title for Slide 3. Enter each of the following as Level 1 bullets for Slide 3: National names?, and International names?

e. Add this speaker note to Slide 3: These may be hard questions to answer quickly because there are relatively few women in these fields.

f. Type Here are some names to get you started as the title for Slide 4.

g. Type each of the following as Level 1 bullets for Slide 4: Sally Ride, Christa McAuliffe.

h. Add this speaker note to Slide 4: For different reasons, both of these women were important in the development of the aerospace industry.

Imported Outline

You have an outline on women in STEM that was created in Microsoft Word and also a slide show on that topic. You reuse this content to build your slide show.

a. Position the insertion point at the end of the outline after Slide 4.

b. Use the Slides from Outline option to insert the p02c1Stem outline.

c. Delete Slide 5 and any blank slides.

d. Demote the last two bullets on the new Slide 5.

e. Click the first bullet on Slide 6. Cut and paste the text after the name and date from the bullet point to the Notes pane. Replace She with Hypatia. Repeat for the remaining two bullets.

f. Delete all text after physics for the first bullet of Slide 7. Replace the comma with a period.

g. Position the insertion point at the end of the outline.

h. Reuse Slides 2 and 3, using the same order, from p02c1Work to add two slides to the end of the presentation.

i. Modify the outline structure by reversing slides 8 and 9.

Design

The content of some of the imported slides does not fit well and the font colors are not uniform across all of the slides. You want to adjust the layout and font color to create a well-designed presentation. Then you decide to view a slide show to verify your changes.

a. Switch to Normal view. Change the layout of Slide 9 to Blank.

b. Check Slides 5-7 to ensure the title placeholder font is Arial Black (Heading) with the color set to Black, Text 2. Check the subtitle font to Arial Body with the color set to Grey 80%, Text 1.

c. Use the spelling checker and proofread the presentation.

d. View a slide show from the beginning.

e. Move Slide 5 to just before Slide 8.

Sections

To facilitate organization of the presentation and moving between the slides, you create sections.

a. Add a section before Slide 2 and rename it Quiz.

b. Add a section before Slide 5 and rename it History.

c. Add a section before Slide 7 and rename it Reasoning.

d. Print the outline as directed by your instructor.

e. Save and close the file. Based on your instructor's directions, submit p02c1Women_LastFirst.

Attachment:- Assignment Files.rar