MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Introduction to Excel 2007-What’s New?

Figure 0-1: The Ribbon: Home tab

Figure 0-2: Microsoft Office Button

Figure 0-3: Quick Access Toolbar

Figure 0-4: You can add, remove, and arrange commands on the Quick Access Toolbar.

Figure 0-5: The Mini toolbar

Figure 0-6: Formula AutoComplete feature

Figure 0-7: Theme choices

Figure 0-8: SmartArt choices

Figure 0-9: SmartArt example

Figure 0-10: Zoom slider

Figure 0-11: Workbook view options

Figure 0-12: Compatibility Checker

Chapter 1: Range Names

Figure 1-1: You can create a range name by selecting the cell range you want to name and then typing the range name in the Name Box.

Figure 1-2: Naming cell F3 east and F4 west

Figure 1-3: Naming range A1:B4 Data

Figure 1-4: Naming a noncontiguous range of cells

Figure 1-5: By naming the cells that contain state sales with state abbreviations, you can use the abbreviation when you refer to the cell rather than the cell’s column letter and row number.

Figure 1-6: Select Create From Selection

Figure 1-7: Select the Left Column check box

Figure 1-8: The New Name dialog box before creating any range names

Figure 1-9: New Name dialog box after creating a range name

Figure 1-10: Name Manager dialog box for States.xlsx

Figure 1-11: Historical investment data

Figure 1-12: You can add a range name to a formula by using the Paste Name dialog box.

Figure 1-13: Example of AutoComplete feature

Figure 1-14: Name Manager dialog box with worksheet and workbook names

Figure 1-15: How to apply range names to formulas

Chapter 2: Lookup Functions

Figure 2-1: Using a lookup function to compute a tax rate. The numbers in the first column of the table range are sorted in ascending order.

Figure 2-2: Looking up prices from product ID codes. When the table range isn’t sorted in ascending order, enter False as the last argument in the lookup function formula.

Figure 2-3: Using an HLOOKUP function to determine a price that changes depending on the date it’s sold.

Chapter 3: The INDEX Function

Figure 3-1: You can use the INDEX function to calculate the distance between cities.

Figure 3-2: Distances for a Seattle Sonics road trip

Chapter 4: The MATCH Function

Figure 4-1: Using the MATCH function to locate the position of a value in a range.

Figure 4-2: The MATCH function can be used in combination with functions such as INDEX and VLOOKUP.

Figure 4-3: This example uses the MAX, MATCH, and VLOOKUP functions to find and display the highest value in a list.

Figure 4-4: Using the MATCH function to calculate an investment’s payback period.

Chapter 5: Text Functions

Figure 5-1: Examples of text functions

Figure 5-2: Using the TRIM function to trim away excess spaces.

Figure 5-3: Using text functions to extract the product ID, price, and product description from a text string.

Figure 5-4: Extracting East, North, and South sales with a combination of the FIND, LEFT, LEN, and MID functions.

Figure 5-5: Text To Columns Wizard dialog box

Figure 5-6: Result of Text To Columns Wizard

Figure 5-7: Using the REPT function to create a frequency graph.

Chapter 6: Dates and Date Functions

Figure 6-1: Use the Format Cells command to change dates from serial number format to month-day-year format.

Figure 6-2: Reformatting a serial number to month-day-year format.

Figure 6-3: You can use the DATEVALUE function to format a date in serial format.

Figure 6-4: Using the DATEDIF function.

Chapter 7: Evaluating Investments by Using Net Present Value Criteria

Figure 7-1: To determine which investment is better, we need to calculate net present value.

Figure 7-2: Using the XNPV function

Figure 7-3: NPV converted to today’s dollars.

Chapter 8: Internal Rate of Return

Figure 8-1: Example of the IRR function

Figure 8-2: Project with more than one IRR

Figure 8-3: Project with no IRR

Figure 8-4: IRR can lead to an incorrect choice of which project to pursue.

Figure 8-5: Example of the XIRR function

Figure 8-6: Example of the MIRR function

Chapter 9: More Excel Financial Functions

Figure 9-1: Example of PV function

Figure 9-2: Example of FV function

Figure 9-3: Examples of PMT, PPMT, CUMPRINC, CUMIPMT, and IPMT functions

Figure 9-4: Example of RATE function

Figure 9-5: Example of NPER function

Chapter 10: Circular References

Figure 10-1: A loop causing a circular reference

Figure 10-2: A circular reference can occur when you’re calculating taxes.

Figure 10-3: Use the Enable Iterative Calculation option to resolve a circular reference.

Figure 10-4: Excel runs the calculations to resolve the circular reference.

Chapter 11: IF Statements

Figure 11-1: You can use an IF formula to model quantity discounts.

Figure 11-2: Hedging example that uses IF statements

Figure 11-3: Freeze Panes options

Figure 11-4: Moving-average trading rule beats buy and hold!

Figure 11-5: Using IF statements to model the first roll in craps

Figure 11-6: Pro forma assumptions and balance sheet

Figure 11-7: Pro forma income statement

Figure 11-8: Error trapping formulas

Figure 11-9: Examples of Excel error values

Chapter 12: Time and Time Functions

Figure 12-1: Examples of time formats

Figure 12-2: Excel time formats

Figure 12-3: Determining time needed to complete jobs

Figure 12-4: Using the Now() and Today() functions

Figure 12-5: Computing length of time worked by employees

Figure 12-6: Determining total hours worked during the week

Figure 12-7: Entering a sequence of times

Chapter 13: The Paste Special Command

Figure 13-1: Using the Paste Special command to paste only values

Figure 13-2: The Paste Special dialog box with Values selected. Selecting Values pastes only values and not any formulas.

Figure 13-3: Use the Transpose option in the Paste Special dialog box to transpose a row of data into a column or a column of data into a row.

Figure 13-4: Data for using the Divide option in the Paste Special dialog box to divide a data range by a constant.

Figure 13-5: You can apply an option in the Operation area of the Paste Special dialog box to a range of cells.

Figure 13-6: Results of using the Divide option in the Paste Special dialog box

Chapter 14: The Auditing Tool

Figure 14-1: The Formula Auditing toolbar

Figure 14-2: You can use the auditing tool to trace formulas in complex spreadsheets.

Figure 14-3: Tracing dependent cells

Figure 14-4: Clicking Trace Dependents repeatedly shows all the dependents of the price growth assumption.

Figure 14-5: Direct precedents for Year 1 before-tax profit

Figure 14-6: Click Trace Precedents repeatedly to show all precedents of Year 1 before-tax profit.

Figure 14-7: Data for using the auditing tool with data on multiple worksheets

Figure 14-8: Results of tracing precedents with data on multiple worksheets

Figure 14-9: With the Go To dialog box, you can audit data in multiple worksheets.

Chapter 15: Sensitivity Analysis with Data Tables

Figure 15-1: The inputs that change the profitability of a lemonade store

Figure 15-2: One-way data table with varying prices

Figure 15-4: One-way data table with varying prices

Figure 15-3: Creating a data table

Figure 15-5: A two-way data table showing profit as a function of price and unit variable cost

Figure 15-6: You can use a data table to determine how mortgage payments vary as the amount borrowed and the interest rate change.

Figure 15-7: You can use a data table to calculate how many years it will take to break even.

Figure 15-8: A two-way data table

Chapter 16: The Goal Seek Command

Figure 16-1: We’ll use this data to set up the Goal Seek feature to perform a breakeven analysis.

Figure 16-2: The Goal Seek dialog box filled in with entries for a breakeven analysis

Figure 16-3: You can use data such as this with the Goal Seek feature to determine the amount you can borrow based on a set monthly payment.

Figure 16-4: The Goal Seek dialog box set up to calculate the mortgage example

Figure 16-5: Goal Seek can help you solve story problems.

Figure 16-6: The Goal Seek dialog box filled in to solve an algebra story problem

Chapter 17: Using the Scenario Manager for Sensitivity Analysis

Figure 17-1: The data on which the scenarios are based

Figure 17-2: The scenario summary report

Figure 17-3: Data inputs for the best-case scenario

Figure 17-4: Defining the input values for the best-case scenario

Figure 17-5: The Scenario Manager dialog box displays each scenario I defined.

Figure 17-6: Use the Scenario Summary dialog box to select the result cells for the summary report.

Chapter 18: The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK Functions

Figure 18-1: The song database we use for the COUNTIF examples

Figure 18-2: Using COUNTIF to determine how many songs were sung by each singer.

Figure 18-3: You can combine the COUNTIF function with the not-equal-to operator (<>).

Chapter 19: The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS Functions

Figure 19-1: Data we’ll use for SUMIF examples

Figure 19-2: Results of SUMIF computations

Chapter 20: The OFFSET Function

Figure 20-1: Using the OFFSET function

Figure 20-2: You can do a left-hand lookup by using the MATCH and OFFSET functions.

Figure 20-3: You can use the OFFSET function in calculations when you’re working with data that isn’t always in the same location in a worksheet.

Figure 20-4: Using the OFFSET function to compute development costs for Phases 1–3.

Figure 20-5: Movie example using the OFFSET function

Figure 20-6: Evaluate Formula dialog box

Figure 20-7: Finding the last number in a column

Figure 20-8: Example of a dynamic range

Figure 20-9: Creating a dynamic range

Figure 20-10: We can use the OFFSET function to update this chart dynamically.

Figure 20-11: Creating a dynamic range name for the units sold

Figure 20-12: The formula used to define a dynamic range named Month

Chapter 21: The INDIRECT Function

Figure 21-1: A simple example of the INDIRECT function

Figure 21-2: You can use the INDIRECT function to change cell references in formulas without changing the formulas themselves.

Figure 21-3: Monthly sales (months 1–7) of a product listed by using the INDIRECT function

Figure 21-4: Several ways to sum the values in the cell range A5:A10

Figure 21-5: Results of SUM formulas after inserting a blank row in the original range

Figure 21-6: Use the INDIRECT function to create reference to range name within a formula

Chapter 22: Conditional Formatting

Figure 22-1: Conditional formatting commands

Figure 22-2: Conditional formatting options

Figure 22-3: Highlighting the ten highest temperatures in red

Figure 22-4: Conditional formatting using Top/Bottom rules

Figure 22-5: Using the Highlight Cells rules

Figure 22-6: Conditional Formatting Rules Manager dialog box

Figure 22-7: The result of giving the Last 7 Days rule higher precedence than the Yesterday rule

Figure 22-8: After changing the precedence of rules, the Yesterday format will never be applied.

Figure 22-9: Visually distinguishing numeric values by using data bars

Figure 22-10: Customizing your data bars

Figure 22-11: Three-color scales

Figure 22-12: Customizing a three-color scale

Figure 22-13: Two-color scales

Figure 22-14: Icon sets

Figure 22-15: Assigning icons to numerical values

Figure 22-16: Assigning icons to percentage values

Figure 22-17: Conditional formatting highlights returns in the S&P stock index

Figure 22-18: Applying special formatting to S&P returns greater than 3 percent

Figure 22-19: Coloring stock returns less than -3 percent in red and greater than 3 percent in green

Figure 22-20: HIghlighting increased sales in green and decreased sales in red

Figure 22-21: Logical functions

Figure 22-22: Conditional formatting settings that will display in green the quarters in which revenue increased

Figure 22-23: These conditions will display quarters in which revenue increased in green, and quarters in which revenue decreased in red.

Figure 22-24: Using the WEEKDAY function to highlight weekend days in red

Figure 22-25: The Edit Formatting Rule dialog box set up to display weekend days in red font

Figure 22-26: This worksheet rates each player’s ability to play a position.

Figure 22-27: The Edit Formatting Rule dialog box set up to show player ratings in red fill.

Chapter 23: Sorting in Excel

Figure 23-1: Sales transaction data before sorting

Figure 23-2: Sort dialog box not yet filled in

Figure 23-3: Sort dialog box set up for sales sorting example

Figure 23-4: Sorted sales transaction data

Figure 23-5: Sort dialog box set up to sort by color

Figure 23-6: Results of sorting by color

Figure 23-7: Months to be sorted

Figure 23-8: Dialog box to sort by month

Figure 23-9: Months sorted in chronological order

Figure 23-10: Sorting

Chapter 24: Tables

Figure 24-1: Data for creating a table

Figure 24-2: Table design options

Figure 24-3: Formatted table with filters

Figure 24-4: AutoComplete options for a table

Figure 24-5: Total revenue for original data

Figure 24-6: New data added to table in row 12

Figure 24-7: Unit price formula autocopy

Figure 24-8: Gas price data: 2002–2004

Figure 24-9: Gas price line graph: 2002–2004 data

Figure 24-10: Gas price line graph: 2002–2006 data

Figure 24-11: Makeup sales data

Figure 24-12: Total revenue and units sold

Figure 24-13: Selecting names from the table

Figure 24-14: Filtered subtotals for units and revenue

Figure 24-15: Structured references

Figure 24-16: Conditional formatting extends automatically to new table data

Chapter 25: Spin Buttons, Scroll Bars, Option Buttons, Check Boxes, Combo Boxes, and Group List Boxes

Figure 25-1: Spin button

Figure 25-2: Scroll bar

Figure 25-3: Check box

Figure 25-4: Option button

Figure 25-5: Group box

Figure 25-6: Combo box

Figure 25-7: List box

Figure 25-8: The automobile sales data worksheet without any spin buttons

Figure 25-9: Spin buttons placed in worksheet cells

Figure 25-10: Use the Format Control dialog box to link Year1 sales to a spin button.

Figure 25-11: The Format Control dialog box settings that link the spin button in cell D3 to cell E3.

Figure 25-12: You can freeze panes to see the results of calculations in other parts of a worksheet.

Figure 25-13: Use of a check box to turn conditional formatting on and off

Figure 25-14: Format Control for a check box

Figure 25-15: Format to turn five largest cells green

Figure 25-16: Format to turn five smallest cells red

Figure 25-17: Using option buttons to select product price

Figure 25-18: Combo and list boxes

Chapter 26: An Introduction to Optimization with Excel Solver

Figure 26-1: The Solver Parameters dialog box

Chapter 27: Using Solver to Determine the Optimal Product Mix

Figure 27-1: The product mix

Figure 27-2: The Solver Parameters dialog box

Figure 27-3: The Solver Parameters dialog box with the target cell and changing cells defined

Figure 27-4: The Add Constraint dialog box

Figure 27-5: The Add Constraint dialog box with the resource usage constraints entered

Figure 27-6: The Add Constraint dialog box with the demand constraints entered

Figure 27-7: The final Solver Parameters dialog box for the product mix problem

Figure 27-8: Solver options settings

Figure 27-9: A feasible solution to the product mix problem fits within constraints.

Figure 27-10: An infeasible solution to the product mix problem doesn’t fit within the defined constraints.

Figure 27-11: The optimal solution to the product mix problem

Figure 27-12: The Change Constraint dialog box

Chapter 28: Using Solver to Schedule Your Workforce

Figure 28-1: The data we’ll use to work through the bank workforce scheduling problem.

Figure 28-2: The Solver Parameters dialog box filled in to solve the bank workforce problem

Figure 28-3: This constraint defines as an integer the number of workers who start each day.

Figure 28-4: Data for Problem 4

Chapter 29: Using Solver to Solve Transportation or Distribution Problems

Figure 29-1: Data for a transportation problem

Figure 29-2: The Solver set up to solve our transportation problem.

Chapter 30: Using Solver for Capital Budgeting

Figure 30-1: Data we will use with Solver to determine which projects to undertake

Figure 30-2: Solver Parameters dialog box set up for the project selection model

Figure 30-3: Use the Bin option in the Add Constraint dialog box to set up binary changing cells-cells that will display either a 0 or a 1.

Figure 30-4: New optimal solution for if not Project 3 then Project 4

Figure 30-5: Optimal solution when we can select only 4 of 10 projects

Figure 30-6: Adjusting the Tolerance option

Chapter 31: Using Solver for Financial Planning

Figure 31-1: Solver model for calculating the monthly payment for a loan

Figure 31-2: Solver Parameters dialog box set up to determine mortgage payments

Figure 31-3: Retirement planning data that can be set up for analysis with Solver

Figure 31-4: Solver Parameters dialog box set up for the retirement problem

Chapter 32: Using Solver to Rate Sports Teams

Figure 32-1: Data rating NFL teams that we’ll use with Solver

Figure 32-2: Solver Parameters dialog box set up for NFL ratings

Figure 32-3: Top 10 teams for the NFL 2005 season

Chapter 33: Importing Data from a Text File or Document

Figure 33-1: Step 1 of the Text Import Wizard

Figure 33-2: Step 2 of the Text Import Wizard after selecting the Fixed Width option

Figure 33-3: Step 2 of the Text Import Wizard after selecting Delimited Option

Figure 33-4: Step 3 of the wizard, in which you can select a format to apply to the data you’re importing

Figure 33-5: Excel file with lineup information

Chapter 34: Importing Data from the Internet

Figure 34-1: Analyst forecast for Microsoft in March 2007. The Web contains a lot of useful data, but it isn’t easy to analyze on a Web site.

Figure 34-2: New Web Query dialog box after choosing a URL

Figure 34-3: Analyst forecasts in March 2007 imported into Excel

Figure 34-4: Refresh settings for Web query

Figure 34-5: Use the Enter Parameter Value dialog box to designate the stocks for which you want the dynamic Web query to download information.

Figure 34-6: Downloaded information about Microsoft and General Motors stocks

Chapter 35: Validating Data

Figure 35-1: Use the Settings tab in the Data Validation dialog box to set up data-validation criteria.

Figure 35-2: Error Alert tab options in the Data Validation dialog box

Figure 35-3: Add a data-validation input prompt so that users know what data they can enter.

Figure 35-4: Use settings such as these to ensure the validity of dates you enter.

Figure 35-5: Use the ISNUMBER function to ensure that the data in a range is numeric.

Figure 35-6: The Data Validation dialog box can be used to define a list of valid values.

Figure 35-7: Drop-down list of state abbreviations

Chapter 36: Summarizing Data by Using Histograms

Figure 36-1: Monthly stock returns

Figure 36-2: Histogram dialog box for the Cisco histogram

Figure 36-3: Cisco histogram created by using an Excel Analysis ToolPak function

Figure 36-4: You can change the format of different elements in the chart.

Figure 36-5: Cisco bin-range frequencies

Figure 36-6: Symmetric histogram

Figure 36-7: A positively skewed histogram created from data about family income.

Figure 36-8: A negatively skewed histogram of data plotting days from conception to birth.

Figure 36-9: A multiple-peak histogram

Figure 36-10: Using histograms that include the same bin ranges to compare different data sets

Chapter 37: Summarizing Data by Using Descriptive Statistics

Figure 37-1: Descriptive Statistics dialog box

Figure 37-2: Descriptive statistics results for Cisco and GM stocks

Figure 37-3: Outliers for Cisco highlighted with conditional formatting

Figure 37-4: Conditional formatting rules to select outliers, as shown in the New Formatting Rule dialog box

Figure 37-5: LARGE and SMALL functions and trimmed mean

Figure 37-6: Status bar

Figure 37-7: Geometric mean

Chapter 38: Using PivotTables to Describe Data

Figure 38-1: Data for the grocery PivotTable example

Figure 38-2: The Create PivotTable dialog box

Figure 38-3: The PivotTable Field List dialog box

Figure 38-4: Completed PivotTable Field List dialog box

Figure 38-5: The Grocery PivotTable in compact form

Figure 38-6: The outline form

Figure 38-7: The tabular form

Figure 38-8: The Years field pivoted to the column field

Figure 38-9: The cereal field collapsed

Figure 38-10: The Expand Entire Field and Contract Entire Field buttons

Figure 38-11: The Group field collapsed

Figure 38-12: PivotTable filtering options for the Product field

Figure 38-13: The Customer PivotTable data

Figure 38-14: The Customer PivotTable

Figure 38-15: Top 10 customers

Figure 38-16: Configuring the Top 10 Filter dialog box to show customers generating 50 percent of revenue

Figure 38-17: The top customers generating half of the revenues

Figure 38-18: Sorting on the Quarter 1 column

Figure 38-19: Customers sorted on Quarter 1 revenue

Figure 38-20: PivotChart for unit group sales trend

Figure 38-21: A PivotTable summarizing January–June sales

Figure 38-22: Grocery PivotTable without totals

Figure 38-23: New Formatting Rule dialog box for using conditional formatting with PivotTables

Figure 38-24: Data bars for a PivotTable

Figure 38-25: Travel agency data showing amount spent on travel, age, and gender

Figure 38-26: PivotTable summarizing the total travel expenditures by gender

Figure 38-27: You can select a different summary function in the Value Field Settings dialog box.

Figure 38-28: Average travel expenditures by gender

Figure 38-29: PivotTable showing the average travel expenditures by age

Figure 38-30: Use the Group And Show Detail command to group detailed records.

Figure 38-31: Age/gender breakdown of travel spending

Figure 38-32: PivotChart for the age/gender travel expenditure breakdown

Figure 38-33: Data collected about income, family size, and the purchase of a station wagon

Figure 38-34: Summary of station wagon ownership by family size and salary

Figure 38-35: Percentage breakdown of station wagon ownership by income for large and small families

Figure 38-36: Breakdown of station wagon ownership by family size for high and low salaries

Figure 38-37: Chip data from different countries for different months showing actual, budget, and variance revenues

Figure 38-38: Monthly summary of revenue, budget, and variances

Figure 38-39: Creating each month’s percentage of annual revenue

Figure 38-40: Monthly revenue breakdown

Figure 38-41: Creating a calculated field

Figure 38-42: The PivotTable with calculated field for variance percentage

Figure 38-43: Sales of Chip 2 in France

Figure 38-44: Grouping items together for January, February, and March

Figure 38-45: Use the GETPIVOTDATA function to locate April Chip 1 Sales in France.

Chapter 39: Summarizing Data with Database Statistical Functions

Figure 39-1: We’ll use this data to describe how to work with database statistical functions.

Figure 39-2: Database statistical functions

Figure 39-3: Computed criteria

Figure 39-4: Combining data tables with a DSUM function

Figure 39-5: DGET function

Chapter 40: Filtering Data and Removing Duplicates

Figure 40-1: Makeup sales data

Figure 40-2: AutoFilter heading arrows

Figure 40-3: Choices for filtering or sorting the Name column

Figure 40-4: Jen sells lipstick in the East region.

Figure 40-5: Transactions where Cici or Colleen sold lipstick or mascara in the East or South region

Figure 40-6: Filtering options for a numerical column

Figure 40-7: Number Filters options

Figure 40-8: Selecting all records where units sold >90.

Figure 40-9: Transactions where >90 units were sold for a total of >$280.

Figure 40-10: Possible filtering options for the Date column

Figure 40-11: Sales during 2005 and 2006

Figure 40-12: Date Filters options

Figure 40-13: All sales during October 2005-March 2006

Figure 40-14: Custom AutoFilter dialog box to select all records where salesperson's name begins with C

Figure 40-15: Dialog box for filtering by cell color

Figure 40-16: All records where the product cell color is red

Figure 40-17: Dialog box to select the Top 30 records by $ value

Figure 40-18: Top 30 records by $ value

Figure 40-19: Remove Duplicates dialog box

Figure 40-20: List of salespersons' names

Figure 40-21: Finding unique salesperson, product, and location combinations

Figure 40-22: List of unique salesperson, product, and location combinations

Figure 40-23: Setting up a criteria range to use with an advanced filter

Figure 40-24: Advanced Filter dialog box settings

Chapter 41: Consolidating Data

Figure 41-1: East region sales during January–March

Figure 41-2: West region sales during January–March

Figure 41-3: East and West sales arranged on the same screen

Figure 41-4: Consolidate dialog box

Figure 41-5: Completed Consolidate dialog box

Figure 41-6: Total sales after consolidation

Chapter 42: Creating Subtotals

Figure 42-1: After sorting a list by the values in a specific column, you can easily create subtotals for that data.

Figure 42-2: Subtotal dialog box

Figure 42-3: Subtotals for each region

Figure 42-4: When you create subtotals, Excel adds buttons that, when clicked, display only subtotals or both subtotals and details.

Figure 42-5: Displaying the overall total without any detail

Figure 42-6: Creating nested subtotals

Figure 42-7: Nested subtotals

Chapter 43: Estimating Straight Line Relationships

Figure 43-1: Format Trendline options

Figure 43-2: Plant operating data

Figure 43-3: Scatter plot of operating cost vs. units produced

Figure 43-4: Selecting trendline options.

Figure 43-5: Completed trend curve

Figure 43-6: Computation of slope, intercept, RSQ, and standard error of regression

Chapter 44: Modeling Exponential Growth

Figure 44-1: Cisco’s annual revenues for the years 1990 through 1999

Figure 44-2: Scatter plot for the Cisco trend curve

Figure 44-3: Exponential trend curve for Cisco revenues

Chapter 45: The Power Curve

Figure 45-1: Predicting cost as a function of the number of units produced

Figure 45-2: Plotting sales as a function of advertising

Figure 45-3: Plotting the time needed to produce a unit based on cumulative production

Figure 45-4: Data used to plot the learning curve for producing fax machines

Figure 45-5: Learning curve for producing fax machines

Chapter 46: Using Correlations to Summarize Relationships

Figure 46-1: Correlation near +1, indicating that two variables have a strong positive linear relationship

Figure 46-2: Correlation near –1, indicating that two variables have a strong negative linear relationship

Figure 46-3: Correlation near 0, indicating a weak linear relationship between two variables

Figure 46-4: Monthly stock returns during the 1990s

Figure 46-5: Correlation dialog box

Figure 46-6: Stock return correlations

Figure 46-7: Complete correlation matrix

Chapter 47: Introduction to Multiple Regression

Figure 47-1: Data for predicting monthly operating costs

Figure 47-2: Regression dialog box

Figure 47-3: Original multiple regression output

Figure 47-4: Original multiple regression residual output

Figure 47-5: Multiple regression output with A Made data removed as an independent variable

Figure 47-6: Residual output calculated when A Made data is removed as an independent variable

Figure 47-7: Using the LINEST function to calculate a multiple regression

Chapter 48: Incorporating Qualitative Factors into Multiple Regression

Figure 48-1: Auto sales data

Figure 48-2: Using dummy variables to track the quarter in which a sale occurs

Figure 48-3: Summary output and ANOVA table for auto sales data

Figure 48-5: Residuals for the auto sales data

Figure 48-4: Coefficient information for auto sales regression

Figure 48-6: Presidential election data

Figure 48-7: Regression output for predicting presidential elections

Figure 48-8: Presidential election residuals

Chapter 49: Modeling Nonlinearities and Interactions

Figure 49-1: Nonlinear relationship between demand and price

Figure 49-2: Data for predicting salary based on gender and experience

Figure 49-3: Regression results that test for nonlinearity and interaction

Figure 49-4: Regression results after deleting insignificant gender variable

Chapter 50: Analysis of Variance: One-Way ANOVA

Figure 50-1: Book sales data

Figure 50-2: Anova: Single Factor dialog box

Figure 50-3: One-way ANOVA results

Figure 50-4: Book store data for which the null hypothesis is accepted

Figure 50-5: Anova results accepting the null hypothesis

Figure 50-6: Computation of forecast standard deviation

Chapter 51: Randomized Blocks and Two-Way ANOVA

Figure 51-1: Data for the randomized blocks example

Figure 51-2: Anova: Two-Factor Without Replication dialog box for setting up a randomized blocks model

Figure 51-3: Randomized blocks output

Figure 51-4: Forecast for sales in District 2 by sales Rep 4

Figure 51-5: Video game sales data; no interaction

Figure 51-6: Anova: Two-Factor With Replication dialog box for running a two-factor ANOVA with replication

Figure 51-7: Two-way ANOVA with replication output; no interaction

Figure 51-8: Price and advertising do not interact in this data set.

Figure 51-9: Forecasts for sales with high price and medium advertising

Figure 51-10: Sales data with interaction between price and advertising

Figure 51-11: Output for the two-factor ANOVA with interaction

Figure 51-12: Price and advertising exhibit a significant interaction in this set of data.

Chapter 52: Using Moving Averages to Understand Time Series

Figure 52-1: Quarterly revenues for Amazon sales

Figure 52-2: Time series plot of quarterly toy revenues

Figure 52-3: Four-quarter moving average trend curve

Chapter 53: Winter ’s Method

Figure 53-1: Initialization of Winter’s method

Figure 53-2: Solver Parameters dialog box for Winter’s model

Chapter 54: Forecasting in the Presence of Special Events

Figure 54-1: Data used to predict credit union customer traffic

Figure 54-2: Changing cells and customer forecasts

Figure 54-3: Solver Parameters dialog box for determining forecast parameters

Figure 54-4: Using conditional formatting to spot forecast outliers

Figure 54-5: Forecast parameters and forecasts including spring break and the first three days of the month

Figure 54-6: Errors for Christmas week

Figure 54-7: Final forecast parameters

Figure 54-8: Determing whether the residuals are random

Chapter 55: An Introduction to Random Variables

Figure 55-1: Computing the mean, standard deviation, and variance of a random variable

Figure 55-2: Probability density function for IQs

Chapter 56: The Binomial, Hypergeometric, and Negative Binomial Random Variables

Figure 56-1: Using the binomial random variable

Figure 56-2: Using the hypergeometric random variable

Figure 56-3: Using the negative binomial random variable

Chapter 57: The Poisson and Exponential Random Variable

Figure 57-1: Using the Poisson random variable

Figure 57-2: Exponential pdf

Figure 57-3: Computations of exponential probabilities

Chapter 58: The Normal Random Variable

Figure 58-1: IQ pdf

Figure 58-2: Normal random variable pdf with a mean equal to 60 and a standard deviation equal to 5

Figure 58-3: Normal random variable pdf with a mean equal to 60 and a standard deviation equal to 15

Figure 58-4: Calculating normal probability

Figure 58-5: Calculating the probability that a random variable is between a and b

Figure 58-6: Calculating that the probability random variable is greater than or equal to b

Figure 58-7: Using the Central Limit Theorem

Chapter 59: Weibull and Beta Distributions: Modeling Machine Life and Duration of a Project

Figure 59-1: Machine lifetime data

Figure 59-2: Estimates of alpha and beta for a Weibull random variable

Figure 59-3: Determining probabilities with the Beta random variable

Chapter 60: Introduction to Monte Carlo Simulation

Figure 60-1: Demonstrating the RAND function

Figure 60-2: Simulating a discrete random variable

Figure 60-3: Simulating a normal random variable

Figure 60-4: Valentine’s Day card simulation

Figure 60-5: Two-way data table for greeting card simulation

Figure 60-6: Using the Series dialog box to fill in the trial numbers 1 through 1000

Figure 60-7: 95 percent confidence interval for mean profit when 40,000 calendars are ordered

Chapter 61: Calculating an Optimal Bid

Figure 61-1: Simulating a binomial random variable

Figure 61-2: Bidding simulation model

Figure 61-3: Bidding simulation data table

Chapter 62: Simulating Stock Prices and Asset Allocation Modeling

Figure 62-1: GE, Microsoft, and Intel stock data

Figure 62-2: Simulating GE stock price in one year

Figure 62-3: Data table for GE simulation

Figure 62-4: Pessimistic view of the future

Figure 62-5: Historical returns on stocks, T-Bills, and bonds

Figure 62-6: Simulating five-year returns on stocks, T-Bills, and bonds

Figure 62-7: Optimal asset allocation model

Figure 62-8: Solver Parameters dialog box set up for our asset allocation model

Chapter 63: Fun and Games: Simulating Gambling and Sporting Event Probabilities

Figure 63-1: Simulating a game of craps

Figure 63-2: Estimating the probability that we’ll draw three of a kind in a poker game

Figure 63-3: Simulating the outcome of the NCAA 2003 Final Four

Figure 63-4: Comments in a worksheet

Chapter 64: Using Resampling to Analyze Data

Figure 64-1: Product yields at high and low temperature

Figure 64-2: Implementation of resampling

Chapter 65: Pricing Stock Options

Figure 65-1: Cash flows from a call option

Figure 65-2: Cash flows from a put option

Figure 65-3: Computing the historical volatility for Dell

Figure 65-4: Valuing European calls and puts

Figure 65-5: Using implied volatility to estimate Cisco’s volatility.

Figure 65-6: Goal Seek settings to find implied volatility

Figure 65-7: Format Cells dialog box

Figure 65-8: Allowing user to access unlocked cells

Figure 65-9: Oil well real options

Figure 65-10: Calculating an abandonment option

Chapter 66: Determining Customer Value

Figure 66-1: Value of a credit-card customer

Figure 66-2: Phone incentive analysis

Figure 66-3: Goal Seek settings to determine the maximum incentive that increases profitability

Chapter 67: The Economic Order Quantity Inventory Model

Figure 67-1: EOQ template

Figure 67-2: Template for computing EOB

Chapter 68: Inventory Modeling with Uncertain Demand

Figure 68-1: Determining reorder point when shortages are back ordered

Figure 68-2: Determining reorder point when sales will be lost

Figure 68-3: Determination of reorder point using the service level approach

Chapter 69: Queuing Theory-The Mathematics of Waiting in Line

Figure 69-1: Queuing template

Figure 69-2: Airline interarrival and service times

Figure 69-3: Sensitivity analysis for an airline ticket counter

Chapter 70: Estimating a Demand Curve

Figure 70-1: Fitting a linear demand curve

Figure 70-2: Power demand curve

Chapter 71: Pricing Products by Using Tie-Ins

Figure 71-1: Determining the profit-maximizing price for razors

Figure 71-2: Solver Parameters dialog box set up for maximizing razor profit

Figure 71-3: Price for razors with blade profit included

Figure 71-4: Movie problem data

Chapter 72: Pricing Products by Using Subjectively Determined Demand

Figure 72-1: Lipstick pricing model

Figure 72-2: Configuring the Format Trendline dialog box for selecting polynomial demand curve

Figure 72-3: Configuring the Solver Parameters dialog box to calculate lipstick pricing

Chapter 73: Nonlinear Pricing

Figure 73-1: Cost of quantity discount plan

Figure 73-2: Cost of two-part tariff

Figure 73-3: Profit-maximizing linear pricing scheme

Figure 73-4: Determination of optimal two-part tariff

Figure 73-5: Two-way data table computes optimal two-part tariff

Figure 73-6: Data for golf problem

Chapter 74: Array Formulas and Functions

Figure 74-1: Using array formulas to compute hourly wages

Figure 74-2: Using the TRANSPOSE function

Figure 74-3: Using the FREQUENCY function

Figure 74-4: Summing second digits in a set of integers

Figure 74-5: Finding duplicates in two lists

Figure 74-6: Averaging prices at least as large as median price

Figure 74-7: Makeup database

Figure 74-8: Summarizing data with array formulas

Figure 74-9: Creating second and fourth powers of sales

Figure 74-10: Toy revenue trend and seasonality estimation

Figure 74-11: LOGEST estimates trend and seasonality

Figure 74-12: Format for Problem 17 answer