Using the documents given to you by the class president, you will be entering more data into the Excel workbook you created in Phase 1. You will also insert a new sheet into that workbook that will contain the cost data.
Open the Excel workbook you created in Phase 1, the list of everyone who has responded to their invitations, and the list of costs that will be incurred for the high school reunion.
Download the list of people who have responded to the invitations.
Download the list of costs that will be incurred for the high school reunion.
Complete the following tasks:
Open the guest.xls workbook created in Phase 1.
Review the data on the responded.doc document.
Enter the Attend, Adult, and Child data into your existing workbook.
Create headings on your Classmates sheet for each of the three columns in cells G5, H5, and I5 respectively.
Enter the data for each column based on the data found in the ClassmateResponses.doc document.
Ensure that the formatting of the added columns matches that of the existing table on your Classmates sheet.
Ensure that the columns are wide enough to contain the headings and data you have entered; widen the columns if necessary.
In cell G74, enter the words Total Attending.
Right justify this text.
Change the color of the font of this text to match the color of your headings.
Make this text bold.
Use either the Insert Function feature or the button on your Home Tab ribbon to enter a SUM function in cell H74 to total the number of adults attending the family reunion.
Use either the Insert Function feature or the button on your Home Tab ribbon to enter a SUM function in cell I74 to total the number of children attending the high school reunion.
Review the data in the costs.doc document.
In your Excel workbook, insert a new blank worksheet.
Move this worksheet to the right of your Classmates worksheet.
Rename this worksheet Costs.
Change the color of the Costs tab.
Use the costs.doc document as your guide to create a table on the Costs worksheet to calculate the total costs of the high school reunion.
In Cell A1, enter your name.
In Cell A2, enter the date.
A5:D5 should contain the following headings:
Description
Cost
Multiplier (This is the number that you multiply the cost by to reach your total that will be used for the adult meal, child meal, adult sweatshirts, child sweatshirts, and hours for the photographer.)
Total
Change the font size of your headings.
Change the font color of your headings.
Change the background color for your headings.
Enter the data from the budget.doc document into the sheet.
Food Cost and High School Reunion Sweatshirts should be bold, and the two items under each (Adult and Child) should be indented.
Format the Cost and Total columns to Currency.
Add borders as necessary to create your table.
The Multiplier column should contain the following information:
For the Adult Meal item, create a link that refers to the cell on the Classmates sheet that contains the total number of adults planning on attending the family reunion (Verify that the number that shows as a result of the link in this cell is 73).
For the Child Meal item, create a link that refers to the cell on the Classmates sheet that contains the total number of children planning on attending the family reunion (Verify that the number that shows as a result of the link in this cell is 13).
For the Adult T-shirts and Child Sweatshirts items, create links that refer to the same cells referenced in items 7a and 7b.
For the Photographer item in the Multiplier column, enter 4 as the number of hours that the photographer will be used.
In the Total column, enter formulas in cells D7, D8, D13, D14, and D15 using cell references to calculate the total for each of these items.
In the Total column, create links to pull the information from the Cost column to the Total column for all other items.
In cell C19, enter the text Total Projected Cost.
In cell D19, enter a Sum function to total the Total column (Verify that the result of your formula is $1,900.00).
Save this workbook using the following naming convention: YourlastnameYourfirstnameIP2.xls.
Save a copy of this Excel workbook on your hard drive or removable storage device
