Modeling Flashcards Preview

Pace > Modeling > Flashcards

Flashcards in Modeling Deck (26):


Givens - need numbers can't use variables. Use "name manager" for better model - formulas are easier to follow by others.


Profit Analysis Model (e.g. T-Shirts)

Demand vs. Ordering - Profit analysis. Find profit using IF/THAN formula for revenue...Profit = Revenue - Cost

Revenue = IF(Demand > Orders, Price "before tournament*demand, Price "before"*demand + price "after" * (Orders - Demand)

Costs = Fixed + Variable Cost * Orders


Projecting Costs Model (e.g. Bookshelves - Oak and Cherry)

Cost of parts and labor will go up per year - Project Costs using spreadsheet for years to come starting with year 0.

Inputs - costs and rates of inflation
Spreadsheet - costs each year add rates COST from previous year*(1+$Inflation$Rate)
Graph to show projected unit costs


Breakeven Analysis Model (e.g. Catalogs)

Using What/If Analysis to see how sensitive model is to inputs. E.g. how sensitive are profits to response rate and where is the break-even

Use inputs (fixed and variable) to find revenues and costs based on a response rate, calculate profit
Create model to use What/If analysis - one column for the sensitivity factor (response rate) and one column for profits, leave space in between headers, enter profit in that row, What/if analysis - column input cell is response rate


Ordering with Quantity Discounts (e.g. Bookstore)

1. Inputs and range names
2. Revenues - use MIN and IF functions
3. Total ordering cost - use v-lookup formula
4. Profit
5. Two-way data table - row input - demand, column input - quantity
6. Expected profit - sumproduct(two way data table at each qty, Probabilities)
7. Choose highest expected profit


Estimating Sensitivity of Demand (e.g. Golf Clubs)

- Graph price versus demand to find the best type of function fit (linear, power, exponential) - whatever has the highest R-squared
- Make profit model using equation components as part of the inputs - this will predict demand by creating a formula and plugging in X - price in this case
- Revenue = predicted demand * price
- Cost = predicted demand * cost
- Profit = revenue - cost
- Calculate one-way What/If table to find max profit at given prices


Functions and their Equations

1. Linear: y = ax+b (line)
2. Power: y = ax^b (curve except when b = 1) - when x changes by 1%, y changes by a constant b%
3. Exponential: y = ae^bx (curve) - when x changes by 1 unit, y changes by a constant 100 x b%, e = 2.7182



Absolute Percentage Error = (observed demand - predicted demand)/observed demand
Mean of those values = MAPE
Smallest MAPE = best fit


Optimization Modeling

1. Label model and skip a line
2. Identify the decision variables - how many of each to make, how much to invest in each, etc.
3. Identify objective function (e.g. profit, etc.)- what to you want to max/min - sumproduct(changing cells, coefficients)
4. Constraints - name them
5. Changing Cells - give them a range name
6. Next to constraints - "Actual" = sumproduct(constraintvariables, changing cells) for each contstraint
8. Slack = available - actual


Solver - Basic Function

1. Objective = objective
2. Changing variable cells = changing cells
3. Constraints - cell reference = actual, constraint = number (270 and 300)
4. Select Solving Method = Simplex LP
5. Always click "Make Unconstrained Variables Non-Negative"


Sensitivity Analysis

Sensitivity is how a solution changes relative to the changing inputs. Can use Solver or Solver Table (one-way and two-way tables) to get a more accurate and comprehensive result


Reduced Cost

has to do with only non-basic variables (i.e. variables assigned a zero in final value) - how much better that coefficient must be before that variable enters at a positive level or how much the total profit will go down if you squeezed in one unit of that variable


Allowable Increase/Decrease (variable cells)

How much each coefficient of the objectives can increase or decrease before the optimal product mix would change


Shadow Price

how much I'll be getting from an extra dollar of that scarce resource (or much less from one less dollar). i.e. When the right-hand side of a constraint changes by a unit amount.
However, when there are linkages, solver might not be correct - better to do it with solver table


Algebraic Model (e.g. Welte Mutual Funds)

Objective function and constrains derived from "rules" of the question. e.g. Neither variable, A or B, should receive more than 50% of the investment of 100,000. The constraint would be A + B <= 50,000.


Integer Constraints (e.g. Advertising)

Forcing the changing cells to have integer values. Add another constraint (changing cells = integer).
In advertising model - if you want to maximize the surplus, make surplus the objective function and one of the constraints that the minimum cost = itself.


Scheduling Model (e.g. Post Office)

Key to the solution is the number in the changing cell is the locked number in each of the corresponding days (e.g. Monday "begin on" changing cell is B14, then on Monday column, each row for 5 days would be $B$14)
- Objective is total number of workers (sum of changing cells) - want to minimize it
- Constraint = total each day is >= required


Dynamic Investment Model (e.g. Barney Smith)

- Decision Variables - how much to invest in each
- Inputs - initial amt to invest, max per investment, interest rate on cash
- Matrix for outgoing cash
- Matrix for incoming cash
- Constraints (e.g. can't invest more than $X in any one)
- Cash Balance Calcs/Constraints - shows "checkbook" - cash comes in - you add, cash goes out - you subtract = Cash After Investing
Beginning cash = ending cash left over and you invest at interest rate
- Want to maximize Final Cash


Solver Table

Input = variable you want to test at a given range
Output = HOLD CONTROL (changing cells; objective cell)


Binary Programming (e.g. Tatham)

Type of integer programming.
Binary values (0,1) - question isn't how much to invest but SHOULD you invest. Invest = 1, Not Invest = 0
Uses NPV (present value - initial investment)
Have to add constraint (changing cells = bin)


PV vs. NPV

Calculate present value (PV) using NPV function (NPV(rate, sum of inflows)) but to calculate NPV, take PV and subtract initial investment.


Nonlinear Optimization

Reasons models are non-linear:
1. non-constant returns to scale - effect on output is non-linear (e.g. diminishing returns)
2. Profit is usually expressed as price times demand - demand is usually a function of price = non-linear

Convex ("smiling face")
Concave ("frowning face")

power function - constant elasticity of demand Y =ax^b


Nonlinear Optimization Model (e.g. Madison)

- Graph points on scatterplot, trendline, find equation that fits the best (R-squared), show equation to get the coefficient/exponent or slope/intercept
- Get max profit using solver by calculating demand (using coefficient/exponent or slope/intercept), price (demand*price - pick a price), cost (demand*cost)
- Use what/if to graph all points


Local Optimization vs. Global Optimization

When there are more than one peak in a non-linear graph, the highest point of all the peaks is Global Optimization. Each of the other peaks are Local Optimization.


Alphas & Betas

x = rate of change of the "market" (independent variable)

y = rate of change of investment (dependent variable)

Listing of all the x's and y's, can determine the least-squares regression line (y = alpha + beta*x)

Alpha = risk-free rate of return (occurs when the x value is movement = no risk)
Beta = risk of investment relative to the market as a whole (slope of the line...if x increases by 1, y increases by beta...shows which is riskier - market or investment)


Estimating Beta Model (e.g. Stock Beta)

1. Calculate Returns - e.g. (Sept '07 - Aug '07)/Aug '07) = market return (independent variable) and stock return
2. Alpha, Beta - Data set manager, define data set, regression & correlation, regression, x variable is market, y variable is the rate of return, run
3. Look at coefficients - alpha's is the risk-free rate of return, beta's is compared with 1...if it's >1, greater than the market, >1, less than the market