INTRODUCTION 1 PROJECT SCENARIO 2 CREATING THE WORKSHEET 2 GRAPHING YOUR RESULTS 4 INSPECTING YOUR COMPLETED FILE 6
After you complete all of the assigned readings, activities, and exercises in the Microsoft Office Excel study unit, you’ll be ready to complete your final graded project. The project requires you to use your Excel skills to create a simple work- sheet to calculate the cost of office supplies and to track their increase or decrease in cost over a two-month period. Throughout this project, you’ll input specific information in an exact location, ensuring that the formulas and functions reference the proper information. You’ll then create a graph to visually display the results.
You must enter all functions and formulas using cell refer- ences. If at any point in the project you simply enter a value into the cell instead of the proper function or formula, or use a value rather than cell references in a formula or function, that part of the project will be considered incorrect and points will be deducted.
In working through the graded project, you’ll use many of the same functions and layouts you’ve already worked with in the study unit exercises.
FIGURE 1—Worksheet Data
The office where you work would like to track the cost of office supplies used for a two-month period. As office man- ager, you’re asked to compare two months’ worth of inventory and prepare a graphical representation of the comparison to show the increase or decrease in supply use.
You’re given the amounts by the purchasing department and must prepare an Excel spreadsheet to be sent to the Chief Financial Officer of your company.
CREATING THE WORKSHEET
To begin, launch Excel and create the worksheet shown in Figure 1. Please be sure to include the shading in cell ranges A17:F17 and H17:K17. (The color choice is yours.) Save your file as Office Supplies.
Highlight the range of cells A1:F1, merge the cells, and cen- ter the text. Format “January” in Arial, 12 point, bold font.
Highlight the range of cells H1:K1, merge the cells, and cen- ter the text. Format “February” in Arial, 12 point, bold font.
Click in cell A3. Format the text in Arial, 10 point, bold font. Format all the remaining text in row 3 in Arial, 10 point, bold font. Right align the text in the cells.
Your worksheet should now look like Figure 2.
FIGURE 2—Worksheet with Data Entered
In column E (cell ranges E5:E16), use an appropriate for- mula to calculate the total cost for each type of office supply. Format the results in the Currency style. (Note: You’ll need to create your own formulas. There are several formulas you can use, but the ones you choose must produce the correct results and you must use cell references in your formulas.)
In cell E18, use an appropriate formula to calculate the grand total of column E. Format the result in the Currency style.
In column F (cell ranges F5:F16), use an appropriate formula to calculate the percentage of the grand total for each type of office supply. Format the results in the Percentage style. If necessary, decrease the decimal places to round the results to the nearest whole percent.
In cell F18, use an appropriate formula to calculate the total percentage. Format the result in the Percentage style and decrease the decimal places if necessary to round the result to the nearest whole percent. (Obviously, the result should equal 100%.)
Repeat these procedures for column J (Total Cost) and col- umn K (% of Grand Total) for the month of February. (Place the grand total of column J in cell J18 and the grand total of column K in cell K18.) Again, remember to use formulas and cell references.
Sort the office supplies alphabetically in ascending order (A–Z). Remember: To make sure all your calculations remain correct, you must select all information in cell ranges A5:K16 before doing the sort.
Format columns C and H in the Currency style if you haven’t done so yet.
Type “% Change from January to February” in cell A21.
In cell D21, type the formula to calculate the % change from January to February: =(J18-E18)/ABS(E18). Format the result in the Percentage style and decrease the decimal places if necessary to round the result to the nearest whole percent.
Your worksheet will now look like Figure 3.
FIGURE 3—Your Worksheet So Far
GRAPHING YOUR RESULTS
Graphing data is useful when you wish to determine a change in data from one period of time to another, and where a graphical representation would make those changes easier to see.
You’ll graph your results by creating the 3-D clustered column graph shown in Figure 4. The graph includes the name of the office supplies and the total costs for January and February. (Hint: The labels on the horizontal axis are positioned at a –45 degree angle.) Be sure to add the correct information to the legend and to add a title to the graph. Align the top left corner of the graph with the top left corner of cell A23 when positioning the graph in the worksheet. Adjust the width and height of the graph as necessary to show all of the content.
FIGURE 4—Office Supplies Graph
INTRODUCTION 1 CREATE AND EDIT A COVER LETTER 1 CREATE A FACT SHEET ABOUT WORD 2013 6 USE A FLIER TO GENERATE PUBLICITY 9 DESIGN A REGISTRATION FORM 12 REVIEW YOUR WORK AND SEND IT TO
THE SCHOOL 13 INSPECT YOUR COMPLETED FILES 13
Now that you’ve learned how to use Microsoft Word, it’s time to put your skills into practice. You’ll use many of the con- cepts and techniques featured in your studies to complete this project, which your instructor will grade.
This project involves a case study based on a fictional firm. You’ll assume the role of a director of training (Jo Bill) responsible for creating promotional literature for the firm, named Learn-2-Compute, Inc. The promotional documents will be mailed to a potential customer of the firm. The cus- tomer is being personally invited to a training seminar on the new features of Word 2013.
For this project, you’ll complete four sections of the promo- tional literature package. The sections consist of a cover letter introducing the firm and inviting the potential customer to the training session, a fact sheet highlighting the new fea- tures of Word 2013, a flier promoting the training, and a registration form. Detailed instructions for completing and editing the promotional items follow.
CREATE AND EDIT A COVER LETTER
1. Open a new document and save it with a new name.
• Open Word and start a new document. The goal of this project is to make you thoroughly familiar with how to plan and format a letter from scratch. Note, however, that various templates are available in Word for generating already formatted business documents. After completing the project, feel free to explore the templates.
• Save the new document as cover letter.
• Remember to save the document periodically as you work on it. You can set Word’s AutoRecover feature for a short cycle. This doesn’t take the place of saving the document yourself, so consider also getting into the habit of pressing Ctrl + S to save your work whenever you pause while typing.
2. Click on the Show/Hide ¶ button on the main toolbar to display the hard returns in your document.
3. Set the paragraph style (font), line spacing, and margins
for the •
• 4. 5.
entire document, as follows:
From the Font group, select Times New Roman font. If you don’t have this font, you may choose a similar font. Select Automatic (black) for the Font color. Select a 12-point size.
From the Paragraph group, click the Line Spacing button and select 1.0.
From the Page Layout tab, click Margins and select Normal for 1" margins (Figure 1).
Now type the text in Figure 2 as the promotional package’s cover letter.
Insert the current date at the top on the left edge of the page.
• You’ll insert the date as a field that will update automatically. First highlight and delete the current date of the letter. From the Insert tab, click Date and Time. Choose the Month, Date, Year format, the third selection. Make sure Update Automatically is checked (Figure 3). Click OK. The Date and Time dia- log box will reflect your system’s current date.
Emphasize key text with special formatting.
• Convert the list of new features into a bul- leted list. Place a hard return after the colon following the word “features” in the first paragraph. Delete the space and the colon. Also place a hard return after each item sep- arated by a comma. Delete the commas, the word “and,” the period after “Web,”and any extra spaces.
FIGURE 1—You can increase or decrease the size of a margin.
Director, Human Resources Smith, Jones & Brown, Inc. 346 Parkway Drive
Blue Smoke, ID 83300
Dear Ms. Loomis:
Whether your employees are novices or seasoned professionals at word processing, the new Word 2013 offers features to improve productivity, professionalism, and per- formance in your organization. Here are a few of these new or improved features: Reading mode designed for tablet users, bookmarks for tracking where you last read, the ability to edit PDFs in Word, simpler markups for tracking changes, and insert- ing pictures from the Web.
For more details on the features of Word 2013, refer to the enclosed fact sheet.
LEARN-2-COMPUTE, INC., a training company in Idaho with a ten-year track record, offers one-day seminars in the new features of Word 2013. A series of these training sessions will be held in the conference room at the Blue Smoke Shilo Inn the week of November 10. Now is the time to start planning for your employees to attend.
To alert your employees to the new features of Word 2013 and to this unique hands- on training, I am enclosing fifteen copies of a flier that you can distribute. I have also enclosed fifteen registration forms so you can pre-register anyone who is interested. Space is limited to fifteen people in each daylong session, so get your registrations in early! The cost is only $175 per participant.
If I can provide you with more details or information, please call me at our LEARN-2- COMPUTE, INC. main office: 1-888-555-3467, ext. 4471.
Director of Training
FIGURE 2—Cover Letter
FIGURE 3—Select how the date will appear in your document.
FIGURE 4—Select the bullet type.
• Capitalize the first word of every bullet item. Then highlight the list—beginning with “Reading” and ending with “Web”—and click on the Bullet button on the Home tab. Select the option for round, black bullets (Figure 4) from the Bullet drop-down list arrow, if it’s not already selected.
• Make every mention of the company name, LEARN- 2-COMPUTE, Inc., into bold and italic. Try using Word’s Navigation feature to search for the phrase throughout the document.
When reformatted, the bulleted list in the letter should look like Figure 5.
7. Adjust wording.
• After reviewing your on-screen document, you decide that you prefer the word “seminar” to “session” or “sessions.” Use Word’s Find/Replace feature to search for every instance of “session” or “sessions,” and replace with the word “seminar” or “seminars” (Figure 6).
8. Adjust the closing to leave more room for the signature.
• You should have three hard returns after the word “Sincerely.”
9. Check the spelling and grammar, and make any appropriate corrections.
10. After some time has passed, proofread the entire docu- ment. Inspect the overall layout, and try to spot and correct any errors that Word’s grammar checker may have missed.
Note: If the signture runs over to a second page, check the spacing on the inside address. Highlight the address and click on the down arrow in the paragraph panel. Change the Before and After spac- ing to 0 pts.
FIGURE 5—The Formatted Bullet List
FIGURE 6—Word’s Find and Replace Feature
FIGURE 7—Fact Sheet
CREATE A FACT SHEET ABOUT WORD 2013
1. Create a new document and name the document fact sheet.
2. Type the text from Figure 7. Note that each system is different, so your document may appear slightly different. Type the entire document in Times New Roman, 12 point, double-spaced. Be sure to save your work frequently.
New in Word 2013
This view allows users to flip through documents like a book, without Word toolbars as distractions. This is a handy tool for tablet users.
Bookmarks are placed where you last read. So when you reopen a document, you can continue where you left off.
Edit text in PDFs in Word
You can edit PDF documents directly in Word. This is a feature many have requested in the past.
Simple Markup Tracking
If you’ve worked with Word’s Track Changes in other versions of Word, you’ll note there’s a new feature called Simple Markup. It’s a cleaner version showing proposed changes. The traditional view of track changes is still there if you choose to view All Markup.
Inserting Online Pictures and Video
Besides inserting pictures from your computer, you can also search online using the Bing search engine. You can also insert online videos directly into Word documents.
To learn these new features, attend a one-day training seminar the week of November 10. For complete details and registration materials, call Jo Bill at 1-888- 555-3467, ext. 4471.
3. Select the first two lines and format them as the title of the fact sheet.
• Capitalize the first line of the title. Change the font to Arial.
• The first line should be italic and the second line should be regular. Both lines should be in bold.
• Center the title.
4. Add a horizontal line by pressing the Enter key after the
end of Fact Sheet, and then in the Borders and Shading dialog box, click Horizontal Line. After setting your title, it should appear similar to Figure 8.
FIGURE 8—Your Updated Title
5. Select Shapes from the Insert tab and choose a symbol from the Shapes collection and place it to the left of your title. We chose the four-point star. You’ll need to resize the symbol you choose to fit between the borders. Repeat the process to insert a matching symbol on the other side (Figure 9).
6. Apply the Heading 1 style to the following lines:
• Reading Mode
• Edit Text in PDFs in Word
• Simple Markup Tracking
• Inserting Online Pictures and Video
FIGURE 9—Inserting Shapes into Your Heading
FIGURE 10—Your document should look like this.
After formatting the first line, you can use the Format Painter feature on the other lines. Delete extra lines between headings and normal text.
6. Format the descriptive text beneath the headings with Normal style and then change the spacing to 1.5.
7. Format the last two lines of text as Normal style and then change the font to Calibri (Body), 12 point, bold. Use center alignment for these lines.
8. Check the spelling and grammar, and make any appropriate corrections.
9. After some time has passed, proofread the entire docu- ment so far. Inspect the overall layout, and try to spot and correct any errors that you may have overlooked previously (Figure 10).
USE A FLIER TO GENERATE PUBLICITY
1. Enter the text for the flier from Figure 11. Type the document in Normal style, single spaced. Save the document as flier.
Learn What’s New in Word 2013
Attend a daylong, hands-on workshop conducted by LEARN-2-COMPUTE, Inc.
Workshop covers these new features: Reading Mode, Bookmarks, editing text in PDFs in Word, Simple Markup Tracking, and inserting online pictures and video directly in Word.
To learn these new features, attend a one-day training seminar the week of November 10. All workshops conducted in the conference room at the Blue Smoke Shilo Inn.
For complete details and registration materials, call Jo Bill at 1-888-555-3467, ext. 4471.
FIGURE 11—Text for the Flier
2. Create a special heading. Insert five hard returns between the first line of text “Learn What’s New in Word 2013” and the second line “Attend a daylong. . . .”
Use WordArt to create the heading. First, delete the first line of text. Then, click WordArt from the Insert tab. Choose the WordArt style in the top row, second column from the left (Figure 12). When the WordArt text box appears, move the text box so that it’s centered and approximately 11⁄2 inches from the top of the page. Type the heading into the text box. Set the font as 28-point Arial.
3. Emphasize key information with special formatting.
• Format the next two lines, beginning with “Attend a daylong . . .” and ending with “new features,” and set the font at 18 point. Insert a hard return after “new features.”
FIGURE 12—Choose the WordArt in the top row, second from left.
FIGURE 13—Use the Bullets feature to enhance your bulleted list.
• Convert the list of features of the workshop to a bulleted list. In the Bullets list, choose the four-small- diamond design (Figure 13). Format the text of the list at 16 point, bold. Change the spacing to 2.0.
• Insert three hard returns before the last three lines of text on the flier.
4. Format the remaining text at 16 point, bold, italic. Center align the text.
5. Insert text into a table format.
• Place the cursor in front of the word “To” in the first line. From the Insert tab, click Table and then Insert Table. Change the number of columns and rows to 1 (Figure 14). A 1 × 1 table should now appear above the text you selected.
• Cut and paste the lines of text inside the table. You may need to add a hard return to center the text.
6. Select Online Pictures from the Insert menu and choose an appropriate image. Figure 15 shows the image we chose. Use the Picture tools to insert, resize, and position the image to the right of the bulleted list. Note: Please refer back to your Microsoft Word study unit if you’re having trouble moving the image.
7. Save your work, making sure the flier fits on one page.
FIGURE 14—The Insert Table dialog box allows you to customize the size of the table.
8. Check the spelling and grammar, and make any appro- priate corrections.
9. After some time has passed, proofread the entire docu- ment so far. Inspect the overall layout, and try to spot and correct any errors that you may have overlooked previously.
FIGURE 15—Insert an image to the right of the bulleted list.
DESIGN A REGISTRATION FORM
1. Prepare to enter text on a new page by inserting a page break at the end of the flier you created.
2. Create a table to organize the registration form.
• On the new page, insert a table consisting of five columns and seven rows.
• Select the first row of the table. Under the Table Tools layout tab, click on Merge Cells. The cell bor- ders will disappear, leaving one long row. Using Figure 16 as your guide, continue adjusting and merging the cells to form the remainder of the form.
3. Enter the text into the form as it appears in Figure 16. You may need to adjust the size of the cells to create a visually pleasing and useful form.
4. Check the spelling and grammar, and make any appropriate corrections.
5. After some time has passed, proofread the entire document. Inspect the overall layout, and try to spot and correct any errors that you may have overlooked previously.
FIGURE 16—The Completed Registration Form
REVIEW YOUR WORK
AND SEND IT TO THE SCHOOL
Congratulations! Now that you’ve completed the Word 2013 project, you’re ready to apply all you’ve learned about word processing in your own enterprises. All you have left to do with the project is to review your completed work one last time and send it to the school for grading.
INSPECT YOUR COMPLETED FILES
1. Proofread your work.
• If you have a printer, check a hard copy of your work for errors. If you don’t have a printer, you should still check your work carefully by reviewing it line-by-line on your computer screen.
• Your project grade will be based on the accuracy with which you entered and edited the information. Make sure your final document is free of errors, and that you closely followed the instructions for any changes.
2. Check the layout.
• Check that all bullets, margins, and any special formatting are laid out correctly. Depending on the properties of your printer, you may need to make adjustments in spacing or pagination to fit the doc- ument neatly on a printout.
• Even if your computer isn’t connected to a printer, you can still inspect your work using Print Preview from the File tab.
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.