Engineering economy module
Master
In Maynard (USA)
Description
-
Type
Master
-
Location
Maynard (USA)
-
Start date
Different dates available
This intensive micro-subject provides the necessary skills in Microsoft® Excel spreadsheet modeling for ESD.71 Engineering Systems Analysis for Design. Its purpose is to bring entering students up to speed on some of the advanced techniques that we routinely use in analysis. It is motivated by our experience that many students only have an introductory knowledge of Excel, and thus waste a lot of time thrashing about unproductively. Many people think they know Excel, but overlook many efficient tools, such as Data Table and Goal Seek. It is also useful for a variety of other subjects.
Facilities
Location
Start date
Start date
Reviews
Subjects
- MS Excel
- Microsoft excel training
- Engineering
- Systems
- Materials
- Simulation
- Design
- Spreadsheet
- Microsoft Excel
- Credit
- Excel
Course programme
This MIT OpenCourseWare site is based on the materials from Professor de Neufville's ESD.70J Web site.
Lectures: 4 sessions in one week, 2 hours / session
This intensive micro-subject provides the necessary skills in Microsoft® Excel spreadsheet modeling for ESD.71 Engineering Systems Analysis for Design. It is also useful for a variety of other subjects. Its crux is the dynamic rather than the general deterministic modeling.
Its purpose is to bring entering students up to speed on some of the advanced techniques that we routinely use in analysis. It is motivated by our experience that many students only have an introductory knowledge of Excel, and thus waste a lot of time thrashing about unproductively. Many people think they know Excel, but overlook many efficient tools, such as Data Table and Goal Seek.
We assume students know Excel basics such as referencing, names, functions, and formulas. If you do not know these basics, please get yourself an introductory manual and learn them.
Three units of credit are offered to those who care to complete the assignment. However, we expect most students will simply learn from doing the worked out examples in class, and will not require credit.
Day 1: NPV and sensitivity analysis
Objective: Set up spreadsheet properly for efficiency, set up charts, and understand sensitivity analysis by Data Table
1. Proper set-up of spreadsheets: entire entries of input in separate area; set up by formulas (the question is generated from the self-assessment problem)
2. One-way/two-way Data Table (one-way Data Table: for sensitivity on the discount rate for Plan B while the discount rate for Plan A is fixed; two-way Data Table: for sensitivity on variable costs of Plan A and B; using Goal Seek functionality to find the maximum of variable cost for Plan A so that Plan A is still favored)
3. Drawings (plot the difference of NPVs for the two plans)
4. Reference point for the function of "NPV" (also, students need to know how to set up the NPVs using formulas), examples of the use of "Text" function
1. Opening introduction of the course (objective, schedule, teaching methods, and requirements)
2. Objective of the day's class
3. Question (for setup of the base case), students try 5 minutes
4. Lecturing on proper set-up of the spreadsheet and charts
5. Students set up the spreadsheet for the base case, 15 minutes
6. Posting solution
7. Q&A
8. Question (for sensitivity analysis), students try 5 minutes
9. Lecturing on Data Table/Goal Seek/Drawings
10. Students set up spreadsheet for sensitivity analysis, 15 minutes
11. Posting solution sheets
12. Lecturing on "NPV" and "Text" function
13. Q&A
Day 2: Simulation
Objective: Random number generator (using simple even distribution), how to count, and simulate
1. Random number generator function such as "Rand()"
2. Function such as "Countif"
3. Generating histogram, cumulative distribution function
4. Establish a complete simulation sheet (simulate 2000 times for NPV of Plan A)
1. Objective of the class
2. Question (generate a random realization of demand over years), students try 5 minutes
3. Lecture on random number generation function
4. Students set up spreadsheet for generating realization of demand, 15 minutes
5. Posting solution
6. Q&A
7. Question (how to simulate thousands of times and draw histogram and CDF), students try 5 minutes
8. Lecturing
9. Students set up spreadsheet for simulation, 15 minutes
10. Posting solution sheets
11. Q&A
Day 3: Modeling uncertainties
Objective: Generation of advanced distribution of random variables and the statistical package to analyze available data
1. Triangular, normal, lognormal distribution
2. Exponential growth of demand
3. Trend plus uncertainty (generating a stock price realization path)
4. Try call option
5. Statistical analysis tools to find out important parameters from data set (find out average return and standard deviation for return)
1. Objective of the class
2. Lecture on triangular, normal, and lognormal distribution, exponential growth
3. Question (simulate stock price), tell them basics for stock price movement
4. Students set up the spreadsheet for stock price movement
5. Posting solution sheet
6. Ask how to simulate a call option and give answer
7. Q&A
8. Lecture on statistical analysis tool
9. Question (find out average return and standard deviation for return)
10. Students work on a data set of stock price
11. Posting results
12. Q&A
Day 4: Analyzing flexibility and other topics
Objective: What-if analysis and building contingency rules (flexibility) into the spreadsheet, and further
1. Logical functions ("if", "and", "or", etc.)
2. Value at Risk and Gain (VARG) chart
3. Using spinner to facilitate sensitivity analysis for simulation spreadsheet where Goal Seek does not work
4. Introduction to using Solver
5. Future self-learning: overview of Excel, how to use Excel Help
1. Objective of the class
2. Question (adding flexibility and simulating to see what happens), students try 5 minutes
3. Introducing logical functions
4. Students set up spreadsheet for studying flexibility
5. Posting solution sheets
6. Q&A
7. Questions on VARG, students try 10 minutes
8. Posting solution sheets
9. Q&A
10. Question (find out the maximum of variable cost for Plan A so that Plan A is still favored under uncertain demand), students try 10 minutes
11. Posting solution sheets
12. Q&A
13. Lecturing on self-learning Excel and overview of Excel for future guidance
Don't show me this again
This is one of over 2,200 courses on OCW. Find materials for this course in the pages linked along the left.
MIT OpenCourseWare is a free & open publication of material from thousands of MIT courses, covering the entire MIT curriculum.
No enrollment or registration. Freely browse and use OCW materials at your own pace. There's no signup, and no start or end dates.
Knowledge is your reward. Use OCW to guide your own life-long learning, or to teach others. We don't offer credit or certification for using OCW.
Made for sharing. Download files for later. Send to friends and colleagues. Modify, remix, and reuse (just remember to cite OCW as the source.)
Learn more at Get Started with MIT OpenCourseWare
Engineering economy module