Planet Airways
An Integrated Case Study with Microsoft® Office
© Robert Grauer and Maryann Barber
The Planet Airways case study describes an independent airline that offers charter flights and special tours. The airline works with independent travel agents to book trips for the company on a commission basis. Planet currently has three aircraft. It has been profitable during its first two years of operation and is seeking venture capital to expand.
This document provides a series of exercises in Microsoft Office that relate to the Planet Airways case study. Each exercise describes a specific task the director has to accomplish and typically requires the use of multiple applications within Microsoft Office for solution. Many of the exercises are cumulative in nature. For example, the student is asked to create an Excel chart in one exercise, and then incorporate that chart into a Word memo and a PowerPoint presentation in subsequent exercises. A summary of the exercises is shown below:
1. Planet Revenue workbook and Chart (Excel)
2. Report to the President: (Word and Excel)
3. Presentation to the President (PowerPoint and Excel)
4. Last Minute Change (Word, Excel, and PowerPoint)
5. Importing Data (Access and Excel)
6. A Relational Database (Access)
7. Access Objects: Forms, Queries, and Reports (Access)
8. An Access Switchboard (Access)
9. Mail Merge (Word and Access)
10. Worksheet References (Excel)
11. Presentation to the Board (PowerPoint and Excel)
12. Letter to the Board (Word and Excel)
13. Submission Check List (Word)
All exercises are based on material from Exploring Office XP Volume I by Robert Grauer and Maryann Barber. Specific chapter references are deliberately not provided. The exercises also utilize a series of practice files that are available on our Web site at www.prenhall.com/grauer. This document may be freely duplicated in conjunction with any text in the Exploring Office series by Robert Grauer and Maryann Barber.
W / EP / A
1) Planet Revenue workbook and Chart (Excel): You will find a partially completed version of the spreadsheet in Figure 1 in the Planet Revenue workbook in the Planet Airways folder. (You can download the practice files for this case study from our Web site at www.prenhall.com/grauer.) Open the workbook and save it as Planet Revenue Solution so that you can return to the original workbook if necessary. Proceed as follows:
a) Click in cell F3 and enter the formula to compute the total revenue for the first plane, the Airbus A320. Copy this formula to the remaining cells in column F.
b) Click in cell B6 and compute the total revenue for the first quarter. Copy this formula to the remaining cells in this row.
c) Use the AutoFormat command as the basis for formatting your worksheet in an attractive fashion. You can match our formatting or you can use your own design.
d) Select the data in cells A2 through E5 to create a side-by-side column chart. Specify that the data series are in columns so that the different planes appear on the X-Axis. Save this chart in its own sheet. Print this chart for your instructor.
e) Create a second side-by-side column chart using the same data as in part (d). This time, however, specify that the data are in rows so that the quarters appear on the X-axis. Save this chart in its own chart sheet. You do not have to print this chart at this time.
f) Print the completed worksheet twice, once to show displayed values and once to show the cell formulas.
g) Save the completed workbook for use in subsequent exercises. Exit Excel.
Figure 1 – Planet Revenue Workbook and Chart
W / EP / A
2) Report to the President: (Word and Excel): This exercise builds on the previous exercise by creating a Word document that contains the Excel worksheet and a chart. The latter is to be dynamically linked to the Word document, so that any changes in the workbook are automatically reflected in the memo.
a) You will find a partially completed version of the document in Figure 2 in the Report to the President document in the Planet Airways folder. Open this document, and then save it as Report to the President Solution. Change the To and From lines in the memo to contain your instructor’s name and your name, respectively.
b) Open the completed workbook from the previous exercise if it is not already open. Click and drag to select the completed worksheet then click the Copy button on the Standard toolbar.
c) Click the Microsoft Word button on the Windows taskbar to return to the Word document. Click below the first paragraph. Pull down the Edit menu and click the Paste Special command to display the Paste Special dialog box. Select Microsoft Excel Worksheet Object in the displayed list, click the Paste Link button, and then click OK to insert the worksheet. Do not worry about the size or position at this time.
d) Use the same technique as in part (b) to link the side-by-side column chart (that displays quarters on the X-axis) to the Word document. The Excel chart should appear. The only tricky part (if any) is to display the entire chart (it may be cropped initially). Select the chart then click and drag a corner sizing handle to make the chart smaller. Right click the chart and click the command to display the Picture toolbar. Select the Crop tool, then click and drag the side handle at the right of the chart to show the portion of the chart that may have been cropped initially.
e) Move and size the chart within the memo as necessary. You may find it convenient to change the zoom specification to “Whole Page” so that you can position the chart more easily.
f) Save the completed document. Print the completed document for your instructor. Exit Word.
Figure 2 – Report to the President
W / EP / A
3) Presentation to the President (PowerPoint and Excel): A partially completed version of the presentation in Figure 3 has been saved as the Presentation to the President in the Planet Airways folder. Open this presentation and save it as Presentation to the President Solution so that you can return to the original presentation if necessary.
a) Insert a new slide that contains a mission statement as the second slide in the presentation. The mission statement should read as follows: The mission of Planet Airways is to provide courteous, on-time charter services to our customers in well-maintained aircraft flown by highly trained, professional flight crews.
b) Open the completed workbook from exercise one. Use the same technique as in the previous problem to link the worksheet and the side-by-side column chart (with the data in rows) to the appropriate slides in the presentation, as shown below. Move and/or size these objects after they have added to the presentation.
c) Add a textbox to the slide containing the worksheet that points to the total revenue in the worksheet. The text box should indicate that the total revenue is just under $15 million.
d) Include animation effects as you see fit within a slide and add transition effects from one slide to the next.
e) Add your name to the title slide. Print the audience handouts of the completed presentation (six per page) for your instructor.
f) Save the completed presentation. Exit PowerPoint. Close the Excel workbook and exit Excel.
Figure 3 – Presentation to the President
W / EP / A
4) Last Minute Change (Word, Excel, and PowerPoint): It was just discovered that the revenue from a few end-of-the year flights was not included in the original workbook. Your task is to correct all of the documents that reflect this information. Open the completed Planet Revenue Solution workbook from the exercise one.
a) Click in cell E3 and change the revenue for the Airbus A320 in the fourth quarter to $790,000. Click in cell E4 and change the value of this cell to $2,900,000. The row and column totals change automatically in the worksheet as do the associated charts. Save the workbook. Exit Excel.
b) Open the Report to the President Solution document from the second exercise. The worksheet and chart should be updated automatically to reflect the corrected revenue data. (The total revenue is now $15,110,000.) If either the worksheet or the chart is not updated, right click the object to display a context sensitive menu and click the Update Link command. If the chart is still not updated, right click the object, click the Linked Worksheet (or Chart) Object command, then click the Links command to display the Links dialog box where you can check the source (folder) of the linked objects.
c) Click at the beginning of the opening paragraph in the memo and enter the new text, which is shown in bold italics in Figure 4. (Be sure to change the sentence within the paragraph to indicate that revenue is over $15,000,000.) Save and print the completed report. Exit Word.
d) Start PowerPoint. Open the Presentation to the President Solution that you created earlier. PowerPoint detects that a change has been made in the underlying workbook and prompts you to update. Click the button to update the links. (You will have to change the contents of the text box to say that total revenue is just over $15,000,000.)
e) Change to the Slide Sorter view. Press and hold the Shift key as you select the slides containing the Excel worksheet and chart. Pull down the File menu, click the Print command, and print the selection (these two slides) as audience handouts, two slides per page.
f) Save the presentation. Exit PowerPoint
Figure 4 – Last Minute Change
W / EP / A
5) Importing Data (Access and Excel): Planet Airways flies all over the United States. Thus, it maintains bases in different cities to reduce the travel time for its flight crews. The Planet Airways database contains two tables, one for employees (flight crews), and one for the bases of the flight crews. The Employees table already exists within the Access database although data has not been entered for every employee. The base information, however, is in an Excel spreadsheet. Your first task is to import the Excel worksheet into the Access database.
a) Start Access. Open the Planet Airways database in the Planet Airways folder. Click the Tables button if necessary. Pull down the File menu, click (or point to) the Get External Data command, and then click the Import command to display the import dialog box.
b) Click the down arrow on the Look in list box and change to the Planet Airways folder (the same folder that contains the database). Change the file type to Microsoft Excel. Select the Bases workbook to start the Import Spreadsheet Wizard.
c) Check the box that indicates the first row contains column headings as shown in Figure 5. Click Next. Select the option button to store the data in a new table. Click Next.
d) You do not need information about the individual fields. Click Next.
e) Select the option to choose your own primary key. Click the drop-down arrow on the list box and select BaseID. Click Next. Access indicates that it will import the data into a Bases table.
f) Click the Finish button, then click OK when the Wizard indicates that it has imported the table. The Bases table appears in the Database window.
g) Click the Forms button in the Database window and open the Bases form. (This form was previously created using the control names in the Bases table that was just imported.) Locate the record for Miami (record 3) and change the supervisor’s name to your instructor’s name. Print the completed form with your instructor’s data.
Figure 5 – Importing Data
W / EP / A
6) A Relational Database (Access): The Planet Airways database contains a one-to-many relationship between the Bases table and the Employees table as shown in Figure 6. One base has many employees, but a specific employee is assigned to only one base.
a) Pull down the Tools menu and create the relationships diagram that corresponds to the report in Figure 6. Check the box to enforce referential integrity when you create the relationship.
b) Pull down the File menu and click the Print Relationships command to create a report containing the relationships diagram. Change to Design view, and then modify the report to match Figure 6. Print the completed report for your instructor. Close the report and return to the Database window.
c) Click the Query button in the Database window, and then create a new query in Design view. The query is to contain fields from both tables as shown below:
i) Select the LastName, FirstName, Position, Address, City, State, ZipCode, and BaseID fields from the Employees table.
ii) Select the Office Supervisor and Phone fields from the Bases table.
iii) Display the employees in alphabetical order by last name.
iv) Save the query as Employee Information. You do not have to print the query at this time.
d) Create a report that groups employees by position, and displays the employees alphabetically within each position. The report should include the employee’s first and last name, base, supervisor, and supervisor’s phone number and is to be based on the Employee Information query that you just created. Print this report for your instructor.
Figure 6 – A Relational Database