Bus 150 Fall 2007 3

Professor Chun

PART 2: Hands on Part (A, B, C) (total points 250 points)

ENTER YOUR NAME: ______

*** IMPORTANT *** You have to return this sheet with your name on in order to receive a grade on this part

A.  Hotel Reservation Database (70 points)

1.  Retrieve data file hotel.accdb and save it to your PC (myDocument folder) or your flash drive.

2.  Open the database file.

3.  You will see a table called RESERVATION. It contains a log of hotel reservations.

4.  Design a reservation entry form (use split form). Save it as “Reservation form”. Add logo and colors on the form.

  1. Add a validation rule to state that daily rate should be greater than equal to 0 and less than 500. The error message should be provided in case this rule is violated in data entry (e.g. Invalid: Daily rate should be between 0 and 500).

5.  Design a query to list the name and rooms of all the guests who checked in December 5, 2001.

  1. Save the query as “Guests for December 5, 2001”
  2. Design a report containing this query result. Save it as “List of Guests”.
  3. Export List of guests report to a web page.

6.  The owner wants to create a room charge bill for “Darlene Shore”.

  1. Design a query that contains the name of Darlene Shore, the number of nights she stayed, and the charge for the stay. Save it as “Invoice” (hint: use calculated field.)
  2. Generate a report. (hint: you will have to create a report based on the query “invoice”) Add a logo on the header section, and add a text with your name on the footer section. Save the report as “invoice report.”

7.  Upload the database file (hotel.mdb) to the digital drop box. (Use “send” option.)

B.  Excel data (20 points)

  1. Go to the class blackboard and save the data file as “myvendor.xlsx” onto your hard disk or your favorite disk (floppy or flash disk) (remember where you are saving it).
  2. Background Information of the excel data

(Take a look at the data by opening the excel workbook.)

A big car assembly company CAR Inc. has four suppliers for parts. The data in the spreadsheet contains a list of all the items that CAR Inc. ordered from its suppliers over the past three months.

Each field in the spreadsheet contains the following information:

Vendor information: Vendor Name, Vendor No

Order information:

Order No, Item No, Item Description, Item Cost (=unit cost),

Quantity (=how many items are ordered and shipped)

Total cost

Payment information: Account payable terms

Shipping (Delivery) information:

Shipping Date = date when an item is shipped to CAR Inc.

Promised Transit Time = days the supplier said it would take to deliver parts

Actual Arrival Date = date when an item actually arrived to CAR Inc.

  1. To determine the best suppliers in terms of reliable delivery time, you have to calculate:

·  Calculate total cost

·  Actual Transit Time (This is how long it actually took for an item to arrive from the shipping date.)

·  Days Late (The difference between the promised transit time and actual transit time.)

  1. Save the EXCEL workbook “myvendor” and quit.

C.  Access Database (100 points)

5.  OPEN MS ACCESS and create a new database “vendordb.accdb” in myDocument folder.

  1. Import the vendor data in the excel sheet in myvendor.xlsx into “vendor” table. Accept an automatic number as a primary key.
  2. Create a query to list “Vendor Name, Description, Cost, Quantity, Total Cost, Days late” where Vendor name is “Durrable Products” (save this query as durable products)
  3. Create a query to list “Vendor Name, Description, Cost, Quantity, Total Cost, Days late” where your could input Description as a parameter. (Save it as description query)

9.  Create a query to list vendor names only once, and save it as “vendor name list”. (hint: query property needs to be changed to select unique vendor names only.) (save it as unique query)

10.  Create a query to list the names of vendors and average days late for each vendor. (hint: use groupby and average). Save the query as “Average days late by vendor”.

For example, the query results may look like this

Vendor Average days late

Name of vendor 1 3

Name of vendor 2 4

…..

  1. Export (or copy) the query “Average days late by vendor” into an excel file (chart.xlsx). Create a bar or column chart to show the relationship between vendors and their average days late.

11.  Create a query to calculate the average “total cost” for gasket grouped by each vendor.

a.  It should show the list of vendor name, average of total cost

b.  Save the query as “gasket average total cost”

c.  Create a report on the average total cost for gaskets for each vendor (hint: use the query in 11).

D.  Powerpoint (60 points)

12.  Open MS PowerPoint application. Create a new presentation called “Vendor Analysis.pptx”

13.  Apply a document theme of your preference.

14.  Use the following outline for creating the slides.

·  (TITLE PAGE) Vendor Performance Analysis

o  Presented by

o  YOUR NAME

·  (SLIDE 1) Benefits of Vendor Performance Analysis

o  Ability to quantify specific costs

o  Share vendor cost criteria

o  Improve productivity.

o  Reduce costs by comparing costs between vendors.

·  (SLIDE 2) Current Vendors

o  [insert a MS Access query result on “Vendor Name list” created in question (14) ]

·  (SLIDE 3) Vendor Performance Analysis;

o  [Insert the chart you created in EXCEL] (copy from your excel sheet)

·  (SLIDE 4) Competitive Pricing

o  [Insert a MS Access query result on “gasket average total cost” for each vendor ]

·  (SLIDE 5) Strategic Sourcing

o  [Summarize your decision on the best vendor]

15.  Add at least one clip art in one slide and use custom animation

16.  Set an slide transition for all slides.

17.  Add header and footer in all slides (page number, footer (your name), date) (hint: use slide master view)

18.  Put CUNY logo on the slide master, so that it can show on each slide.

19.  Save “Vendor Analysis.pptx” and close the powerpoint file.

20.  Close MS Access. Close MS EXCEL. Close MS Powerpoint

21.  Zip Excel (myvendor.xlsx, chart.xlsx), Access (hotel.accdb, vendordb.accdb) and PowerPoint (Vendor Analysis.pptx) files as “mytest4.zip” file and upload it to the blackboard digital dropbox. Make sure these files are uploaded as a single zip file. To verify your upload is complete, download your zip file in desktop and unzip and see all the files are in it, and can be opened.