Module 14 Prompt V2
docx
keyboard_arrow_up
School
Northeastern Illinois University *
*We aren’t endorsed by this school
Course
379
Subject
Industrial Engineering
Date
Dec 6, 2023
Type
docx
Pages
3
Uploaded by mehranj1364
Module 14 Homework Prompt
Introduction
This is an individual assignment; while you are permitted to ask the instructor or a classmate
specific questions if you get stuck, you are to complete your own work.
This assignment has two parts. First, you will develop a what-if model that will detail the growth
of an investment account over a number of years. To help you with your what-if model, this
assignment comes with a template; no template is perfect, but this one follows my convention of
input parameters in blue, decision variables in red, constraints are in simple black borders with
relation symbols in between, and the objective function is double-bordered in black.
The information below will provide the context you need to set up the what-if model required for
this assignment. The overall template has been provided for you, and all the input parameters
have been entered into the template, so now you must complete the spreadsheet model by
entering the formulas necessary to calculate what is missing. Read the Problem Statement below
to understand the problem setting, then follow the instructions to complete the assignment.
Remember that your spreadsheet should be built containing formulas to calculate all values using
the input parameters – hard-coding is NOT permitted! Additionally, the template is NOT to be
modified in any way; adding or subtracting cells will be penalized.
Once you are finished, upload your completed workbook the Module 14 Monte Carlo Simulation
Homework folder. If you wish to make changes to your submission, you may click on the title of
the assignment to begin a new submission, but completing a new submission will overwrite the
previous one. As you always should, make sure you save early and often!
Problem Statement
Suppose that you begin planning your investments on your 40
th
birthday (the sooner you start,
the better!), retire at the age of 65, and expect some amount of growth in your salary year to year.
Further suppose that you intend to invest some consistent proportion of your income each year
and wish to understand the final value of your retirement account based on what proportion of
your income you invest each year. Finally, to make the math easier, let’s assume that you deposit
each year’s investment all at once at the beginning of the year.
NOTE: Like the demonstration
video from this Module, you can tweak and reuse this template for your own personal planning :)
Part 1
Suppose you’ve set a goal of having a million dollars in your retirement portfolio by the age of
60; begin by developing a what-if model using the template provided to represent the problem
above i.e. to assess what balance you can expect at the age of 60. To make the math easy, assume
for the moment that both your salary growth and your portfolio’s return on investment will be
consistent each year. To validate your model, enter an Annual Investment Rate of 7.5%; if you
have built the model correctly, you should find a Total Profit of $907,782. Once you’ve
confirmed that your what-if model is correct, use Goal Seek to determine the minimum Annual
Investment Rate necessary to have a balance of at least $1,000,000 when you turn 60.
Part 2
Now turn to the Part 2 worksheet where we will expand on the ideas of Part 1. First, copy the
investment return rate you found through Goal Seek in Part 1, then paste-values-only (found in
the right-click menu) into the according Annual Investment Rate (cell B5) on the Part 2
worksheet. Next, use the techniques of Monte Carlo simulation to simulate your Salary Growth
and Investment Earnings for each year in columns D and G. Then, adapt the formulas you
developed in Part 1 to this version of the problem. If done correctly, you should be able to copy-
and-paste or Fill the formulas from the second row of the table (row 10) all the way down
through year 25. Be careful when you fill the formulas though, and either paste-formulas-only in
the right-click menu or Fill Without Formatting so that you do not overwrite the double border
and bold in cell H33.
Given that this problem is highly uncertain, I cannot give you a reliable number for validation,
but the 25-year balance should likely be somewhere around 1.5 million. To see if your
spreadsheet is producing this result, repeatedly click
under the Formulas tab
and see if the values seem to center around a million and a half.
Obviously, a more scientific approach than the “eyeballing it” would be preferable, which we can
achieve through a Data Table, as we did in the Simulation Replications lecture video. The table
has been mostly prepared for you, and just needs relative references (don’t hard code!) in cells
B36 and B37 to bring in the predicted balance in Year 20 and 25 respectively. After those
formulas are referenced, create the Data Table to include all 1000 Simulation Trials. To facilitate
this, Trials 8 through 998 are hidden.
After completing the Data Table, complete the yellow-highlighted cells to provide summary
statistics for all 1000 replications of both key years in the Data Table. For validation, your 20-
Year Mean should be around $860k-$875k, and again the 25-Year Mean should be around $1.47
million to $1.50 million.
Once both Parts 1 and 2 are completed, save and upload your completed Template to the Module
14 Monte Carlo Simulation Homework folder.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help