Spreadsheets in Decision Making: What If

1
Exploring Microsoft Excel

• Chapter 3
• Spreadsheets in Decision Making What If?

2
Analysis of a Car Loan

• Can I afford it?
• How do I calculate for
• rebates
• down payments
• interest rates
• the number of payments

3
Analysis of a Car Loan

• Set up a worksheet template with initial
  conditions
• Use a PMT function which requires interest
  rate/period, number of periods, and amount of
  loan
• Payment amounts and the number of payments are
  usually expressed in months, while interest rates
  are annual rates. Divide the interest rate by 12
  to come up with a monthly rate
• The amount of the loan (present value) should be
  expressed as a negative number.

4
How Much Money Will I Have at Retirement?

• The Future Value (FV) function to return the
  future value of a series of payments
• A common example would be contributions you make
  to your IRA or 401K plan
• Use the FV function which requires the expected
  rate of return, the number of periods, and the
  investment each period.
• The Moral of the Story Start planning for your
  retirement now!! The sports car can wait!!!!

5
Inserting a Function

• Use the Insert Function command from the Insert
  menu
• Use the list box to select the name of the
  function
• functions categorized by function
• Let the Wizard help you enter the arguments

6
Isolate Your Assumptions

• Enter your assumptions (the arguments needed for
  the function) into cells and use those cells for
  your arguments
• For example, in the PMT function, enter the loan
  amount, number of payments, and interest rate
  into cells, then use those cells in the PMT
  function
• Change the values in those cells to test
  different scenarios
• Easier than editing the formula when you want to
  change on or more of your variables

7
Setting up Goal Seek

• Establish PMT
• interest rate
• term
• down payment
• Use Goal Seek
• change result by changing one variable
• you can only change one variable

8
Getting the most from Excel

• Relative versus absolute addressing in a
  worksheet
• Knowing the difference and when to use each when
  copying makes setting up your worksheet more
  efficient and more accurate
• Mixed references
• Either the row or the column is absolute the
  other is relative
• Dont forget to isolate your assumptions!!

9
Using Functions in Excel

• Statistical Functions MAX,MIN, AVERAGE, COUNT
  and COUNTA
• Use functions instead of arithmetic expressions
• IF function enhances decision making
• allows for different results based on different
  conditions
• VLOOKUP(vertical lookup) Function
• assigns a value to a cell based on a numeric
  value in another cell

10
The IF function

• Allows for different results, based on a
  condition
• for example, if you work over forty hours in a
  week, you will receive overtime pay
• Requires three arguments
• a condition, which Excel must be able to evaluate
  as true or false
• a value if true
• a value if false
• The value if true and value if false may contain
  additional (nested) IF functions for more complex
  decisions.

11
The VLOOKUP function

• Allows Excel to look up a value in a table and
  return a related value
• for example, Excel can look up your average in
  this class and return your grade
• Requires three arguments
• the numeric value (or cell) to look up
• the range of the table
• the column number containing the value you want
  to return

12
Managing Large Worksheets (1 of 2)

• Scrolling causes the screen to move horizontally
  or vertically as you change the active cell
• Freezing Panes allows row and column headings to
  be seen while scrolling
• AutoFill capability enter series into adjacent
  cells
• Hiding rows and columns makes rows and columns
  invisible on the monitor or when printed

13
Managing Large Worksheets (2 of 2)

• Page Preview command (View menu) lets you see
  where the page breaks are
• Page Setup command (File menu) lets you change
  how the sheet prints
• Change from portrait (8 ½ x 11) to landscape (11
  x 8 ½)
• Change margins
• Scale the worksheet to print on one sheet
• AutoFilter command lets you display only rows
  that meet certain criteria

14
Finished Grade Book
15
Exploring Microsoft Excel

• Chapter 4
• Graphs and Charts Delivering a Message

16
What is a Chart?

• A graphic representation of data in a worksheet
• Based on descriptive (text) entries called
  category labels and numeric values called data
  points
• Data series a grouping of data points

17
Chart Types

• Always remember to keep it simple and clear
• Pie and Exploded pie charts are effective for
  displaying proportional relationships
• Column charts are used for displaying numbers
• Bar charts show numbers horizontally

18
Pie Charts
19
Column Charts
20
Creating A Chart

• Select the cells containing your labels and data
  points
• Use the Chart Wizard a four step process
• Step one choose a chart type
• Step two review data series
• Step three final touches
• Step four where to store

21
Chart Wizard
22
Finishing the Chart

• Embedding in a worksheet or separate chart sheet
• Enhance with Drawing toolbar

23
Using the Drawing toolbar
24
Multiple Data Series

• Decide what message to convey with the chart
• What do you want to emphasize?
• Rows versus columns
• If data series are in rows the Chart Wizard will
  use first row for X axis and use first column for
  legend text
• If data series are in columns the Chart Wizard
  will use first column for X axis and use first
  row for legend text

25
Stacked Column Charts

• A column chart that depicts totals by category
  instead of each individual data point
• Useful when you want to compare totals by category

26
Using Multiple Data Series
27
Object Linking and Embedding

• Create a document in one application that
  contains objects from another application
• For example, contain a Word document that
  contains objects from an Excel worksheet
• Embedded object is stored in the document
• an Excel chart becomes part of the Word document
• Linked object is stored in its own file
• any change in this file is automatically
  reflected in the main document
• Multitasking the ability to have more than one
  application open at the same time