Business Analysis - EXAM 1
Goal Seek
A what-if analysis tool that finds the input needed in one cell to arrive at the desired result in another cell.
Error =
Actual - Forecast
Seasonal Index
Actual Sales / sales if no seasonality
Adding a Trend Line
Add "Chart Element" in the 'Design' tab when working with a chart in Excel
A moving average can be used to smooth which of the following? A) cyclical variation B) seasonal variation C) irregular variation D) All responses are correct
D) All responses are correct
Regression can be used to predict a variety of _____________________ variables with many possible ______________________.
Dependent; Independent
When there is NO SEASONALITY...
EACH demand will be the same - average of the "actuals"
The quarters where the bar went ABOVE the trend line had...
GREATER THAN AVERAGE demand, therefore their index would be GREATER than 1
The quarters where the bars were BELOW the trend line had...
LESS THAN AVERAGE demand, therefore their index would be LESS THAN 1
If the regression line is really bad at predicting data points...
R^2 will be ZERO because SSR will be ZERO
One of the most important types of data analysis is...
Regression
SST =
SSR + SSE
The better the line, the bigger ______ and less ________.
SSR; SSE
____________________ could be a factor no matter what forecasting method is using
Seasonality
When using ___________________ as your __________________ variable, convert the actuals dates into numbers that can be used to create the regression equation.
Time; Independent
Simple Exponential Smoothing Equation
a * previous period (ACTUAL) + (1 - a) * previous period (FORECASTED) - if there is no "previous period" forecast, USE NAIVE
Business Modeling
a business model helps managers to explore complex choices using a set of assumptions to represent alternative future operative environments
Regression is...
a causal forecasting method
Business Analysis
a research discipline of identifying business needs and determining solutions to business problems
Delphi Method
a structured communication technique originally developed as a systematic, interactive, forecasting method, which relies on a panel of experts
Calculating Sum Squared Error (SSE)
add together difference between actual and regression line, then square it
Calculating Sum Squared Regression (SSR) ~ green line
add together difference between forecast and regression line and square it
Seasonality is like an "_____________ - __________" or adjustment to the forecast
add-on
Vlookup
allows you to look for a value in a column, then when you find a match or it falls in a range, return a value from another column
If Statement
allows you to test for something and then tell Excel what to do if it is true or false - test to see if something is <, >, =, <=, =>
Trend
an increase or decrease in data that continues over time
Grassroots Method
based on the concept of asking people who are closest to the eventual customer their opinion
What does this arrow do in excel?
bottom right of cell to continue a value or pattern
Random Fluctuations
changes that DON'T reflect a pattern
Consensus Panels
consist of a panel of people selected for their expertise and other characteristics deemed to be relevant
Seasonality
data repeats/changes based on seasons
The Naive Method works better when...
demand is stationary OR when there is NO pattern of increasing/decreasing demand
Y
dependent variable; the "thing" we are trying to predict
Another word for error is...
deviation
Excel Sheet example of Excel Regression:
df SS MS Regression. # SSR --> # # Residual # SSE --> # MSE --> # Total # SST --> #
Time (YEARS)
for cyclical
NEGATIVE Errors
forecast is TOO HIGH
POSITIVE Errors
forecast is TOO LOW
Squared Errors
highlights having larger areas
R and R^2 tells you:
how good the line represents the data
X
independent variable
Heuristics (rule of thumb)
intuitive judgement, common sense, and best practices
least square regression
method for finding a line that summarizes the relationship between two variables.
A bias of -10 means your method is _____ forecasting
over
"a" in Simple Exponential Smoothing
reflects which you think is more important; the previous actuals or the previous forecast - cannot be less than 0 or greater than 1
Cyclicality
represents a pattern that repeats over a number of years
N-Period Moving Average
sum of N periods / N
Time (MONTHS)
sum to 12
Time (WEEKS)
sum to 52
Qualitative Forecasting
techniques are subjective, based on the opinions and judgement of consumers or experts
Sum Squared Error (SSE) ~ yellow line
the difference between the data point (actual) and what the regression line tells us the data point should be
Error
the difference between what actually happened and what your forecast predicted
Root Mean Squared Error
the error term "loved by data analysts" - also called Standard Error
Median
the middle value when data is sorted
forecasting
the process of making predictions of the future based on past and present data and analysis of trends
Seasonal index should add up to:
the total number of periods EX --> periods = 4, SO seasonal index total = 4
If our regression equation perfectly predicts our data points...
there will be NO SSE, therefore SSR will EQUAL SST and R^2 will be 1
What does this arrow do in excel?
to block off a bunch of values that you then can: copy, delete, move
What does this arrow do in excel?
to move 1 or more values to a different cell
Mean Absolute Value
treating positive and negative errors the same; what is the average amount of error?
What is Heuristics used for?
used to forecast FUTURE data as a function of past data
Sum of Squared Regression (SSR) ~ red line
we want to see how close, compared to other alternatives, the regression line is getting us to our individual data points
Absolute Percent Error
what percent your error is compared to what actually happened
least square regression equation
y = a + bx
How to get the value for the regression line?
y = mx + b - m = slope or rise/run - b = the y-intercept
Using the trend line to create a forecast:
y = the demand for product/service x = the time period we want to create a forecast for
Occam's Razor
"all things being equal, the simplest solution tends to be the best one."
=VLOOKUP
(value looking for, table containing where you are going to find match and return value, column return value is in, exact match or range)
Moving Average
- N Period - Weighted
Types of Heuristic Forecasting:
- Naive - Moving Average - Simple Exponential Smoothing
Steps to Seasonality (using average seasonal index)
- Starting with the historical data, you would... 1. create an average seasonal index 2. take the seasonality out of your historical data so you can see what it would look like with NO seasonal trend 3. use this data WITHOUT seasonality, run a regression to get a forecast that includes the trend (but NO seasonality) 4. use the seasonal index (step 1) to adjust the trend (regression) forecast (step 3) to create a seasonally adjusted forecast
A model is...
- an analogy - compares - explains & predicts - recipe ~ a representation of something, but seldom a perfect representation
Input vs. calculated data
- input is data that is manually typed in - calculated data = formulas
Why use models?
- make inferences about the real situation by studying and analyzing the model - save money - save time - aid decision making
Types of Causal Forecasting:
- regression
Weighted Moving Average
.5 * (period 1) + .3 * (period 2) + .2 * (period 3) - periods that are MORE CURRENT are weighted 'heavier' or with more importance
Coefficient of X tell you have much ______ unit change in X will change Y
1
How to find Squared Errors?
1. Error = actual - forecast 2. take the absolute value ~ -7 = 7 3. square the absolute value of the error ~ -7 = 7 = 7^2 = 49
How to find the Absolute Percent Error?
1. Find the Absolute Value of error 2. Divide by the actual
Types of Quantitative Data
1. Heuristic or Time Series 2. Causal
Types of Business Models:
1. Physical Models 2. Symbolic Models ~ math formulas ~ language
How to find Mean Absolute Deviation
1. SUM of the absolute values of the NEGATIVE errors PLUS the SUM of all POSITIVE errors 2. DIVIDE the total by the number of errors calculated
4 Components of Time Series Data
1. Trend 2. Seasonality 3. Cyclicality 4. Random Fluctuations
Calculate Mean Absolute Percent Error:
1. absolute value of error / actual 2. absolute percent error / total # of errors
Average Season Index (multiple years)
1. adds all "seasonal indexes" for the same quarter/period ~ (QT1, 2010) + (QT1, 2011) + (QT1, 2012) 2. divide the sum by number of quarters/periods (find the mean) ~ (sum of (QT1, 2010; QT1, 2011; QT1, 2012)) / 3 or # of QTs
How to create a forecast when seasonality impacts demand
1. calculate the "seasonal index" 2. take out seasonality (de-seasonalize forecast) 3. forecast underlying demand (can use regression, simple moving average, naive, SES, etc.) 4. add seasonality back into the forecast
Seasonal Forecast --> seasonal index using seasonality
1. divide "demand forecasted for the year" by the number of quarters 2. multiply result of Step 1 by the already calculated "seasonal index"
How to find Bias
1. error = actual - forecast 2. Add up all errors (keeping negatives negative) 3. Then take the average
Calculate Mean Squared Error:
1. error = actual - forecasted 2. error ^2 3. total of the sum of all squared errors 4. divide by number of periods
Calculate Root Mean Squared Error:
1. error = actual - forecasted 2. error ^2 3. total of the sum of all squared errors 4. divide by number of periods 5. square root of total after division
Problem Solving Process
1. identify what is the problem 2. develop model/acquire data 3. do the results look right? / what is the model telling you? 4. make decision / implement model
Process for Forecasting using TIME SERIES:
1. look at the data - convert data to graph 2. Find appropriate models to test with data 3. Evaluate the model - based on historical data
Calculating Sum Squared Total (SST)
1. look at the error between our "historical" data and our "forecasted" data 2. sum all together (error)^2 -NOTE --> square the difference BEFORE adding together
Keys to improve usability and reduce errors are:
1. plan out your spreadsheet 2. separate input data and calculated fields 3. data should only be entered once, after that you reference it 4. calculated fields (formulas) should contain only cell references 5. use formatting, descriptive labels, and comments
Seasonality in EXCEL
1. seasonal index = actual/(demand without seasonal impact) 2. demand w/o seasonality = actual / seasonal index 3. use the de-seasonalized data to create a forecast 4. demand without seasonal impact * seasonal index = FORECAST W/ SEASONAL IMPACT
Sales is in Units of $1000.00 and Advertising is in Units of $100. What does the value of sales INCREASE in dollars when we increased advertising by one UNIT($100)? The equation is: Y=45.9+11,359.00X. This is asking for the slope converted into Sales units.
= 11,359.00 * 1000 ANSWER= 11359000
R^2
= SSR / SST
R
= square root of R^2
What function lets you test if something is true or false and then do different things depending on which result you get? (include the = and (), but don't need to include the conditions)
=if()
IF statement formula
=if(what testing for, what to do if true, what to do if false)
