HOMEWORK ASSIGNMENT
Problem Solving
Fortune 500 Companies Problem
Background Information
The Fortune 500 listing has been published since 1955 to analyze
the top five hundred companies in the United States. However,
these companies grow, merge, change, and dissolve on a yearly
basis.
Historically, have the same industries and companies remained at
the top, or does this change frequently? What companies and
industries have averaged the highest profits and revenue? We will
examine the different industries and the companies within these
industries to get an indication of what companies have tended to continue doing
well throughout the list’s history.
Problem Statement
In this assignment, we will analyze profits and revenue for companies and
industries listed historically on the Fortune 500 list. From this data, we will chart
the profit trends for the top three companies and theorize on future growth.
Project Instructions
IMPORTANT: Complete the below steps in the order they are given. Completing the
steps out of order may complicate the project or result in an incorrect result.
1.
Download the following files onto your computer:
a.
b.
industries.xml – List of industries.
Table: Industries
Field Name
IndustryID
Type
AutoNumber
IndustryName
Short Text
Description
Primary key. Unique identifier for each
industry.
Industry name.
cpifactors.xml – Listing of Consumer Price Index adjustment factors.
Table: CPIFactors
Field Name
Type
DataYear
Number
CPIFactor
Number
Introduction to Computer Applications
West Virginia University
Description
Primary key. Year of CPI adjustment factor.
CPI adjustment factor for 2011-valued dollars.
Page 1 of 10
Version 13.10
Modified 5/19/2015
HOMEWORK ASSIGNMENT
Problem Solving
Fortune 500 Companies Problem
c.
companies.xml – Listing of companies appearing on the Fortune 500
list.
Table: Companies
Field Name
Type
CompanyID
AutoNumber
CompanyName
IndustryID
d.
Short Text
Number
Description
Primary key. Unique identifier for each
company.
Company name.
Industry categorization for each company.
rankings.xml – Data on company rankings from 1955 to 2011.
Table: Rankings
Field Name
DataYear
Type
Number
Ranking
Number
CompanyID
Number
Revenue
Currency
Profit
Currency
Description
Part of composite key. Year of ranking on
Fortune 500 list.
Part of composite key. Yearly ranking for
company on the Fortune 500 list.
Part of composite key. Numeric value
representing company name.
Amount of revenue generated by the company
in the previous year.
Amount of profit generated by the company in
the previous year.
e.
commercialbanking.txt – Report text file to format in Word.
f.
bank.png – Image of a bank building for use in PowerPoint.
Microsoft Access Portion
2.
Begin by creating a new Microsoft Access database named
lastname_firstname_ffhcp.accdb .
3.
Import the structure and data of the tables from industries.xml,
cpifactors.xml, companies.xml, and rankings.xml into your new database.
4.
Create the appropriate relationships for the following tables. Enforce
referential integrity, but do not enable cascade updates or cascade deletes.
a.
Rankings and Companies
b.
CPIFactors and Rankings
c.
Industries and Companies
Introduction to Computer Applications
West Virginia University
Page 2 of 10
Version 13.10
Modified 5/19/2015
HOMEWORK ASSIGNMENT
Problem Solving
Fortune 500 Companies Problem
5.
Create separate queries to provide the information requested below. Name
each query after the step in which it appears (e.g., name the query in Step 5a
as Query5A ).
HINT: Run your queries to test them. Make sure that they display all and only
the records that you would expect to appear.
a.
Create a new query that lists each company’s name, its industry name,
and each year where it appeared on the Fortune 500 list. Include two
calculated fields, one to find the yearly revenue in inflation-adjusted 2011
dollars and another to find the yearly profit in inflation-adjusted 2011
dollars.
You can calculate the yearly revenue in inflation-adjusted 2011 dollars
using the formula:
[𝑅𝑎𝑛𝑘𝑖𝑛𝑔𝑠. 𝑅𝑒𝑣𝑒𝑛𝑢𝑒] ∗ [𝐶𝑃𝐼𝐹𝑎𝑐𝑡𝑜𝑟𝑠. 𝐶𝑃𝐼𝐹𝑎𝑐𝑡𝑜𝑟]
You can calculate the yearly profit in inflation-adjusted 2011 dollars using
the formula:
[𝑅𝑎𝑛𝑘𝑖𝑛𝑔𝑠. 𝑃𝑟𝑜𝑓𝑖𝑡] ∗ [𝐶𝑃𝐼𝐹𝑎𝑐𝑡𝑜𝑟𝑠. 𝐶𝑃𝐼𝐹𝑎𝑐𝑡𝑜𝑟]
Format both calculated fields as currency with no decimal places. Sort by
company name and then by year, both in ascending order.
HINT: This query will show 28,500 records and 5 fields.
b.
Make a new query. For each year, calculate the total revenue, total
profit, average revenue, and average profit for the top 10 companies.
Only include records for companies ranked #1 through #10 in their
respective years. Do not show the ranking field in the result.
Format all four calculated fields as currency with no decimal places. Sort
by year in ascending order.
HINT: For the ranking field, change the Total option from Group By to
Where.
HINT: This query will show 57 records and 5 fields.
Introduction to Computer Applications
West Virginia University
Page 3 of 10
Version 13.10
Modified 5/19/2015
HOMEWORK ASSIGNMENT
Problem Solving
Fortune 500 Companies Problem
c.
We want to know which industries annually average more than $1 billion
in revenue and also more than $1 billion in profit. For this query, copyand-paste the below SQL code:
SELECT Industries.IndustryName, Avg(Rankings.Revenue) AS
AverageRevenue, Avg(Rankings.Profit) AS AverageProfit,
Count(Rankings.Ranking) AS RankedCompanies
FROM Industries INNER JOIN (Companies INNER JOIN Rankings ON
Companies.CompanyID = Rankings.CompanyID) ON
Industries.IndustryID = Companies.IndustryID
GROUP BY Industries.IndustryName
HAVING (((Avg(Rankings.Revenue))>1000000000) AND
((Avg(Rankings.Profit))>1000000000))
ORDER BY Avg(Rankings.Profit);
IMPORTANT: Do not make any modifications to this query other than
entering the above SQL code.
HINT: This query will show 5 records and 4 fields.
d.
We would like to gather information on three major banking companies:
Bank of America Corp., Citigroup, and FleetBoston Financial. For this
query, copy-and-paste the below SQL code:
SELECT Companies.CompanyName, Industries.IndustryName,
Rankings.DataYear, CCur([Revenue]*[CPIFactor]) AS
AdjustedRevenue, CCur([Profit]*[CPIFactor]) AS AdjustedProfit
FROM Industries INNER JOIN (CPIFactors INNER JOIN (Companies
INNER JOIN Rankings ON Companies.CompanyID =
Rankings.CompanyID) ON CPIFactors.DataYear =
Rankings.DataYear) ON Industries.IndustryID =
Companies.IndustryID
WHERE (((Companies.CompanyName)="Bank of America Corp." Or
(Companies.CompanyName)="Citigroup" Or
(Companies.CompanyName)="FleetBoston Financial"))
ORDER BY Companies.CompanyName, Rankings.DataYear;
IMPORTANT: Do not make any modifications to this query other than
entering the above SQL code.
HINT: This query will show 44 records and 5 fields.
6.
Run the Compact and Repair Database utility on your database.
Microsoft Excel Portion
7.
Create a new Microsoft Excel workbook named
lastname_firstname_ffhcp.xlsx .
Introduction to Computer Applications
West Virginia University
Page 4 of 10
Version 13.10
Modified 5/19/2015
HOMEWORK ASSIGNMENT
Problem Solving
Fortune 500 Companies Problem
8.
9.
We must adjust the sheets in our workbook.
a.
Rename Sheet1 to Most Profit by Industry .
b.
Add a new sheet named Commercial Bank Revenue .
We need to import the results of the Microsoft Access queries into the new
Microsoft Excel workbook.
a.
We wish to import the results of Query5C into the Most Profit by Industry
sheet. This query returns information on the industries that generated an
annual average of at least $1 billion in revenue and $1 billion in profit.
Using the DATA ribbon, import the data from Query5C of your Microsoft
Access database and place it starting in cell A1. View the data as a table.
b.
We want to import the results of Query5D into the Commercial Bank
Revenue sheet. This query returns Fortune 500 rankings for three major
commercial banks.
Using the DATA ribbon, import the data from Query5D of your Microsoft
Access database and place it starting in cell A1. View the data as a table.
10. We would like to create a chart to display information on the most profitable
industries.
Create a new 2-D clustered column chart based on cells A1 through C6 of the
Most Profit by Industry sheet. Move the chart to a new sheet named Most
Profit by Industry Chart .
Specify appropriate chart and axis titles. Format the value axis as currency
with no decimal places.
11. To better understand the data, we wish to create a PivotChart with an
associated PivotTable.
a.
Create a new PivotChart and PivotTable based on the data in cells A1
through E45 of the Commercial Bank Revenue sheet. Place the PivotTable
on a new sheet named Bank Revenue PivotTable and move the PivotChart
to a new sheet named Bank Revenue PivotChart.
b.
On the PivotTable, do the following:
i.
Add the year as a Rows field.
ii.
Add the company name as a Columns field.
iii.
Add the inflation-adjusted revenue as a Values field.
Introduction to Computer Applications
West Virginia University
Page 5 of 10
Version 13.10
Modified 5/19/2015
HOMEWORK ASSIGNMENT
Problem Solving
Fortune 500 Companies Problem
c.
We need to perform some formatting on the PivotTable.
i.
Format the cells as indicated below:
(1) Bank of America Corp., Citigroup, FleetBoston Financial,
and Grand Total columns: currency with no decimal places
d.
We also need to perform some formatting on the PivotChart.
i.
Change the chart type to a 2-D line chart.
ii.
Format the value axis as currency with no decimal places.
iii.
Specify appropriate chart and axis titles.
Microsoft Word Portion
12. Create a new Microsoft Word document named
lastname_firstname_ffhcp.docx .
13. Insert a cover page using the built-in Sideline style. On the cover page,
include the information noted below.
a.
Company: The words Computer Science 101 Section followed by the
section number in which you are enrolled.
b.
Title: Fortune 500 and Commercial Banking
c.
Subtitle: Definitions and Examples
d.
Author: Your first and last name.
e.
Date: The current date.
14. Insert a second page if one does not already exist. On it, copy-and-paste the
contents of the file commercialbanking.txt.
NOTE: The imported text file contains directions and comments (inside of “<>”
symbols) for use in formatting the document. You should remove these
directions and comments once you have applied the formatting specified.
15. Where indicated, include the chart from the Bank Revenue PivotChart sheet in
your Microsoft Excel workbook. Size the chart to be 4-inches high by 6.5inches wide. Be sure to include a caption for the chart.
16. Where indicated, insert a new equation using the Microsoft Word equation tool
representing the formula below. Your formula must identically reproduce:
[𝑃𝑟𝑜𝑓𝑖𝑡]
[𝐶𝑃𝐼𝐹𝑎𝑐𝑡𝑜𝑟]
Introduction to Computer Applications
West Virginia University
Page 6 of 10
Version 13.10
Modified 5/19/2015
HOMEWORK ASSIGNMENT
Problem Solving
Fortune 500 Companies Problem
17. At the end of the document, we wish to create a bibliography page.
a.
Define the following sources as references for your report.
i.
ii.
iii.
iv.
b.
Type: Web site
◊
Name of web page: Bank of America Homepage
◊
Name of web site: Bank of America
◊
Year: 2008
◊
Date accessed: December 18, 2008
◊
URL: http://www.bankofamerica.com
Type: Web site
◊
Name of web page: Citigroup Homepage
◊
Name of web site: Citigroup
◊
Year: 2008
◊
Date accessed: December 18, 2008
◊
URL: http://www.citigroup.com
Type: Web site
◊
Name of web page: JPMorgan Chase Home Page
◊
Name of web site: JPMorgan Chase
◊
Date: December 18, 2008
◊
Date accessed: December 18, 2008
◊
URL: http://www.jpmorganchase.com
Type: Article in a periodical
◊
Title: 2008 Fortune 500: Wal-Mart’s No. 1
◊
Periodical title: Fortune
◊
Date: May 5, 2008
Where indicated on the last page, insert a Bibliography-style bibliography
using an APA style.
18. We must finish formatting the document.
a.
Apply the formatting and changes to your report text as specified by the
included comments.
Remove the included comments from your document once you have
applied the required formatting.
Introduction to Computer Applications
West Virginia University
Page 7 of 10
Version 13.10
Modified 5/19/2015
HOMEWORK ASSIGNMENT
Problem Solving
Fortune 500 Companies Problem
b.
Ensure the document conforms to the following requirements:
i.
Modify the Normal formatting style to specify Verdana 11-point as
the default font.
ii.
In the right-hand corner of the header on all pages after the cover
page, list your last name followed by the page number. The page
number should begin at 1 on the first actual page of text (the page
after the cover page).
iii.
The first line of your text paragraphs should be indented by 0.5inch. Do not indent the cover page, headings, images, captions,
equations, or bibliography.
iv.
Except on the cover page, use double line spacing.
v.
Ensure that there is no (0-point) line spacing before and after each
paragraph except on the cover page.
vi.
Use 1.0-inch margins on all sides.
vii.
Delete any duplicate line breaks.
NOTE: This includes spaces between paragraphs and spaces
between or after headings.
Microsoft PowerPoint Portion
19. Create a new Microsoft PowerPoint presentation named
lastname_firstname_ffhcp.pptx .
20. In the presentation, create the following slides:
a.
Title slide listing a title for the presentation, your full name, course
section, and an automatically-updating date.
b.
Slide titled Most Profit by Industry . Include the chart from the Most
Profit by Industry sheet in your Microsoft Excel workbook.
21. Create slides to answer four of the five below analysis questions, one question
per slide. Title the slide with the name of the question being answered (e.g.,
"Question A").
a.
Even after adjusting for inflation, the total revenue of Fortune 500
companies has had over a 10-fold increase from 1955 to 2011. What
might this indicate about the companies on the list?
b.
The #29 company on 2003’s Fortune 500 list, Time Warner, lost $98.7
billion on $41.8 billion of revenue the previous year. Why didn’t Time
Warner have to file for bankruptcy with such a huge loss?
c.
Looking through the companies on the Fortune 500 lists, name at least
one industry that you think would be a good investment and one that you
think would be a bad investment. Explain your rationale.
Introduction to Computer Applications
West Virginia University
Page 8 of 10
Version 13.10
Modified 5/19/2015
HOMEWORK ASSIGNMENT
Problem Solving
Fortune 500 Companies Problem
d.
When profits and revenue are adjusted for inflation, the top four
industries all involve finances in some way. Why might this be the case?
e.
The Fortune 500 listings only include publicly-traded companies. Why
aren’t privately-owned companies listed?
22. We wish to apply formatting to the presentation. Use your best judgment to
create a professional-looking presentation.
a.
Apply one slide design of your choice to all slides.
b.
Apply an animation of your choice to the Most Profit by Industry chart.
c.
Apply slide transitions to all of the slides.
d.
Edit the parent (top-most) slide master to add the bank.png image to
the bottom-left corner of the slides. Size the image to be 0.5-inches high
by 0.53-inches wide.
NOTE: Depending on the slide design used, the image may not appear on
title slides. This is fine as long as the image is correctly added to the slide
master.
e.
Add your name, an automatically-updating date, and the slide number to
the footer of all slides except the title slide.
Project Requirements
You must submit all four files (Microsoft Access, Microsoft Excel, Microsoft
PowerPoint, and Microsoft Word) for this assignment.
Curriculum Information
Project Type
Microsoft
Microsoft
Microsoft
Microsoft
Access database
Excel spreadsheet
PowerPoint presentation
Word document
Relationship to GEC Objective 2
In this assignment, students perform data management and analysis on real-world
statistics related to the economy. They explore the data to find explanations for
the historical data and to understand the possible future impact of existing trends.
Introduction to Computer Applications
West Virginia University
Page 9 of 10
Version 13.10
Modified 5/19/2015
HOMEWORK ASSIGNMENT
Problem Solving
Fortune 500 Companies Problem
Relationship to GEC Objective 4
For over 50 years, the Fortune 500 listing has served as an important barometer of
the American economy. By reviewing its listings, we can gain important insights to
how the economy and business world has evolved over the past half-century.
Grading Rubric
This project is worth 50 points and will be graded based upon the following
components. The instructor may adjust the below values as he or she feels
appropriate:
Step 3
Steps 4a-c
Steps 5a-b
Steps 5c-d
Steps 8a-b
Steps 9a-b
Step 10
Steps 11a-d
Steps 13a-e
2 points
1.5 points total
2.5 points each
1 points each
1 points total
1.5 points total
3 points
5 points total
2.5 points total
Step 14
Step 15
Step 16
Steps 17a-b
Steps 18a-b
Steps 20a-b
Steps 21a-e (pick 4 of 5)
Steps 22a-e
2.5 points
1.5 points
1.5 points
2.5 points total
3.5 points total
2.5 points total
2.5 points each
2.5 points total
Acknowledgments
The image in the introduction appears courtesy of Fortune magazine [1].
References
[1] Fortune 500 magazine cover. 2004. Available:
http://i.cnn.net/money/.element/img/1.0/sections/mag/fortune/global500/fortu
ne_g500_cover06.jpg.
Introduction to Computer Applications
West Virginia University
Page 10 of 10
Version 13.10
Modified 5/19/2015