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 the Access table that contains your two queries into Excel worksheets and
then develop information requested by the exchange management.
Importing and Using Table and Query Data
Open a new file in Excel and save it as Breweries.xlsx. To import the FinancialData table into Excel, click the
Data tab and then select From Access in the Get External Data group. Specify the Access filename, the table
name, and where to place the data in Excel (cell A1 is recommended). Rename the worksheet FinancialData.
The data is imported into Excel as an Excel data table. Select Convert to Range in the Tools group to
change the data to a range. Format the data for currency and the proper number of decimal places.
By Excel formula, compute the capitalization for each company in each year. The data should look like
that in Figure 12-8.
FIGURE 12-8
Financial data table with capitalization
Note that only the first few rows of the data are shown in Figure 12-8. The capitalization data is shown
under a Product heading.
By formula, compute the brewery company index for each year. Summarize the data by year in a
convenient spot in the worksheet, and then copy that data to a new worksheet named Plot Index. To create a
scatter plot of the yearly indexes, highlight the data set, including the labels, then click the Insert tab and select a
scatter chart in the Charts group. In the Labels group of the Chart Tools Layout tab, use the Chart Title and Axis
Titles buttons to create titles and labels. Your work should look like the illustrative data shown in Figure 12-9.
FIGURE 12-9
Yearly brewery index data and index scatter plot
Create a new worksheet and name it EPS. Import the EPS query output into the worksheet and format
the data appropriately. Compute the unweighted average EPS for each year. (Use the AVERAGE function, and
do not weight EPS values by outstanding shares.) Summarize the Average EPS data by year in a convenient
spot in the worksheet, and then copy that data to the Plot Index worksheet. Use the directions from the
preceding paragraph to create a scatter plot of the yearly values with a proper title and axis labels. Your
worksheet should look like the illustrative data shown in Figure 12-10.
 
Search JabSto ::




Custom Search