Spread Sheet Modeling - Excel and pivot tables.

1. (Chapter 2: Question 6) The spreadsheet model for Sam’s Bookstore (see Example 2.4 in Section

2.5 of the textbook) contains a two-way data table for profit versus order quantity and

demand. Experiment with Excel’s chart types to create a chart that shows this information

graphically in an intuitive format. (Choose the format you would choose to give a presentation

to your boss.)

2. (Chapter 2: Question 36) The payback of a project is the number of years it takes before the

project?s total cash flow is positive. Payback ignores the time value of money. It is interesting,

however, to see how differing assumptions on project growth impact payback. Suppose, for

example, that a project requires a $300 million investment at year 0 (right now). The project

yields cash flows for 10 years, and the year 1 cash flow will be between $30 million and $100

million. The annual cash flow growth will be between 5% and 25% per year. (Assume that this

growth is the same each year.) Use a data table to see how the project payback depends on the

year 1 cash flow and the cash flow growth rate.

3. (Data Manipulation/Reporting in Excel): Given the Publications Data spreadsheet, use Excel’s

Pivot function to produce the following reports:

? A list of all the unique main authors sorted alphabetically within the full-time (F-T) and

part-time (P-T) categories with a count of articles by each author.

? A list of the unique journal names.

? In one table: list the journals by rank with columns for journal, journal rank, journal title

and main author together with a count of the publications in each journal by each author.

Filter out the journals that have no ranking (marked N/A or not applicable).

? Draw a line graph of total publications by year.

? Produce a table of total publications by year by F-T and P-T faculty categories (years

should be listed on the horizontal axis.) Depict the same data in a vertical bar graph.

Important: The results of each exercise should be contained in ONE spreadsheet consisting

of a number of worksheets, each worksheet should have an appropriate title and date at the top

and each worksheet tab should be appropriately labeled.

Spread Sheet Modeling – Excel and pivot tables.