Excel Templates to accompany Operations Management, Eleventh Edition
created by Lee Tangedahl
Copyright © 2012 by The McGraw Hill Companies, Inc. All rights reserved.
Instructions for Using Excel Templates
General Instructions
Index of Templates
Index of Tutorials
Copyright © 2012 by The McGraw Hill Companies, Inc. All rights reserved.
<Back
General Instructions
Templates
The text website contains 21 template workbooks (or files), each workbook corresponds to a chapter or chapter
supplement in the text (e.g. ch04s.xltm is the template workbook for the supplement to chapter 4). The
workbooks include a total of 67 templates – see the list of templates in the Index of Templates in this workbook.
Most of the templates include both the “standard” version of the template and a “basic” version which is provided
as a starting point for students wanting to build their own spreadsheet solutions. See instructions below for
using both standard and basic templates.
Tutorials
The text website contains eight screen-cam tutorials which demonstrate the use of these templates – see the
Software Requirements
The templates are fully functional in Excel 2010 and 2007. The templates can also be saved in a format to be used
with earlier versions of Excel by using File Save As and selecting an Excel 97-2003 worksheet.
Display Resolution
These templates were created for a display resolution setting of 1280 x 960 pixels but can be used with other
settings. Use View/Zoom in Excel to fit a template to the screen size.
Macros and Setting Macro Security
Most of the templates include buttons and spinner controls which are programmed with macro instructions,
The procedure to set macro security in Excel 2010:
Select File Menu (extreme upper left-hand corner of screen)
Select Options (bottom left panel of dialog box)
Select Trust Center (bottom left panel of dialog box)
Select Trust Center Settings… (right side of dialog box)
Select Message Bar
Select the option to “Show Message Bar in all applications when active content … has been blocked“
Select Macro Settings
Select the option to “Disable all macros with notification“
Press OK
Press OK
Opening a Template Workbook (or File)
Although you can open a template workbook directly off the text website, it is probably preferable to first save the
template workbook on your hard drive or USB flash drive (right click on the link and use Save As) and then open
the template workbook. In either case you should enable the macro content (see above).
Saving a Template Workbook (or File)
Contents of the Template Workbooks
A template workbook is made up of worksheets (or pages), each worksheet has a name which appears on the
tab at the bottom of the Excel screen. The first worksheet is the table of contents followed by the templates,
each on a separate worksheet. Each student workbook also contains one worksheet with copies of the
examples in the text and one worksheet with copies of the solved problems in the text (see below).
You can move from one worksheet to another either by clicking on a worksheet tab at the bottom of the Excel
screen or by clicking on a hyperlink like the one to the left, this hyperlink will take you back to the top of this
<Back section.
Basic Templates
he top of the worksheet and the “basic” version below it. There are hyperlinks (like at
The basic templates are simpler and do not have any cell protection, absolute addressing, programmed controls,
Note that the basic templates do not contain absolute addressing to facilitate this copying, but in some cases
you may want to use absolute addressing as you expand on it. Also note that if you select and copy a basic
template with a graph, it will be necessary to correct the range references for the graph to refer to the ranges in
the worksheet you are pasting it into.
Examples and Solved Problems
The student workbooks contain copies of the examples and solved problems in the text, these copies show the
When you open a template workbook, a digit is appended to the file name (e.g. ch02 becomes ch021) to indicate
Entering Data
Enter data only in cells which are shaded like the cell to the right. Input Area
Deleting Data
Many of the templates have a clear button which will enable you to delete all input data from the
shaded cells. For templates without a clear button or to delete selected data from shaded cells,
select the cell(s) and press delete, do not enter a space or zero because many templates will
distinguish between a blank cell and a cell containing the space character or zero.
Programmed Buttons
Programmed buttons (see right) will allow you to clear the data from a
template or to solve the current problem entered in a template – just click on
it to press it.
may appear without a variable increment (see right) or with a variable
Notes
Some of the templates have notes for using the template. The notes are at the Notes
bottom of the template and there may be a hyperlink like the one at the right to take
you down to the notes.
Making a copy of a Template
You can copy an entire template (i.e. the entire worksheet) by right-clicking on the worksheet tab and selecting
Inserting a Blank Worksheet
You can insert a blank worksheet into a template workbook by right-clicking on the worksheet tab of a template
Command
You can enter formulas and cell references with an equal sign (e.g. =5*.243/12 or =H3) and you can link one
Pasting Data into a Template
When you copy data from another worksheet and paste that data into the input (i.e. shaded) area
Printing a Template
You can print a template by simply selecting the File menu (extreme upper left-hand corner) and selecting
Print, at that point you can select print options and print the template.
Using Trial and Error or Goal Seek
Some problems may require trial and error (e.g. changing the smoothing constant to achieve the lowest MAD),
spinner buttons are provided to facilitate such trial and error.
Using Solver
The Transportation Method (in ch08s and in ch14), and the Assignment Method (in ch17) use the Excel Add-In
Solver. Notes are provided in each of these templates for adding in and using Solver.
Round off
There are several types of round off. First, a digital computer represents numbers as digital
<Back
Excel Templates to accompany Operations Management, Eleventh Edition
created by Lee Tangedahl
Copyright © 2012 by The McGraw Hill Companies, Inc. All rights reserved.
<Back
Index of Templates
(B) – includes Basic template
Chapter Chapter
2 Competitiveness, Strategy, and Productivity 10S Acceptance Sampling
Productivity (B) Acceptance Sampling (B)
13 Inventory Management
4S Reliability ABC Classification System (B)
Reliability Basic Economic Order Quantity (EOQ) Model (B)
MTBF – Exponential Distribution (B) Economic Production Quantity (EPQ) Model (B)
Service Life – Normal Distribution (B) Quantity Discounts (B)
Availability (B) Reorder Point (ROP) with EOQ Ordering (B)
Fixed Order Interval Model (B)
5
Single Period Model (B)
Efficiency (B)
Process Requirements (B)
14 JIT and Lean Operations
Breakeven Analysis (B) Level Capacity Loading
Comparative Breakeven Analysis (B)
5S Decision Theory Expected Number of Breakdowns (B)
16 Scheduling
Sensitivity Analysis Assignment Model (Basic)
6 Process Design and Facility Layout Johnson’s Rule
Forecast Accuracy (B) Aggregate Planning (B)
12 MRP and ERP
Trend and Seasonal (B) Component Requirements
17 Project Management
7 Work Design and Measurement Pert/CPM with Deterministic Time Estimates
Sample size (B) Pert/CPM, Probabilistic Time Estimates
Normal and Standard Times (B) Pert/CPM, Probabilistic Completion Time
Time-Cost Tradeoffs: Crashing
7S Learning Curves
Learning Curves 18 Management of Waiting Lines
Single Channel Waiting Line Model (B)
8 Location Planning and Analysis Multiple Channel Waiting Line Model (B)
Locational Cost-Profit-Volume Analysis Multiple Priorities Waiting Line Model (B)
Center of Gravity Method (B) Finite Source Waiting Line Model (B)
8S The Transportation Model 19 Linear Programming
Excel Templates to accompany Operations Management, Eleventh Edition
created by Lee Tangedahl
Copyright © 2012 by The McGraw Hill Companies, Inc. All rights reserved.
<Back
Index of Tutorials
Tutorial 1: Introduction to Excel 2010
Tutorial 2: Using the Excel Templates
Tutorial 3: Forecasting
Tutorial 4: Transportation Model
Tutorial 5: Aggregate Planning
Tutorial 6: Inventory Management
Tutorial 7: Project Management
Tutorial 8: Linear Programming