Reference no: EM132910425

CMIS 2250 MODELS, SPREADSHEET ENGINEERING, AND SCENARIOS

You are to complete this assignment your Data Analytics group- and complete all remaining assignments and the project in the same group. Individual submissions will not be accepted.

• Word Document
o Classification exercise
o Problem statement
o Brief story of each What-if cases in Scenario 2 and Scenario 3
o Sensitivity Analysis of the assumption parameters: least impact and greatest impact
• Excel Spreadsheet
o a completed model of John's plan including at least 3 scenarios (base case + two others)
o spreadsheet model follows principles described in Powell textbook Modeling

Introduction
In data modeling, it is important to first organize the data (numbers) and information available into the following categories:
1. Assumptions: Facts that will be held constant that will simplify a data model. This will typically be facts that tend to change over time. For example, trying to predict future interest rates is difficult, so assume inflation to be at 2% into the future). Assumptions will either be a parameter or part of the goal. These are values John can't (or won't change) to try and reach his goal.
2. Parameters: The inputs that will be used in the spreadsheet calculations.
a. Variable Parameters: Facts that can be changed and adjusted to meet a goal. These are facts and values that can be influenced. For example, spending habits can be adjusted if budgets cannot be initially met.
b. Assumptions Parameters: Facts that will not be adjusted they are bound by the assumptions stated.

3. Goal: The outputs that will be calculated from the parameters. May include non-numerical facts that are important to note.

4. Irrelevant: Facts that have no value to the problem we are trying to solve. If it can't be represented in the spreadsheet (in numbers), it's not relevant.
After the data is categorized, a problem statement can be formulated (i.e. what are we trying to solve). The problem statement should also state any key assumptions.

Word Deliverable
John is saving for a house and is planning to make a purchase after saving for an additional two years from today. He currently has a planned budget, an idea of the purchase price of a house, and savings for a down payment and would like to consult you to ensure his expectations are realistic. The statements below outline John's current financial situation.
<complete all answers in the template provided for submission>

1. Classify the following facts into Irrelevant, Assumption: Goal, Assumption Parameter, Variable Parameter, and Goal. Explain your choice in the justification section. (Complete list in template file)

Statement Classification Justification
John currently has $20,000 in savings
Current mortgage interest rate is 3.00%

2. With the facts classified, formulate a problem statement (1-2 sentences and should be in the form of a question) that includes all goals and key assumptions. In other words, what is john trying to solve under what general circumstances?
Introduction
After gaining an understanding of a problem to solve, the next step in the problem-solving process is to create spreadsheet to enable data analysis. The main sections of the spreadsheet are as follows:

1. Assumptions Parameters: The name and value of each assumption parameter declared in from the problem analysis during the modeling phase. For example: Inflation

2. Decision (Variable) Parameters: The name of each variable parameter. Note the values stated in a case will form the base case and will be entered into the Scenario 1 (base) column

3. Goal (Output): The resulting calculations from the assumption parameters and variable parameters. Note the values will be calculated from the single set of assumptions and each set of variable parameters under each scenario.

4. Calculations: Any additional calculations that will show you the steps needed to get to the result.

Excel Deliverable
After completing the fact classification, create a spreadsheet model in preparation for data analysis using the provided "Spreadsheet Model Template" Excel file. The template starts with a limited number of rows. Therefore, insert new rows to each section as required.

1. We will first set up the base case (scenario 1). The base case is the scenario based on what is currently known and assumed (from the facts outlined earlier in the assignment). Complete the sections of the spreadsheet as follows:
• Title: Name of the case
• Assumption Parameters: Enter the assumption parameter names into the red boxes and values adjacent from the modeling exercise.
• Variable Parameters: Enter the variable parameter names into the blue boxes and the values under the Scenario 1 (base) column.
• Goal (Output): Enter the goal (output) names into the green boxes and the values under the Scenario 1 (base) column.

2. Create the calculation formulas (green) for Scenario 1 (base) with the following guidelines:
a. Interest from savings will be compounded annually for two years with the savings interest rate. (Current Savings + New Net Income - All Annual Expenses).
b. Ensure your final output is the total savings after two years.

c. Assumption Parameters Values should use absolute references in the calculation formulas. For example, in the screenshot below, Assumption 1 Value is absolutely referenced in the formula in cell C26 with ‘$B$5'. By making a cell reference absolute, formulas will not shift the absolute cell references when formulas are copied. You can press the ‘F4' key after selecting cell B5 to automatically insert the characters, or you can manually type them in.

3. With the base case completed, we can conduct ‘what-if' analysis. Keeping the same assumption parameters, try changing a combination of the variable parameters (blue) for Scenario 2 and Scenario 3
NOTE: You can alternately use the Scenario Manager tool described in Chapter 4 for this part.

4. If the absolute references were set up correctly, you can click your calculation cell (green) at the bottom right and drag across the columns to copy the formulas across to Scenario 2 and Scenario 3

5. With the three scenarios set up, describe John's situation in Scenario 2 and Scenario 3 so we can tell a brief story, to John, about the numbers selected in each of the ‘what-if' scenarios. Be specific about the changes John will need to make and the results that he will achieve.

6. With scenarios and respective formulas created, we will conduct sensitivity analysis. Experiment with changing the Assumption Parameters (red), selecting one to change at a time, to higher and lower values and observe how the changes impact the calculations (green) for each scenario. Ensure to change them back to their original values after conducting sensitivity analysis. Be realistic about those parameters that could change.

Attachment:- Modeling and Spreadsheet Engineering Template.rar