spread sheet week 4

1

WEEK # 4 - EXERCISES CHAPTER # 3

Exercise Ch3-1: Level 1 – Illustrating Travel Data for the Florida Department of Tourism

As a travel industry consultant, you have been working for the state of Florida Department of Tourism. The department manager has asked you to present a report on where Florida residents travel to and where Florida visitors come from. The modes of transportation must also be presented. You will also include some information from a survey that your firm has recently completed. The travel survey is located in the workbook named Florida.xlsx. Complete the following: 1. Open the workbook named Florida-1.xlsx attached with the data of the assignment, and then save the file as 3-1-Florida-Travel-YourName.xlsx. 2. Create two bar charts to illustrate the differences in Trips per Traveler and Miles per Traveler of the average Florida resident, the average U.S. resident, and the average Florida visitor.

(Hint: Create the charts for Florida and Florida Visitors first, and then add the data for United States using the Select Data Source dialog box. (Hint: Delete the “= {1}” value in the Series values box before selecting the United States data.)

3. Create a bar chart to illustrate a state-by-state comparison of the number of residents from the rest of the nation who visit Florida, and the number of Florida residents who visit other states. Rank the information in this chart by the states with the most visitors to Florida. 4. The Florida Department of Tourism is very interested in learning more about how people travel in Florida. They want to use this information to better target advertising to specific demographic groups and methods of travel. Use the appropriate chart or series of charts to illustrate:

a) How people travel b) The purpose of their travel c) Income levels, age, and gender.

This information should be presented for people who live within the state and those visiting the state with a comparison between the two groups. 5. Add your name and date at the end of the exercise 6. Save and close the 3-1-Florida-Travel-YourName.xlsx workbook.

2

WEEK # 4 - EXERCISES CHAPTER # 3

Exercise Ch3-2: Level 2- Analyzing Stock Performance for Universal

Investments. As an analyst at the Universal Investments financial company, you regularly monitor the performance of your clients’ investments. You are preparing for a meeting with one of your investors to discuss how their stocks have performed over the last year and changes they should make to their portfolio. Your client has asked for information on four companies: Intel, AMD, Time Warner, and Wal-Mart. You will find daily stock price and volume information contained in the Invest.xlsx workbook. Complete the following: 1. Open the workbook named Invest-1.xlsx attached with the data of the assignment, and then save the file as 3-2-Investment-Performance-YourName.xlsx 2. Create a chart for each company using the appropriate stock chart sub-type. Be certain to use each chart sub-type only once. 3. Place each chart on a new sheet in the workbook. 4. On the Stock Comparison worksheet use the appropriate chart to compare the change in stock price for each company over the last year. 5. Using the information on the Portfolio worksheet, prepare the appropriate chart to display the relative contribution of each stock to the value of the portfolio. 6. Create an appropriate Combo chart on the Intel Summary worksheet that shows the daily closing price and volume data. Add a linear and a 30 day moving average trend line to the chart. 7. Add your name and date at the end of the exercise 8. Save and close the 3-2-Investment-Performance-YourName.xlsx workbook.