Integration Unit D Project 1: Silver Screen Catering
• Start MS Word and save a new blank document as LastName_FirstName Int D1.
• In the styles group of the home ribbon right-click on the normal style, choose modify, and then change the font to Arial size 13.
• Use word art to insert a shape similar to the shape shown in figure D1 (Silver Screen Catering).
Size the word art you inserted as 1/2 inch high by 3 1/2 inches wide. Apply square text wrapping style, then apply right horizontal alignment.
• Change the font to size 10 and type the address as shown in figure D-1.
• Press enter three times after the web address and then insert a horizontal line. Note: If you hold shift as you draw the line it will keep it level horizontally. Make sure your line extends from the left margin all the way to the right margin.
• Change the line style so that it has a weight of 4.5 points and color 25% gray.
• Click below the line and press enter three times. Drag the line up if you need to so it appears just under the letterhead address as shown in figure D1. If you click normal in the style list it should change the font back to size 13. From the text group of the insert ribbon choose a formal date&time, i.e., April 6, 2006. Press enter twice.
• Type all the text for the letter as shown in figure D1. The price, discount, and hyperlink are all put in later after the excel spreadsheet is created.
Step 10: Save your document.
A. Start Excel and save a blank workbook as LastName_FirstName Int D1.
B. Copy the top of your word document to one Paragraph mark below the line. Paste it into the Excel workbook and then you should be able to drag the word art (Silver Screen Catering) to the left so that it starts in cell E1.
C. Click cell A9, enter all the labels for cells A9 to H29 as shown in figure D-2. Select cells B20 to F20, click the merge and center button. Then select cells G21 to H29 and click the currency style button. Be sure to format labels the way you see them (i.e., bold, etc.).
D. Select cell H21 and enter the formula =A21*G21. Select cell H21 and drag the corner fill handle of H21 down to cell H24 to copy the formula down. Widen the column as needed to display the data correctly.
(You multiply the quantity by the unit price to determine the total amount due for each set of items) E. Select cell H26 then click the autosum button and verify that $8,500.00 appears in cell H26.
F. Select cell H27 and enter the formula =H24*0.15.
G. Select cell H28 and enter the formula =(H26-H27)*0.07. You calculate the 15% discount on the total snack pack amount due in cell H24 and then calculate 7% tax on the subtotal less the discount. Verify that $574 appears in cell H28.
H. In cell H29, enter the formula to subtract the discount from the sum of H26 and H28. Verify that $8774 appears in cell H29.
I. Select cells A9 to D12, and apply a thick box border around the cells. Apply the same border style used to cells A14to D17. Add a top and bottom border to sell H26, and a top and double bottom border to sell H29.
The completed worksheet appears as shown below in Figure D-3.
J. Save the workbook.
K. Click cell H29, click the Copy button, switch to Word, then click after the first occurrence of the word 'of' in the second paragraph of the letter.
L. Choose the bottom half of the paste button, click Paste Special, click the Paste link, click Unformatted Text, click OK, then delete any extra spaces around the pasted amount. The value in cell H29 of the invoice ($8,774.00) should now appear in the word document.
M. Switch to Excel, select and copy cell H27, switch to Word, click after '15% discount of' in the second paragraph, paste the value as a link (Unformatted Text), and delete any extra spaces around the pasted amount, then save the document.
The copied amounts appear in the Word letter as shown in Figure D-4.
To further assist Silver Screen Catering, you create a hyperlink from text in Word to the invoice in Excel. Then, you change some of the values in the Excel invoice and update the links in the Word document so that they relate to a newer version of the Excel invoice.
Select the word 'here' in the last sentence of the second paragraph, Insert a Hyperlink to the excel file LastName_FirstName Int D1.xls. Click ScreenTip in the Insert Hyperlink dialog box, and type "This link opens Invoice for Marlin Productions in Excel.), as shown in Figure D-5. (Note: The word "here" is made into a hyperlink that, when ctrl-clicked, opens the invoice in Excel.)
Switch to the excel workbook (you should be able to ctrl-click the hyperlink you just made), and type "Thank you for your business!" in cell A35 in the Excel workbook, format it with italics, and center it across cells A35 to H35. Go to page layout and change the right margin to 0.5, select the Center on page 'Horizontally' check box, then dick OK.
Save both the Word Document and the Excel Workbook.
In the Excel workbook, change selected values and text to update the invoice to that as shown in Figure D-6. The new total in cell H29 should be $10,539.50. Save the workbook.
Switch to Word, verify that $10,539.50 and $450.00 appear in the letter as the invoice amount and discount, then change the text "12 days between May 1 and May 14" to "21 days between June 1 and June 21".
Compare your work to the final versions of the letter and invoice as shown in Figure D-7 and D-8 below.
Save and close both the letter (Word Document) and invoice (Excel Workbook).
