Determining Terms Of Automobile Leases

MBA 6203 Students:
You will use spreadsheet software (e.g. Excel) to complete Case 12-37 in the textbook, “Determining
Terms of Automobile Leases”. Use the template provided on Blackboard. The case requires that you
establish a “base case” and then reevaluate with different inputs. Instead of adding more worksheets or
copying the formulas, etc., you will use the SCENARIO MANAGER in Excel. Follow these steps:
1. On the “Part a (Base Case)” worksheet, enter the required information in the green-shaded cells
2. The Lease Payment should be a calculation using the formula provided in the “HINT” in the
textbook
3. Go to Data >> What-if-Analysis >> Scenario Manager
4. Click “Add”
5. Name this first scenario “Part a (Base Case)”
6. In the “Changing Cells” space, select all of the green-shaded cells
7. Click “Ok”
8. For the Base Case Scenario (Part a), DO NOT change any of the Scenario Values
9. Click “Add”
10. Name the next scenario referencing the requirement in the case (e.g. Part b or Requirement b)
11. In the “Changing Cells” space, select the cells that need to be changed for the respective
requirement. For example, for Part b, you would select the down payment cell.
12. Repeat Septs 9 – 11 as needed to create all required scenarios
13. Click “Summary”
14. On the Scenario Summary pop-up, ensure that the Scenario Summary Report Type is selected.
15. Enter the cell references of the results that you want included in your summary report. (Excel
will automatically enter cell references. You should confirm that these are what you want
reported, and make changes, if necessary. You are interested in what the new lease payments
would be in the different scenarios, so those are the cells that should be included.)
16. Click “Ok”
17. Excel will produce a Scenario Summary worksheet that includes a summary of the changes and
new calculations for each different scenario.
18. Save the workbook with the Scenario Summary worksheet included and submit via the link in
Blackboard