Office 2 Performance Based Test

Select One from Each Program Covered in Office2 (Word, Excel, and Access) Create a Folder in your Google Drive for class work folder called Final and save each project into the folder. Save as Final and the Program and the Number.  So for example, number 2 from the Word section below would be saved as FinalWord2 in the Final folder on Google Drive.

 

Word (select one number from below, if there are 2 letter parts, do both)

 

1.                  

a Formatting a Newsletter

Problem: You have become the editor of the Smith Family newsletter. They have an old copy as a PDF and have asked you to recreate it, getting as close as you can with the new version of Word. (it was created in the old version so it won't be identical) It is your responsibility to create a monthly newsletter that is both attractive and informative. You need to create a nameplate and then enter the newsletter information as illustrated in Figure W6B – 1. You need to create the text box.

Instructions: Perform the following tasks.

1. Change all margins to .75-inch.

2. Create the nameplate using the formats identified in Figure W6B – 1. Create a continuous section break below the nameplate and format section 2 into two columns.

3. Insert the clip art picture (or one close to it) as shown in Figure W6B – 1 and position it as illustrated.

4. Insert the Smith Family data from this document into section 2 below the nameplate..

5. Format the newsletter according to Figure W6B – 1. Create the text box as shown in the figure. Use the Format Painter button to automate some of your formatting tasks.

6. Save the document with FinalWord1a as the file name.

7. Save the document as PDF.

 

b. Letter with Data Source

Nature Pride Naturals, where you work part-time, is a mail order company specializing in vitamins, herbal remedies, and organic foods and beverages. Your boss has asked you to design an form letter that can be sent to customers thanking them for their orders and listing the new specials. This month, organic teas and multivitamins are featured items. The teas are 100 percent pure organic, handpicked, and are caffeine- and pesticide-free. The multivitamins are available in 50-, 100- and 200-caplet bottles(use a multilevel ist for this). You must also inform the customers that you have enclosed a coupon for their next order. If they spent less than $50.00 they received a 10 percent off coupon; If they spent more than $50.00, they received a 15 percent off coupon (use an IF field for this). Obtain or make-up the names and addresses of at least 5 people and use them as records in the data source. Create a directory of the data source records. Add accompanying labels or envelopes for the form letters.

 

2.

            a. Form Letter with Data Source

You work part time at HomeCare, a home health care service. Your boss has just announced that all company computers are being upgraded to run Office 2007. He needs to schedule training sessions for all employees in the two departments he oversees, accounting and communications. He has asked you to create a memo that can be sent to both departments and gives the pertinent information. Use a suitable memo template and include the following information. The memo should be sent to both departments. The accounting department should be informed that they are going to receive training on Word 2007, and the communications department (use an IF field in the To: portion of the memo). The training session for Accounting will be held on March 26th from 1-4 pm; the training session for communications will be the same day from 8-11 am. Both sessions will meet in Conference Room G. Merge and save the memos as FinalWord2a memos. Merge the data source to a directory. Convert the directory to a table and apply formatting to make the table more attractive and readable. Save the table as FinalWord2a table.

 

            b. Newsletter

As a part time library assistant, you have been asked to create a monthly newsletter. The library director wants the first issue to publicize the new technology center and to alert patrons to the many events scheduled at the library this month. Especially important, he believes, are the children’s reading program, the used book sale, and upcoming author lectures. He also wants you to list the new releases in both fiction and nonfiction. Use the internet to assist you with elaborating on information presented here. Organize the information and create a newsletter. The newsletter should contain at least two of these graphic elements: clip art image, picture, SmartArt graphic, pull-quote. Enhance the newsletter with a drop cap, WordArt, color, ruling lines, and a page border. Use leader characters to fill the tab space in the issue information line.

 

Excel (select one number from below, if there are 2 letter parts, do both)

 

1. Public Safety Division Budget Proposal

San Pueblo's Public Safety division comprises three departments —Streets and Sanitation, Fire, and Police. The departments have submitted figures comparing this year's budget with next year's budget in four categories (Table below). Develop a template that can be used to prepare each department's budget and the Public Safety division's consolidated total budget within one workbook.(Each department should have a tab with totals on another tab) Include this year's budget, next year's budget, and the variance [(next year's budget - this year's budget) / this year's budget] for each expenditure. Indicate totals where appropriate. Create an embedded chart on the Public Safety division's worksheet comparing the division's expenditures this year and next.

 

Table 6-12 San Pueblo's Public Safety Division Expenditures

 

Streets and Sanitation

Fire

Police

 

Next Year

This Year

Next Year

This Year

Next Year

This Year

Equipment

212000

198150

62350

78345

225175

220650

Maintenance

68350

62450

22750

17000

98375

102500

Miscellaneous

48125

44520

37600

38200

47500

32800

Salaries and Benefits

116000

112400

198000

211000

150000

162750

 

2. Creating a Consolidated Balance Sheet

Jeans-For-Teens is a New York-based company that sells high-end jeans globally. After launching its Web site five years ago, the company has attracted so many clients from Europe that the own­ers opened a shop in Paris. The New York and Paris shops' assets last year, respectively, were: cash $825,101 and $650,450; accounts receivable $557,190 and $325,860; marketable securities $345,213 and $211,450; inventory $845,258 and $326,120; and equipment $82,250 and $56,200. The liabilities for each store were: notes payable $1,512,381 and $345,000; accounts payable $213,360 and $179,900; and income tax payable $82,100 and $125,350. The stockholders' equity was: common stock $812,300 and $235,000; and retained earnings $324,242 and $162,400.

Design a template as a balance worksheet to reflect the figures above. Include totals for assets, liabilities, and stockholders' equity. Use the template to create a balance worksheet for the New York store, the Paris store, and the consolidated balance worksheet for the corporation.

 

3. Analyzing Company Profits by Category

Elite Software sells computer software and supplies. Merchandise is divided into six categories base profit margin: individual application packages (22%), integrated application packages (9%), entertainment software (16%), system software (25%), learning aids (18%), and supplies (10%). Last year’s salary data has been collected for the State Street and Western Avenue Stores as shown in the Table below.

Develop a template that can be used to determine marketing strategies for next year. Include sales, profit margins, profits (sales x profit margin), total sales, total profits, and functions to determine the most and least sales, profit margins, and profits. Use the template to create a worksheet for each outlet, a consolidated worksheet for the entire company, and a chart on a separate sheet reflecting the company's profits by category.

 

State Street Store

Western Avenue Store

Individual applications

$148,812

$52,864

Integrated applications

140,135

93,182

Entertainment software

62912

72345

System software

22769

25278

Learning Aids

9562

21397

Supplies

44215

34921

 

PowerPoint(choose 1)

PowerPoint Test A - Zip File

PowerPoint Test B - Zip File