Forecasting and Trend Analysis Using Microsoft Office Excel 2003
Course 4002: 1/2 day; Instructor-Led and eLearning
On This Page
 | Introduction |
| Audience |
| At Course Completion |
| Prerequisites |
| Course Outline |
| About the Author |
| Take This Training |
Introduction
Microsoft Excel is the spreadsheet program most commonly used by financial analysts, project managers, academics, and small business owners around the world to track and analyze business and personal data. Because of the increasing demands on their time, business professionals need to learn efficient and effective data forecasting methods that give them the answers they need. This course will provide experienced Excel users with a practical, hands-on understanding of advanced Excel data forecasting and charting techniques. It examines the risks and benefits of forecasting, teaches different forecasting and trending methods, and explores ways to maximize profit potential.
Audience
This course is designed for experienced Microsoft Excel users, including project managers, financial analysts, accountants, business owners, and other business professionals who have a vested interest in forecasting trends at the industrial, corporate, and project levels. This audience uses Excel on a regular basis and has no difficulty creating formulas, charts, and cell formats.
At Course Completion
After completing this course, students will be able to:
| • | Describe the role data forecasting plays in organizational planning. |
| • | Identify the positive and negative aspects of data forecasting. |
| • | Create formula-based data forecasts. |
| • | Define best, middle, and worst case scenario data. |
| • | Establish target values using Goal Seek. |
| • | Calculate moving averages. |
| • | Chart moving averages interactively. |
| • | Calculate Net Present Value and Internal Rate of Return. |
| • | Define and solve problems in Solver. |
Prerequisites
Before attending this course, students must have:
| • | Experience with analyzing business data to make decisions about products, projects, and strategic direction. |
| • | The ability to use Excel to create formulas, including advanced formulas using the Insert Function dialog box. |
| • | The ability to create line graphs and column charts from Excel data. |
| • | Familiarity with named ranges (for example, abbreviations that replace cell addresses, such as C3:D15, with nicknames such as AllSales). |
Course Outline
Module 1: The Risks and Benefits of Forecasting Data
This module introduces the risks and benefits of data forecasting both in general and in the context of your business environment.
Topics and Activities
| • | What Is Data Forecasting? |
| • | The Business Needs for Data Forecasting |
| • | Summary of the Risks and Benefits of Data Forecasting |
After completing this module, students will be able to:
| • | Describe the data forecasting process. |
| • | Identify the business needs that data forecasting addresses. |
| • | Summarize the risks and benefits of data forecasting. |
Module 2: Creating Formula-Based Forecasts
This module introduces the FORECAST formula, a versatile Excel function that you can use to create data forecasts; scenarios, which enable you to define best-case, middle-case, and worst-case data scenarios; and Goal Seek, an Excel tool that enables you to find the inputs required to make a formula generate a desired result.
Topics and Activities
| • | Three Formula-Based Forecasting Resources |
| • | Walkthrough: Creating Forecasts from Existing Data |
| • | Demonstration: Analyzing Data by Using Goal Seek |
| • | Exercise: Establishing Targets by Using Goal Seek |
| • | Tips and Tricks for Formula-Based Forecasts |
After completing this module, students will be able to:
| • | Build a FORECAST formula. |
| • | Create forecasts based on best-case, middle-case, and worst-case scenarios. |
| • | Establish target values by using Goal Seek. |
| • | Implement tips and tricks for formula-based forecasts. |
Module 3: Forecasting Using Moving Averages
This module introduces moving averages and shows how to calculate and chart averages in Excel. The information in this module also places moving averages in the context of a business that is not affected by strong seasonal business cycles. One example is a toy manufacturer, which might have trouble forecasting sales that cross one or more gift-giving seasons.
Topics and Activities
| • | What Is a Moving Average? |
| • | What Decisions Do Moving Averages Help Me Make? |
| • | Walkthrough 1: Calculating and Charting a Moving Average |
| • | Walkthrough 2: Creating an Interactive Chart |
| • | Tips and Tricks - Refining Moving Average Analysis |
After completing this module, students will be able to:
| • | Define moving averages. |
| • | Describe the decisions that moving averages enable. |
| • | Calculate and chart moving averages. |
| • | Make a chart interactive. |
| • | Implement tips and tricks for using moving averages. |
Module 4: Maximizing Profit Potential
This module introduces Net Present Value and Internal Rate of Return, two factors often used to project product viability. The module then introduces Solver, a tool used to find the maximum (or minimum) output for a given set of constraints. The final exercise in this module shows you how to use Solver to find the most profitable mix of products to manufacture.
Topics and Activities
| • | Net Present Value and Internal Rate of Return |
| • | Walkthrough 1: Calculating Net Present Value |
| • | Walkthrough 2: Calculating Internal Rate of Return |
| • | Introduction to Solver |
| • | Walkthrough 3: Determining Optimal Project Mixes |
| • | Discussion: Best Practices for Defining Problems in Solver |
| • | Summarizing Data Forecasting Benefits |
| • | Next Steps |
After completing this module, students will be able to:
| • | Calculate Net Present Value. |
| • | Calculate Internal Rate of Return. |
| • | Describe Solver and the problems it helps to resolve. |
| • | Solve profit-maximization problems using Solver. |
About the Author
Curtis D. Frye is the author of numerous books on Excel, most notably Microsoft Office Excel 2003 Step By Step, a best-selling book from Microsoft Press. Through his company, Technology and Society, Incorporated, Curt consults for clients in the publishing and entertainment industries.
Take This Training
|