CS 107 Introduction to Data Processing

Spring 2006

MS Excel

DVD Rental Store Consultants Assignment

UNH Home Page Computer Science Department Home Page Contact Instructor

Syllabus

Assignments

Lectures

Software Exercises
For this assignment, you and your team of top-notch computer consultants will be using MS Excel to assist the Digital Divide DVD Rental Store in dealing with their customers, DVD inventory, and in providing information to help the owners with some decision making. The scope of the project is defined below. If this project meets DD's expectations, your team will be given a contract to set up similar systems in all of the DD's sattelite stores.

Customer List

This will be a list on one page of the Excel workbook you are setting up for the Digital Divide which will list all of their current customers. Your team decides that you must also add a unique customer number to DD's customer information. The format for the number is up to your team. The information from the store for its customers is:

Customer Last Name
Customer First Name
Street Address
City
State (Two character code)
Zip code
Phone number (with area code)
Type of membership - (Standard, Deluxe, or Supreme)

Customer information is normally sorted by the customer's last name, but DD would like to be able to sort by zip code when they send out store flyers. DD would also like to be able to filter the information so they can see only customers in a particular town or with a particular type of membership.

You team should make up at least 20 "fake" customers for testing your design

DVD Inventory

On another page of the Excel Workbook, your team will be setting up a list of all of the DVD's currently in DD's store. DD would like the following information for each DVD.

DVD ID # (your team will be creating this)
Name of the DVD
Distributor of the DVD
Genre (Western, Action, Horror, Children/Family, Drama, Comedy, Classic, Documentary, Special Interest, Romance, etc.)
Rating (ie G, PG, PG-13, R - DD is a family friendly store, no X)
Release Date

DD would like to be able to filter this information based on any of the information and would also like o be able to sort the list using any of the fields.

Again, your team will be making up fake test data which should include at least one example of each of the possible entries into a field

Decision Support System

On the final workbook worksheet, DD would like your team will be doing some data analysis to assist DD in some decision making.

Currently DD rents each DVD for $2.00. Of that $2.00, $1.00 covers the cost of the DVD, employee salaries, store overhead, etc. The net profit for any given day is calculated as follows:

(# of rentals x $2.00) = Total Income
(# of rentals x $1.00) = Total Cost
Total Income - Total Cost = Net Profit

The average number of rentals per day is 250 DVDs.

1) Set up a one-variable data table showing how Net Profit will change as the price charged for the rental of on DVD varies. Your team should come up with at least 10 alternative prices both higher and lower than $2.00 varying the amount by $.25 (Include the rental cost of $2.00 in this table)

2) Set up a two-variable data table which shows how Net Profit will vary based on a) changing the rental price, and b) changing the total number of DVDs rented on an average per day. Show at least eight different rental amounts, both less than 250 and greater than 250 varying the amount by 25 and be sure to include 250 in that data.

3) Extra Credit Option - (which in the real world can translate to extra $$) - set up a Pivot Table using a data table containing the following information

DVD Name
DVD Distributor
Month (use January, February, and March)
Number of times the DCD was rented that month

Project Notes

Make sure that at least one of your team members keeps all your work on some type of storage device. Not only will you be working on this project over the next several classes, your team will also be working together on a project in Access and you will need your information from this project for the Access project.

All of the team member's names should be on all of the worksheets for your project. Worksheets should also have the name of store, with the date of the project.

Grade for the project will be dependent upon both a hardcopy of all of the worksheets in the project and a live demo to the Digital Divide owner (aka your instructor),

Last Updated: 4/16/06