List of Figures


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




Microsoft Press - Microsoft Office Excel 2007. Data Analysis and Business Modeling
MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)
ISBN: 0735623961
EAN: 2147483647
Year: 2007
Pages: 200

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net