1 / 17

Financial Forecasting: Building Models and Advanced Formatting

Learn how to develop a financial forecast model, use advanced formatting techniques, and protect worksheets. Explore scenarios, data validation, templates, and more.

atina
Download Presentation

Financial Forecasting: Building Models and Advanced Formatting

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 (1+the 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(A1>0,A2>0,A3>0) • 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

More Related