Inputs
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 tournamentdemand, 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)
- Inputs and range names
- Revenues - use MIN and IF functions
- Total ordering cost - use v-lookup formula
- Profit
- Two-way data table - row input - demand, column input - quantity
- Expected profit - sumproduct(two way data table at each qty, Probabilities)
- 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
- Linear: y = ax+b (line)
- Power: y = ax^b (curve except when b = 1) - when x changes by 1%, y changes by a constant b%
- Exponential: y = ae^bx (curve) - when x changes by 1 unit, y changes by a constant 100 x b%, e = 2.7182
MAPE
Absolute Percentage Error = (observed demand - predicted demand)/observed demand
Mean of those values = MAPE
Smallest MAPE = best fit
Optimization Modeling
- Label model and skip a line
- Identify the decision variables - how many of each to make, how much to invest in each, etc.
- Identify objective function (e.g. profit, etc.)- what to you want to max/min - sumproduct(changing cells, coefficients)
- Constraints - name them
- Changing Cells - give them a range name
- Next to constraints - “Actual” = sumproduct(constraintvariables, changing cells) for each contstraint
- Slack = available - actual
Solver - Basic Function
- Objective = objective
- Changing variable cells = changing cells
- Constraints - cell reference = actual, constraint = number (270 and 300)
- Select Solving Method = Simplex LP
- Always click “Make Unconstrained Variables Non-Negative”
- SOLVE
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
Min/Max/increment
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:
- non-constant returns to scale - effect on output is non-linear (e.g. diminishing returns)
- 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 (demandprice - pick a price), cost (demandcost)
- 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.
