Free Essay

# Bae Project

Submitted By melhasbaoui
Words 1904
Pages 8
Homework #2 – Excel Charts and Pivot Tables
Overview and Learning Objectives
This homework assignment asks you to use Microsoft Excel to use the VLookup function in a report, and also to generate charts and pivot tables. These are all useful skills. VLookup simplifies spreadsheet logic in many situations, and charts offer a visual perspective that can help people to see trends and data relationships. And once you get the idea of pivot tables, they are extremely useful in summarizing data “on the fly” in a variety of formats. This assignment is related to course objective #4 (“Design and implement Microsoft Excel spreadsheets and Microsoft Access databases to support business decision making”).

Quality Furniture Enterprises, Continued
This homework assignment continues the scenario from Homework #1, about Quality Furniture Enterprises. Caitlin’s uncle (and boss) was pleased with the sales forecast report she created. “I guess I should learn Excel,” he says. “I’ve been using the calculator on my laptop to compute the totals, and then copying and pasting things into a Word document.” Caitlin did her best not to appear smug, but it was nice to be appreciated, especially since this was still her first month on the job. But before she had any time to bask in her success, her uncle said, “And since you did so well on that report, I have another project for you.” So much for taking an early lunch break today. “Sure,” she said. “What do you need?” “First off, remember how I told you we were thinking about changing our commission structure? We’ve decided to vary it depending on the product line. Product lines 1 and 2 are our core business, and people are used to buying those things from us, so we’re only going to offer lower commissions on those products. But product lines 3 and 4 aren’t as well established; we’re going to offer higher commissions on those to encourage the sales force to give those product lines more attention. Here’s a chart showing the new commissions.” He handed Caitlin a sheet of paper with this table: Product Line PL1 PL2 PL3 PL4 Commission % 2.5% 2.5% 3% 3.5%

“Second,” he continued, “the report you created was great, but we also need some reports that summarize the information, instead of showing all the details for each customer. For example, the sales manager wants one that just shows each sales rep’s total forecasts per month. We want so rev. 2014-06-10

see if any of the reps need help finding more potential business. And the product line managers want to see the forecasts by product line, so they know how much potential business they have.” He handed her three sheets of paper. “Here are the reports we need.” Caitlin glanced through the reports. They had similarities, but no two were the same. She thought she knew a way to create them quickly. “And finally …” He paused and sighed. “The Big Bosses went to a furniture industry conference and trade show last week. I hate it when this happens, because they always come back with big ideas that we can’t afford. This time, it’s dashboards and data visualization. They want executive-level displays that they can just glance at to get an idea of what’s going on, but they don’t want to spend any money. Excel can create charts and graphs, right? Just give them a few of those. Maybe a pie chart showing forecasts by product line. And what about a column chart showing forecasts by month and product line? That should be enough for now.” Caitlin knew what her uncle meant. The Big Bosses (better known as her grandparents) were notoriously cheap when it came to spending money on information technology. But an even bigger problem, as she knew from her MIS class, was that a lot of QFE’s business processes would have to be changed to collect and share data more effectively before an executive information system would be a realistic possibility. For now, it looked like she was headed back to Excel, to create some new forecast reports and charts.

Getting Started

Assignment Part A (10 points): VLOOKUP Function
In this part of the homework, you will handle the change in commission structure. 1. Add a “Projected Commission” column to the Forecast Data spreadsheet. Using the VLOOKUP function, calculate the projected commission in dollars for each row in the spreadsheet. Use the commission percentages Caitlin’s uncle provided (see above). 2. Now, revise your commission formula. Add an IF function so that the formula calculates commission ONLY for forecasts with a probability of at least 70% (you may remember that we also calculated commission for “likely orders” only in Homework #1. 3. OPTIONAL for EXTRA CREDIT: Add another forecast column called “Commission 2.” Reproduce the calculations in the Projected Commission column using nested IF functions instead of the VLOOKUP function. Make sure the commission values are the same in both columns. rev. 2014-06-10

2

Assignment Part B (40 points): Pivot Tables
The various types of reports Caitlin’s uncle mentioned offer an example of “slice and dice” data analysis (as discussed in Chapter 2 of the text), where decision makers need to see the data from different perspectives and at different levels of detail. Although you could build each report from scratch as we did with the Homework #1 report, the easiest and most flexible way to create different “views” of the data is to use pivot tables. That’s what you will do in this part of the assignment. 1. (8 points) Create a pivot table from the Forecast Data spreadsheet showing each sales rep’s total forecast per month. Be sure to check the range of cells Excel selects for the report to make sure that your data table for the VLookup function was excluded. Place the pivot table on a new sheet. Change the tab on your pivot table sheet to have the label “Step B1.” (These labels are important; getting them wrong will cost you points.) 2. (8 points) Create a second pivot table that shows the total forecasts for each product line per month. Place this pivot table on a new sheet and label the tab “Step B2.” 3. (15 points) Create a third pivot table showing forecasts for each customer, subdivided by month and, within month, by product line. Place this pivot table on a new sheet and label the tab “Step B3.” 4. (9 points) Format the Step B3 sheet, considering the suggestions in the “Spreadsheet Formatting Tips” article from a previous unit. NOTE: If you have trouble figuring out what the Step B3 sheet should look like, you can obtain a sample showing row and column labels (without the detail data) by emailing me. This hint will cost you five points against your score on the assignment.

Assignment Part C (30 points): Charts
In this part of the homework, you will create the two charts Caitlin’s uncle requested.

1. (9 points) First, you need to create a 3D pie chart of the total forecast, split up by product line. Start by creating a pivot table summarizing the forecasts by product line; i.e., use product line as the row label. Then use that pivot table to create the pie chart. (Some versions of Excel have a “Pivot Chart” facility that lets you combine the pivot table and the chart into a single step.) Here is what it should look like before any format changes (colors may be different)

2. (6 points) Label the tab “Step C2.” Then modify the design, layout, and format as follows: a. Enlarge the chart so it’s easier to see the details. b. Add a descriptive chart title. c. Make sure you have a legend as shown in the example. rev. 2014-06-10

3

d. Add data labels showing the forecast amount for each product line. e. If the data labels do not show that the values are in dollars, fix this (the best way to do this is to go back to the pivot table and format the data values in dollars). f. Enlarge the font used for the data labels and legend so they are easier to read. 3. (9 points) Next, create a column chart summarized by product line and month. Again, it’s easiest to create a pivot table first, with the product lines as row labels and months as column labels. The table should NOT show sales rep or customer information. Then create a 3D column chart with one column for each month, and within the column, separate colors for each product line. You will need to look at the column chart options to figure out which one to use to get this result. Here is what it should look like before any format changes (the colors may be different):

HINT: If your chart comes out with four bars—one for each product line—and the months are in the legend, go to the “Design” tab under “Chart Tools” and click “Switch Row/Column.” 4. (6 points) Label the tab “Step C4.” Then modify the design, layout, and format of your chart as follows: a. Enlarge the chart so it’s easier to see the details. b. Add a descriptive chart title (you may have to reduce the size of the column chart to make room). c. Make sure you have a legend showing the months. d. If the vertical axis does not show that the values are in dollars, fix this (the best way to do this is to go back to the pivot table and format the data values in dollars). e. Enlarge the font used for the legend and axes so they are easier to see.

See the Homework folder (the “General Information and Instructions” item) to learn more about:  Working with a partner  Late penalties Submit your completed work on Blackboard as an Excel workbook. Keep a copy of the workbook handy until you receive your grade; sometimes problems arise when uploading files, and I might need you to submit it again.

rev. 2014-06-10

4

This assignment is worth 80 points, with points distributed as shown in the instructions. Points will be awarded as follows:    Full credit: Followed instructions and got the correct results. Partial credit: Tried to follow instructions but something went wrong. No credit: Did not follow the instructions, skipped the step, or hard coded results.

rev. 2014-06-10

5

### Similar Documents

#### Bae Denver Airport

...in Fall of 1993. This project was received with political and public support and promised to aid in preventing an economic free fall. The city used as many Denver area contractors and services as possible to keep their promise of increasing the economic benefits of the new airport. Due to construction beginning later that scheduled, the design-build approach was used, which led to a number of circumstances where partially constructed, or constructed areas had to be changed causing duplication of work, increased costs and delays. Due to lack of proper planning, and that the assumption that airlines would build their own baggage system, an airport wide baggage system was not included in the final plan for the airport. BAE, after already been contracted by United to build their baggage system, was approached and agreed to build an airport wide baggage system based on negotiated conditions. At this point the scheduling and construction of the project and baggage system was out of control. As BAE worked on building and installing the baggage system, the negotiated conditions were not met, leading to further delays. The city took action to filed a lawsuit against BAE penalizing them \$12,000 a day for not finishing the baggage system, possibly BAE’s reputation around the world. I recommend that BAE propose a plan for this point in the project to organize and schedule the remainder of the project to minimize delays and further cost overruns. BAE has expertise......

Words: 2198 - Pages: 9

#### Bae Automated System

...Summary BAE Automated systems was contracted by the United Airlines to build the baggage management for them at the Denver International Airport (DIA). The management team at DIA, looking at the response from other airlines towards the leasing of the own baggage systems, decided to build an integrated system for baggage management at DIA. The DIA approached BAE for the completion of this 195 million dollar project. After much deliberation on the project specifications and timelines, BAE decided to take up the project however they enforced many timelines and freeze dates to be strictly followed by the DIA. BAE highlighted the need for privileges such as unrestricted access and priority treatment which would be necessary for timely completion of the project. The project eventually ran into difficulties after the death of Slinger, the new point of contact between the BAE and the Denver government. Gail Edmond was not as autonomous as Slinger was and even though she tried her best to manage the situation. The issue snow balled into a situation where an external consultant was hired to assess the situation, from a German firm “Longplan”, and based on their recommendation the DIA decided to go ahead with the plan to construct a backup baggage management systems. They also decided to put the blame for the delay in the opening of the airport on BAE and issued a fine of \$12,000 per day from the promised date of delivery and also the whole amount of development of the backup system.......

Words: 1509 - Pages: 7

Free Essay

Words: 1837 - Pages: 8

#### Denvor International Case Study

...In the case of the Denver International Airport (DIA) project, dysfunctional decision making contributed to its demise. The airport's baggage handling system was a critical component in the plan and with its multiple failures ended up as a deal breaker for the project. By automating baggage handling, aircraft turnaround time was to be significantly reduced. Faster turnaround time meant more efficient operations and was a basis of the airports competitive advantage. Although there was good energy and enthusiasm surrounding the launch of the project, the project faced many complications and delays of over a year. The first critical factor that caused setback was when the project management team changed their strategy to include an integrated baggage handling system. Another critical factor that the project team allowed was the acceptance of change requests one the baggage project was underway. One final major factor that contributed to the failure was in the physical design plans. The project management had assumed that individual airlines were going to make their own baggage handling arrangements. This assumption in the initial project plan did not bode well for the team. Once into the project, they realized that other airlines were not coming forward to develop their own baggage systems, this was when the idea occurred to implement an integrated baggage-handling system. Without this initiative in play, the airport was more likely to have carried out its......

Words: 1829 - Pages: 8

#### Case Study

... Session Project: Case Study BAE Automated Systems DIA Baggage-Handling Systems Table of Contents Topic Pages Executive Summary 3 Introduction 4-5 Issue Identification and Root Case Analysis 6-8 Alternatives and Options 9 Recommendations 10 Implementation Plan 11 Monitor & Control 12-13 Exhibits 14 Executive Summary Denver International Airport(DIA) project started in November 1989 it was a very large project. Implementing the automated baggage handling in the middle of the project made it even more complex. Denver after being behind schedule for 18 months the Airport finally opened in February 1995. Once completed the airport had 5 runaways an 88 gates. The baggage handling system of the airport was initially built for United Airlines whom insisted on the system in the earlier planning stages. Denver officials agreed the automated baggage system will be a great addition to the airport; however not just for United Airlines concourse but all concourses in the airport. Boeing Airport Equipment(BAE) was contracted for building the system by officials in Denver long after the construction on the airport began. When the airport opened it had both the automated, a back-up for the automated system and conventional baggage handling system in place. Unfortunately, the project at Denver......

Words: 3154 - Pages: 13

#### Bae Case Deliverable

...BAE Case Deliverable 11/13/13 BAE’s reputation in implementing baggage-handling technology was already heralded as one of the best, which is why they were chosen for the project. However, it is evident before planning even began that BAE was both reluctant and wary of taking on much a larger and complex baggage implementation in Denver International Airport. Furthermore there was already increased pressure from the stakeholders to complete the airport itself which was also a work in progress. A combination of the pressure from time constraints, a lack of communication, and a series of errors in management and planning is what doomed this project from the beginning. With BAE undertaking such a huge venture, the company needed to have clearly defined the parameters of the project’s scope to its stakeholders and top management (mainly Mayor Webb and the city of Denver). Communication between BAE and the city of Denver was initially strong with both sides agreeing that BAE have unrestricted access and priority over areas of construction in DIA. BAE should be given credit for expressing its utmost caution and concern for the completion of the project and providing realistic milestones within a given time frame. But most projects never go according to planned. To look at what went wrong with BAE’s planning and design process, one must look at the ongoing project alongside the baggage implementation (the completion of DIA). The completion of DIA was extremely behind......

Words: 543 - Pages: 3

#### British American Airline

...BAE Automated Systems Executive Summary This report concerns the automated baggage handling system which was built by BAE Automated systems. Faced with the need for greater airport capacity, the city of Denver elected to construct a new state- of- the- art airport that would cement Denver’s position as an air transportation hub. Covering a land area of 140km-squared, the airport was to be the largest in the United States and have the capacity to handle more than 50 million passengers annually. A mechanized baggage system was at the heart of the new Denver Airport, as for all major new airports. In the case of Denver, this was to be something unique: the “integrated Automated Baggage Handling System”, originally designed to distribute all baggage, including transfers- automatically between check-in, the aircraft and pick-up on arrival. The airport’s baggage handling system was a critical component in the plan. By automating baggage handling, aircraft turnaround time was to be reduced to as little as 30 minutes. Faster turnaround meant more efficient operations and was a cornerstone of the airports competitive advantage. Despite the good intentions, the plan rapidly dissolved as underestimation of the project’s complexity resulted in problems and public humiliation for everyone involved. Thanks mainly to problems with the baggage system, the airport’s opening was delayed by a full 16 months. Expenditure to maintain the empty airport and interest charges on......

Words: 2625 - Pages: 11

#### Bae Airlines Summary

...Fonso is whether BAE should cancel, modify or continue with the contract for installing the baggage-handling system at the Denver International Airport (DIA) amidst the severe deterioration in communication and rising hostility. Furthermore, BAE must consider what additional resources would be required if the company chooses to negotiate with the City for the support to finish the system and whether the size and complexity of the system, given its departure from conventional technology, should be reduced. All the while, BAE is recognized worldwide for baggage-handling systems, and therefore the company’s decision must maintain BAE’s strong reputation in the industry and its shareholder value over the long-term. Environment and Root Causes Problems for BAE with regards to the implementation of a state-of-the art baggage handling system at DIA are a direct result from problems that originally stemmed from the DIA project. From the beginning, the DIA project was subjected to political and economic commotion. Original project stakeholders made commitments with regards to project completion and project budget, which in the future no longer became feasible. These forced new political stakeholders to commit, undesirably to a project plagued with increasing pressures while balancing administrative, social and political imperatives. Consequently a thorough and effective master plan never materialized. The DIA project was not well defined and organized. The project organization......

Words: 641 - Pages: 3

Free Essay

#### Bae Case

...BAE, a well-reputed organization in building automated baggage handling systems was awarded a contract to design and build an integrated baggage handling system for the DIA. Unfortunately, many issues arose throughout the process of building the baggage handling system and the schedule of opening DIA was delayed several times. In August 1994, the City of Denver notified BAE of a \$12,000/ day penalty for not finishing the baggage handling system by the original completion date, which was October 29, 1993. The City of Denver also demanded BAE pay for the \$50 million conventional tug-and-card baggage system. The delays were due to many reasons. When BAE started the project, they were faced with fully defined project scopes, which proved to be a major planning error. There were many project management issues. The communication channel was not managed well between the stakeholders. As well, the City of Denver regulations had its fair share in delaying the project. The political and economic situations played a role in the problem. The political involvement in the DIA project started since day one. There was too much pressure to fast track this project due to mayoral races. In the mid-1980s, there was a free fall in the economy. Officials in the region looked at the DIA project as a mean to increase the economic activity in the region. BAE has few options on dealing with this problem. The first option is to maintain the status quo. The second option is to cancel the project,......

Words: 321 - Pages: 2

Free Essay

#### Denver Case Study

...provided throughout the project. It helped me in better understanding about the subject and come up with a righteous work. Above all, I appreciate each and everyone who have supported me in any respect of completion of project.   Abstract Denver International airport (DIA) is situated 23 miles northeast of downtown Denver (Colorado) with a size of 137.8 sq. km . Currently the airport handles around 41 airlines and having traffic more than 3,905,138 passengers . During the construction of the airport, implementation of an automated integrated baggage system to minimize the turnaround time was a critical component . But it turned to be one of the woeful incidents in the history of technology project implementation. The airport scheduled to open in October 1993 finally came to live on March 2005 with a delay of 16 months due to under estimation of the baggage handling system. This delay almost cost them \$1.1M per day for the maintenance and loans. In March 1994, the BAE systems ran a test run in front of several media groups. The result of the test was a complete failure. The baggage were partially or completely damaged or misplaced to the wrong gate. The system was not able to cater the needs properly and thus United Airlines abandoned the system. The system was used in a small scale with an efficiency of 90% during the opening of DIA . This incident made clear that improper project management methodology and poor design approach can lead a project into a total......

Words: 1313 - Pages: 6

#### Denver International

...Case Study – Denver International Airport Baggage Handling System – An illustration of ineffectual decision making  Synopsis Dysfunctional decision-making is the poison that kills technology projects and the Denver Airport Baggage System project in the 1990’s is a classic example. Although several case studies have been written about the Denver project, the following paper re-examines the case by looking at the key decisions that set the project on the path to disaster and the forces behind those decisions. Background What was to be the world’s largest automated airport baggage handling system became a classic story in how technology projects can go wrong. Faced with the need for greater airport capacity, the city of Denver elected to construct a new state of the art airport that would cement Denver’s position as an air transportation hub. Covering a land area of 140 Km2, the airport was to be the largest in the United States and have the capacity to handle more than 50m passengers annually. The airport's baggage handling system was a critical component in the plan. By automating baggage handling, aircraft turnaround time was to be reduced to as little as 30 minutes. Faster turnaround meant more efficient operations and was a cornerstone of the airports competitive advantage. Despite the good intentions the plan rapidly dissolved as underestimation of the project’s complexity resulted in snowballing problems and public humiliation for everyone involved. Thanks mainly......

Words: 4958 - Pages: 20

Free Essay

#### Analysis of the Denver International Airport Baggage System

...Functionality Of Original BAE Design Problems and Solutions System Complexity Comparative Functionality Opening Delays Financial Hardship Summary Glossary References THE DENVER INTERNATIONAL AIRPORT AUTOMATED BAGGAGE HANDLING SYSTEM by Michael Schloh Computer Science Department School of Engineering California Polytechnic State University 1996 Date Submitted: February 16, 1996 Advisor: Daniel Stearns ABSTRACT This document discusses events at the new Denver International Airport that resulted in opening delays of the airport. The scope is limited to the automated baggage handling system, which was the primary source of failure warranting the airport's several opening delays. Analysis of the failing system is comprehensive. Research is conducted using a variety of sources. The final report is published on the worldwide web. CONTENTS Introduction 1 Reasons For Automation 2 Functionality Of Original BAE Design 3 Problems and Solutions 6 System Complexity 12 Comparative Functionality 14 Opening Delays 15 Financial Hardship 17 Summary 19 Glossary 20 References 21 INTRODUCTION This research concerns the automated baggage handling system which was built by BAE Automated Systems, Incorporated of Carrollton, Texas for the Denver International Airport. The analysis of this system provides an important topic of study. From the baggage system's failure, principles of computer systems were clarified and many lessons were learned or relearned by those involved in the BAE project. While......

Words: 12573 - Pages: 51

#### Case Analysis Denver International Airport

...DENVER INTERNATIONAL AIRPORT Summary of Findings 3 There has been considerable controversy documented through case study over the inadequacy of project and risk management structure associated with the Boeing Airport Equipment (BAE) automation design technology for a new baggage transport system at the Denver International Airport (DIA). Research background conducted by Kerzner (2011) under Case 23 revealed a documented tenyear history of this complex project with inherent risks. In another case study conducted by Montealegre, Keil, and Robinson (2000), a risk practice methodology was presented through “lessons learned” for improved implementation of the project’s risk management structure. Unfortunately, analogy comparisons for this type of project to incorporate past lessons learned did not exist prior to its inception since its technological innovation was breakthrough in design and concept. The city and DIA staff thought that they would be able to meet project budget and deadline, but in retrospect caused the project to be rushed beyond cost and schedule control due to inadequate risk processes. According to Kerzner (2011, p. 125), “better risk management leads to better decision-making” and is “perhaps the biggest skill needed for the twenty-first century” (p. 231). It is important to analyze project risk to improve project performance. Therefore as part of this case research and recommendations, an exploration of PMI’s six-stage risk process as outlined in the......

Words: 4143 - Pages: 17

Free Essay

#### Denver Airport Fiasco

...Denver Airport Fiasco Presentation Timeline I will start by finishing up the timeline. * In April 1994, the airport authorities organize a demonstration of the system for the media without informing BAE. It’s a disaster as clothes are ejected from crushed bags. As a result, the mayor of Denver is forced to delay the opening for the fourth time. * In May of the same year, BAE says the system is fine and that most of the problems are caused by the airport staff using the system incorrectly. The problems persist as testing continues so the airport builds a manual tug and trolley system instead. * In august, the City of Denver starts fining BAE 12 thousand dollars per day for further delays. * The airport finally opens on February 28th 1995 using part of the BAE system and a manual tug and trolley. * Finally, in 2005, the system fully scrapped for the manual one. Software Issues Next I will discuss the main software issues. * One of the system’s big problems was that carts were late to pick up bags, destabilizing the whole chain. This was, in part, due to the mathematical nightmare that represented the development of a line-balancing algorithm to feed carts at more than 100 pickup points. * It was also due to the fact that BAE’s codes expected perfect behavior from the system components. They didn’t allow for proper margins of error. The components were expected to perform better than they did. * Another major cause of......

Words: 754 - Pages: 4