Figure 1: Current Portfolio Worksheet BJR Investments, Inc. Portolio Warksheet (Current Date and Time] The total cost, portfolio percentage, current share value, return, commission, and P/E ratio are computed, thus requiring formulas. Figure 2 provides formulas for computing the total cost, portfolio percentage, current value, P/E ratio, and return. You ask Mr. Reynolds to explain how to derive the commission. Mr. Reynolds explains that he charges either 2 percent of the purchase price or a $25 flat fee, whichever is greater. Based on this explanation, you decide that the commission cells must determine what fee to charge. The determination of what commission to charge requires the use of the IF function. Although the P/E ratio is retrievable from the Web, Mr. Reynolds wants this value computed based on data contained in the worksheet. The P/E ratio computation is easy to make, because a stock's P/E ratio is determined by dividing the stock's last price by the stock's EPS. Figure 1 includes a capitalization level column. As Mr. Reynolds explains, this column specifies whether the stock's market capitalization is large-cap, mid-cap, small-cap, or micro-cap. Figure 3 shows the capitalization classifications. The IF function can be used to determine the company's capitalization level. As you study the requirements for the new worksheet, you realize that the capitalization, last price, previous close, and EPS ratio require current stock market information. Luckily, you recall that the MSNStockQuote function makes retrieval of this information easy. When the MSNStockQuote function is refreshed, it returns the latest available stock market information to the cell. You decide to use the MSNStockQuote function to retrieve the company name, capitalization, last price, previous close, and EPS values. Once you obtain the most recent stock market information, you can use formulas to calculate the current value and return. (In addition to the MSNStockQuote function, the MSN MoneyCentral Investor Stock Quotes Web query retrieves current stock information. Your instructor will specify which feature to use. At this point, you may wish to use your system's online help function to review Web queries.) Mr. Reynolds requests the averages, minimums, and maximums for the commission, purchase price, current share value, return, and P/E ratio columns. Mr. Reynolds wants the minimums and maximums for the portfolio percentages, previous close, and EPS columns. As he will work with this data as a table, he requests that you use database functions, where applicable, to determine the values for these columns. Mr. Reynolds will analyze the stock data at varying levels of detail. For instance, he wants to see the return by industry, and he wants a chart comparing the return by industry. Next, he wants to see the return, P/E, and EPS for each stock. He wants this information categorized by industry, and he does not wish to see grand totals. You suggest that he use the PivotTable and PivotChart tools for this purpose. In addition to the information requirements specified above, Mr. Reynolds requests that you perform the following operations. Where appropriate, you should use the Advanced Filter, Filter, and PivotTable tools. 1. Identify the most expensive stock. 2. Identify which stock has the highest return. Lowest return. CASE 9: BJR Investments, Inc. 3. Identify which stock has the largest P/E ratio. Lowest P/E ratio. 4. Identify which stock had the highest previous close. Lowest. 5. Identify which trades the client pays a $25 flat fee. Identify which trades the client pays a 2 percent commission. 6. Identify which stocks have a purchase price greater than $50 and a negative return. 7. Identify which stocks have a purchase price less than $50 and a positive return. 8. Identify the purchase price, last price and previous close for each stock. Mr. Reynolds wants to view this information on a separate "page" for each industry. 9. Identify which stocks are traded on NASDAQ. Identify which stocks are traded on NYSE. Figure 1: Current Portfolio Worksheet BJR Investments, Inc. Portolio Warksheet (Current Date and Time] The total cost, portfolio percentage, current share value, return, commission, and P/E ratio are computed, thus requiring formulas. Figure 2 provides formulas for computing the total cost, portfolio percentage, current value, P/E ratio, and return. You ask Mr. Reynolds to explain how to derive the commission. Mr. Reynolds explains that he charges either 2 percent of the purchase price or a $25 flat fee, whichever is greater. Based on this explanation, you decide that the commission cells must determine what fee to charge. The determination of what commission to charge requires the use of the IF function. Although the P/E ratio is retrievable from the Web, Mr. Reynolds wants this value computed based on data contained in the worksheet. The P/E ratio computation is easy to make, because a stock's P/E ratio is determined by dividing the stock's last price by the stock's EPS. Figure 1 includes a capitalization level column. As Mr. Reynolds explains, this column specifies whether the stock's market capitalization is large-cap, mid-cap, small-cap, or micro-cap. Figure 3 shows the capitalization classifications. The IF function can be used to determine the company's capitalization level. As you study the requirements for the new worksheet, you realize that the capitalization, last price, previous close, and EPS ratio require current stock market information. Luckily, you recall that the MSNStockQuote function makes retrieval of this information easy. When the MSNStockQuote function is refreshed, it returns the latest available stock market information to the cell. You decide to use the MSNStockQuote function to retrieve the company name, capitalization, last price, previous close, and EPS values. Once you obtain the most recent stock market information, you can use formulas to calculate the current value and return. (In addition to the MSNStockQuote function, the MSN MoneyCentral Investor Stock Quotes Web query retrieves current stock information. Your instructor will specify which feature to use. At this point, you may wish to use your system's online help function to review Web queries.) Mr. Reynolds requests the averages, minimums, and maximums for the commission, purchase price, current share value, return, and P/E ratio columns. Mr. Reynolds wants the minimums and maximums for the portfolio percentages, previous close, and EPS columns. As he will work with this data as a table, he requests that you use database functions, where applicable, to determine the values for these columns. Mr. Reynolds will analyze the stock data at varying levels of detail. For instance, he wants to see the return by industry, and he wants a chart comparing the return by industry. Next, he wants to see the return, P/E, and EPS for each stock. He wants this information categorized by industry, and he does not wish to see grand totals. You suggest that he use the PivotTable and PivotChart tools for this purpose. In addition to the information requirements specified above, Mr. Reynolds requests that you perform the following operations. Where appropriate, you should use the Advanced Filter, Filter, and PivotTable tools. 1. Identify the most expensive stock. 2. Identify which stock has the highest return. Lowest return. CASE 9: BJR Investments, Inc. 3. Identify which stock has the largest P/E ratio. Lowest P/E ratio. 4. Identify which stock had the highest previous close. Lowest. 5. Identify which trades the client pays a $25 flat fee. Identify which trades the client pays a 2 percent commission. 6. Identify which stocks have a purchase price greater than $50 and a negative return. 7. Identify which stocks have a purchase price less than $50 and a positive return. 8. Identify the purchase price, last price and previous close for each stock. Mr. Reynolds wants to view this information on a separate "page" for each industry. 9. Identify which stocks are traded on NASDAQ. Identify which stocks are traded on NYSE