Excel%20Project%20Three

1
Excel Project Three

• What-If Analysis, Charting, and Working With
  Large Spreadsheets

2
Excel Project Three

• Excels automatic recalculation feature to
  complete what-if analysis
• Absolute cell references
• The if function
• Working with large worksheets

3
Using cell references

• When setting up a worksheet use as many cell
  references as possible in your formulas
• Cell references in a formula are called
  assumptions
• Change the assumptions, and you can change the
  output of your worksheet

4
Project Three Description

• Need a worksheet and 3-D Pie chart showing
  projected monthly revenue, expenses, and net
  income
• Data Projected revenues and assumptions based on
  historical data. Other values computed by formula
• Calculations based on assumptions

5
Series

• Series is mathematical term for a sequence of
  terms with some relation between them
• Relation generates the next term of the series
• Sequence can be numbers, dates, or another
  pattern
• Fill handle allows you to quickly set up and
  expand a series

6
Absolute vs. relative addressing

• When you copy cell references from one cell to
  another, Excel normally updates the cell
  reference
• Example autosum on one column, then copy it one
  column over and references are updated

7
Absolute references

• Sometimes you want to include a single value in
  calculations in different parts of your
  spreadsheet
• You can turn off the Excel cell reference
  updating with absolute references (with )
• See table p. 3.25

8
Entering formulas with absolute references

• Change to formulas version (CTRL ACCENT MARK
  ()
• Follow formula exactly
• Be careful, it is easy to make a mistake!

9
The if function

• Allows excel to examine a value and make a
  decision
• Make a logical test using comparison operators
• Has three parts
• Logical test
• The value if true
• The value if false

10
Excel as an analysis tool

• What-if analysis change assumption, use undo to
  reset
• Goal seeking allows you to analyze
  relationships between values in your spreadsheet
  and optimize a value

11
Summary of project three

• Using the format painter
• Creating a series with the fill handle
• Using absolute references in a formula
• Using the if function
• Entering the system date
• Adjusting assumptions
• Using goal seek

12
Summary cont.

• Selecting non-adjacent ranges for a pie chart
• Creating an exploding pie chart
• Freezing and unfreezing worksheet titles