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

#### Project

...Chapter 3 Planning the Project This chapter begins by discussing the nine key elements of the project plan. The following two sections address the planning process in greater detail with considerable emphasis placed on the project launch meeting and the hierarchical planning process by which parts of the plan are sequentially broken down into finer levels of detail. This provides a natural transition to the creation of the Work Breakdown Structure. Finally, the chapter is concluded with a discussion of multidisciplinary teams including the topics of integration management and concurrent engineering. Cases and Readings Some cases appropriate to the subject of this chapter are: Harvard: 9-692-058 Taco Bell Corp. This 31-page best-selling case describes a project to implement a new strategic plan involving major changes in layout, staffing, quality, product design, and information systems. The coordination and integration of all these project activities is what makes the plan successful, and hard for competitors to imitate. Two teaching notes are available for this case: 5-692-091 (20 pages) and 5-196-073 (12 pages). Harvard: 9-694-059 Hardcard Project: Plus Development Corp. (A1); 9-694-060 Hardcard Project: Plus Development Corp. (A2) This 10-page (12-pages for A2) case allows the students to evaluate, using Microsoft Project software, the time line, resource allocations, and other aspects of a major project concerned with developing a hard disk drive...

Words: 3660 - Pages: 15

#### Bae Case Study

...The baggage handling system is unique being integrated and automated. The BAE automated systems had enjoyed the reputation of being among the best and on the strength of its good work has been responsible for most of the major baggage systems recently installed in the United States. Weaknesses The baggage handling system has poor scheduling, new and untested technology, complexity of the system and changing requirements, highly visible mechanical problems, does not deliver productivity and efficiency, lower cost-effectiveness of the system. The Denver Airport did not open as scheduled; there were enormous costs on the part of the owners due to delays and high costs of maintaining the airport. On the management system, there were also a number of weaknesses: resignation of the DIA project head; death of the Chief Airport Engineer Walter Slinger who was the key player in the negotiations; communication was a problem from the beginning channels between (a) the city (b) the project management team (c) Consultants were never well defined; the city did not get airlines together to ask them what they wanted or needed to operate; the management team had no experience of baggage handling systems and treated it as being similar to pouring in concrete or fitting air-conditioning ducts; poor management relationships. BAE had to change its working structure to conform to DIA’s project management team structure. BAE felt restricted with the breaking over their agreement on......

Words: 655 - Pages: 3

#### Bae Black Money Scandal

...BAE Black Money Scandal “You have to learn the rules of the game. And then you have to play better than anyone else.” (Einstein, n.d.). It has been the better of two decades that one of the most prestige military arms manufacturers has armed United States troops. Sophisticated weaponry is the cornerstone that signifies a military’s strength and dominance. To meet the service and supply demands of today’s military, BAE System has made a commitment as the United States number one arms vendor. As chief distributors of arms, BAE have made customers on a global scale fulfilling the needs of militaries around the world. In the race for attracting customer dominance the better practices of business always have not been BAE’s top priority. Allegations that BAE were not using ethical business practices led to a case that inevitably unraveled BAE’s integrity. The United States lost confidence in BAE Systems by inconclusive allegations of the Al-Yamamah arms deal, bribery, and fraud. Al-Yamamah Arms Deal The Al-Yamamah arms deal can best be described as “you scratch my back and I will scratch yours” recorded arms sales. Behind the smoke screen there was an agenda brewing between two parties the United Kingdom and Saudi Arabia. The Al-Yamamah arms agreement was an arms agreement between the two countries. September 1985 Saudi Arabia paid for the commercial order of arms sales by means of 600,000 barrels of crude oil per day delivered to the United Kingdom. The delivery......

Words: 788 - Pages: 4

#### Project

...with any project team is a lack of good communication.This type issue could arrise where team members are scareterd all over the world without face to face communication.The communication through the internet, skype or some of the other communication technologies that are now available are sometimes not reliable and could cause miscommunication. what about one of the team members performing one of the tasks without informing the other team members about it. Overdetermining - project managers sometimes overstates their capabilities of comleting a project on time. That is when a project will take say two months to accomplish they use presure or lack of experience or presure to perform even the other factors that ignores reality for something that is not imporsimble to accomples in the time given. When everyone is not available at the same time to work as a team on the project this could cause a delay to finish and deliver the project on time. The trade offs that is to make a clear policy of communication from the begining of the project that nobody goes off course and that everyone have to work as a team and must be on the sameline with everyone on the team. The project head must make him or herself available at all time to make sure that all the policys are met by everyone in the group. Handster inc. 1. Which projects would you recommend Handstar pursue based on the NPV approach? The spreadsheet bellow summarizes the NPV calculations for the six projects......

Words: 518 - Pages: 3

#### 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

#### The Trophy Project

...Project Control Management Assignment Abstract Introduction Section 1: The Trophy Project: Project Controls 1.1 Project Planning and Estimating 1.2 Requirements Definition 1.3 Risk & Issue Management 1.4 Reporting (Cost & Status) 1.5 Integrated Planning Section 2: The Importance of Integrated Planning as a Project Control 2.1 Integrated Planning 2.2 Integrated Planning in BAE Systems Section 3: The Consideration of Human Factors at WCE 3.1 The WCE Strategic Change Programme 3.2 Why Change Programmes Fail Conclusion Abstract Wynn Computer Equipment – a reasonably successful and good business to work for (benefits & salary wise), had been suffering with issues in productivity. These issues were mainly derived from a combination of poor communication, bad planning, a confusing organisation structure and a lack of direction & team morale/ teamwork. The company received a contract for the ‘Trophy Project’ – which was project managed by a previously junior member of staff – Reichart. The project was extremely problematic and was delivered very late and at great cost, mostly due to the poorly executed project control mechanisms within the business (during planning and project implementation). The business could have greatly benefitted from using a more integrated approach to project planning and control- something which BAE Systems finds of great importance to their project management activities. Introduction...

Words: 4881 - Pages: 20

Free Essay

#### Project Failure

...Doyne Mc Nally Project Failure Essay Title: International Airport Baggage Handling System Why the project was established: Denver International Airport, often called DIA, is an airport in Denver, Colorado. By land size, at 140 km2, it is the largest international airport in the United States, and the third largest international airport in the world. Back in late 1980’s the city of Denver felt they needed a larger airport to deal with the increasing amount of air traffic through the city, so they elected to construct a highly efficient, fully automated airport. The predicted passenger handling was an outstanding 50 million annually. It was envisioned as a state of the art airport mainly due to its luggage handling system. This system of automated baggage-handling would greatly reduce aircraft turnaround time. Faster turnaround meant more efficient operations and was a cornerstone of the airports competitive advantage. Project Sponsors and Stakeholders: The Airport Project Management team on behalf of the City of Denver was the major stakeholder but funding was also received from United and Continental Airlines as they would eventually use terminals as major hubs for their respective organisations. The company BAE Automated systems was employed by DIA to design a conveyor system to meet the airports needs. Ultimately the majority of funding came from the pockets of the citizens of Colorado. The overall cost of the project was \$ Success Criteria: The airport's......

Words: 769 - Pages: 4

#### 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 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

#### Projects

...THE CONTRIBUTION OF PROJECT MANAGEMENT TO THE DEVELOPMENT OF GHANA INTRODUCTION Project management as a management discipline underpins much economic activity. In industries as diverse as pharmaceuticals, software and aerospace, projects drive business. And in the public sector, it is effective project management that translates politicians' promises of new roads, schools and hospitals into gleaming new constructions that improve everyday life. The development of Ghana Successive Ghanaian governments pursued various models of economic development throughout the 1960s, 70s and 80s. In the immediate post independent era of the early 1960s, Ghana’s economic development objectives were geared towards the ‘Big Push’ primary economic policies. These revolved around: a major investment effort; an industrialization drive; increased import substitution; processing of commodities and minerals previously exported in their raw form; and direct state participation in leading areas of the economy. The 1970s ushered in a policy of increased ‘Ghanaianisation’ or indigenisation with specified industrial activities reserved for state participation, Ghana-foreign ownership, and exclusive Ghana ownership, among others. The early 1980s saw the liberalization of the Ghanaian economy and the encouragement of foreign investments. All these approaches, however, fell far short of expectations and the failures were exacerbated by adverse terms of trade, falling commodity prices, and crippling debt......

Words: 1230 - Pages: 5

#### 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

#### Jumar Bae

...that are capable of being independently verified, while other forms of performance measures are subject to manipulation. C. The use of ROI may lead managers to refect capital investment projects that can be justified by using discounted cash flow models. D. The use of ROI can make it undesirable for a skillful manager to take on troubleshooting assignments such as those involving turning around unprofitable divisions. Answer (B) is correct. (CMA, adapted) REQUIRED: The false statement about ROI as a performance measurement, DISCUSSION: Return on Investment is the key performance measure in an investment center. ROI Is a rate computed by dividing a segment's income by the invested capital. ROI Is therefore subject to the numerous possible manipulations of the income and investment amounts. For example, a manager may choose not to invest In a project that will yield less than the desired rate of return, or (s)he may defer necessary expenses. Answer (A) is incorrect. ROI can be misleading when the quality of the investment base differs among segments. Answer (C) is incorrect Managers may reject projects that are profitable (a return greater than the cost of capital) but would decrease ROI, For example, the managers of a segment with a 15% ROI may not want to invest in a new project with a 10% ROI, even though the cost of capital might be only 8%. Answer (D) is incorrect. The use of ROI does not reflect the relative difficulty of tasks undertaken by......

Words: 9841 - Pages: 40

Free Essay

#### Haarp Project

...The High Frequency Active Auroral Research Program (HAARP) was an ionospheric research program jointly funded by the U.S. Air Force, the U.S. Navy, the University of Alaska[clarification needed], and the Defense Advanced Research Projects Agency (DARPA),[1] designed and built by BAE Advanced Technologies (BAEAT). Its purpose was to analyze the ionosphere and investigate the potential for developing ionospheric enhancement technology for radio communications and surveillance.[2] The HAARP program operated a major sub-arctic facility, named the HAARP Research Station, on an Air Force-owned site near Gakona, Alaska. The most prominent instrument at the HAARP Station is the Ionospheric Research Instrument (IRI), a high-power radio frequency transmitter facility operating in the high frequency (HF) band. The IRI is used to temporarily excite a limited area of the ionosphere. Other instruments, such as a VHF and a UHF radar, a fluxgate magnetometer, a digisonde (an ionospheric sounding device), and an induction magnetometer, were used to study the physical processes that occur in the excited region. Work on the HAARP Station began in 1993. The current working IRI was completed in 2007, and its prime contractor was BAE Systems Advanced Technologies.[1] As of 2008, HAARP had incurred around \$250 million in tax-funded construction and operating costs. It was reported to be temporarily shut down in May 2013, awaiting a change of contractors. In May 2014, it was announced that......

Words: 269 - Pages: 2

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