Microsoft Office Tutorials and References
In Depth Information
Case 12: The Brewery Index Calculation
attractive to investors who think the brewery industry will prosper in the coming years. The new fund would
also increase the exchange
s business in two ways. First, the fund manager would need to buy and sell stocks
of the breweries in the index. Second, the brewery index fund would be listed on the exchange, meaning that
investors would buy and sell the fund on the exchange.
Investors often try to predict the price changes of individual stocks. Investors who think that a stock
price will increase might buy more shares. Investors who think a stock
s price will decrease might sell shares.
Predictions are sometimes based on the earnings per share ratio (EPS), which is the ratio of a company
net income after taxes to the number of its shares outstanding. For example, if a company
s net income is
$10 million and it has 1 million shares outstanding, its EPS is 10. Most stock market theorists think that a
s stock price is related to its profitability. Thus, the ability to predict a company
s EPS should help
predict its stock price.
Some predictions are based on price earnings (PE), which is the ratio of the stock
s price on the
exchange to the company
s earnings per share. For example, if a company has an EPS of 10 and its stock
sells for $90 on the exchange, its PE ratio would be 9. The ability to predict the PE ratio for a company
stock is helpful in decisions to buy and sell stocks.
Of course, some investors would try to predict changes in the value of the index as a way of anticipating
changes in the value of a brewery index fund. Exchange managers wonder how strongly the value of the
brewery index correlates with brewery EPS and PE ratios.
You have been hired by the exchange to calculate an index for listed breweries, and to analyze the
correlation of the index to company EPS and PE ratios. An Access database file named Breweries.accdb has
been provided for your assistance; this file contains eight years of data for each brewery. To get the database,
go to your data files, select Case 12, and then select Breweries.accdb.
Figure 12-5 shows the first few records of the FinancialData table.
Brewery financial data
The following list describes the columns in the table.
The number of the record. The primary key field.
The name of the company.
The year number. Year 0 is the first year. Note that the data type is Text.
s net income after taxes in the year.
The number of shares outstanding during the year.
The share price of the company
s common stock on the exchange at the end of the year.
Note that there are 10 brewing companies. Figure 12-5 shows the first-year records for all companies,
and second-year records for two of the companies.