Installation

The software installs in the same fashion as most Windows programs.

•	The software is located on the CD that accompanied your textbook.

•	The installation program will ask you for a directory name. The default directory is Program Files\Excel OM  but you may change this if you like.

•	After entering the name the installation will copy the files to that directory and create the program group.

•	In addition, a shortcut key will be placed on the desktop.

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 Prentice-Hall textbook.

When the installation is complete you may get started.

Excel OM  by Howard J. Weiss, Temple University, (c) 2008


Startup

There are a few different ways that Excel OM can be started.  The easiest way is to click on the Excel OM 3 icon that is placed on your desktop at installation. If you are already in Excel then you may Open the file ExcelOM.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 that are posted at prenhall.com/weiss.

Using Excel OM/QM

Regardless of the version of Excel that you have, two menus, named Excel OM and Heizer, will be added to Excel. In Excel 2007 you can find these menus on the Add-Ins Tab of the Excel 2007 ribbon. In Excel 2003 and earlier you will find these menus on the main Excel menu bar.

-----------------------------------------------------------------------------------------------------------------------------------

Add-ins

Excel OM is an addin and it is possible (though I do not recommend it) to set up Excel so that Excel OM automatically loads every time that Excel loads. The means for doing this depends on your version of Excel.

Excel 2003 and earlier

In order to do this simply use Tools, Addins, Browse; find ExcelOM3.xla/ExcelQM3.xla in the directory and click OK.

Please note that this product and the Crystal Ball addin do not coexist well. Do not have both loaded at the same time.

Excel 2007

Click on the Office button in the upper left hand corner, click on the Excel Options button at the bottom, Click on Add-Ins at the left. Click on GO near the bottom and then Browse your computer for ExcelOM3.vba/ExcelQM3.VBA

Excel OM  by Howard J. Weiss, Temple University, (c) 2008


Creating a problem
Problem/Sheet Creation

To start a problem, go to the Excel OM Menu or the Heizer menu on the main Excel menu bar at the top of the window or on the Add-Ins tab of the Excel 2007 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.

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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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 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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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 , Prentice-Hall, 1959, page 462.The table is designed  in order to ensure that both α and ß will be met or bettered.  The program also displays the ACTUAL α 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  by Howard J. Weiss, Temple University, (c) 2008


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 Prentice-Hall 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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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 Menu or the creation screen.

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.

Sheets

The two options in this box refer to the creation of new spreadsheets and to the creation of charts/graphs that would be associated with the sheets.

•	Create each problem on a new sheet

It is possible to have each new problem placed on the current spreadsheet (thereby erasing the information there) or to have each new problem begin on a new spreadsheet. There are advantages and disadvantages to both approaches.

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

•	Border

Check here if you want borders around the data and results

•	Grid lines

These can be turned on/off by using the checkbox

•	Row/Column Headings

These can be turned on/off by using the checkbox

•	Instruction visible

These can be turned on/off by using the checkbox

Excel OM  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


Technical support

In the event of any questions or problems please contact dsSoftware@prenhall.com or visit

www.prenhall.com/weiss

Please note that Excel OM can not be loaded with Crystal Ball simultaneously.

Excel OM  by Howard J. Weiss, Temple University, (c) 2008


Factor Weighting
Factor Rating

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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


Solver

The following instructions are available by clicking on Help in Excel and searching for the term "Load solver".

Excel 2007

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 OM  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


Clear sheet

This will clear the current cells and place an instruction to go to the menu in cell A1.

Excel OM  by Howard J. Weiss, Temple University, (c) 2008


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

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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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.

Excel OM  by Howard J. Weiss, Temple University, (c) 2008


Toolbar
Excel OM Toolbar

When you open Excel OM a toolbar will be added. In Excel 2007 the toolbar will be readily visible on the Add-Ins tab. To view the Toolbar in earlier versions of Excel use the OM menu.

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

•	Windows help (this file)

Excel OM  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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 software. (Default value is C:\Program Files\ExcelOM3.

NOTE: The maximum number of nodes permitted in the tree is 30.

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  by Howard J. Weiss, Temple University, (c) 2008


Decision Analysis

See Decision Tables or Decision Trees or One Period Inventory

Excel OM  by Howard J. Weiss, Temple University, (c) 2008


Location

Models

Center of Gravity

Factor Weighting

Cost-Volume Analysis

Locational Break-Even Analysis

Excel OM  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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.

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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008


Excel OM Menus

There are two menus available in this version of the software. The Excel OM 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 the menus appear on the Add-Ins tab while in earlier versions of Excel the menus appear on Excel's Main Menu bar.

Excel OM  by Howard J. Weiss, Temple University, (c) 2008


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  by Howard J. Weiss, Temple University, (c) 2008