Improving Decision Making: Using a Database to Clarify Business Strategy

IS 340 Management information Systems Chapter 3 Lab

IS 340 Management information Systems Chapter 3 Lab

Lab 3-10: Improving Decision Making: Using a Database to Clarify Business Strategy (Individual Assignment with a total of 70 points possible)

Due September 21st @ 5:30 PM

Resources needed:

1. A windows PC with Microsoft Access installed.

2. Ess13ch03_questionfile.mdb

In this exercise, you’ll use database software to analyze the reservation transactions for a hotel and use that information to fine-tune the hotel’s business strategy and marketing activities.

Scenario: In the Chapter 3 Assignments module under Content you’ll find a database for hotel reservation transactions developed in Microsoft Access (Ess13ch03_questionfile.mdb) with information about The President’s Inn hotel in Cape May, New Jersey. At the Inn, 10 rooms overlook side streets, 10 rooms have bay windows with limited views of the ocean, and the remaining 10 rooms in the front of the hotel face the ocean. Room rates are based on room choice, length of stay, and number of guests per room. Room rates are the same for one to four guests. Fifth and sixth guests must pay an additional $20 per person per day. Guests staying for seven days or more receive a 10 percent discount on their daily room rates.

The owners currently use a manual reservation and bookkeeping system, which cannot provide management with immediate data about the hotel’s daily operations and revenue. Use the database to develop reports on average length of stay per room type, average visitors per room type, base revenue per room (i.e., length of visit multiplied by the daily rate) during a specified period of time, and strongest customer base. After answering these questions, write a brief report about the Inn’s current business situation and suggest future strategies.

Deliverables: I have included the SQL scripts for each of the needed queries and how to create each report. After running each SQL script, right-click on the default name of your query and choose Save. Give each query a meaningful name. It will then appear on the left-hand pane if you choose All Access Objects.

To create a Report, select the query you just saved in the left-hand pane, then Create and then Report Wizzard. The wizard will walk you through the steps to format your report. You can choose the format you want as long as it shows the necessary information. An example is below:

Repeat the steps to create both a query and report from the list below:

· Reports on the following: (10 points for each set of queries and reports (30 points total))

· Average length of stay per room type

· SELECT [Reservations].[Room Type], Avg([Departure Date]-[Arrival Date]) AS [Avg Visit Length] FROM Reservations GROUP BY [Reservations].[Room Type];

· Average visitors per room type SELECT Reservations.[Room Type], Avg(Reservations.[No of Guests]) AS [AvgOfNo of Guests] FROM Reservations GROUP BY Reservations.[Room Type];

· Base revenue per room during a specified period of time SELECT [Reservations].[Room], [Reservations].[Daily Rate], [Departure Date]-[Arrival Date] AS [Length of Visit], [Daily Rate]*[Length of Visit] AS [Base Income/Room], [Reservations].[Room Type] FROM Reservations;

· Copy and paste each query output and report below: (To copy the reports, click in the report window, select all and copy. When you paste into this document, it will paste as a table) (20 points total for all 6 items)

· Brief report about the Inn’s current business situation and suggest future strategies. (40 to 60 word minimum. (20 Points)

MS 340 Management Information Systems (2020) Brian Volkmuth	1

MS 340 Management Information Systems (2020) Brian Volkmuth