Flashcards in Modeling Deck (26):

1

## Inputs

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

2

## 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

3

## 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

4

## 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

5

## 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

6

## 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

7

## 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

8

## MAPE

###
Absolute Percentage Error = (observed demand - predicted demand)/observed demand

Mean of those values = MAPE

Smallest MAPE = best fit

9

## 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

10

## 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"

6. SOLVE

11

## 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

12

## 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

13

## Allowable Increase/Decrease (variable cells)

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

14

## 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

15

## 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.

16

## 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.

17

## 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

18

## 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

19

## Solver Table

###
Input = variable you want to test at a given range

Min/Max/increment

Output = HOLD CONTROL (changing cells; objective cell)

20

## 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)

21

## 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.

22

## 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

23

## 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

24

## 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.

25

## 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 zero...no 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)

26