Capstone

TOPIC for PowerPoint Project ➔ Check with your Instructor

Some rules to follow in creating your presentation along with some suggestions:

1. Do not use foul language (keep it clean)
2. Do not make sexual innuendoes
3. Keep it “G-rated” (something you could have kids to read)
4. Try to “teach” or “inform”

Use a template or a theme.

For Templates: Click on File/New, and then you can select PowerPoint presentations and slides, Sample Templates, or you can enter a module in the “Search Office.com for Templates” box to search for various templates (though that will also give you invitations, and all sorts of other things)

Use at least 3 slide layout choices. You must use at least:

1. 1-Title Slide (this is to be your first slide)
2. 1-Title and Content Slide
3. 1-Two Content Slide (any type of two content slide)

Follow the 7 x 7 rule

1. 7 x 7 rule means do not exceed 7 words per line, and 7 lines per slide.
2. Follow the rule for the most part (exceptions here and there are fine).

Add speaker notes to your Overview slide.

Remember, this is why the 7x7 rule works. Use speaker notes when you want to convey more information than is on the slide. Print using Notes Pages, and then make sure and “talk” about “your notes” during your presentation!

Using Graphics Effectively:

1. Insert at least 1 “Online Picture” and resize (if necessary)
2. Change the Picture Style of the picture!

1. Insert a shape into a slide
2. Enhance your shape by adding color and using a shape style.
3. Now add text to your shape by adding a text box on the shape.

Include (on all slides) a footer that includes:

1. The date
2. A slide number
3. Footer text of: By: Firstname Lastname. Replace the word Lastname with your last name and replace Firstname with your first name. For example, if your name is Tom Jones, then your footer text would be: By: Tom Jones.
4. Check the box so that the footer does not show on the title slide!

• Save and Close the PowerPoint presentation.
• You may now proceed to word project instructed below.

Insert a Footer into the document with Your Name in the left section, today’s date in the center (choose any style but make sure it is NOT automatically updated) then, tab to the right section and insert a Page Number in the Current Position, choosing Accent Bar 1 style (see image below).

Note: It is very important to choose Current Position after you select the Page Number drop-down arrow.

Add 4 blank lines into your file as follows (see image below):

1. Move your cursor to the end of the second paragraph, Part 1: PowerPoint, and then press Enter to add a new blank line.
2. Add three more new blank lines by pressing Enter after the following three paragraphs:

Part 2: Word

Part 3: Excel

Part 4: Access

Add a heading title to your file as follows (see image below):

1. Move your cursor to the top of the document by pressing Ctrl-Home and then Enter to create a new blank line at the top of the file.
2. Press Ctrl-Home again to move to the top of the file and then enter the following text: BCIS PROJECT SUMMARY
3. From the Home menu, in the Styles group, change the style of the heading text to the Heading 1 style.

Apply the Heading 2 style to the following four paragraphs (see image below):

Part 1: PowerPoint

Part 2: Word

Part 3: Excel

Part 4: Access

Modify the Heading 1 and Heading 2 styles:

1. Modify the Heading 1 style to be bold and centered.
2. Modify the Heading 2 style to the Black, Text 1 color, and underlined.

Set the Before and After paragraph spacing:

1. Move your cursor to the first body paragraph of the document, starting with: This document will contain
2. On the Layout tab, in the Paragraph group, set the Spacing Before and the Spacing After to 12 pt.

At this point, the text in your document should look as follows (Note: You may have one or two blank lines at the end of the document. Either is fine.).

Insert a picture into your word document as follows:

1. Move the cursor to the blank line after: Part 1: PowerPoint
2. Insert Online Pictures of your choice in into your Word document at the current location.
3. Resize the image to have a Shape Height of 2”.

At this point, your document should look similar to the following image (though the image will be image of your choice):

Create tabs as follows:

1. Move the cursor to the blank line below Part 2: Word
2. Enter the following text: The following is a list of all the files in the Project and then press Enter.
3. With the insertion point (cursor) on the new blank line, set the following tabs:
   1. Left Tab at 1"
   2. Center Tab at 3"
   3. Right Tab at 5.5"
   4. Press the Tab key to position cursor at the first tab stop (1") and start entering the text shown in the image below making sure to:
      1. Enter the text at the appropriate tabs
      2. Use the actual filenames, with your name included as part of the file names

Format the text as follows:

1. Select the text: The following is a list of all the files in the Project
   1. Center the text
   2. Apply the Cambria font with a 16pt font size
2. Select the next line of text: Application File Extension
   1. Underline the text

Create a table:

1. Move the cursor to the blank line after Part 3: Excel
2. Insert a 6-row, 3-column (3x6) table
3. Apply any of the Grid Table 4 styles.
4. Set the Table Column Width for the three columns to 2” each.
5. Merge the cells in the first row
   1. Enter the following text in the first row: Files in BCIS Project
   2. Center the text in the first row
6. Apply any shading to the second row
7. Add a border to the table

Enter the following data, where the file names are your actual filenames, with your names included as part of the file names:

Create a SmartArt as follows:

1. Move the cursor to the blank line after: Part 4: Access
2. Insert a SmartArt Graphic
   1. Select the Accent Process SmartArt, in the Process category
   2. Select the ENTIRE SmartArt (not just one shape), and in the SmartArt Tools Format tab, in the Size group, change the Height to 1” and the Width to 5”
   3. With the ENTIRE SmartArt still selected, in the SmartArt Tools Design tab, in the Create Graphic group, Add Shape so there are 4 parts to the SmartArt
   4. Add the following text to the SmartArt (see image below):

1. Move the cursor (Insertion Point) to the beginning of the line: Part 3: Excel and insert a Next Page Section Break
2. Set the orientation of this second page to Landscape

When completed your word document should look similar to the following:

• Save and close the word document.
• You may now proceed to the Excel project instructed below.

1. Enter the following data into cell range A1:F7 into Sheet1 worksheet of your empty, newly created Excel file
2. AutoFit the column width for columns A through F

Note: to AutoFit the columns, do not use the AutoFit Column Width menu option under Format. Instead, select the entire Sheet1 worksheet by clicking the diagonal down arrow 1 (left of column A) and simply position the mouse over the column line in the column headers, between any two columns so the cursor becomes a double headed arrow 2 and double-click so all columns will AutoFit at the same time.

Below is a sample of what your file should look like at this time

Rename and Copy the worksheet as follows:

1. Rename the Sheet1 worksheet to: Products
2. Right-click the Products worksheet tab and select Move or Copy from the worksheet menu.
3. The Move or Copy dialog box will appear. Under Before sheet: select (move to end) to place the worksheet to the right of the existing worksheet.
4. Check the box next to Create a copy, then click OK.
5. Rename the new worksheet to: Updated Products

In the Updated Products worksheet, add the following column headings:

1. In cell G1 enter Profit per Unit
2. In cell H1 enter Possible Discount
3. In cell I1 enter Quantity
4. In cell J1 enter Net Income

In cell G2, insert a formula to calculate the difference between the UnitPrice and the UnitCost.

1. Use the fill handle to copy this formula down to row 7.

NOTE: Verify formula result with the image in step 48 below

In cell H2, insert an IF function that displays a value of "Y" (when true) or "N" (when false) if the UnitCost (E2) is greater than or equal to 100.

1. Use the fill handle to copy this formula down to row 7.

Enter the following data:

1. In cell I2 enter 100
2. In cell I3 enter 150
3. In cell I4 enter 200
4. In cell I5 enter 20
5. In cell I6 enter 10
6. In cell I7 enter 1
7. In cell I9 enter Discount:
8. In cell I10 enter Highest Value:
9. In cell I11 enter Lowest Value:
10. In cell I12 enter Sum:
11. In cell I13 enter Average:
12. In cell J9 enter 5

NOTE: Verify data entered with the image in step 48 below

Format as follows:

1. Autofit Column Width for column I
2. Format J9 to Currency Number Format with 2 decimal places
3. Select row 1 and Wrap Text which will display column headings in G1, H1 and J1 as multiple lines within the cell.
4. Select the column headings A1:J1 and apply Cell Style of Heading 3.

NOTE: Verify formatting with the image in step 48 below

In cell J2, insert an IF function that calculates the value for Net Income as follows:

1. If the Possible Discount (H2) is equal to "Y",
   1. (true) The gross income is the (Profit per Unit-Discount) multiplied by the Quantity
   2. (false) Or else, the gross income is the Profit per Unit multiplied by the Quantity.

(G2-$J$9)*I2

1. Use the fill handle to copy this formula down to row 7.
2. Format the cells in J2:J7 to Currency Number Format with 2 decimal places

NOTE: Verify function result with the image in step 48 below

Use appropriate functions to do the following:

1. In cell J10 enter a function to display the highest value for the range J2:J7
2. In cell J11 enter a function to display the lowest value for the range J2:J7
3. In cell J12 enter a function to sum the cells in the range J2:J7
4. In cell J13 enter a function to average the cells in the range J2:J7

NOTE: Verify function results with the image in step 48 below

Insert a 3-D Clustered Column chart (see image in step 48 below) as follows:

1. Simultaneously select the following data: B1:B7, D1:D7, E1:E7, G1:G7, I1:I7
2. Insert a 3-D Clustered Column chart
3. Move and resize the pie chart so that it is located in cells B9:G23
4. Change the Chart Title to Excel Project
5. Change the Chart Style to Style 6.

1. Remove the data labels as follows:
   1. the Chart Tools Design tab, in the Chart Layouts group, click Add Chart Element, click Data Labels, and select None.

Change the Page Layout of the Updated Products worksheet so that it (see image in step 48 below):

1. Is set to Landscape orientation and
2. Will FIT to one Page, and
3. Has a Customer Header with Your Name in the left section, Sheet Name in the center section, and Current Date on the right section, and
4. Will print with Gridlines.
5. Will print with Row and column headings

Creating a new worksheet and using VLOOKUP:

1. Copy the data from the Products worksheet to a new worksheet named: Vlookup
2. Note: See step 38 for information on how to copy data from one worksheet to another
3. Enter the following text
4. In cell G1: Handling Fee
5. In cell A12: Category
6. In cell A13: AT
7. In cell A14: DE
8. In cell A15: MD
9. In cell A16: WD
10. In cell B12: Handling Fee in Dollars
11. In cell B13: 0
12. In cell B14: 2
13. In cell B15: 5
14. In cell B16: 10
15. AutoFit the column width for column G
16. In cell G2, insert a VLOOKUP function that determines the correct handling fee as follows:
17. The function will refer to the Lookup_value in C2
18. The function with use the Table_array A12:B16 (though it will need to be absoluted so that each row will reference the same table array once it’s copied)
19. The function will take the value from the table array from column 2
20. The function must contain a Range_lookup of FALSE so that the categories are identical matches between the table array and the main data (if not, AT could be matched in more than one place … ask your instructor to explain this)
21. Use the fill handle to copy this formula down to row 7.
22. Format G2:G7 to Currency Number Format with 2 decimal places

• Save and close the excel workbook.