limited finance test 2.5 hour

Lecture 01

C01: June 21, 2021

C02: June 22, 2021

Instructors:

Seyyed Hossein Alavi Zdravko Dimitrov

3QA3/2DA3

Management Science for

About The Instructors

S. Hossein Alavi

 Ph.D. candidate in

Management Science

(Operations Management area)

 M.Sc. in Industrial Engineering

 B.Sc. in Industrial Engineering

 Contact info:

 [email protected]

2

Zdravko Dimitrov

 Master of

Administration (MBA)

 Bachelor,

Administration (BBA)

 Lean Six Sigma, Green Belt

 Contact info:

 [email protected]

mailto:[email protected]

mailto:[email protected]

Class Information

3

Section Days Time Room Instructor

C01

C02

Monday & Wednesday

Tuesday & Thursday

6:30 – 9:30 pm

6:30 – 9:30 pm

Virtual – Zoom

Virtual – Zoom

Seyyed Hossein Alavi

Zdravko Dimitrov

Teaching Staff and

Contact Information

4

Instructors:
Zdravko Dimitrov Seyyed Hossein Alavi

[email protected] [email protected]

Office Hours (on Zoom):

Tue and Thu 9:30-10:30pm

Office Hours (on Zoom):

Wed and Fri, 4:00-6:00pm

Teaching Assistants:
Zeinab Vosooghi,

PhD student

[email protected]

Office Hours (on Zoom):

Tue and Wed 6:00-8:00pm

Zahra Mashayekhi,

PhD student

[email protected]

Office Hours (on Zoom):

Mon and Thu 4:00-6:00pm

Sina Khosravinia,

PhD student

[email protected]

Office Hours (on Zoom):

Tue and Thu 11:00am-1:00pm

mailto:[email protected]

mailto:[email protected]

mailto:[email protected]

mailto:[email protected]

mailto:[email protected]

Zoom Calls Overview

5

 How to use Zoom

A little ice-breaker

6

 Let’s go to Zoom chat and type the following information

about yourself:

 Your level of experience with Excel (i.e. beginner,

intermediate, advanced)

 What would you like to learn in this course?

Course Outline Review

Intended Learning Outcomes

 Learn how to use:

 Linear/Integer/Nonlinear Programming,

 Decision Analysis,

 Simulation, and

 Queuing Models

to model and analyze business problems

 Learn how to use these tools in Excel to analyze and solve

business problems

8

Course Materials – Textbook

 Textbook:

— Managerial Decision Modeling with Spreadsheets

— Balakrishnan, Render, B., and Stair, R.M.,

— Third edition, Pearson/Prentice Hall (2013)

 Online Companion:

— http://wps.prenhall.com/bp_balakrishnan_mdms_3/

 downloadable Excel data files for the examples discussed in the textbook,

 the software that will be used, and

 some internet case studies that help you to practice the skills acquired from
the class

 Lecture Notes:

 Will be posted on Avenue to Learn

9

http://wps.prenhall.com/bp_balakrishnan_mdms_3/

Course Materials – Textbook

 The textbook is highly recommended but is not required.

 Any new books, used books, electronic books, etc. can be used.

 The electronic book is sold via Access Code in the Bookstore for
$75.

 To purchase that Access Code click here to go to the Bookstore Buy
Access Codes Online.

 Other editions (e.g. the 2nd edition) of the textbook are not as
useful as the 3rd edition.

 The international version of the 3rd edition has a major
shortcoming so it should be avoided:

 It is missing ‘Chapter 4: Linear Programming Sensitivity Analysis’,
which is one of the most difficult topics in the course.

10

https://campusstore.mcmaster.ca/cgi-mcm/ws/txsub.pl?wsTERMG1=212&wsTERMDESC1=Spring%2FSummer+2021&wsDEPTG1=COMMERCE&wsDEPTDESC1=COMMERCE&wsCOURSEG1=3QA3&wsSECTIONG1=DAY+C02&crit_cnt=1

Course Materials – Software

 Students are encouraged to use their computers to follow in-

class Excel models.

 The following software is used in the course:

 Excel: Excel 2010 or later is preferred

 https://office365.mcmaster.ca/microsoft-365-for-students-start-

here/

 Excel Solver add-in: Available in Excel on Windows and Mac.

 TreePlan: Excel add-in for building and analyzing decision trees.

Available on Avenue.

 Queuing Modules: Excel templates for analyzing queuing

problems. Available on Avenue.

11

https://office365.mcmaster.ca/microsoft-365-for-students-start-here/

Course Materials – Software

 First completely update Microsoft Office. Then completely update
Excel. If Excel is not completely updated the add-ins and modules
may not work.

 Students may need to set the security setting on Excel to
“medium” to “enable” the “macros” in these programs.

 All software runs on a Windows; students using a Mac must ensure
that the software runs properly on their computer.

 Students will be tested at the Midterm Exam, at the Final
Exam and two assignments on their proficiency with the
material and the software.

12

Evaluation

13

 The Final Exam is not comprehensive; rather it only tests the material since the

Midterm Exam.

 Practice Problems for self-study are assigned (see the Course Schedule).

 All Practice Problems and solutions will be posted on Avenue.

 Additional problems may be assigned during the course.

Component Best of below weighting schemes Description

Assignment 1 10% Individual, submitted online via Avenue

Assignment 2 10% Individual, submitted online via Avenue

Midterm Exam 40%
Virtual, problems & computer -approx. 2

hours and 30 mins,

Final Exam 40%
Virtual, problems & computer -approx. 2

hours and 30 mins

Total 100%

Exams

 Around 35-40 questions: True/False, Multiple Choice

 Around 0-5 questions: Short Answer, Problem Solving

 At least 1 Excel problem.

 To prepare, follow the practice questions.

14

Course Roadmap

 Lecture 1: Introduction

 Lecture 2: Linear Programming, Graphical & Computer Methods

 Lecture 3: Standard LP Problems, Transportation Problems

 Lecture 4: LP Sensitivity Analysis

 Lecture 5: LP Sensitivity Analysis, Integer/Nonlinear Programming

 Lectures 6-8: Decision Analysis

 Lectures 9: Simulation

 Lecture 10: Queuing or Waiting Line Models

 Lecture 11: Solving practice problems
15

Expectations

 Instructors are expected to:
 Help you learn the materials
 Be available for your questions during office hours
 Give you feedback in a timely manner

 TAs are expected to:
 Answer your questions during their office hours
 Reply to your emails (when you are unable to attend the office hours)
 Give you feedback on your Midterm exam and assignments

 Students are expected to:
 Attend the lectures
 Participate by using their laptop, following and implementing the

models
 Do the practice problems

16

Chapter 1

Decision Modelling

Intended Learning Outcomes

18

 At the end of this lecture, you will:

 become familiar with the concepts of decision modeling.

 become familiar with Excel spreadsheet

 be able to use basic Excel features and functions.

What is Decision Modelling?

 Model Definition:

—A representation of real-world problems or phenomenon, which is

—Built upon a set of relationships and logical assumptions

 Model Types:

—Mental models

—Visual models

—Physical/Scale models

—Mathematical models

19

Arrangements for a new apartment furniture

A set of drawings or the sketch of a building

Car designers, wind resistance and fuel consumption

All decisions variables can be quantified and the

relationships between the variables, as well as

the given assumptions can be mathematically

expressed.

What is Decision Modelling?

 Decision Modelling:

— The development of a mathematical model of a real-world problem
scenario or environment

— Not affected by personal bias, whim, emotions, and guesswork.

— Also referred to as: Management Science, Operations Research, or
Quantitative Analysis

— Example
— A company sells two products

— Product 1 requires 9 hours of labor and 5 units of material

— Product 2 requires 5 hours of labor and 7 units of material

— The company have 800 hours of labor and 200 units of material

— Profit: product 1: $150/unit product 2: $130/unit

20

Types of Decision Models

 Deterministic (Chapters 2-6):

—All input data values are known with certainty

—Example: the resource planning problem

 Probabilistic or Stochastic (Chapters 8-10):

—Some input data values are uncertain

—Example:
— A store manager: decide on the amount of a product to be ordered before

demand arrives

— The customer demand is highly unpredictable, so he/she must
incorporate this uncertainty into decision-making, to avoid:

— excessive inventory, while maintaining a certain service level.

—The decision must be made before the information is known

—Please read Appendix A to refresh your memory (Probability
Concepts and Applications)21

Example: Newsboy (Newsvendor) Problem – Deterministic

—Newsboy must decide how many newspapers to buy in the morning

— knowing that unsold copies will be worthless at the end of the day

—Cost: $1.5 each, Revenue: $2 each (Profit: $0.5 each)

—Demand: 50 papers/day

Decision: buy 50 newspapers

Total Profit: 50 X ($2.00-$1.50) = $25

22

—Newsboy must decide how many newspapers to buy in the morning

— assuming that unsold copies will be worthless at the end of the day

—Cost: $1.5 each, Revenue: $2 each

—Demand: Unknown?? Prob(D=10)=0.4 and Prob(D=100)=0.6

Decision: buy 50 newspapers

Profit:

if demand=10 10 X $2 – 50 X $1.5 = -$55

if demand=100 50 X $2 – 50 X $1.5 = $25

Other decision: buy 100 newspapers

The profit could be 100 X ($2-$1.5) = $50

The profit could be 10 X $2 – 100 X $1.5 = -$13023

Example: Newsboy (Newsvendor) Problem – Stochastic

Role of Spreadsheets

 Spreadsheets can handle many decision modeling techniques

 A good alternative to commercial software, e.g., CPLEX, GAMS, AMPL

 Convenient for educational purposes and more user-friendly in terms

of data processing and solution analysis

 We use Microsoft Excel built-in functions, procedures, and add-ins

 An Excel tutorial is provided in Appendix B

 “Excel Tutorial” in Avenue -> Content -> Excel Tutorial

24

Decision Modeling Steps

1. Formulation:

 Transforming a real problem scenario into a

mathematical model

 Each aspect of the problem is expressed in

math terms

 The most important and challenging part of

DM

2. Solution:

 Solving the mathematical model and finding

the optimal solution

3. Interpretation:

 Analyzing the results and implementing the

solution
25

Figure 1.1 The Decision Modeling Approach – Page 5

1- Formulation

I. Defining the problem:

 Develop a clear and concise problem statement

 The most important part of formulation

II. Developing a model:

 Quantify all factors and relationships under the consideration, and

make assumptions if necessary

 Identify problem parameters and variables

 Develop a mathematical model

III. Acquiring input data:

 Obtain accurate data for use in the model

26

2- Solution

I. Developing a solution:

 Solving a set of mathematical expressions

 Optimal solution VS Good solution

 Trial and error

 Complete enumeration

 Heuristic Algorithms

II. Testing the solution:

 Necessary before analyzing and implementation

 Data Examination

 Model Examination

27

3- Interpretation

I. Analyzing the results:
 Determine the implications of the solution

 Consider actions or changes in organization’s operation

II. Perform sensitivity analysis:
 Vary input and test the changes to the optimal solution

 Post-optimality or What-if analysis

III. Implement the results:
 Needs overcoming organization’s resistance

 Needs a good and workable implementation plan

 Needs a close monitoring-feedback-amend cycle

28

Excel Tutorial

I. Basics

II. Formulas

III. Min / Max Functions

IV. Sum products

V. IF Statement

VI. Multiplication Table

29

Layout of an Excel Worksheet

30

Open
“Lecture 01 – Excel Tutorial and examples – starting file.xlsx”

Example 1: Break-Even Point

 Number of units sold that will cover the total cost:

 Profit = Total Revenue – Total Cost = $0

 Find break-even point for a company:

 Selling Price: $10/unit

 Fixed Cost: $5,000

 Variable Cost: $5/unit

 Profit = $10X − $5000 − $5X=0

 Excel “Goal Seek” and “Data Table”

32

Example 2: Tax Computation

 Sue and Rob want to calculate their tax, based on the
information below:

 Save 5% of total income for retirement, up to $6,000
maximum

 Standard deduction for married couple: $11,600

 Tax brackets for taxable income:
 10% for 0 to $17,000

 15% for $17,001 to $69,000

 25% for $69,001 to $139,350

33

Example 2: Tax Computation (cont.)

 Save 5% of total income for retirement, up to $6,000 maximum

 Standard deduction for married couple: $11,600

 Tax brackets for taxable income:

 10% for 0 to $17,000

 15% for $17,001 to $69,000

 25% for $69,001 to $139,350

If Sue income + Rob income = $ 105,000

 Retirement saving = MIN(6% × $105,000, $6,000) = $5,250

 Taxable income = $105,000 – $6,000 – $11,600 = $80,750

 Tax@10% = 10% × $17,000 = $1,700

 Tax@15% = 15% × $52,000 = $7,800

 Tax@25% = 25% × $11,750 = $2,937.50
34

Example 3: Car

 Find Monthly Payments

 Purchase Price: $18,500

 Down Payment: $2,000

 Trade-In Value: $3,500

 Term of Loan: 4 years

 Annual Interest Rate: 8%

35

Practice Problems

 CHAPTER 1

 Discussion Questions 2, 12

 Problems 21, 22, 23, 24

36