limited finance test 2.5 hour
Csqiezi
Lecture01.pdf
Lecture 01
C01: June 21, 2021
C02: June 22, 2021
Instructors:
Seyyed Hossein Alavi Zdravko Dimitrov
3QA3/2DA3
Management Science for Business
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:
2
Zdravko Dimitrov
Master of Business
Administration (MBA)
Bachelor, Business
Administration (BBA)
Lean Six Sigma, Green Belt
Contact info:
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
Office Hours (on Zoom):
Tue and Wed 6:00-8:00pm
Zahra Mashayekhi,
PhD student
Office Hours (on Zoom):
Mon and Thu 4:00-6:00pm
Sina Khosravinia,
PhD student
Office Hours (on Zoom):
Tue and Thu 11:00am-1:00pm
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
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
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
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 Finance
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
