Financial Forecast

1
Chapter 6

• Financial Forecast

2
Agenda

• Developing a financial forecast model
• Advanced formatting
• Using the scenario manager to facilitate
  decision-making
• Shared workbook
• Tracking the spreadsheet editing changes
• Using the Formula Auditing toolbar
• Inserting comment
• Data validation
• Creating template
• Protecting worksheet
• Function

3
Forecasting Model

• Separating assumptions and initial conditions
• Initial conditions expected values for the first
  period of the forecast
• Assumptions initial conditions plus assumptions
  about the rate of change
• Including the initial conditions and assumptions
  in the formulas for the forecasting model

4
Building the Forecast Model

• Starting with initial conditions for the first
  period
• Creating the formulas for the second period
• Starting with the values for the first period
• Adding in the change between the periods
• First period expected rate of change (1the
  change rate)
• Using absolute and relative references
• Copying the formulas for the second period to the
  remainder of the forecast

5
Advanced Formatting

• Rotating text to give headings a vertical
  orientation (format menu, cells, alignment,
  degree text box)
• Use merge and center to center the heading
  (format menu, cells, alignment, merge cells or
  merge and center button on the format bar)
• Indenting text for main headings and subheadings
• Using conditional formatting
• Apply formatting to a cell based on its value
• Format menu, conditional formatting

6
Creating a Style

• Style a set of formatting characteristics under
  a specific name
• Displaying the style list button on the
  formatting toolbar tools menu, customize,
  command tab, format category, clicking and
  dragging the style list box to the formatting
  toolbar
• Creating a style formatting the cell, format
  menu, style, entering the name for the style
• Applying styles selecting the cell, selecting
  the style name from style list button
• Deleting a style format menu, style, delete
• Changes to a style automatically affect all cells
  defined by that style

7
Scenario Manager

• Scenarios (sets of assumptions such as
  optimistic, pessimistic, and likely )
• Creating scenarios
• Creating range name selecting cell, insert menu,
  name, define, entering the name for the range
• Selecting the cell, entering the first name for
  the scenarios
• Tools menu, scenarios, entering the first name
  for the scenarios, entering the cells to changes
  in the changing cells text box, ok, add (to add
  the second scenarios)
• Deleting scenarios
• Tools menu, scenarios, selecting the scenarios
  name, delete
• Scenario summary compares results of different
  scenarios side-by-side
• Clicking the cell for comparison, tools menu,
  scenarios, summary, entering the cell for
  comparison in the result cells text box, ok

8
Workgroups and Sharing Files

• Workgroup a group of people working on a project
• Shared workbook making comments or changes by
  other members
• Tools menu, shared workbook, edit tab, checking
  allow changes

9
Tracking Changes

• Tracing changes using reviewing toolbar or track
  changes command of tools menu
• Tools menu, customize, toolbars tab, checking
  reviewing
• Tools menu, track changes, clicking highlight
  changes, entering options, checking the highlight
  changes on screen
• Accepting or rejecting the changes
• Tools menu, shared workbook, advanced tab,
  checking ask me which changes win, ok
• Tools menu, track changes, accept or reject
  changes

10
Auditing the Worksheet

• Formula auditing toolbar showing the graphical
  relationships in formulas
• Tools menu, customize, toolbars tab, checking
  formula auditing
• Identifies precedents and dependents
• Precedents cells referenced by a formula
• Dependents formulas that reference a cell

11
Inserting Comment

• User comments written in the shared workbook
• Selecting the cell, insert menu, comment
• New comment button in the reviewing toolbar
• Red triangle in the upper-right corner
• Tools menu, options, view tab, checking comment
  indicator only
• Deleting comment
• Selecting the cell, right clicking, delete
  comment
• Delete comment button in the reviewing toolbar

12
Data Validation

• Data validation restricting entering values for a
  cell
• Useful in shared workbooks
• Data menu, validation, error alert tab, entering
  necessary values
• Be sure un-checking the track changes while
  editing related to the track changes in tools
  menu)

13
Templates

• A special type of workbook
• Used as the basis for other workbooks
• Containing formulas and formatting, but no data
• Creating template
• Select the assumption area
• Edit menu, clear, contents
• Format menu, cells, protection tab, deselect
  locked check (unlock the changing cells)
• Tools menu, protection, protect worksheet
• Tools menu, options, view tab, clearing zero
  values, ok
• Save the file as a template type

14
Protecting Worksheet

• A two-part process
• Unlock all of the cells for the user input or
  change
• Select cells for allowing changes, format menu,
  cells, protection tab, clearing the locked box
• Protect the worksheet
• Tools menu, protection, protect worksheet,
  checking select locked cells, checking select
  unlocked cells

15
Functions

• AND
• Return True or False
• AMD(A1gt0,A2gt0,A3gt0)
• DATE(YEAR, MONTH, DAY)
• Return numeric day, month, and year
• MONTH(A1)
• MATCH
• Actual number of payments (the relative position
  of a number in a array)
• Three arguments looking up value (a zero ending
  balance) associated cell range (cells containing
  the balance at the end of each period), and type
  of match (whether or not it is an exact match)
• Type 1 for the relative position this is greater
  or equal to type 0 is the relative position that
  is equal to type -1 is the relative position
  that is smaller or equal to
• INDEX
• Determine the payoff date using the result of the
  match function (the corresponding value of a
  cell)
• Three arguments cell range in the form of a
  table (all potential payments), a row number
  within the table (value returned by the MATCH
  function), a column number within the table
  (contains the date of payment)

16
Points to Remember

• Developing a financial forecast model
• Advanced formatting
• Using the scenario manager to facilitate
  decision-making
• Shared workbook
• Tracking the spreadsheet editing changes
• Using the Formula Auditing toolbar
• Inserting comment
• Data validation
• Creating template
• Protecting worksheet
• Function

17
Assignment

• Exercises 5, 6, 11, and 12
• Due date