The Adventure Works Cycles Case Scenario Free Essay Example

The purpose of this case study is to enable you, the application architect, to understand the nature of business problems that you will need to solve. Adventure Works Cycles, a large, multinational manufacturing company, produces and distributes metal and composite bicycles to North American, European, and Asian commercial markets. While its base operation is located in Bothell, Washington, and employs 500 people, several regional sales teams are located throughout the company’s market region. In 2000, Adventure Works Cycles bought a small manufacturing plant, Wide World Importers, which is located in Mexico City, Mexico.

Wide World Importers manufactures several critical subcomponents for the Adventure Works Cycles product line. These subcomponents are shipped to the Bothell location for final product assembly. In 2001, Wide World Importers became the sole manufacturer and distributor of the touring bicycle product group. After a successful fiscal year, Adventure Works Cycles is looking to broaden its market share by focusing its sales efforts on the company’s best customers, extending product availability through an external Web site, and reducing the cost of sales by reducing production costs.

What Are the Business Problems?

The business problems can be categorized according to the various departments—Sales, Human Resources (HR), Wide World Importers Acquisition, Purchasing, Information Systems, Production, System Administration, and Engineering. 1. Business problems in the Sales department

Sales representatives from regional sales offices have assigned sales territories in the United States, Canada, England, Australia, Germany, and France. Each regional office consists of several sales representatives and a team manager. In their daily sales activities, sales representatives use both laptops and Handheld PCs that run Microsoft® Windows CE.

A typical work day for a sales representative starts with the representative dialing in to the regional office and downloading current data such as inventory, product, and promotional information. During customer visits, the sales representative takes orders on the laptop or Handheld PC.

At the end of each day, the sales representative sets up appointments for the following day or week, checks the appointments of other representatives in the area for possible collaboration, and updates the contact list. The sales representative dials back in to the regional office, sends updated information, and receives any new internal communications from the base office or regional office. The company currently uses Microsoft Outlook® for e-mail. The sales teams have identified the following requirements that will enable them to perform their jobs better: • Customer segmentation and profiling. The sales team needs to be able to extract valuable information from raw data available in the databases to answer questions such as the following: o What are the early warning signs of problems?

o Who are the best customers across all product lines? With whom should the sales team focus its efforts for building long-term relationships? o What are customers’ issues, categorized according to demographic groups (geographic location, revenue history, and so on)? o What products are the customers buying and at what rate? • Sales activity.

o The current discount policy allows sales representatives the discretion to discount a particular order up to 15 percent. Sales managers can increase their own discounts or customer discounts up to 20 percent. The product should allow employees to provide appropriate discounts to customers, depending on the employee’s role. o To support sales activity throughout the world, the sales team needs international support, including the ability to have product information, especially dates and pricing, available in multiple languages and currency types. • Internal communication. Each sales representative must receive customer and sales data pertinent only to that representative. Each team manager must receive relevant customer and appointment data along with detailed information for each sales representative on that manager’s team. A manager must be able to assign customers to sales representatives based on their relationships with the customers, though usually customers are assigned by region.

• Opportunity management. Sales representatives need a method to store and access sales opportunity data and, when a sale is generated, to convert some or all of the information into a sales order without reentering information. • Decision support system. The decision support system should provide the following features: o Allow marketing/sales staff to query and use customer data to generate standard reports; execute custom queries; obtain information related to promotion tracking, sales forecasting, and customer segmentation; and access third-party data sources and financial evaluation tools. o Present all customer activity in a unified way, including multiple contacts, conversations, and transactions. o Allow marketing personnel to initiate new promotions and programs on a multinational basis. Currently, sales representatives do not know how to associate these programs with specific areas for the best impact. o Identify, analyze, and share all aspects of customer relationships with individuals throughout multiple departments.

2. Business problems in the HR department

The current retention rate of Adventure Works Cycles employees is 90 percent for hourly employees and 75 percent for salaried employees. Management predicts that they will need to hire 35 percent more workers during the next fiscal year to replace departing employees and meet the projected increase in production. In addition, the acquisition of Wide World Importers has significantly increased the department’s hiring and employee retention tasks because of the increase in employees and job candidates. Management has determined that they need a solution to track both employee reviews and the resumes of prospective employees. In addition, they want to improve their ability to forecast the number of employees needed and to plan for changes in work compensation and benefits. The HR department requires the following features from the solution: • Resume and review management. All resumes and reviews are stored in documents of different formats. A system is needed to provide: o Unified storage for all file types

o Access to existing employee data (relational database tables) with links to reviews or resumes o Tools for converting all files types to documents that can be shared internally across departments o Ability to secure some areas of documents, such as salary information, from designated users o Ability to search resumes or reviews for keywords or phrases • Analysis and planning. The HR department needs support for performing the following types of analysis: o Compensation and benefits analysis, including impact of international currency exchange rates o Planning to assess the required workforce

o Payroll cost simulations and forecasting

3. Business problems in the Wide World Importers Acquisition department Wide World Importers is located in Mexico City, Mexico. They manufacture several critical components used in the Adventure Works Cycles product line. Adventure Works Cycles recently purchased Wide World Importers in an effort to expand its infrastructure to support the expected growth of the company. Wide World Importers is considered a separate business unit from Adventure Works Cycles; however, it is imperative that some applications and data be shared between the two companies. The business problems associated with this department are as follows: • Data transfer. Adventure Works Cycles cannot migrate and transfer data regularly because: o Wide World Importers does not have a high-speed data transfer utility for moving data from their local database to the three Microsoft SQL Server™ databases in Bothell.

o Adventure Works Cycles has a centralized environment; it needs to enhance the scalability of its production database by transitioning to a distributed environment. o The data is currently not being transferred by means of a secure network connection. • Administration and support. Wide World Importers has limited Information Systems (IS) support. Departments maintain their own workstations and the servers that support the Oracle database are monitored and maintained by two administrators and the IS manager. The company lacks many of the standardized processes used by Adventure Works Cycles in its daily operations management. 4. Business problems in the Purchasing department

Adventure Works Cycles currently uses several vendors to supply various components and raw materials for its product line. The Purchasing department has identified a major supplier who is interested in establishing an Electronic Data Interchange (EDI) with Adventure Works Cycles to transmit critical data and documents such as purchase orders, invoices, payments, and product specifications. Purchasing agents and accounting employees spend more than 50 percent of their time handling these major vendors. After the EDI solution is fully implemented, the Purchasing department manager anticipates a 30 percent increase in employee efficiency in these departments. The Adventure Works Cycles application must provide the following features: • The ability for Adventure Works Cycles and the vendors to transmit and receive a variety of data and file types, including structured and semi-structured data

• The ability for vendors to submit their data directly to the Microsoft SQL Server™ purchasing tables • A Web-based system that provides secure information specific to each vendor • The ability to automatically detect when the vendors have incoming files or other data ready for Adventure Works Cycles to receive 5. Business problems in the Information Systems department The Adventure Works Cycles Internet site is being developed. Currently, customers can access basic product information, find the closest sales office, and request printed information. They cannot order products online or view the status of an existing order. The ability to search the current Web site for product information is limited to viewing products by category. To increase its customer base and provide additional functionality for existing customers, Adventure Works Cycles is expanding its Internet site to include the following features: • Online product ordering for customers

• Online order-status checking • Better search capabilities for product information • Ability to access explicit sections of engineering product specifications • Ability to view product information and pricing in international currency and character sets In addition to the Internet site, Adventure Works Cycles has a small intranet Web site consisting of a home page and links to all department sites. Each department is responsible for maintaining its own site and notifying the IS department of any changes that need to be made to its links on the home page. The focus for the intranet site is to improve internal communication.

Jose Lugo, Adventure Works Cycles’ finance manager, says that it is difficult to get information from one department and supply it to other departments. The department managers want to route more file transactions and data access through the internal
Web site. This intranet site must support the following features: • The ability to search for information across departmental sites. • The ability to change or update to internal information (pricing changes, customer complaints, and so on). • The availability of product data to all departments. It must be visible and adaptable to multiple needs (sales, marketing, and engineering).

6. Business problems in the Production department

The Adventure Works Cycles product line consists of nine bicycle product groups. Each product contains one or more subcomponents, depending on the customer order. Currently, production receives a specification from the Engineering department and uses it to assemble the product. A product clerk enters all the specification information used by production. Certain basic product information is available to customers on the external Web site. However, customers do not have access to the entire product specification.

The entire product specification is available to the sales staff from the internal site. As products move through each assembly area, subcomponents and other value-added materials are added to the product until it reaches the final assembly area. Some of the problems associated with production workflow are as follows: • Scheduling and production. To avoid delays in production, the team needs: o Easier access to manufacturing data, such as work orders and inventory levels, that is applicable to a specific assembly area. o The ability to analyze workflow data.

• Inventory auditing. Instead of manually auditing and updating the inventory system, the company wants to use barcode scanning by using a Handheld PC with the existing inventory audit application.

7. Business problems in the System Administration department Full availability of Adventure Works Cycles’ key systems has become a focus for the System Administration department. Complaints about the amount of system downtime have been increasing steadily. The System Administration department does not have central control of database management functions. As a result, each database group is developing its own practices and procedures. This decentralized approach might not be an optimal use of resources. In addition, the IS staff has requested a better method of monitoring system resources across all operating systems.

The System Administration department at Adventure Works Cycles must provide the following functionality to address these business problems: • Availability of services at all times to support Adventure Works Cycles’ key systems • Backup and recovery systems for all databases being used at Adventure Works Cycles, including data stored at World Wide Importers • Monitoring of all resources across all operating systems 8. Business problems in the Engineering department

The Engineering department is responsible for designing the major components for all Adventure Works Cycles products. The team needs a system to manage the documentation needs of the department. Some of the requirements of this system are as follows: • Collaborative content management. The department needs an automated system that allows them to control how drawings and specifications are reviewed, approved, and released to manufacturing for use. • Accessing and storage of multiple file types. The department needs a consistent method for storing, retrieving, and archiving various files, such as computer-aided design (CAD) drawings and XML product specifications. This system must be linked to the tabular data used to track drawing and specification versions.

What Are the Requirements of the Adventure Works Cycles Application? The Adventure Works Cycles application is a Web-based retail application. It is intended for both customers and other businesses, such as retail stores reselling Adventure Works Cycles products. In addition, the application needs to host one or more pages to allow potential job applicants to submit resumes to Adventure Works Cycles and retrieve and edit the resumes. Business tasks

The Adventure Works Cycles application must support the following tasks:

• Customers must be able to place orders. • Customers must be able to delete orders. • Application system must be able to reject orders. • Customers must be able to modify existing orders that are being processed. • Customers and resellers must be able to review existing orders. • Customers must be able to create new customer records. • Customers must be able to edit customer information. • Customers must be able to submit resumes for employment consideration. • Customers must be able to retrieve and edit previously submitted resumes.

NOTE

Customers and resellers can access only their own information. Web site requirements

The Internet Web site for Adventure Works Cycles must support the following features: • Every page on the Web site must display a search option with appropriate search controls and a navigation bar on the left side. • The customer must be able to search for products by using a part of a product description and a price range. • The home page must display products that are on sale and special offers, and it must include a picture and description of each product. • A Groups page must display a hierarchical list of links to groups of bicycle models or bicycle parts in a tree structure. • A Product Details page must display information about a single product model. It must contain the product name and description, a large picture, and a price range. If the model is available in multiple sizes, multiple colors, or both, appropriate drop-down lists must be provided. When the customer selects a size or color, the other list is repopulated, and the price range narrows to the price of the specific product.

• A Current Order page must display the products a customer has ordered and the quantity of each product, and it must include unit and total prices. A customer must be able to change quantities or remove items from this page. A Continue Shopping button must take the customer back to the last instance of the Products page visited. • A Customer Sign-in page must allow registered customers to sign in with their e-mail addresses and passwords. New customers must be able to register from this page. (Resellers will not use this page.) • A Sign-in Information page must allow customers to input or change their e-mail addresses, passwords, and other personal information. • An Address Maintenance page must allow customers to create, view, update, and delete billing and shipping addresses in their profiles.

• An Addresses Selection page must allow customers to select or remove billing and shipping addresses for an order. • An Order Summary page must display an order, and it must include sales tax, shipping cost, and the details of reseller discounts. The customer must be able to change the quantities and remove items. • An Arrange Payment page must allow customers to use credit cards that are on file in the database or to input new credit card information. Resellers must be able to select payment types and input purchase order numbers. • An Order Confirmation page must display a summary of the submitted order, and it must include an order date, a confirmation number, and order status. • An Order Status Lookup page must display a list of all orders entered by the signed-in customer. The customer must be able to select one of the orders so that it can be displayed in the Order Confirmation page. • An Available Jobs page must allow customers to submit their resumes.