What are 4 qualitative Forecasting techniques?
Delphi methods / Jury of Executive opinion / Sales force composite / Consumer market survey
What are 4 time series forecasting techniques?
Moving average / Exponential smoothing / trend projections / decomposition
What are 2 casual method forecasting techniques?
Regression analysis / Multiple regresssion
Describe time-series models in detail. What do they do? What does it use? What factors does it ignore?
They predict the future based on the past, using historical data on one variable it extrapolates the past values of a series.
They ignore factors such as the economy, competition, or selling price
What are the components/possible components of a Time-series model? How do the possible components appear visually?
Trend (T) - values adhere to a visual trend
Seasonal (S) - values appear to have similar changes in concurrence with a seasonal cycle
Cyclical (C) - values have a visual cycle - could be seasonal but not necessarily
Random (R) - values are all over the map
What are the four measures of Forecast accuracy? What is the purpose of these measuring forecast accuracy?
MAD - Mean absolute deviation
MSE - Mean Squared error
MAPE - Mean absolute percent error
Bias - average error
These measures allow comparison of actual values with forecasted values to compare models
What would likely have not happened when creating a forecasting model that would make computing the MAD informative? How do you compute MAD (Mean Absolute Deviation?
The forecast was based on a naive model (ie. Used immediate past data to predict current data).
To calculate MAD
1. Subtract A - F
2. Sum all of the values obtained
3. Divide this sum by the number of total values
4. This is the MAD
How do you compute MSE (Mean squared Error)?
1. Subtract the forecast value from the actual value for each value. (A - F)
2. Square the result for each value
3. Add the total of the squares
4. Divide this total by the total number of values that were operated on
5. This is the MSE
How do you compute MAPE (Mean absolute Percent Error)?
Find the error of each line by subtracting the forecast value from the actual value ( a - f)
1. For each line, divide the error (numerator) by the actual value
2. Sum all of the results
3, Divide this sum by the total number of lines
4. Multiply this result by 100%
5. This number is the MAPE
How do you calculate a moving average forecast?
1. Add up the number of lines that are to be averaged. (example - for a three month moving average, add the first three months)
2. divide the sum by the total number of lines added. (example - for 3 months, divide the sum of the first 3 by 3.
3. This number is the moving average - it is placed by the 4th line in our example
4. Populate the remaining lines in a similar manner, moving down a line each time
How is weighted moving average calculated?
Similar to moving average, except each value is multiplied by an assigned weight.
Example: (weight value(expressed as .5) x value) + (weight value x value) + (weight value x value) = WMA
What is exponential smoothing? What is the alpha? What is the range of alpha values?
It is a type of moving average that is forecasted as a function of the previous actual and forecasted value.
Alpha is a weight or smoothing constant. Alpha value will range between 0 and 1
How do you apply exponential smoothing?
1. Using the previous periods values - subtract actual from previous
2. Multiply the number obtained by your alpha value
3. Add this value to the previous forecasted value
4. This value is the forecast value
Determining the correct value for a smoothing constant is a matter of trial and error, true or false?
This is true
If trend is present in a time series forecasting model, what must be accounted for?
The trend must be accounted for
Do exponential smoothing and simple average respond to trends? What two common techniques can be used if trend is present?
No they do not. If trend is present then use exponential smoothing with trend or trend projection (trend line)
How can trend equations be developed? What is the simplest way to develop the linear model?
They can be developed based on exponential or quadratic models. The simplest method is using a linear model developed using regression analysis
What are the components of trend equation?
Y = the expected value (the result)
The first value in the equation is the intercept
The values after slope are the slope variables multiplied by the independent variable (x).
What would indicate that a trend line may need to be seasonally adjusted?
Recurring variations over time
What is a seasonal index? What do index values represent (ie. Index of 1, i>1, or i<1)?
A seasonal index indicates how a particular season compares to an average season.
1 = an average season
Less than 1 = a season lower than average
More that 1 = a season higher than average
What are the steps to introducing a seasonal index to a forecast?
- Deseasonalize the data
- Create a forecast
- Reseasonalize the data
How are seasonal indices calculated when there is no trend?
1. Find the average value for the entire data set. (example - add all 24 months and average)
2. Then average each month (example - add both Septembers and average them)
3. Divide these two numbers together to obtain seasonal index
What approach is taken to creating seasonal indices when trend is present? What does this approach prevent?
Center moving average should be introduced. This will prevent trend from being interpreted as seasonal.
How is the CMA calculated? What does the CMA represent? How the seasonal index obtained from the CMA?
1. Decide on the number of periods to be represented by the average (ie. Year, quarter, month)
2. Assume you decide on quarter. Then work from the month to be represented and expand left 2 values, taking the .5 of the furthest value, and expand right 2 values taking .5 of the furthest value
3. Sum these values and divide by 4 (for the quarter)
4. This is the CMA
If two seasonal ratios are present for the same time period(two months of September, or 3 of the Q1 from different years) what is done to combine them?
How is data deseasonalized?
By dividing each observation by the appropriate seasonal index
How is data re-seasonalized?
Deseasonalized forecasts are multiplied by the seaonal indices
What variables are represented on each axis of a scatterplot?
Independent variables go on the x-axis (months, days, etc)
Dependent variables go on the y-axis (the variable to be forecasted)
What are the requirements of a linear programming problem?
They will have four properties in common:
1. There is a need to maximize or minimize some quantity (the objective function)
2. There are restrictions or constraints present
3. Alternative courses of action are available
4. Linear equations or inequalities are present
What are the steps in formulating an LP Problem?
1. A mathematical model must be developed, so:
2. Completely understand the problem being faced
3. Identify the objective and the constraints
4. Define the decision variables
5. Use the decision variables to write mathematical expressions for the objective function and the contraints
What is a common problem where LP can be applied?
Product mix. Two or more products are produced using limited resources.
Profit must be maximized based on the profit contribution per unit of each product to determine how many units of each product to produce.
What are the properties/assumptions of a Linear Programs?
- One objective function
- One or more contraints
- There are alternative courses of action
- The objective function and contraints are linear
- There is certainty
- There is divisibility - activity units can be divided into any fractional levels
- Nonnegative variables
In LP, What is the objective function? What are it's components? Where are they found in excel solver?
The objective function is the value for which the max or min will be sought. It is composed of the sum of all profit/cost per item in product mix.
It will be located under a main heading like profit or cost.
For example: the objective function would be units produced times profit per each - using the sum product function
In LP, What are the decision variables? What are their components? Where are they found in excel solver?
Decision variables are the amount of each product that is to be produced. Solver will fill these in during its process.
They are normally found right at the top. These values multiplies by the contraints hours to determine the contraints used.
In LP, what are constraints? What are their components? Where are they found in excel solver?
Contraints are factors that must be taken into account. For example. A lamp takes x hours of such and y hours of such.
The bottom half. The will have a < or > relationship set to them on their RHS
In LP, What is slack?
The amount of a resource that is not used for a less than or equal to constraint.
Amount available minus amount used = slack
In LP, What is surplus?
Is used with a greater than or equal to constraint to indicate the amount by which a RHS is exceeded.
Actual amount used minus minimum amount = surplus
In LP, What are 2 graphical solution methods?
The Isoprofit and cornerpoint methods
What is sensitivity analysis? Why is it useful? What does it involve?
It analyzes how sensitive a deterministic solution is to changes in the assumptions of the model. Involving a series of what if questions concerning contraints and variables it is the preferred way of post optimality analysis
In a Solver sensitivity report: a) What do the names presented represent? B) what is meant by allowable decrease/increase? What happens if the allowable amounts are exceeded?
A) The names are taken from combined text post solver
B) This is the amount that the variables can be changed so that the objective function will still remain optimal. If these amounts are exceeded than the solution is no longer valid and must be resolved.
In a solver sensitivity report: a) what is shadow price? What is reduced cost?
Shadow price is the profit that can be gained/lost by changes in the constraint value
Reduced cost is the profit that can be gained/lost by changes in the units produced.
What is a simulation? What is the purpose?
A descriptive modelling techique
A simulation is one of the most widely used quantitative analysis tools. It tries to duplicate the features, appearance, and characteristics of a real system. Can be mathematical, physical, or virtual.
It allows the testing of specific values over a specific period of time. To test their impact on the dependent variable.
When is a simulation a good method to find a solution to a problem?
When a problem is complex
When a problem does not conform to assumption required in other modeling techniques.
What are the steps in the simulation process?
1. Define the problem
2. Introduce Important variables
3. Construct a simulation model
4. Specify values of variables to be tested
5. Conduct the simulation
6. Examine the results
7. Select the best course of action
Can a simulation be time independent or dependent?
It can be both. Time dependent could be waiting lines...time independent could be inventory levels.
What are disadvantages of simulation?
- No optimal solution is guaranteed
- Can be a costly modelling process
- Solutions or inference are not transferable to other situations
- Validations can be difficult
What are advantages of simulation?
- Theory is straightforward and easy to understand
- Handles wide variation of problem types
- Allow manager to experiment with different alternatives
- Can include real life complexities
What are the elements of a simulation with excel?
A probabilty table
- The probabilty of an event / the range of that probabilty(lower) / the cumulative probability (upper range)
A simulation record
- Listed events / a random number / the simulated result taken from the random number
What are the main excel formulas used in a simulation model in excel?
Rand() - used in the sim to generate a number between 0 and 1
=VLOOKUP(G6,$B$3:$D$8,3, TRUE) - this will refer to the random number then relate it to values in a column to determine simulated demand
=FREQUENCY(H3:H12,A16:A22) - will report on the frequency of events in a simulation
When running a simulation, What are good methods to establish probabilty distributions?
Historical outcomes can be used to establish a probabilty distribution. Also, managerial estimates based on judgement and experience can also be used
When building a simulation, what is the purpose of building a cumulative probability distribution for each variable?
The cumulative value is the probability that a value will be less than or equal to a value
When building a simulation, why assign random numbers? What do they accomplish? What is the range? What do they signify?
The numbers represent values or an outcome. The range of numbers will correspond exactly to a particular outcome.
How are random numbers generated in a simulation?
- With a computer
- Roulette wheel or manually
- Random number table
When running a simulation, how do the random numbers interact with probability range?
The random number will fall in a range in the table. The demand value that corresponds to this range will be the simulated demand.
What are common variables in real-world inventory situations?
Common real world variables are lead time and demand
What is a DBMS? What are some examples of DBMS?
Database Management System: A software that enables users to define, create, maintain, and control access to the database.
Example: MS Access, MS SQL Server, Oracle, MySQL, IBM DB2
In DBMS, what is end-user data?
Raw facts of interest to the end user.
In DBMS, What is Metadata?
Data about data (description of the data). Provides a description of data characteristics and relationships in data
What are 5 different ways databases can be broken into type? With examples?
1. Single user vs. Multiuser (number of users)
2. Centralized vs. Distributed (location)
3. Operational vs. Analytical (Expected type and extent of use)
4. Structured data vs. Unstructured data (NoSQL - Facebook)
5. Basis of design (Hierarchal, network, relational, object-oriented)
What are 3 database models? With description?
- Hierarchal database model - information is organized into a tree-like structure using parent/child relationships. In such a way that it cannot have too many relationships.
- Network Database model - A flexible way of representing objects and their relationships
- Relational database model - stores information in the form of logically related two-dimensional tables
What is the ERM (Entity Relationship Model)? What does is depict?
A widely accepted standard for data modelling.
Uses ERD (Entity relationship diagram) for graphic representations to model database components; entities are mapped to a relational table.
What are the three ERD types of ERM relationships? With examples?
Chen Notation: Uses boxes and diamonds
Crow's foot notation: uses boxes with headers and lines with spokes and dashes
UML notation: uses columns in boxes with lines and text
In ERD drawing an ERM relationship, how would a One-to-many(1:M) relationship be depicted?
For Chen - see pic
For Crow's - see pic
For UML - see pic
In ERD drawing an ERM relationship, how would a Many-to-many(M:M) relationship be depicted?
For Chen - see pic
For Crow's - see pic
For UML - see pic
In ERD drawing an ERM relationship, how would a One-to-One(1:1) relationship be depicted?
For Chen - see pic
For Crow's - see pic
For UML - see pic
In building an ERM, What is an entity? Examples?
It is anything about which data are to be collected and stored. Refers to entity SET AND not to a single entity.
Will be the rows in a diagram: Will be a noun: CUSTOMER, STUDENT, ACCOUNT, etc.
In building an ERM, what is an attribute? examples?
The characteristics of an entity. In a database this can be required or optional
Will be the columns in a diagram: STUDENT FIRST NAME, STUDENT LAST NAME, STUDMAJOR, etc.
In building an ERM, What are characteristics of some different types of attributes?
Attributes can be identifiers (primary keys) that maintain entity integrity. These are one or more attributes that uniquely ID each entity instance
Attributes can be foreign keys that maintain referential integrity. This means that values in a column/attribute in one table/entity match values in a source table attribute.
In building an ERM. What are two types of keys? What do they ensure?
Primary Keys - maintains entity integrity
Foreign keys - maintain referential integrity
In building an ERM, what is a relationship? With examples?
A relationship is the association between two entities - it always operates in both directions.
Ie. A CUSTOMER may generates many INVOICES, but each INVOICE is generated by one CUSTOMER
In building an ERM, what is connectivity and Cardinality? How are they depicted?
Connectivity: Describes the relationship classification
Cardinality: Expresses the minimum and maximum of entity occurrences associated with one occurrence of related entity.
What are the basic components of MS Access, What does each component do?
Tables: The basic building blocks, where all data is stored
Forms: these simplify data input and maintenance
Queries: How you retrieve information from the database. It links tables through primary and foreign keys.
Reports: Makes output from tables or queries "shareable"
What is decision theory? What are components of a good decision?
It is an analytic and systematic approach to the study of decision making.
A good decision is one that is based on logic, considers all available data and possible alternatives, and applies a quantitative approach.
What are the six steps in Decision Making?
1. Clearly define the problem at hand
2. List the possible alternatives
3. Identify the possible outcomes or states of nature
4. List the payoff (typically profit) of each combination of alternatives and outcomes
5. Select one of the mathematical decision theory models
6. Apply the model to make your decision
What are the elements of a decision table? Describe each?
The left column will display alternative courses of action.
The rows will refer to states of nature
The fill in the rows will be the result of an alternative given the state of nature
In a decision table, what the independent decision variables? What are the dependent variables?
Independent variables are the states of nature. The dependent variables are the alternative courses of action
What is a state of nature?
A possible event that the decision maker cannot control
What are 4 zones of decision making? Describe each
Ignorance - absolutely nothing is known or sought
Uncertainty - the decision maker does not know the probabilities of the various outcomes
Risk - The decision maker knows the probabilities of the various outcomes
Certainty - the decision maker knows with certainty the consequences of every alternative or decision choice
Knowledge increases with each step
What a five possible criteria that can be used for making decisions under uncertainty?
- Maximax (optimistic)
- Maximin (pessimistic)
- Criterion of realism (Hurwicz)
- Equally likely (Laplace)
- Minimax regret
How would an optimistic decision be made?
The decision that would maximize the maximum payoff.
Locate the maximum payoff for each alternative
Select the alternative with the maximum number
How would a pessimistic decision be made?
The decision that maximizes the minimum payoff
Locate the minimum payoff for each alternative
Select the alternative with the maximum number
How would a decision be made using the criterion of realism (hurwicz)?
Use when a compromise between optimism and pessimism is desired.
Select a co-efficient of realism (0 is perfectly pessimistic to 1 is perfectly optimistic)
Compute the alternatives uses the co-efficient and sum them
Select the alternative with the highest value
How would a decision with equally likely outcomes be determined? (Laplace)
Considers all the payoffs for each alternative
Find the average payoff for each alternative
Select the alternative with the highest average
How would a decision be made when consideration of opportunity loss is important?
Minimax regret would be used
This is the difference between optimal profit and actual payoff for a decision.
When making a decision under risk, what methods that can be used? Will the two methods achieve different results?
Determine the expected monetary value (EMV) for each alternative. Choose the highest value to max or the lowest to min
Expected Opportunity Loss (EOL). Calculate the regret for each outcome and choose the smallest expected regret.
The two methods will result in the same choice.
What is the EMV method of decision making under risk?
Multiply each state of nature by its probabilty and sum
The sum is the EMV
What is EOL decision making? How is it performed?
The cost of not picking the best solution.
For each alternative, multiply the opportunity loss by the probability of that loss for each possible outcome and add them together.
Min EOL will always be the same as max EMV
In EOL will always equal EVPI
What is a decision tree? What are the main components? How are they graphically represented?
A graphical representation of a problem that has sequential or multi-period decisions.
Decision points: squares: points where the decision-maker must make a choice of alternative actions - alternatives are shown as branches from these points.
Chance points: circles: a chance event occurs at this point - one of a number of states of nature will occur - states of nature are shown as branches from these.
How are calculations made using decision trees?
1. Sketch out the tree starting from the left and working right with the payoffs at the far right
2. Then work right to left - multiply the payoffs by their probability
3. Sum the state of nature at their chance points
4. Select the highest sum and carry it over to the decision point, eliminate the other states.
5. Continue working left in this manner until the highest decision becomes evident.
What is EVPI? What does it do? What does it equal?
The expected value of perfect information. It places an upper bound on what you should pay for additional information.
EVPI = EVwPI - Best EMV
What is EVwPI? What does it represent?
Expected Value with Perfect Information. It is the long-run average return if we have perfect information before a decision is made.
How EVwPI calculated?
EVwPI = (best payoff for first state)(probability) + (best payoff for second state)(probabilty) + etc...
When EVwPI and EMV are known, how is EVPI calculated? What does this number represent?
EVPI = EVwPI - max EMV
The number obtained is the maximum that should be paid for additional information.
What is the purpose of SUMPRODUCT in excel? What is the Syntax?
Multiplies corresponding components in the given arrays, and returns the sum of those products.
SUMPRODUCT(array1, [array2], [array3], ...)
The SUMPRODUCT function syntax has the following arguments:
Array1 Required. The first array argument whose components you want to multiply and then add.
Array2, array3,... Optional. Array arguments 2 to 255 whose components you want to multiply and then add.
What is the purpose of VLOOKUP in excel? What is the syntax?
Use VLOOKUP, one of the lookup and reference functions, when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number.
In its simplest form, the VLOOKUP function says:
=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE).
What is the purpose of Rand() in excel? What is the syntax?
Returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.
The RAND function syntax has no arguments.
How does the analysis tool pak random number generator differ from RAND()?
These numbers do not change when F9 is hit
What is the purpose of FREQUENCY in excel? What is the syntax?
Calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because FREQUENCY returns an array, it must be entered as an array formula.
The FREQUENCY function syntax has the following arguments:
Data_array Required. An array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros.
Bins_array Required. An array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.
What is the purpose of IF in excel? What is the syntax?
The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect. In its simplest form, the IF function says:
IF(Something is True, then do something, otherwise do something else)
So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.