CSE 111 - Fall 2002

Lab #5

 

Background

Now that we have explored Word and Excel - let's see how we can use them together.

 

The TA will begin lab by introducing some of the ideas that you will need to complete the lab.  Ask the TA if you have any questions about the contents of the lab, or if you are unsure how to accomplish a specific task.  Please note that there examples of almost all the tasks you are asked to perform in this lab in the Word and Excel books.  Please also refer to these books for step-by-step instructions on how to accomplish many of the tasks for this lab.

 

Creating a Folder for Lab 5

Please refer back to the directions in Lab 1 if you don't remember how to create a folder. Create a folder in CSE111 entitled Lab5 that you will save all your Lab 5 work in.

 

Modify your Spreadsheet from Lab 3

You will be modifying the spreadsheet you created in Lab 3 that models a household's income.  First, you should open up in Excel the spreadsheet you created for Lab 3.  Then, you should do a "Save As" and save the spreadsheet into your Lab5 folder with the name "HouseLab5.xls".  You will only be modifying Sheet 1 or "Sheet with Values" and Sheet 2 "Sheet with Formulas" by adding the following elements:

 

• Add conditional formatting on the Column B (the column that gives the monthly income) so that the cells in that area will turn a color (of your choice) if the Monthly income is under $2,500 in any given month.

• Implement Data Validation on the Cells in Column G in the Monthly Expenses box so that you can not insert a monthly expense in any of the boxes that is greater than $600.  See Project 6 in the Excel Book for further information on how to do Data Validation

• Add a column to the area where you computed left over income after each month so that it contains a formula that displays "OVERSPENT" in the cell if you do not have leftover income in a particular month, and displays nothing otherwise.

• Create a table of tax brackets.  This information should be available by searching the web.  Use the VLOOKUP function to calculate what tax bracket this household would fall into based on their yearly income.  Then, create a column next to (gross) monthly income (Column B) that will indicate what the household's net income after the taxes are removed will be.  Make this column C.  Use this net income to compute left over monthly income in your left-over monthly income section.

• Insert a comment on two different cells in this workbook.

• Finally, copy the new contents of Sheet with Values to Sheet with Formulas and make sure that Sheet with Formulas is displaying the formulas.  Save your workbook.

 

 

Merging the Spreadsheet with a Word document 

You will now embed this Excel worksheet into a Word document.  See Project 4 in the Excel book for more information.  If you follow the directions in that Project, you will have a spreadsheet in your Word document that updates if you change the actual values in your Excel document.  This is what you want to happen. A change in the Excel document should be reflected in the Word document.  Save this document in your Lab5 folder as "EmbeddedHouse.doc".

 

You will now add the following elements to this Word document:

• A saved Macro.  It is up to you what this Macro does, but it should do at least two different things.  Give the Macro any name you like.  You can make a keyboard shortcut for it if you want.  See Project 2 in the Word book for help on creating Macros.

• At least one of the following elements from Project 6:

• A Footnote

• A Bookmark

• A Text Hyperlink (to another document or a web page)

• A Graphical Hyperlink (to another document or a web page)

• A Cross-Reference

• At least one of the following elements from Project 7.  You may have to add quite a bit of additional text and or graphics to make these meaningful.  You can put any type of text you wish.  It can be from something you did for another class, or from the Internet (make sure you site your source).  Again, we are not really concerned with the content, just your ability to perform these tasks with Word.

• An Index

• Table of Contents

• Table of Figures

• Table of Authorities

 

 

What should I hand in and when is this due? 

You should save both your documents to your Lab5 folder.  Put the Lab5 folder (and its contents) on a 3.5" floppy disk and hand it in to your TA at the beginning of your recitation next week.  Please make sure your name appears on the disk.  You should not print out any documents to hand in for this lab.

 

Refer to the list of due dates below for your recitation.