Table of Contents
General Information
Installation
Starting the Add-in
Creating a problem/sheet
Tabs and Toolbars
The Models
Toolbar (Preferences, colors, etc)
Solver
myomlab
Technical Support
Excel OM Modules
Aggregate Planning
Assembly Line Balancing
Assignment
Breakeven and Cost-Volume Analysis
Capital Investment
Center of Gravity
Decision Analysis
Factor Weighting
Forecasting
Inventory
Layout
Learning Curves
Linear Programming
Location
Lot Sizing
Material Requirements Planning
Productivity
Project Management
Quality Control
Reliability
Simulation
Statistics
Transportation
Waiting Lines
Work Measurement
Excel QM Modules
Assignment
Breakeven and Cost-Volume Analysis
Decision Analysis
Forecasting
Games
Inventory
Linear Programming
Markov Chains
Material Requirements Planning
Networks
Project Management
Quality Control
Simulation
Statistics
Transportation
Waiting Lines
Installation
Windows
The software installs in the same fashion as most Windows programs. The installation program will ask you for a directory name. The default directory is Program Files (x86)\ExcelOMQMv5 but you may change this if you like. After entering the name the installation will copy the files to that directory and a shortcut key will be placed on the desktop. The easiest way to start the program is to double-click the shortcut key.
One question in the installation is whether you have a 32-bit version of Excel or a 64-bit version of Excel. Please keep in mind that the question is about your Excel program, not your version of Windows. If you don't give the correct information the program will issue a "compile error" when you try to use it. The default value checked is usually correct.
Please note that this software may be installed on a network only if each student who uses the software has purchased a copy of the appropriate Pearson textbook.
When the installation is complete you may get started.
Mac
The program is presented as a ZIP file. SImply download the file, extract the files from the ZIP file and open the ExcelOMQMv5.XLA file that is in the folder. Be sure to open the file from the folder that contains the ExcelOMQMv5.LIC file.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Starting Excel OM/QM
There are a few different ways that Excel OM/Excel QM can be started. The easiest way in Windows is to click on the Excel OM/QM 5 icon that is placed on your desktop at installation. If you are already in Excel then you may Open the file ExcelOMQMv5.XLA to start this add-in.
What appears on your screen depends on the version of Excel you have and your security settings. You may get a message asking if you want to enable the Macros, answer YES. It is possible that the macros will be turned off by Excel and not even give you the opportunity to enable them. In this case do the following.
Excel 2003 and earlier
Go to Tools, Macros, Security and set the security to medium. Then close Excel and begin again.
Excel 2007
Please see the instructions for creating a trusted location that are included in the file named Security inthe program folder or posted at prenhall.com/weiss.
Using Excel OM/QM
Regardless of the version of Excel that you have, two menus, will be added to Excel. In Excel 2007 and later you can find these menus on the Excel OM/QM Tab on the Excel 2007 ribbon. In Excel 2003 and Excel 2017 (Mac) and earlier you will find these menus on the main Excel menu bar.
-----------------------------------------------------------------------------------------------------------------------------------
Add-ins
Excel OM/QM is an addin and it is possible (though I do not recommend it) to set up Excel so that Excel OM/QM automatically loads every time that Excel loads. The means for doing this depends on your version of Excel.
Please note that this product and the Crystal Ball addin may not coexist well. Do not have both loaded at the same time.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Tabs and toolbars
If you are running Excel 2007 or later on a PC or Excel 2016 on a Mac then a tab will be added to your Excel tabs. The tab will be named either Excel OM or Excel QM depending on the textbook you are using. If you are running Excel 2003 or Excel 2017 (Mac) the menus will appear on the Main Excel menu and the preferences and other options are available on a toolbar.
The Menus group of the tab contains two menus for creating the models. On PCs, one has the models in alphabetical order while the other has the models in order of the chapters of your textbook. Please notice that the bottom of the alphabetical menu has options to display only the OM models, only the QM models or both. On a Mac( Excel 2016) there is an OM menu and a QM menu.
The next group is used for copying data from myomlab and pasting the data into Excel OM models. This is only available for Excel OM and you must be enrolled in a myomlab course at www.myomlab.com in order to use this feature.
The next group has six buttons on it, 0 through 4 and 6, that will format the selected cells cell in the active worksheet with that number of decimals. This is very useful when using myomlab since most myomlab questons specify a specific number of decimals.
The calculations group offers an easy-to-use Normal Distribution Calculator, the Windows Calculator and a link to Excel's Goal Seek tool.
The settings group offers the user options to customize the appearance of the spreadsheets that are created or to revert to the default settings.
The Support group offers 5 items for support of the add-in.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Problem/Sheet Creation
Excel 2003 or on a Mac witth Excel 2011
Go to the Excel OM/QM Menu or the Author menu on the main Excel menu bar at the top of the window;
Excel 2007 or later on a PC or on a Mac with Excel 2016
Go to one of the two menus on the Excel OM/QM tab on the Ribbon..
You can select from several modules. For some of the modules you will need to pick the model from a list of submodels. After you select the model a creation screen will appear. This screen requests different pieces of information depending on the model. A sample is displayed below.
Problem title
The title is optional. If you enter one then it will be placed at the top of the spreadsheet (cell A1) when the spreadsheet is generated.
Number of rows
Many, but not all models, may have a variable number of rows. This number may be given by the text box or the attached spinner. There are essentially no limits on the number of rows.
Row names
For models with variable number of rows sequential row names will be generated. You may change the default name. For example, in decision analysis the default row names are Decision 1, Decision 2, Decision 3, etc., but if you change the word Decision to the word Alternative then the names generated will be Alternative 1, Alternative 2, Alternative 3, etc.
Number of columns
Many, but not all models, may have a variable number of columns. This number may be given by the text box or the attached spinner. There are essentially no limits on the number of columns.
Column names
For models with variable number of columns sequential column names will be generated. You may change the default name. For example, in decision analysis the default column names are Scenario 1, Scenario 2, Scenario 3, etc., but if you change the word Scenario to the word State then the names generated will be State 1, State 2, State 3, etc.
Option box (lower left)
Some modules have alternatives that must be chosen such as minimize vs maximize. One of the option buttons must be chosen.
Option box (right side)
Some modules have optional information that may or may not be included with the spreadsheet such as a graph. Check off the ones desired.
After clicking OK a spreadsheet will be generated that contains the model as specified on the creation screen.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Aggregate Planning
Model:
Aggregate Planning
Transportation Model for Aggregate Planning
Aggregate Planning
Problem Creation
The creation form asks for the number of periods for which to do the planning. In addition shortages can be handled as backlogs (delivered late) or as lost sales (we lose the demand for products that can not be delivered on time). One last option is that the costs can be formatted as dollars and cents or as dollars only.
Data
The data area has three parts. The top part consists of the unit costs for regular time production, overtime production, subcontracting, holding, shortage, increase from period to period and decrease from period to period. You may need to convert some textbook data in order to get cost/unit. For example, if hiring costs $1000 per laborer and each laborer produces 200 units per month then this represents an increase cost of $1000/200 or $5 per unit.
The middle data area has two entries for starting conditions. We need the initial inventory and we need the units produced in the period prior to the start of this problem in order to be able to track changes.
The main data area will contain columns for the demand, amout to be produced during regular time, overtime and subcontracted.
Results
The results area consists of columns for the inventory (which may be positive or negative), number of units held, number of units short, production change (which may be positive or negative), production increase and production decrease. The column totals are presented as well as the total costs for each column and the overall total cost.
Formulas/notes by columns (Excel functions used are in parentheses)
Inventory - The inventory may be positive or negative. (SUM)
The inventory calculation depends on whether shortages or lost sales was selected at the time of problem creation
Holding is the inventory if the inventory is positive, else it is 0. (IF)
Shortage is the inventory if the inventory is negative, else it is 0. (IF)
Change is based on regular time production only and may be positive or negative. (SUM)
Increase is the change if the change is positive, else it is 0. (IF)
Decrease is the change if the change is negative, else it is 0. (IF)
Transportation Model for Aggregate Planning
Data entry for this model is nearly identical to the data entry for the aggregate planning model The difference is that the transportation model does not consider changes in production levels so there is no data entry allowed for increase and decrease costs or for units last period. Since the data is the same this means that all costs must be the same throughout the time horizon. If this is not the case then you need to set the problem up by yourself, using the transportation model from the main menu rather than this transportation model.
Results
After entering the data use Tools, Solver, Solve in order to generate the solution. Solver is a little bit peculiar so if you have some trouble getting a solution you may want to go to the Excel solver help screen as well as the help screen on solver in this package. Be sure that the message from solver is that a solution has been found. Solver does NOT add dummy variables!!
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Decision Tables
Decision tables presents the expected value, maximin (minimax) and maximax (minimin) when examining different alternatives under different states of nature. The option exists to include the expected value of perfect information and a regret table.
Problem Creation
The creation form requests the number of options/alternatives and the number of scenarios/states of natures. Default names may be given for these. In addition, we must idicate whether this is a profit problem (maximize) or a cost problem (minimize). Optionally, the expected value of perfect information and/or a regret/opportunity loss table may be created.
Data
The data consists of the probabilities of each scenario (state of nature). Some problems do not use probabilities so it is permissable to leave these at 0. If probabilities are used then, of course, they must sum to 1. The profit/cost for each option under each scenario must be given.
Results
The results include the expected value of each option, the best for each option, the worst for each option, the best expected value, best worst and best best.
If the EVPI was selected then this is presented while if the regret opportunity loss table was requested then this is presented.
Formulas/notes by columns (Excel functions used are in parentheses)
EMV - For each row the weighted average of the data in the row multiplied by the probabilities (SUMPRODUCT)
Minimum - For each row the minimum in the row (MIN)
Maximum - For each row the maximum in the row (MAX)
Maximum row - For each result column the maximum (for profit problems) or the minimu (for cost problems) (MIN or MAX)
Expected value of perfect information
For each column the best (maximum or minimum) in that column (MAX or MIN)
Expected value under certainty - The weighted average of the best outcome for each column multiplied by the probabilities. (SUMPRODUCT)
Expected value of perfect information - The difference between the expected value under certainty and the best expected value
Regret
For each column; by row - the opportunity loss or regret. The best (maximum or minimum) in that column minus the element in the cell.
EMV - For each row the weighted average of the data in the row multiplied by the probabilities (SUMPRODUCT)
Maximum - For each row the maximum in the row (MAX)
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Forecasting
Six models are available under forecasting - moving average, weighted moving average, exponential smoothing, regression/trend analysis, multiplicative decomposition and multiple regression.
Problem Creation
At the time of creation some information must be given prior to generating the spreadsheet for some of these. For example - the number of periods in the moving average or the number of seasons for multiplicative decomposition. Graphs are available at the time of creation.
Data
The data consists of the past demands
Results
The results consist of the forecasts generated and the errors generated (bias, MAD, MSE and standard error).
Note: While Excel itself has forecasting, these models are more consistent with the textbooks in terms of the error analysis. For multiple regression Excel OM/QM sets up the spreadsheet for use with Excel's data analysis option found in the TOOLS menu.
Formulas/notes by columns (Excel functions used are in parentheses)
Forecasts
Moving Averages
The average of the selected number of previous periods (AVERAGE)
Weighted moving averages
The weighted average of the selected number of previous periods. Computations depend on whether or not the weights sum to 1. Notice the relationship of the weights to the data values. The weights must be entered from oldest to newest (increasing order) (SUMPRODUCT)
Exponential smoothing
Forecast for the previous period plus alpha multiplied by the difference between the previous data and the previous forecast
Regression/trend analysis
Intercept + slope multiplied by the value of x (INTERCEPT, SLOPE)
Decomposition (see below)
Error Analysis
Error = Data - Forecast
Absolute = Absolute value of the error (ABS)
Squared = Square of the error
For each of the above columns
Total = The total of each column (SUM)
Average = The average of each colun (AVERAGE)
SE (Standard error) = Square root of total error squared divided by n-1 or n-2 (chosen by user at problem setup) (SQRT)
Decomposition
Average
A centered moving average or the average of all the data as specified by the user (AVERAGE)
Ratio
The ratio of the demand in that period (row) to the period average
Seasonal
The seasonal factor for that period (computed where it says ratios)
Smoothed
Original data divided by the seasonal factor
Unadjusted
Forecast according to a regression line based on the smoothed data
Adjusted
Unadjusted forecast multiplied by the seasonal factor
Ratios
The ratios in the ratio colun above are rearranged into this two dimensional table and sorted into columns by season.
Average = The average of the ratios for each season (AVERAGE)
Regression projector
You may make several forecasts by filling in the values for the regression coefficients in the first column and the values for the independent variables in the remaining columns.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Inventory
Inventory has several models available.
Economic Order Quantity (EOQ)
Production Order Quantity (POQ)
Inventory with shortages
Quantity discount
ABC Analysis (see below)
Safety Stock -Normal distribution (see below)
Safety Stock -Discrete distribution (see below)
Graphs are available for most of these models at the time of creation
EOQ Problem Creation
For the EOQ Based models you must indicate whether the holding cost is a fixed amount (such as $20 per unit) or a percentage of the unit price (such as 20% of the unit price). In addition, For the EOQ model itself there is an option for determining the reorder point (based on lead time demand). For the EOQ, POR and Qunatity discount model models there is an option for creating a graph.
Data
For the first four models the data is very similar. It includes the demand rate, the setup (order) cost, the holding cost and the unit cost. The holding cost may be given as a fixed amount or as a percentage of the unit price. This option is set at the time of problem creation.
Results
The results consist of the order quantity, the average inventory, the maximum inventory, and the costs.
Formulas/notes by columns (Excel functions used are in parentheses)
EOQ Model
Optimal Order Quantity, Q* = Square root of (2 * demand * setup/holding) (SQRT)
Maximum Inventory = Q*
Average Inventory = Q*/2
Number of Setups = Demand/Q*
Holding cost = H * average inventory
Setup cost = S * number of setups
Unit costs = Demand *Cost per unit
Total cost, Tc = Holding cost + Setup cost + Unit costs
Reorder Point = Daily demand rate * Lead time in days
Production Model
Optimal production quantity, Q* = Square root of (2 * demand * setup/holding) * Square root (daily production rate/(daily production rate - daily demand rate) (SQRT)
Maximum Inventory = Q* multiplied by (p/(p-d)) where p = daily production rate and d = daily demand rate
Average Inventory = Maximum inventory/2
Number of Setups = Demand/Q*
Holding cost = H * Average inventory
Setup cost = S * number of setups
Unit costs = Demand *Cost per unit
Total cost, Tc = Holding cost + Setup cost + Unit costs
Inventory with shortages
Optimal Order Quantity, Q* = Square root of (2 * demand * setup/holding) * Square root (holding cost + shortage cost)/Holding cost (SQRT)
Maximum Inventory =Q* multiplied by g/(h+g) where g is the shortage cost and h is the holding cost
Average Inventory = (Q-B)^2/2Q
Number of Setups = Demand/Q*
Maximum Backorders = Q* multiplied by h/(h+g)
Holding cost = h * Average inventory
Setup cost = S * number of setups
Backorder cost = g * average backorders
Unit costs = Demand *Cost per unit
Total cost, Tc = Holding cost + Setup cost + Unit costs
Quantity Discount
For each price range
Q* (Square root formula) = Square root of (2 * demand * setup/holding) (SQRT)
Order Quantity, Q = Q* if it is greater than range minimum, else range minimum (IF)
Holding cost = h*Q/2
Setup cost = DS/Q
Unit costs = cD
Total cost, Tc =Holding cost+setup cost+Unit costs
ABC Analysis
Problem Creation
To create an ABC analysis you must simply indicate the number of items to be examined. Default names for these items may be given.
Data
The data consists of the demand for each item and the cost for each item.
Results
The spreadsheet computes the total dollar volume and percentage dollar volume for each item. It may be useful to sort (Data, Sort) by dollar volume after the data is entered.
Reorder Point - Normal Distribution
??Daily demand. This is the daily demand rate during the lead time.
Demand standard deviation. This is the standard deviation for the daily demand rate. If the daily demand rate is fixed enter a standard deviation of 0.
Service Level. This is the percentage of demands which should be met.
Lead time in days. This is the lead time in days.
Lead time standard deviation. This is the standard deviation of the lead time. If the lead time is fixed enter 0 for the standard deviation.
Some models are based on a given lead time demand and standard deviation and need only three inputs. To use the model, if the problem has a given lead time demand and standard deviation then set the lead time days to 1 and lead time standard deviation to 0.
Reorder Point - Discrete Distribution
Reorder point w/o safety stock. This is the reorder point prior to consideration of safety stock.
Carrying cost per year. This is the usual cost of carrying inventory.
Stockout cost. This is the cost per unit of not being able to meet the demand.
Orders per year. This is the number of times per year we perform the ordering process.
Probability distribution. This is the lead time demands and their associated probabilities.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Job Shop Scheduling
The software creates spreadsheets for one-machine and two-machine job shop scheduling.
Problem creation
For this module you must enter the number of jobs to be scheduled. In addition, there is the option to have jobs start at day 0 or at some other time. A Gantt chart may be created for this problem
Data
The data area contains the time for each job and its due date.
Results
The first set of results is given according to the First Come First Serve (FCFS) rule assuming that the jobs are processed in the order of the list. In order to change this rule it is necessary to sort the data according to whatever criterion (column) you desire (time(SPT), due date or slack).
The first column is slack. This is followed by completion time and lateness.
Formulas/notes by columns (Excel functions used are in parentheses)
Slack = Due date - processing time
Flow time = processing time + flow time of preceding job in schedule
Late days = Flow time - due date if this is positive, else 0. (IF)
For flow time and late days columns
Total = Column total (SUM)
Average = Column average (AVERAGE)
Average number of jobs = Total flow time/Maximum flow time
Two machine scheduling
For 2 machine scheduling, Johnson's method is used to determine the schedule which finishes the soonest (minimizes the makespan)
Results
For two machine scheduling the makespan is the time the last job is completed. For each job its order and the time it finishes on each machine is given.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Learning Curves
Two models are available under learning curves. Graphs are available at the time of problem creation.
Problem Creation
You must enter the unit number for the last unit. A graphis optional.
Find times
Data
At the time of problem creation the creation screen will request the last unit to be determined. The data consists of the unit number of the base unit and its time.
Results
The spreadsheet computes the time and cumulative time from unit 1 to the last unit requested.
Formulas/notes by columns (Excel functions used are in parentheses)
Time for first unit = time for base unit/unit number of base unit^(LN(leasrning curve coefficient)/LN(2)) (LN)
b = (LN(leasrning curve coefficient)/LN(2) (LN)
Unit time = time for base unit * b^unit number (POWER)
Find coefficient
Data
At the time of problem creation the creation screen will request the last unit to be determined. The data consists of the unit number of the base unit and its time, the unit number of a second unit and its time.
Results
The spreadsheet computes the learning curve coefficient and rhe time and cumulative time from unit 1 to the last unit requested.
Formulas/notes by columns (Excel functions used are in parentheses)
b =LN(time for last unit/time for base unit)/LN(unit number of last unit/unit number of base unit) (LN)
Learning curve coefficient =EXP(b * LN(2)) (EXP, LN)
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Quality Control
Models
Control charts
Acceptance sampling
Process capability
Control Charts
Problem Creation
Enter the number of samples. Enter 1 if this is a problem where a control chart is being designed on specifications rather than on past data. A graph is optional.
Data
p-charts
Enter the number of defective units per sample.
For each sample, percent defective = number defective/sample size
Total Sample Size = Number of samples * sample size
Total Defects = Sum of defect column (SUM)
Percentage defects = Total defects/Total sample size
Std dev of p-bar = Square root((1-percentage defects)*percentage defects)/sample size) (SQRT)
Upper Control Limit = percentage defects + 3 * std dev of p-bar
Center Line = percentage defects
Lower Control Limit = percentage defects - 3 * std dev of p-bar if this is positive, else 0 (IF)
x-bar charts
Enter the average measurement per sample and either the range of each sample or the population standard deviation as set by the user at the problem set up screen.
x-bar value =Average of the sample averages (AVERAGE)
z value set by user
Sigma x bar = population standard deviation/square root of the sample size (when the population standard deviation option is selected) (SQRT)
Upper control limit = x bar + z*sigma x bar
Center line = x bar
Lower control limit = x bar - z*sigma x bar (not checked for non negativity)
c-charts
Enter the number of defects per item.
Total units sampled = Given at problem setup
Total Defects = Sum of defects in all units sampled (SUM)
Defect rate = Total defects/number of units sampled
Standard deviation = Square root of the defect rate (SQRT)
Upper Control Limit = Defect rate + 3 * standard deviation
Center Line = Defect rate
Lower Control Limit = Defect rate - 3 * standard deviation
Results
The software creates the control limits and center line.
Acceptance sampling
Find sample plan - attributes.
Items will be sampled from lots and based on the number of defects the lots will either be accepted or rejected. A sampling plan is the determination of the number of items to be sampled (the sample size, n) and the maximum number of allowable defects in a sample (c, the critical value).
Sampling plans are designed to meet certain error specifications. The specifications are the input to this model. These include the AQL (0 to .99), the LTPD (0 to .99), producer's risk which is a dropdown box set to .05 or .01 and consumer's risk ß which is a drop down box set to .01, .05 or .10. Based on these four settings the program computes the sample size and critical value or in some cases states that the critical value is too large. (The program can not find the sampling plan if the critical value is greater than 49.) In addition, the actual probabilites of making type 1 and type 2 errors are computed (see below- results)
Find sample plan - variables
Items will be sampled from lots and a measurement will be taken. Based on the average measurement in the sample the lot will either be accepted or rejected. A sampling plan is the determination of the number of items to be sampled, (the sample size, n) and the maximum or minimum allowable sample average (the critical value). All tests in this model are one-tailed.
Sampling plans are designed to meet certain specifications. The specifications are the input to this model. These include the expected mean, µ0, an alternative mean, µ1,a standard deviation, a producer's risk (alpha)and consumer's risk ß. Based on these five settings the program computes the sample size and the critical value.
Find errors/OC Curve
This is the model for determining the errors when given a sampling plan. A sampling plan is given by the sample size, n and the critical value c. The errors are determined at the AQL and the LTPD. Given these four values n, c, AQL and LTPD the program will determine the producer's risk = the probability of a type I error and ß, the consumer's risk = the probability of a type II error. The program can also display an operating characteristic curve. This is a plot of the probability of acceptance (on the y-axis) versus the percentage of defects (on the x-axis). Two different versions of the OC Curve may be plotted.
Results
The sample size and the critical value are displayed for attributes sampling. The table used for this computation is from Bowker and Lieberman's Engineering Statistics , Pearson, 1959, page 462.The table is designed in order to ensure that both a and ß will be met or bettered. The program also displays the ACTUAL a and ß based on a binomial distribution.
The errors are computed using a binomial distribution. This may differ from some other results that use a Poisson distribution. Furthermore, computations are performed with regard to the sample size but WITHOUT REGARD TO THE LOT SIZE. That is, a binomial distribution assumes that the lot size is large (infinite). The actual probabilities do not match exactly the given probabilities for producer and consumer risk. They should be at least as good (no higher).
The sample size and critical value are displayed for variables sampling.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Waiting Lines
There are multiple waiting line models. Some of the data and results are similar for each of these. The models available correspond to the models in your text if you are using a Pearson text.
Data
The arrival rate and service rate must be entered. Please notice that these are RATES such as 4 customers per hour) not TIMES (such as 4 minutes per customer). The time unit (e.g., minutes, hours) for the arrival rate and service rate must be the same.
For some models (M/M/s, M/M/s with a finite queue, M/M/s with a finite population) the number of servers is given.
For the M/G/1 model the standard deviation of the service times must be given.
For the M/Ek/1 model the k for the Erlang k distribution must be given.
For the finite queue model the maximum system size must be given.
For the finite population model the population size must be given.
Results
For all models the spreadsheet computes the average server utilization, the average number waiting, the average number in the system, the average time waiting and the average time in the system and the probability that the system is empty (P0).
M/M/1
Average server utilization = arrival rate/service rate
Average number of customers in the queue(Lq) = arrival rate^2/arrival rate*(service rate-arrival rate)
Average number of customers in the system(L) = arrival rate/(service rate-arrival rate)
Average waiting time in the queue(Wq) = arrival rate/arrival rate*(service rate-arrival rate)
Average time in the system(W) = 1/(service rate-arrival rate)
Probability (% of time) system is empty (P0) = 1 - average server utilization
M/D/1
Average server utilization = arrival rate/service rate
Average number of customers in the queue(Lq) = arrival rate^2/(2*arrival rate*(service rate-arrival rate))
Average number of customers in the system(L) = average number in the queue + server utilization
Average waiting time in the queue(Wq) = arrival rate/(2*arrival rate*(service rate-arrival rate))
Average time in the system(W) = average waiting time + average service time
Probability (% of time) system is empty (P0) = 1 - average server utilization
M/Ek/1
Average server utilization = arrival rate/service rate
Average number of customers in the queue(Lq) =((1+K)*arrival rate^2)/(2*k*(service rate*(service rate-arrival rate)))
Average number of customers in the system(L) = average number waiting + average server utilization
Average waiting time in the queue(Wq) =((1+K)*arrival rate)/(2*k*(service rate*(service rate-arrival rate)))rate)
Average time in the system(W) = average waiting time + average service time
Probability (% of time) system is empty (P0) = 1 - average server utilization
M/G/1
Average server utilization = arrival rate/service rate
Average number of customers in the queue(Lq) = (B5^2*B7^2+E5^2)/(2*(1-E5))
Average number of customers in the system(L) = average number waiting + average server utilization
Average waiting time in the queue(Wq) = average number in the queue/arrival rate)
Average time in the system(W) = average time in the queue + service time
Probability (% of time) system is empty (P0) = 1 - average server utilization
The remaining models are rather complicated.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Breakeven & cost-volume analysis
Breakeven is used for the traditional case of a fixed cost, a variable cost and a selling price while cost-volume analysis is used for comparing two options each with a fixed cost and a variable cost. In either case there is the option to include a volume analysis and/or a graph.
Problem Creation
For cost volume analysis the creation form asks for the number of options as we can perform a cost-volume analysis on any number of options. In addition, a default namefor these options may be given. Breakeven analysis assumes that there are two "options". One option is cost while the other option is profit. For either breakeven or cost volume analysis there is the option to perform volume analysis (profit at a given volume) and to create a graph.
Data
For cost volume analysis the data are the Fixed costs and variable costs for each option. For breakeven analysis the data are the Fixed costs, variable costs per unit and selling price or revenue per unit. In either case, if volume analysis has been created then the volume at which the (optional) volume analysis should be performed can be entered.
Results
The results consist of the break-even point(s) in both units and dollars and a graph and volume analysis if requested.
Formulas/notes by columns (Excel functions used are in parentheses)
Cost volume analysis
BEP (units) = difference in fixed costs/difference in variable costs
BEP (dollars) = fixed costs of option 1 + BEP(units)*variable costs of option 1
Volume analysis
dollars = fixed costs of option + BEP(units)*variable costs of option for each option
Graph
The total cost for each option is computed at 0 and twice the breakeven point.
Breakeven analysis
BEP (units) = fixed costs/(selling price - variable costs)
BEP (dollars) = fixed costs + BEP(units)*variable costs
Volume analysis
Revenue = price*units
Cost = fixed costs + BEP(units)*variable costs
Profit = Revenue - Cost
Graph
The total cost for each option is computed at 0 and twice the breakeven point.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Assignment
The assignment option creates a Linear Program so that SOLVER can be used. After entering the data use Solver in order to generate the solution.
Problem Creation
The creation form asks for the number of assignments to be made. The number of rows and the number of columns will be the same. Rows and columns may be given default names. In addition, there is an option to select minimization or maximization.
Data
The data area consists of the cost of assigning each 'row' to each 'column'.
Solution
In order to get the solution you must use Solver The solution area has a 1 where each assignment should be made and the total cost of the assignments.
Formulas/notes (Excel functions used are in parentheses)
Solution table - row and column totals (SUM)
Lower right corner of solution table
Display of grand row total and grand column total (SUM, CONCATENATE)
Total Cost (bottom left) - multiplication of one table by another (SUMPRODUCT)
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Transportation
The transportation option creates a Linear Program so that SOLVER can be used. After entering the data use Tools, Solver, Solve in order to generate the solution. Solver is a little bit peculiar so if you have some trouble getting a solution you may want to go to the Excel solver help screen as well as the help screen on solver in this package. Be sure that the message from solver is that a solution has been found. Solver does NOT add dummy variables!!
Problem Creation
The creation form asks for the number of origins and the number of destinations. Origins and destinations may be given default names. In addition, there is an option to select minimization or maximization.
Data
The data area consists of the cost of transporting units from each 'row' to each 'column' as well as the supplies at each row and the demands of each column.
Solution
The solution area has the number of shipments that should be made from each row to each column and the total cost of the shipments.
Formulas/notes (Excel functions used are in parentheses)
Lower right corner of data table and solution table
Display of grand row total and grand column total (SUM, CONCATENATE)
Row and column totals (SUM)
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Lot sizing
Problem Creation
The creation form asks for the number of periods for which to do the lot sizing. In addition, the costs can be formatted as dollars and cents or as dollars only.
Data
The data area consists of two parts. The top parts contains the setup cost, holding cost and initial inventory while the main data area consists of the demand and amount produced. The amounts produced must be provided by the user.
Results
The spreadsheets computed the amount held each period and the number of setups and the appropriate costs. A button is available to use the Wagner-Whitin method to compute the optimal lot sizes.
NOTE: The software has a button to compute the optimal solution as given by the Wagner-Whitin method.
Formulas/notes by columns (Excel functions used are in parentheses)
Inventory = Previous period inventory + production - demand (This is NOT checked to ensure that it is positive!)
Holding = Holding cost * inventory
Setup = Setup cost if an order was produced, else 0 (IF)
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
User Preferences
There are several options available when setting up a problem. These options are set by using the User Preferences which can be accessed through either the OM/QM Menu (Excel 2003 and Excel for Macs) or the Excel OM/QM tab (Excel 2007 and later).
User
This is the name that can be placed on the spreadsheet. If the software is operating on a network then you likely will need to change this each time that you start if you want your name displayed on the spreadsheet.
Graphs
Place charts on separate sheets
When creating a graph or chart for a spreadsheet (as indicaterd by the option box on the right of the problem creation screen) the graph may be placed on the spreadsheet or on its own worksheet. If it is placed on its own sheet then it is larger than if it is placed on the same sheet. On the other hand when it is placed on the same sheet it is possible to see both the data and the graph simultaneously.
Sheet font size
Default font size
Please note that the default font and font sizxe are different in Excel 2007 and prior versions. In Excel 2007 the default Font is Calibri and the default font size is 11. In earlier versions of Excel the default font is Arial and the default size is 10. In Excel OM/QM you can change the font size for each sheet that is created but you can not change the default font. The font, of course, can be changed in Excel.
Information
Name
Check here if you want your name to appear at the top of the spreadsheet.
Date
Check here if you want the date to appear at the top of the spreadsheet (below the name if checked).
Sample data
Check here if you want randomly generated data thrown into the problem at the time of creation.
Style
Color
Check here if you want colors used rather than black and white (and gray).
Shade data
Check here if you want the area shaded where the data is to be entered. THIS IS VERY USEFUL.
Shade results
Check here if you want the area shaded where the results appear. THIS IS VERY USEFUL.
Border
Check here if you want borders around the data and results
Grid lines
These can be turned on/off by using the checkbox. These can be changed AFTER a sheet is created by using the controls on the Excel OM/QM tab.
Row/Column Headings
These can be turned on/off by using the checkbox. These can be changed AFTER a sheet is created by using the controls on the Excel OM/QM tab.
Instruction visible
These can be turned on/off by using the checkbox. These can be changed AFTER a sheet is created by using the controls on the Excel OM/QM tab.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Color Selection
Several colors that are used may be set by the user including colors for
Student name
Date
Problem title
Module name
Submodule name
Instruction text box
Data label and border
Data shading
Results shading
Results label and border
Block data (cells for which data should not be entered)
In order to change the colors press the button for the item you want and then select a color from the color frame.
Default colors
If for some reason your colors do not look right then use the default colors button to reset them to their original specifications.
Note that it is possible to have everything in monochrome (black/white) by not checking the colors check box on the user preferences options.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Technical support
In the event of any questions or problems please contact dsSoftware@pearson.com or visit
www.pearsonhighered.com/weiss
Please note that Excel OM/QM can not be loaded with Crystal Ball simultaneously.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Factor Rating (Weighting)
Problem Creation
The creation form requests the number of factors and the number of locations. Default names may be given for these. Of course, since factor rating is simply a weighted sum this may be used for problems other than location problems.
Data
The data consists of a number of factors, their weights and the scores of each candidate (location) for each factor.
Results
The results are the weighted totals and weighted averages of the factors for each candidate (location)
Formulas/notes by row (Excel functions used are in parentheses)
Total - The total for each column is presented for both the weights and the scores (SUM)
Weighted sum - The weighted total (weights multiplied by scores) are presented for each candidate (site) (SUMPRODUCT)
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Center of Gravity
Problem creation
The creation form requests the number of locations and the default name for the locations. Additional options are to create a table of distances from each location to every other location and to create a graph.
Data
The data area consists of the x and y coordinates for each site as well as weights for each site. If the problem does not contain weights then enter 1 for each weight.
Results
The results consist of the average and weighted average of all of the sites.
Formulas/notes by rows (Excel functions used are in parentheses)
Sum = The column sum is computed for both the weights and the coordinates (SUM)
Average = The averages are computed for the coordinates (AVERAGE)
Weighted Average = This is the weighted sum for each coordinate by the weights divided by the sum of the weights (SUMPRODUCT)
Distance table
Distance from one location to another is given by the distance formual based on the Pythagorean Theorem. (SQRT)
Total distance - This is the total distance from the site named at the top of the column to every other site (SUM)
Weighted Total - This is the total of the distance from the site named at the top of the column to every other site multiplied by the weight (number of trips) (SUMPRODUCT)
Graph
A graph of the sites is available
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Solver
The following instructions are available by clicking on Help in Excel and searching for the term "Load solver".
Excel 2007 and later for PCs
The Solver Add-in is a Microsoft Office Excel add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) program that is available when you install Microsoft Office or Excel. To use it in Excel, however, you need to load it first.
1. Click the Microsoft Office Button , and then click Excel Options.
2. Click Add-Ins, and then in the Manage box, select Excel Add-ins.
3. Click Go.
4. In the Add-Ins available box, select the Solver Add-in check box, and then click OK.
Tip If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in.
If you get prompted that the Solver Add-in is not currently installed on your computer, click Yes to install it.
After you load the Solver Add-in, the Solver command is available in the Analysis group on the Data tab.
Excel 2003 and Earlier
The Solver Add-in is an Excel add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) program that is available when you install Microsoft Office or Excel. To use it in Excel, however, you need to load it first.
1. On the Tools menu, click Add-Ins.
2. In the Add-Ins available box, select the check box next to Solver Add-in, and then click OK.
Tip If Solver Add-in is not listed, click Browse to locate it.
3. If you see a message that tells you the Solver Add-in is not currently installed on your computer, click Yes to install it.
4. Click Tools on the menu bar. When you load the Solver Add-in, the Solver command is added to the Tools menu
Excel for Mac
If you have not already done so you will need to download and install Solver on your computer. Please visit http://www.solver.com/mac/
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Graphs
The graphs are computed with default values set by the add-in. It is easy to modify the graphs and improve their appearance. For example, if you want to change the legend then simply double click on it and you can move it or delete it.
On the user preferences sheet you may set whether you want the graphs to be on the same spreadsheet as the data or on their own sheets.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Material Requirements Planning
Problem Creation
MRP requests the number of lines in the Indented Bill of Materials or Product Tree and the number of periods for which to do the planning. Consider the product tree in the following picture
Missing image: mrp.bmp
The number of items in the product tree is 7. Item e must be included twice!.
MRP is different from the other modules in that after you use the problem creation screen a second screen comes up which continues with the creation. The screen is used for creating the indented Bill of Materials or Product Tree. We show below the starting tree.
BOM Line 1 is the end item. This needs to be renamed A. The next line needs to be indented one level (as shown), renamed B and given a usage factor of 2. (The usage factors can be changed on the spreadsheet but the indenting is critical to the creation process.). We continue with the children of Item B. Therefore, the next two lines are for E and F and are indented one more time than B is. We continue with item C (indented at same level as B) and then finish with items D and E (indented one level in from C). The finished screen appears below.
Click on DONE and the spreadsheet will be created.
Data
As always the data is entered in the shaded area. In MRP, the shaded areas are interspersed with the results area. There is an area at the top where you may place the usage per item (which could already be completed due to the initialization screen shown above). Then there is data to be entered for each item.
Lead time - This is the standard MRP lead time
Safety stock - The Excel spreadsheet will allow you to indicate the minimum level which the inventory for this item should ever reach. In most homework problems this is 0.
Lot size - You may indicate that ordering must take place in lots that are multiples of the lot size. For example, if the lot size is 12 and you need 18 items then 24 items will be ordered. Typical homework problems use 1 (lot for lot) for the lot size.
Minimum quantity - You may indicate a minimum quantity to be ordered. For example if the minimum quantity is 12 and the need is for 5 units then 12 will be ordered.
On hand inventory - On a different line indicate any starting inventory.
There are two types of items and some data entry differs for these types. For end items (those at level 0 or leftmost in the screen displayed above) the demands are entered in the Gross requirements row. For the other items, if there are any scheduled receipts of these items they are placed in the scheduled receipts row. Do not touch the Gross requirements row for items that are not end items. We have created the formulas in here that aggregate the usages from the parent items.
Results
For each item we have the following results by row.
Gross requirements - For non end items these are given by multiplying the usage per item for each parent by the demand (gross requirements) for each parent in the same time period.
Scheduled receipts - data, as indicated above
On Hand inventory -
Net POQ Requirements - This is the gross requirement minus any scheduled receipts minus any onhand inventory.
Planned receipts and planned orders - these go hand in hand and are a function of the requirements, lot sizes and minimum order quantities.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Operations Layout
Problem Creation
Problem creation asks for the number of processes or rooms. There is a one to one relationship. As usual, default names may be given for either.
Data
The data consists of two tables beginning in column C. These are the table of flows/trips/movement form one process to another and the table of distances from one room to another.
Results
There is a column on the left of the first table into which room numbers may be placed to put the rooms in there. For example, in the screen below we are showiing that we have placed Department 1 in room 3, Department 2 in Room 1, Department 3 in Room 4 and Department 4 in Room 2. Scrolling down would displays a total distance of 868 for this arrangement.
If we want the arrangement that minimizes the total movement then click on the Solve button on the spreadsheet. This will perform an explicit enumeration of all possible layouts. It may be time consuming for large problems.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Project Management
Problem Creation
Project Management uses a precedence list for precedences rather than node naming (beginning -end) for precedence creation. To create a problem enter the number of tasks in the project and enter the maximum number of precedences for any of these tasks. In addition, you must indicate whetehr this is a single or triple time estimate problem. A Gantt chart is an option.
Data
Enter the time(s) for each task and enter the precedences. Precedence names must match task names (case does not matter).
Results
The results are the standard results of Early start, Late start, Earlt finsih, Late finish and slack. In addition for 3 time estimate problems the activity times are computed according to (a+4m+b)/6, the activity standard deviations are computed according to (b-a)/6 and these times are squared to yield the activity variances. Th eproject variance is computed as the sum of the variances of the critical activities. The project standard deviation is computed as the square root of the variance.
Project management is used for estimating the length of time that a project will take and which of the project activities are critical. The two most commonly used techniques are CPM and PERT. This program does not distinguish between the two because they are so similar.
This program has five submodels available. These are single time estimate, triple time estimate, crashing, budgeting and two time estimate (mean and standard deviation). The initial data for any of these methods consists of
the problem title
the number of tasks.
network type designation (use a precedence list or use starting and ending nodes)
The data screen has an option box for choosing whether the network should be represented by precedences or by start and end nodes. In addition the method box allows you to change submodels.
The first column in the data table is the task name. This is optional for networks defined by start and end nodes but required for networks defined by precedences. For activity on arc (AOA) networks the start and end nodes are next. Following this, are the tasks times. For three estimate problem optimistic, pessimistic and most likely times must be given.
For precedence list (activity on node or AON) networks the tasks which precede the named task must be given. On the right hand side there is space to list task which precede this one. YOU ONLY NEED TO LIST THE IMMEDIATE PREDECESSORS. For example, if task A precedes B which precedes C you do NOT have to list A as preceding C. The program will figure this out.NOTE: The names are case insensitive. That is 'wiring', 'WIRING' and 'WiRiNg' all represent the same task. Also note that 'A" and Task A" are not the same.
Results
The standard output of start and finish times as well as slack times are presented.
For three time estimate models the times for each task, the standard deviation for each task and the project variance are computed.
TIME = (Opt time + 4* Like time + Pess time)/6
For all models
ES = Early Start = The earliest an activity can begin
EF = Early Finish = ES + task time
LS = late Start = LF -task time
LF = Late Finish = The latest an activity can end without delaying the project
SLACK = LF -EF or LS -ES
SD( ) = activity standard deviation = (Pess time -Opt time)/6
PROJECT COMPLETION TIME = The maximum of the Early finish times
PROJECT VARIANCE = The sum of the variances of EVERY activity that has slack = 0. Be CAREFUL !!. This overstates the standard deviation for projects that have multiple critical paths.
Crashing model
Please note that the crashing option crashes the project to its minimum time.
Budget model
The budget model will prorate the costs over each time period in order to determine how much would be spent at any point in time under the early start/early finish times or under the late start/late finish times.
Results in other windows
For the three time estimate model a window of the time estimate and standard deviation computations is available
Charts are available including
a Gantt chart for early start times
a Gantt chart for late start times
a Gantt chart for both
a Precedence graph for AON graphs.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Help
This Help file is for both Excel OM and Excel QM. Therefore some of the topics may not apply to the add-in that you have.
Use the contents or index tab at the top to search for help.
The Help file on a Mac is an HTML file.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Excel OM/QM Toolbar
When you open Excel OM/QM in Excel 2003 or on a Mac with Excel 2011 a toolbar will be added. The toolbar contains tools for
Setting preferences
Setting colors
Printing on one page
Changing the appearance of the spreadsheet (by toggling headers and lines on/off)
Removing the instruction textbox that is at generated at the top of each sheet
Unloading Excel OM/QM (but not Excel)
Sending email to user support for this product
Trimming blanks (cells which appear empty but have blanks/spaces in them can cause trouble for many of Excel's functions.)
Opening the web page for support of this product (and other products)
An about screen
Help (this file)
(These options are available on the Excel OM/QM tab in Windows 2007 and later.)
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Assembly Line Balancing
Assembly line balancing is used to assign tasks to stations on an assembly line such that no station is assigned more work than can be completed within the cycle time. The initial information on the problem creation form is the
problem title
number of tasks to be balanced
names for the tasks. The default names are A, B, C, D, ..
The first piece of datum for the problem is the priority rule to be used. Enter 1, 2, 3, 4 or 5 for standard rules listed below.
1. Longest operation time
2. Shortest operation time
3. Most following tasks
4. Fewest number of following tasks
5. Ranked positional weight.
There is a main body of data to be entered and there is extra data at the top for the cycle time. You will most likely need to perform an Excel calculation to convert the demand rate to a cycle time. Please note that the time unit for the cycle time must match the time unit for the tasks
The normal information for this problem is for each task - its name, its time and its predecessors. To set the time unit for the tasks use the combo box above the data on the right.
Results
Station Assignments
The first part of the output is the station assignments. This table contains the station numbers, the tasks at each station and the idle time at each station.
Results Summary
Below the balance are the standard results. This consists of
is the cycle time which either has been given or computed depending on the data screen. The stations are listed and each column contains a task for that station.THe third line in the table contains the
Cycle TIme - as given by the student
TIME NEEDED = Sum of the task times
MINIMUM THEORETICAL NUMBER OF STATIONS = total production time per unit/cycle time rounded UP!
ACTUAL NUMBER OF STATIONS
TIME ALLOCATED = Actual number of stations * Cycle time
IDLE TIME = TIME ALLOCATED -TIME NEEDED
EFFICIENCY = TIME NEEDED/TIME ALLOCATED
BALANCE DELAY = 1-EFFICIENCY
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Capital Investment
Capital investment may be used to either find the net present value of a stream of revenues and costs or to find the internal rate of return of a stream of revenues and investments. In either case the setup is the same.
The initial input to this module is the number of time periods. This sets up a data table with one row for every time period including a row for the current time period (period 0). Two columns are created. One is named inflow and one is named outflow. However, since negative numbers may be entered it is possible to place an inflow into the outflow column (or vice versa) simply by making it negative.
Example 1: Net present value. Suppose a company purchases a machine for $80,000, that this machines saves the company $20,000 a year for each of the next 5 years and that the machine has a salvage value of $14,000 at the end of that time. The easiest way to enter this data is to
place $80,000 in the outflow for period 0
place $20,000 in the inflow for period 1 and then press the copy down icon on the toolbar to fill in the rest of the inflow column.
place a -$14,000 (minus $14000) in the outflow for period 5. This gives a slavage value of +14,000.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Decision Tree
Decision trees are created through use of the Decision Tree Creation Window displayed immediately below. Please note that a PDF file with an example of a decision tree problem is in the folder into which you placed the Excel OM/QM software. (Default value is C:\Program Files\ExcelOMQMv5.
NOTE: The maximum number of nodes permitted in the tree is 30.
Missing image: dectreecontrol.gif
The bulk of the creation can be handled by the two frames at the top - Selected node and Branches to add.
Node: At any point during the creation process, branches can be added to any existing node. The easiest way to select the node is by clicking on the node. Alternatively, the scrollbar/textbox combination at the top of the Decision Tree Creation Window can be used to select the node.
Branches to add: Immediately below the node number is a scrollbar/textbox combination that can be used to indicate the number of branches to be added. The type of branch that is added, either Decisions or Events, depends on which of the two buttons is selected.
After adding branches a new tree will be drawn and there will be shaded cells into which you should place your input. For all branches you can enter a name. For probability branches you can enter a probability. If you add n Event branches the default probabilties will be set to 1/n. For example, if you add 3 branches the default probabilities will be .333, .333 and .333, Since this is simply an Excel spreadsheet you may, of course, change these probabilities. PLease note that there is no reason to change the last probabilty because it is given by the formula that is 1 minus the sum of the other probabilities.
There is one more cell, below the branch, into which data can be entered for each branch. This is for any intermediate profit (or cost) that is earned along the way. For example, suppose branch 3 relates to a client search that costs $1,000. and either leads to sales of $50,000 or sales of $60,000. One way to handle the job search cost is to simply reduce each of the two sales amounts by $1,000 to $49,000 and $59,000. Alternatively, you may place the cost of $1,000 (-$1,000) below the branch where it occurs. This typically makes for a more meaningful tree because costs can be seen as they occur.
Other actions at node: The first option is very straightforward. If you created a node that should be one type of node but is another, this may be changed. Removing a node is aslo obvious but please be careful because all nodes and branches following the removed noe will also be removed.
Copy and pasting subtrees: Sometimes, identical parts of a decision tree will occur in multiple places in the decision tree. Rather than having to recreate these you may go to the node that begins the repetitive section and select Copy subtree at node x. Then click on the node at which you want to place this section and click on the same button which will now read "Paste subtree at node x".
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
myomlab
The major new feature added in Version 4 of Excel OM is the ability to paste data from myomlab. Pasting is a very straightforward process.
Step 1: Create the appropriate model in Excel OM
Step 2. Click on the copy icon on the top right of the table of data in the problem, right-click on the data to select it and then click on Copy..
Step 3.
Excel 2007 and higher for PCs: Click on the Paste from myomlab icon on the Excel OM tab or right-click on the spreadsheet and click on the Paste from myomlab menu option.
Excel 2003 or Excel for the Mac: Click on the Paste from myomlab icon on the Excel OM toolbar or right-click on the spreadsheet and click on the Paste from myomlab menu option.
Decision Analysis
See Decision Tables or Decision Trees or One Period Inventory
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Location
Models
Center of Gravity
Factor Weighting
Cost-Volume Analysis
Locational Break-Even Analysis
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
One Period Inventory
This model can be used to have Excel create a decision table for the single period inventory (supply/demand) problem.
This model will create a decision table for supply demand problems. Three profits are required.
Regular profit: This is the normal profit (selling price -c ost) when you buy and sell an item.
Profit on excess goods is the profit for units that are overordered. In some cases, where there is a salvage value that exceeds the cost of the unit this will be a profit while in other cases this will be a loss. To express a loss use a negative number.
Profit on goods short. This is the profit for units when we don't order enough. It will be a profit if we can purchase units to sell after the fact at a cost less than the selling price. Otherwise it will be a 0 or possibly a loss. To express a loss use a negative number.
Demands and probabilities. Enter the list of demands and their associated probabilities.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Productivity
The productivity module will compute the ratios of any number of inputs to one output and also will compute productivity changes for each input from period to period.
The initial input is the number of inputs and the number of periods. The basic data consists of the output measure in the first row of the table and the input measurtes in the remaining rows of the table.
The basic results are the measures of productivity (output/input) for each input for each period and the changes in productivity for each period, starting with the second one.
In addition, it is possible to enter dollar values for each input in order to dervie an aggregate productivity measure.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Reliability
This module has five submodels that can be used to compute the reliability of a series of parallel components.
The general model is as follows:
The initial input consists of
the problem title
the number of components that are in the series and
the largest size of any one of these components.
For example, suppose we have a system such as that given by the diagram below.
> x > > x > > x > > x
> x > > x >
> x >
This represents 4 systems in series(from left to right) with 1,3,2 and 1 components. The values to be entered at initialization are 4 for the number of components in series and 3 for the largest number in parallel.
The input for the main data table are the probabilities that each component will work. Probabilities which are listed as 0 are ignored.
Please note that the top model in the diagram below can be solved easily because it consists of 3 serial components each with simply an original unit and a backup. The bottom diagram consists of one complex serial component. It would need to be broken into two separate POM problems in order to be solved.
Missing image: reliability.bmp
The second and third model allow for a list of elements in a single series or a list of elements in parallel.
The fourth and fifth models have all identical elements either in parallel or series. The number of elements is entered in the scrool/text box above the data.
Results
The unnamed boldfaced row at the bottom gives the reliabilities for each of the parallel systems. Below that, the overall system reliability is computed .
Problem Creation
Data
Results
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Work Measurement
Work measurement has three submodels available. The normal time for an operation may be computed, the sample size for time and motion studies may be computed or the work sample size may be computed.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Linear Programming
The initial input on the creation form is given by entering
the problem title
the number of constraints (rows)
the number of variables (columns).
the objective (minimize or maximize).
NOTE: There is no need to include the nonnegativity restrictions as constraints!
The data screen contains an option box for the objective. The major important information deals with the objective function and the constraints. Numbers are placed into the appropriate cells. In addition, when you get to the constraint sign this must match the signs at the top of the spreadsheet.
Results
You ust use Solver in order to get the results. The solution values are given below the columns for each variable. In addition, the total cost is displayed in the lower right corner of the table. The shadow/prices or dual values for each constraint are displayed on the right of the constraint.
Results in other spreadsheets
The solution in list form rather than below and on the right of the table
Ranging
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Simulation
You can simulate frequency distributions or probability distributions. The initial input is
the problem title
the number of categories (up to 10).
On the main data table, for each category enter the value of that category (optional) and enter the frequency. Frequencies do not have to sum to 1 or 100 as they will be readjusted based on their sum by the program.
Above the data table the method box enables you to choose whether random numbers should be generated by the program or taken from your text (if you use a Render or Taylor textbook). There also is an extra data box for the number of trials and another for the seed (or row or column number.) By changing the seed you can have different runs of the simulation. If you leave the seed the results will be the same.
Results
The program will compute and display the total frequency and scale everything accordingly.
The PROBABILITY column converts the frequencies into relative frequencies according to PROB=FREQUENCY/TOTAL FREQUENCY.
The CUMULATIVE PROBABILITY column accumulates the running sum. This is used for deciding in which category an observation falls.
The VALUE*FREQUENCY column is used to compute the expected value.
The OCCURRENCES tallies the total number of occurrences for that category from the simulation itself.
The CUMULATIVE OCCURRENCES accumulates the running sum of occurrences.
PERCENTAGE expresses the occurrences divided by the total number of trials.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Statistics
This module can be used to compute the mean or expected value or weighted average of data, grouped data or a probability distribution. In addition, it is also possible to perform Normal distribution computations.
The initial input is the number of values and the type of data. For a list of data, this creates a table with one column for data entry while for a frequency or probability table this creates a table with two columns for numbers. The first column contains the values (x) while the second column contains the frequencies or weights or probabilities.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Excel OM/QM Menus
There are two menus available in this version of the software. The Excel OM/QM menu lists all of the modules in alphabetical order. The Heizer menu lists the modules in order of their usage chapter by chapter.
In Excel 2007 and later on PCs the menus appear on the Excel OM/QM tab while in earlier versions of Excel and on a Mac with Excel 2011 the menus appear on Excel's Main Menu bar.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Models
All models have a similar appearance. There is a shaded in area into which the data shoudld be entered. For most models, as you enter the data, the results will appear since the cells have been pre-coded with the appropriate Excel formulas. For some models (linear programming, transportation, assignment, project management crashing) you will need to use Excel's Solver to obtain the results. For one or two models, button shave been added to the spreadsheets to run the algorithms that generate the results.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Markov Chains
The initial input on the creation form is given by entering
the problem title
the number of states (rows and columns)
the number of transitions to be displayed
The data consists of the one-step probabilities of transitioning from one state to another.
Results
The original transition matrix will be multiplied the number of times specified on the creation screen. If the markov chain consists of one recurrent class of states then you may use Solver in order to get the steady state results.
Networks
Two network models are available - maximal flow and shortest path. Both of these models are formulated as linear programs using incidence matrices.
The initial input on the creation form is given by entering
the problem title
the number of nodes
The data consists of the distance table or flow capacity.
Results
You must use Solver in order to get the results.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017
Games
The initial input on the creation form is given by entering
the problem title
the number of strategies for row
the number of strategies for column
The data consists of the game table itself with the payoffs given by column to row.
Results
After entering the data, use Solver in order to get the results. The results include
the value of the game
the row and column mixes. That is the percentage of time that each row should be played and the percentage of time that each column should be played.
the weighted average of each row against column's optimal mix and each column against row's optimal mix.
Excel OM/QM v5 by Howard J. Weiss, Temple University, (c) 2017