Assignment
Learning Objectives:
• To learn and use What-If Analysis in Excel
Part I: multiple choices
Please select the best answer unless it explicitly specifies "check all that apply":
1. Which of the following is the best description of a substitution value for a variable?
(a) Is any value that replaces the original input value of the variable in a data table
(b) Is a none-negative value that replaces the original input value of the variable in a data table
(c) Is a none-positive value that replaces the original input value of the variable in a data table
(d) None of above
2. Which tools are best suited to calculate the impact of multiple interest rates on an auto loan? (select the best answer)
(a) Goal Seek
(b) Scenario Manager
(c) One-variable data table
(d) None of above
3. Which tool is most effective when comparing the impacts of various combinations of interest rates and down payments on a mortgage:
(a) Goal Seek
(b) One-variable data table
(c) Two-variable data table
(d) Scenario Manager
4. This tool calculates the value required in a single cell to produce a desired result with in a related cell:
(a) Goal Seek
(b) One- or Two-variable data table
(c) Scenario Manager
(d) None of above
5. Which dialog box can be used to create a series of numbers that have a pattern:
(a) Scenario Summary Dialog box
(b) Series Dialog box
(c) One-variable dialog box
(d) None of above.
6. Where should you put the formula or reference of a formula in a two-variable data table:
(a) Top Right corner of the data table
(b) Top Left corner of the data table
(c) Bottom Left corner of the data table
(d) Bottom Right corner of the data table
7. Doug would like to purchase a new automobile. He has budgeted for $600 per month. If the interest and number of payments are constant variables that cannot change, which analysis tool should Doug use to calculate the amount to spend on a car:
(a) Goal Seek
(b) One- or Two-variable data table
(c) Scenario Manager
(d) None of above
8. Which dialog box enables you to specify the result cells for a scenario summary report:
(a) Scenario Summary
(b) Scenario Values
(c) Add Scenario
(d) None of above
9. Which of the following tools can incorporate constraints when adjusting a variable's value:
(a) Goal Seek
(b) Data tables
(c) Scenario manager
(d) None of above
10. You need to create a series of substitution values for a variable by using the series dialog box. The starting value is 3.00%, and the ending value 8.00%, what is the step value if you want to create 10 substitution values.
(a) 1%
(b) 2% (c) 0.5%
(d) None of above
Part II: project
Please follow the instructions below to make a Microsoft Excel workbook:
1. Please download e06m1House.xlsx from Blackboard and save it on your computer
2. Open e06m1House.xlsx and save it as e06m1House _Lastname_Firstname.xlsx
3. Use Goal Seek to determine the total finished square footage to meet the total cost goal of $350,000.
4. Enter a series of total square footages ranging from 1,800 to 2,600 in increments of 200 in the range D6:D15. Apply Blue font and Comma Style with zero decimal places to the series. Enter references to the base cost and total cost in the appropriate cells on row 5.
5. Complete the data table using the appropriate input cell. Apply custom number formats to give appropriate descriptions to the second and third columns. Apply these formats to the headings: bold, center, and Blue font
6. Identify the square footage, base price, and total cost that come closest to their goal. Apply Blue, Accent 1, Lighter 40% fill color to those cells in the data table
7. Copy the square footage substitution values to the range H6:H15 and remove the fill color. Enter these lot price substitution values in the range I5:K5: 90000, 96000, and 102675. Format these values with Accounting Number Format with zero decimal places and Blue font.
8. Enter the reference to the total cost formula in the appropriate location for the second data table. Complete the data table using the appropriate input cells. Apply a custom number format to the reference to the formula cell. Apply bold and Blue font color to that cell. Apply Blue, Accent 1, Lighter 40% fill color to the total price in each column that comes closest to their goal.
9. Format results in both tables with Accounting Number Format with zero decimal places.
10. Please create a new worksheet named MultipleChoice and write the 10 multiple questions' answers on Column A. For example, A1 should be the answer to question 1, A2 should be the answer to question 2, A3 should be the answer to question 3, . . . , A10 should be the answer to question 10.
11. Save and close the file.
12. Submit the file e06m1House _Lastname_Firstname.xlsx on Blackboard.
Attachment:- e06m1House.xlsx