Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 2: USING EXCEL FOR DECISION SUPPORT
ASSIGNMENT 2: USING EXCEL FOR DECISION SUPPORT
In this assignment, you will import your two Access queries into Excel worksheets and then develop
information needed to answer league officials
questions about offensive performance.
Importing Queries
Open a new file in Excel and save it as BaseballAnalysis.xlsx. Then import the ThisYearsData query output
into Excel. To import the data, click the Data tab, select Get External Data, and then select From Access.
Specify the Access filename, the query name, and where to place the data in Excel (cell A1 is recommended).
Rename the worksheet ThisYear.
The data will come into Excel as an Excel data table. Select Total Row in the Table Style Options group
to add a Totals row to the bottom of the data table. You will probably need to format the ratio columns for
three decimal places. The first few rows of the ThisYearsData query output should look like the data shown in
Figure 11-5.
FIGURE 11-5
ThisYearsData query output imported
Repeat the preceding instructions to import the LastYearsData query output using a separate worksheet.
Name the worksheet LastYear.
Using Data Tables and Pivot Tables to Gather Data
Now use the data tables and pivot tables to gather data needed to answer league officials
questions. (Consult
Tutorial E if you need help using data tables and pivot tables.)
League officials want to know if offensive performance decreased this season compared with last season.
You can answer this question using data table analysis. Also, league officials have the following additional
questions:
￿
Baseball fans and officials sometimes say that a player
s best offensive season occurs when the
player is 27 years old. Was this statement more true for this year
s players?
You can answer this question by examining the average OPS for both years using pivot tables.
s players or last year
￿
Who were the league leaders this year and last year in batting average, OPS, and on-base
percentage? You can answer this question using data table analysis.
￿
Traditional baseball fans consider batting average the best measure of offensive performance.
However, many modern baseball analysts say that OPS is a broader and therefore better
measure. Which measure is actually better? To shed light on the question, use data table analysis to
create two all-star teams. One team contains the players with the highest OPS at each position.
The other team contains the players with the highest batting average at each position. If the
same players make both teams, you might reasonably conclude that the two measures are
equally powerful. But, if the two teams have mostly different players, the two measures must be
telling different stories.
￿
What were team batting averages this year versus last year? Which teams improved their batting
average this year? You can answer these questions using pivot table analysis.
Data Table Analyses
Use data tables to analyze three sets of data: (1) year-to-year offensive performance, (2) league leaders this
year and last year in batting average, OPS, and on-base percentage, and (3) the all-star team based on OPS
versus the all-star team based on batting average. Your analysis will address the following questions.
 
Search JabSto ::




Custom Search