Integrate Excel and Word Skills project Excel Worksheet 1 JOB 1. Create a worksheet showing the budget for the Software Mastery Training Series, as shown in the spreadsheet on the following page. Save the file as EX-job1. 2. Prepare the worksheet heading as follows. a. B1—Merge and center over columns B-H. Format bold, Antique Olive, 14-point font, and Indigo font color. b. B2—Merge and center over columns B-H, bold, Indigo font color. c. D3—Revised:, bold, Indigo font color. d. E3—Enter date function; bold, Indigo font color, format with 3/14/01 date style. e. A1—Insert clip art; size to fill the range A1:A3. (Hint: Search for computers.) f. Row 1—Change row height to 37.50 and center the text in the row vertically. g. Row 3—Change row height to 24.00. h. Apply a cell border around A1:H3. Choose indigo border with yellow shading. 3. Insert a new row as the fourth item in the Salaries category. Select A17:C17 and move to the new row. Indent A10, like the three other salary categories above. Move C10 to B10. Delete the blank row left when the Staff category was moved. Project Integrate Excel and Word Skills Excel EX-72 Your position as an assistant to Derek Bradberry, Training Director of The Bookstore, Inc., will allow you to showcase your Word and Excel skills. A needs assessment recently completed by Mr. Bradberry revealed the need for staff members to master their software to increase efficiency in completing administrative tasks. Your responsibilities include preparing the budget for the Software Mastery Training Series, preparing a memo with the column chart you prepared displaying the budgeted salaries, designing a worksheet template for the training series, preparing the training design sheet, creating customer labels for the training diskettes, and preparing a Web page. overview (continued) Project Integrate Excel and Word Skills Excel EX-73 4. Enter a formula in C11 to compute total benefits (15% of total salary). (Hint: Reference the percent entered in A23.) 5. Enter a formula in C10 to compute the total salaries. Enter a formula in C19 to compute the total budget. 6. Format number cells: a. B7 and C19—Currency, $ symbol, 0 decimal places b. B8:B10 and C10:C18—Currency, no symbol, 0 decimal places c. A23—Percent, 0 decimal places 7. Change the width of column A to 22.00 and column B to 10.00. (You will need to resize the image you inserted in A1.) 8. Insert the following comment in B13: Cost on lease of 15 laptops and trainer workstation. 9. Create a custom header that displays Training Department at the right. Create a custom footer that displays Software Mastery Training Series at the left and Page # of total pages at the right. 10. Save, but keep the file open. You will print this worksheet after Job 2. Project Integrate Excel and Word Skills Excel EX-74 Memo with Embedded Chart 3 JOB Prepare a memo to Tara Lanford, Vice President, from Derek Bradberry, Training Director. Use the Professional template; key our company name in the heading. Use the Paste Special feature to embed the chart showing the budgeted salaries created in Job 1. Send a copy of the memo to Catherine Lawrence. Save the memo as EX-job3. Excel Column Chart 2 JOB 1. Save the open file as EX-job2. 2. Create a column chart as an embedded object in the worksheet prepared in Job1. Show the breakdown of the Salaries budget item. Move the chart to A25 and fill the range A25:G40. Format as follows: a. Chart title: Salaries Budget for Software Mastery Training Series. Insert thick green border around chart title. b. Data points: Green c. Chart area: Gold d. Plot area: Recycled paper fill effect e. Category axis and value-axis: Bold f. Legend: None 3. Save, print, and close the file. Function Review Paste Special Copy Excel chart. In Word, click Edit, Paste Special. Project Integrate Excel and Word Skills Excel EX-75 To: Tar a Lan ford, Vice Preside nt Fro m: Der ek Bra dberry , Training D irecto r CC: Catherine Lawre nce Date: Feb ruary 20, 20 0- Re: Upd ate on Train ing Pr ogram for So ftware Maste ry Ser ies The train ing pr ogram titled Softw are Ma stery Series is de signed as a result of th e outc ome of a nee ds ass essmen t cond ucted by the Train ing De partme nt las t week . The needs asses sment showed that mor e than three fourths of our staff on ly kno w and use on e four th of the ca pabilities o f their software. Whe n aske d abou t time -savin g features s uch as mail merge, templates, styles , inde x, boo kmarks , hyp erlink s, pas te spe cial, autofo rmat, very few wer e usin g thes e efficiency featu res. Upo n evaluation of th e lear ning o utcome s dete rmined by th e need s asse ssment, I ha ve hir ed a Mic rosoft consu ltant to ide ntify the so ftware compe tencie s need ed by our staff to incre ase efficien cy in completin g administra tive tasks. The c hart b elow s hows the bre akdown of th e sala ries b udgete d for this training p rogram. As training dir ector, I will be r espons ible for des igning the trainin g prog ram up on rec eipt o f the consultant’s repor t. My train ers ar e very qualified to lead the instruc tional sessions. Salaries Breakdown for Software Mastery Series $0 $500 $1,000 $1,500 $2,000 $2,500 $3,000 $3,500 Director Trainers Consultant Staff The Bookstore, Inc. Memo Project Integrate Excel and Word Skills Excel EX-76 Excel Worksheet Template 4 JOB You will prepare a template for all worksheets related to the Software Mastery Training Series. Then you will use the template to create a worksheet for logging the registration for the first two training sessions held on November 15 and 19. 1. Open EX-job1. Save it as a template file named Software Mastery Training Series Template. 2. Delete all the worksheet data except the heading (A1:H3). (Hint: Use Clear feature to delete all content and formats.) 3. Copy everything on Sheet 1 to the same position on Sheet 2. 4. In B1 on both sheets, key Software Mastery Training Series. In B2, key November 15, 200- on the first sheet and November 19, 200- on the second sheet. Format with full date (Wednesday, March 14, 200-). In B3 on both sheets, key Calendar Wizard. Merge and center across the entire worksheet; bold and indigo font color. 5. Resave. Prepare Training Session Worksheet 1. Save the template worksheet as a Microsoft Excel Workbook named EX-job4. 2. Open the data file Participants. Copy the data on the Nov 15 sheet tab to Sheet1 and the data on the Nov 19 sheet tab to Sheet 2 in the EX-job4 worksheet. Rename the sheet tabs appropriately. Change the tab color as follows: Nov 15-blue and Nov 19-yellow. 3. Hide column H on both sheets. 4. Insert a column to display after column C Department. Label the column Department Fee Paid. 5. Enter data in column D as follows to record that Departments 4 and 6 have paid the registration fee: a. Use the custom filter of the AutoFilter feature to display those records in Departments 4 or 6. b. Then enter 25 as the amount paid in column D. Remember to use the fill handle to copy that amount. c. Repeat for the November 19 session. 6. Insert a column to display after column D. Label the column Group Color. Use the Format Painter feature to copy format of one of the other column headings. 7. Assign the participants as follows to the four groups: Blue, Yellow, Green, and Orange. a. At D6, freeze the panes. Scroll until column E is next to column C. b. In E6, key Blue for the first person. Assign the next person Yellow, the third person Green, and the fourth person Orange. c. Now start over and assign the fifth person to the Blue group. Continue until all participants have been assigned a group. d. Repeat for the November 19 session. e. Unfreeze the panes. (continued) Project Integrate Excel and Word Skills Excel EX-77 8. For the November 15 session, print the area that shows the participants registered from Department 6. After printing, clear the print area. 9. Sort each session by last name in ascending order. 10. Select both worksheet tabs. Print both worksheets in portrait orientation. Ungroup the sheets. Word Training Design Sheet 5 JOB Create the training design sheet for the first module of the training session. Use land- scape orientation. Save as EX-job5. Become Efficient in Your Job—Master Your Software Training Design Sheet Module #1 Calendar Template Leader: Tarase Lowry Lab assistant: Kelly Easley Program Goal #5: To increase efficiency in completing administrative duties. Objectives Procedures Time Materials Evaluation The participant will: 1. understand the benefits of using Word templates. 2. create a calendar using the Word calendar template. 3. change font color to add emphasis. 4. insert a graphic. 5. decrease size of textbox. 6. create textbox and key special notes. The trainer will: 1. check the session roster. 2. complete icebreaker to introduce training participants and leader. 3. distribute Handout #1 (yellow) to participants and demonstrate accessing the various Word templates (memo, agenda, web page, etc.). 4. instruct participants to save calendars on 3 _” training diskette. 5. distribute Handout #2 (blue) and demonstrate creating November calendar while participants work along with leader. 6. assist students in completing the independent practice to reinforce training objectives. 7. Close and check for understanding through oral questioning. 2 min. 10 min. 7 min. 15 min. 2 min. 20 min. 4 min. 15 laptop computers for participants 1 demonstration workstation with computer and projection device 3 _” floppy diskettes with labels Handouts: a. #1 Sample Templates (yellow) b. #2 Using the Calendar Template (blue) Independent Practice See Handout #2 December calendar Observation Student Participation Check for Understanding 1. Click _____, _____ to open the Word templates. 2. Would a picture of a duck be a relevant graphic for February’s calendar? 3. On which toolbar do you find • textbox button? • font color button? • insert clip art button Out-of-Session Application See Handout #2 Jan-Oct calendars 1⁄2 1⁄2 Project Integrate Excel and Word Skills Excel EX-78 1. Key the training title in Tahoma 16 point and all other heading information in Times New Roman 12 point. Set spacing after each line to 6 point. Add 11⁄2" border and 15% gray shading. 2. Insert a five-column table. a. Choose the Table Web 2 AutoFormat. b. Use the numbering feature to insert numbers. Decrease indent to display at the left of the column. c. Use Repeat row heading feature to repeat column headings on the second page. d. Insert a footer with Module #1 printed at left and Page 1 of total pages at right; bold, 10 point. Optional: E-mail the training design sheet as an attachment to your instructor and one student. Compose an e-mail message that requests them to review the training design sheet. Ask them to send any suggestions by Friday. You received a number of suggestions for improving the training design sheet. Make the following changes. 3. In the Procedures columns, switch items #4 and #5. 4. Insert a column to the left of the Evaluation column. Label it Cost; center the heading. Center the following costs in the new column: Laptops $1000 Demonstration workstation $500 Diskettes $20 Handout 1 $20 Handout 2 $25 5. Add the following row at the end of table. Merge the cells so the row is one cell. Teacher Reflection Were the participants at ease in the learning environment? Did the participants understand the time-saving capability of using Word templates? Did each participant successfully complete the November and December calendars? Were the participants motivated to return to their offices and complete the Jan-Oct calendars for mastery of this objective? Do you perceive that the participants will call you if additional assistance is needed? 6. Insert a row above the Teacher Reflection row. Enter a formula to total the Time and Cost columns. 7. Save the revisions, print, and close the file. Project Integrate Excel and Word Skills Excel EX-79 Word Mail Merge Labels with Excel Worksheets 6 JOB 1. Prepare labels for the diskettes you will provide the participants in the November 15 training session. Choose Avery label #5097 and use the Excel file EX-job4 as your data source. Hints: When the Edit Recipient List dialog box displays, deselect any rows not needed in the mail merge, e.g., the heading. Note the field names given to each column (F1 for Last Name). You may double-click the headings to size to best fit for ease in reading. 2. Save the main document as EX-job6 merge Nov 15. Save the merged labels as EX- job6a. Print the labels. Double-check each label with the Excel file to be sure groups were assigned correctly. Note that having the teams assigned prior to the training session will save time during the session. 3. Prepare labels for the November 19 session, as shown below. Rename the main document EX-job6 merge Nov. 19. Select a new recipient list (Nov 19 sheet). Save the labels as file EX-job6b. Print. Check group assignments. Word and Excel Web Pages 7 JOB 1. Save the budget created in Job 1 as a Web page named EX-job7. 2. Preview the Web page. (Hint: It is recommended to view in Internet Explorer for software compatibility.) 3. Make any needed revisions. For example, the Antique Olive font in A2 may not display. Change to Arial. Resave the Excel file. Refresh your Web browser.