Determining Terms of Automobile Leases (Requires Spreadsheet)

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.