Business Analysis - EXAM 1

Lakukan tugas rumah & ujian kamu dengan baik sekarang menggunakan Quizwiz!

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)


Set pelajaran terkait

Proof Writing in Discrete Mathematics Test 1

View Set

6 Glial (or Neuroglia) Cells: Location, Description, Function (Exam 3)

View Set

1 Fundamentals of Electricity: Unit 1 - Atomic Structure

View Set

Chapter 3 sensation and perception

View Set

Unit 23: GA Laws, Rules, and Regulations Pertinent to Life and Accident and Sickness Insurance

View Set

Chapter 27: Lower Respiratory Problems

View Set

Essentials of investment Chapter 6

View Set