BPC110 LESS13 HOMEWORK

by
Uncategorized

Part 1 – Microsoft Excel

Use Excel to create a workbook containing four worksheets. The Excel workbook will include hotel guest and reservation data, along with a chart, and the Rio Salado Hotel and Resort remodeling loans.

  1. Create a new blank workbook.
  2. Save the workbook with the filename RSC_Hotel_Workbook_MEID.xlsx. Replace “MEID” with your actual MEID.

Structure of the Workbook 

Your workbook must contain four worksheets:

  • Guest Registration – This worksheet will contain guest contact information.
  • Reservations – This worksheet will show the current room reservations for the Rio Salado Hotel and Resort.
  • Income Chart – This worksheet will provide a visual depiction of the income of the current room reservations.
  • Remodeling – This worksheet will contain contractor bids for the remodeling of the hotel.

Guest Registration Worksheet

The Rio Salado Hotel and Resort wants you to create a new worksheet to keep track of their guests’ contact information:

  1. Rename the worksheet as Guest Registration.
  2. Assign the worksheet tab a color of your choice other than the Office default.
  3. Create a new table to store the following information for each guest (TIP: Remember to use fine data granularity):
    1. Guest Name
    2. Guest Address
    3. Phone Number
    4. Email Address
    5. Guest ID
  4. Align Center and Bold the column headers of the table.
  5. Provide data in each cell of the table for a total of 10 guests using the following specifications:
    1. You provide the data for the Guest Names, Addresses, Phone Numbers, and Email Addresses.
    2. Use Special Formatting for the Phone Number column.
    3. Use Text and Concatenation Functions to generate each Guest ID using the first three letters of the Guest’s Last Name, and the Guest’s entire Zip Code.
  6. Increase the column widths to ensure that you can view the longest data entry in each cell of the worksheet.
  7. Align Left the Zip Code and Phone Number data.
  8. Sort Ascending the table on the Guest Last Name column.
  9. Apply an appropriate Table Style of your choice other than the Office default.
  10. Resolve any Error Messages you might receive in any cells.
  11. Freeze the column header row of the table.
  12. Use the Page Setup Dialog Box to perform the following tasks:
    1. Set the Page Orientation to Landscape.
    2. Scale to Fit to one page wide.
    3. Add a Footer to the worksheet:
      1. Enter your Name in the left section.
      2. Enter your MEID in the center section.
      3. Enter the Course Number and your Section Number in the right section.

Reservations Worksheet

Add a new worksheet to keep track of the guests’ room reservations:

  1. Add a new blank worksheet to the workbook.
  2. Rename the worksheet as Reservations.
  3. Assign the worksheet tab a color of your choice other than the Office default.
  4. In the first row, insert the title Guest Reservations.
  5. In the second row, create a new table with the following column headers:
    1. Room Number
    2. Guest ID
    3. Check-In Date
    4. Number of Days
    5. Check-Out Date
    6. Room Rate per Day
    7. Subtotal of Stay
    8. Room Tax
    9. Resort Fee
    10. Total of Stay
    11. VIP Guest
  6. Merge and Center the title in the first row over the table columns in the second row.
  7. Change the Font Size of the title to 18 pt.
  8. Apply a Fill Color of your choice, other than the default, to the title cell.
  9. Enter data in each cell of the table for 10 rooms using the following specifications:
    1. Room Number – Enter 10 different room numbers.
    2. Guest ID – Copy this data from the Guest Registration worksheet and Paste Values.
    3. Check-In Date – You provide this date. Use Short Date number formatting.
    4. Number of Days – You provide these values; between 1 and 7 days.
    5. Check-Out Date – Use a formula based upon the Check-In Date and Number of Days columns to calculate this date.
    6. Room Rate per Day – You provide these amounts; between $100.00 and $300.00. Use the appropriate number formatting.
    7. Subtotal of Stay – Use a formula based on the Number of Days and the Room Rate per Daycolumns to calculate this amount.
    8. Room Tax –
      1. A few rows below the table, enter a Row Label in Column A with the text “Room Tax”; in Column B of the same row, enter the value of 8.65%.
      2. In the Room Tax column of the table, use a formula to calculate the Room Tax based upon the Subtotal of Stay column and using an Absolute Cell Reference to the value of 8.65% that you entered in the previous step.
    9. Resort Fee –
      1. Below the Room Tax Row Label, enter a Row Label with the text “Resort Fee”; in Column B of the same row, enter the value of $24.99.
      2. In the Resort Fee column of the table, use an Absolute Cell Reference to equate all cells in the column to the value of $24.99 that you entered in the previous step.
    10. Total of Stay – Use a formula to calculate the total of stay based upon the Subtotal of Stay, Room Tax, and Resort Fee columns. Ensure that at least three (but not all) of the Total of Stay amounts is greater than $1,000.00 (adjust the number of days or room rates if necessary).
    11. VIP Guest – Use a Logical Function to display the text “YES” if the Total of Stay is greater than $1,000.00; otherwise, display “NO”.
  10. Increase the column widths to ensure that you can view the longest data entry in each cell of the worksheet.
  11. Using the Table Tools, add a Total Row to your table and use functions to Sum the Total of Staycolumn and Count only the number of VIP Guests.
  12. Apply the appropriate Number Formatting for all cells in your table.
  13. Use Conditional Formatting to apply Bold font style to the Top 2 Totals of Stay in your table.
  14. Apply an appropriate Table Style of your choice other than the Office default.
  15. Resolve any Error Messages you might receive in any cells.
  16. Freeze the first two rows of the worksheet.
  17. Use the Page Setup Dialog Box to perform the following tasks:
    1. Scale to Fit to one page wide by one page tall.
    2. Set the Page Orientation to Landscape.
    3. Add a Header to the worksheet:
      1. Enter your Name in the left section.
      2. Enter your MEID in the center section.
      3. Enter the Course Number and your Section Number in the right section.

Income Chart

The Rio Salado Hotel and Resort CEO wants a visual depiction of the anticipated income from the reservations.

  1. Select the Reservations worksheet.
  2. Insert an appropriate chart using the data from the Room Number and Total of Stay columns.
  3. Using the Chart Tools, move the chart to a new sheet named Income Chart.
  4. Assign the worksheet tab a color of your choice other than the default.
  5. Select a Chart Style of your choice other than the default.
  6. Change the Chart Title to Anticipated Income.
  7. Add horizontal and vertical Axis Titles with appropriate text.
  8. Add Data Labels.
  9. Use the Page Setup Dialog Box to perform the following tasks:
    1. Set the Page Orientation to Landscape.
    2. Add a Footer to the worksheet:
      1. Use the Insert File Name button in the center section.

Remodeling Worksheet

The owners of the Rio Salado Hotel and Resort want to remodel some of the rooms. Add a new worksheet to keep track of contractors’ bids and the amount of loans the owners would need to take out to pay for the remodeling.

  1. Add a new blank worksheet to the workbook.
  2. Rename the worksheet as Remodeling.
  3. Assign the worksheet tab a color of your choice other than the Office default.
  4. Create a new table with the following column headers:
    1. Contractor Name
    2. Bid
    3. Down Payment
    4. Loan Amount
    5. Annual Interest Rate
    6. Term in Years
    7. Monthly Payment
  5. Enter data for a minimum of four contractors using the following specifications:
    1. Contractor Name – You will provide this information.
    2. Bid – You will decide this value and enter the amount.
    3. Down Payment – Use a formula with a cell reference to calculate a 10% cash down payment of the Bid.
    4. Loan Amount – Use a formula with cell references to calculate the amount that will need to be financed.
    5. Annual Interest Rate – Use the rate of 3.5% for all rows. Use the Increase Decimal button, if necessary, to show the entire rate.
    6. Term in Years – Use the value of 10 for all rows.
    7. Monthly Payment – Use an appropriate Financial Function to calculate the monthly payment.
  6. Increase the column widths to ensure that you can view the longest data entry in each cell of the worksheet.
  7. Apply the appropriate Number Formatting to all cells in your table.
  8. Apply an appropriate Table Style of your choice other than the Office default.
  9. Freeze the column header row of the table.
  10. Use the Page Setup Dialog Box to perform the following tasks:
    1. Set the Page Orientation to Landscape.
    2. Scale to Fit to one page wide by one page tall.
    3. Add a Header to the worksheet:
      1. Enter your Name in the left section.
      2. Enter your MEID in the center section.
      3. Use the Insert Date button in the right section.

Required Worksheets

Arrange the worksheets in the following order:

  1. Guest Registration
  2. Reservations
  3. Income Chart
  4. Remodeling

Save and close your RSC_Hotel_Workbook_MEID.xlsx file.

«

Part 2 – Microsoft Access

Use Access to create a database in which you can store and retrieve information about the Rio Salado Hotel and Resort room types, room rates, and hotel services.

  1. Create a new blank database.
  2. Save the database with the filename RSC_Hotel_Database_MEID.accdb. Replace “MEID” with your actual MEID.

Structure of the Database 

NOTE: Read the requirements for the database and be sure you understand how it should work before creating your design.

You will need to complete the following to create your database:

  • Create three tables.
  • Import a table from Excel.
  • Establish table relationships.
  • Create two forms.
  • Create two queries.
  • Create one report.

As you work on the project, remember to follow best practices for creating databases as described in your TestOut materials and the online lesson content.

Create the Tables

You will create new tables that contain information about the Rio Salado Hotel and Resort room types and room rates. When creating fields for your tables, it is important to break down your data into the smallest chunks you can (fine data granularity) to make it easier to extract data from the database later. Remember to assign the most appropriate data type to each of the fields and that one of the fields in each of your tables must be set as the Primary Key using the AutoNumber data type. You do not need to enter data records into your tables at this time; you will create a form later in this project for data entry.

Room Types Table

Create a new table named Room Types Table. At a minimum, your table should include the following fields:

  • Type ID
  • Room Type
  • Number of Beds
  • Maximum Occupancy
  • Number of rooms (of this type)

Room Rates Table

Create a new table named Room Rates Table. At a minimum, your table should include the following fields:

  • Rates ID
  • Type ID
  • Room Rate Description
  • Room Rate

Establish Table Relationships

Once the design of the tables has been completed, the next step is to establish relationships between the tables. You will join the Room Types Table with the Room Rates Table on common fields through the following tasks:

  1. Join the primary key of the Room Types Table with the foreign key of the Room Rates Table in a One-To-Many relationship.
  2. Enforce referential integrity.
  3. Cascade update related fields.
  4. Cascade delete related records.

Create a Form

Once the tables have been designed and the relationships have been established, it is time to enter data. Remember that each field of each record will need to include appropriate data. You will use one form to enter and edit data in the two tables:

  1. Create one form named Room Types Form that can be used to enter data into both tables.
  2. Change the title of the form to Room Types Form.
  3. Insert a row below the Room Rates Table subform. Add a button in the new row to perform the Add New Record action with the text: Add Record.
  4. Use the form to enter a minimum of four room types (e.g., Studio, Suite, 1-Bedroom, 2-Bedroom) and a minimum of three room rates and descriptions (e.g., Rack, Off-Season, Senior) for each room type. Include a Maximum Occupancy greater than 5 for at least two (but not all) room types.
  5. Apply the Retrospect theme to the form.

Create a Query

The ability to extract data from one or more tables is one of the most important functions provided by a database. You will now design, save, and run a query to show only the room types with a maximum occupancy greater than 5 by following these tasks:

  1. Create a query named High Occupancy Query based upon the Room Types Table.
  2. Include the Room Type and Maximum Occupancy fields.
  3. Use a Number Criteria for the Maximum Occupancy field to select only the room types with a Maximum Occupancy greater than 5.
  4. Save and run the query.

Create a Query

Next, you will design, save, and run a query to show the total number of rooms in the Rio Salado Hotel and Resort:

  1. Create a query named Number of Rooms Query based upon the Room Types Table.
  2. Include the Number of Rooms field.
  3. Use the Total Row to Sum the number of rooms.
  4. Save and run the query.

Create a Table

You will create a new table that contains information about the Rio Salado Hotel and Resort services. Remember to assign the most appropriate data type to each of the fields and that one of the fields in each of your tables must be set as the Primary Key using the AutoNumber data type. You do not need to enter data records into your tables at this time; you will create a form later in this project for data entry.

Create a new table named Hotel Services Table. At a minimum, your table should include the following fields:

  • Service ID
  • Service Description
  • Operating Hours
  • Phone Extension Number

Create a Form

Once the table has been designed, it is time to enter data. Remember that each field of each record will need to include appropriate data. You will use a form to enter and edit data in the Hotel Services Table:

  1. Create a form named Hotel Services Form that can be used to enter data into all fields of the Hotel Services Table.
  2. Change the title of the form to Hotel Services Form.
  3. Insert a row below the Phone Extension Number field. Add a button in the new row to perform the Add New Record action with the text: Add Record.
  4. Use the form to enter a minimum of four hotel services (e.g., Room Service, Housekeeping, Valet, etc.).

Create a Report

Reports are used to generate printouts from the tables in a database. The Report Wizard can be used to access multiple tables, select fields, and group data in a report:

  1. Use the Report Wizard to create a report named Hotel Services Report.
  2. Include the Service Description, Operating Hours, and Phone Extension Number fields from the Hotel Services Table.
  3. Group by Service Description.
  4. Use a Stepped Layout in Portrait Orientation and be sure the report fits on one page.
  5. After finishing the Report Wizard, remove the Alternate Row Color from the report.
  6. Add a Shape Fill of your choice, other than the Office Default, to the Service Description rows.

Import a Table from Excel

One of the best features of Access is how easily it can work with data from other programs. Since the Guest Registration information has already been stored in Excel, you need only to import the information in that spreadsheet into an Access table.

  1. Import the table from the Guest Registration worksheet created in Part 1 – Microsoft Excel as a new table named Guest Registration Table into your database.
  2. Your imported table must include Column Headings and a Primary Key.
  3. Assign the most appropriate data type to each of the fields.

Edit Database Properties

Database properties contain information on the title of the database, the author, and other information. Edit the database properties to include the following information:

  1. Go to the Backstage View by clicking on the File tab.
  2. Locate and edit the Database Properties to include the Course Number and your Section Numberin the Subject field.

Required Objects

After creating your database, the RSC_Hotel_Database_MEID.accdb file should contain the following required objects:

  • Tables
    • Guest Registration Table
    • Hotel Services Table
    • Room Rates Table
    • Room Types Table
  • Queries
    • High Occupancy Query
    • Number of Rooms Query
  • Forms
    • Hotel Services Form
    • Room Types Form
  • Reports
    • Hotel Services Report

Save and close your RSC_Hotel_Database_MEID.accdb database.

Submit Your Assignment

After completing both sections of the project, submit your Excel and Access files following the instructions in the lesson.

«

Tags: No tags