Familiarize yourself with advanced Excel features and functions.
finance
Description
Outline
Familiarize yourself with advanced Excel features and functions.
Perform what-if analysis, goal-seek and scenario summary.
Make appropriate charts to present your analysis effectively.
What-If Analysis
You are asked to perform each of the three types of What-If Analyses in Excel using Data
Tables, Scenario Summary, and Goal Seek following these steps.
Data Table Follow these steps to perform a What-If analysis using Data Table.
1. Download the starter file investments2.xlsx from the lab website. Rename the file properly for submission: “Assignment#4,FirstNameLastName”
2. Follow the “Cell Formatting and Alignment” procedure from tutorial instruction.
3. Make a copy of “Base Case” worksheet, move it to the end of the file and rename it as “Data Table”.
4. Create a column-oriented data table that shows how much future value of investment (E15) will change if the interest rate of US Bank changes between 1% and 2% by 0.1% steps. (1 point)
5. Create a row-oriented data table that shows how much total future value of investment (E15) will change if percentage of investment in US Bank changes between 10% and 30% by 5% steps. Make sure that you change the formulas in C11 through C13 such that they always sum up to 100%. Don’t use two-variable data table. (2 points)
Hint: To make sure C11 – C13 sum up to 100%, you can assume you can
assume when percentage of investment in US Bank decreases/increases the
same amount will be added/deducted to/from BOA. You may want to use C13
= 1 – C11 – C12 to satisfy this requirement.
