HMWK 1 - Ohio University



Part II. Lease or Buy a Car?

Analysis

Please see L3 Decision Support (1 of 3)

Requirements Definition

Design a system capable of assisting in lease vs. buy decisions. The system should be as user friendly as possible and should have clear directions.

Design

Software:

( Application Design—Software—Deliverable

1. Design the user interface. Below is one possible mockup but you may make your own as you deem appropriate. However, you must test your system with the data in this mockup.

a. You must add appropriate validation to your system. Create a table as in the L1 and L2 exercises indicating what your validation constraints are. Each input (cells with boxes) should have appropriate validation. The calculations/references can be done simultaneously.

b. Want more challenge? For extra credit name all of your data cells and use those names in your formulas. To name a cell, click on it and type its name in the name box. Each cell must have a unique name. Use underscores rather than spaces for multi-word names. Named cells will also make your formulas easier to understand and debug.

[pic]

c. More challenge still? For more extra credit allow for a down payment on the lease. Some leases require a down payment (say $2000) that may or may not include the first month’s lease payment.

Example System

|Lease or Buy? | | | | |

|Please enter user directions here. | | | |

| | | |Lease | |

|Variables | | |Monthly lease payment |(369) |

|Time period (in years) |3 | |Present value of lease payments |12,020 |

|Payments per Year |12 | |Additional fees due at signing |75 |

|Interest rate on loan |7.00% | |Cost of lease payments and fees |12,095 |

|Sales tax rate |6.25% | | | |

| | | |Terminal Value | |

|Buy | | |Residual value at end of lease |11,941 |

|Purchase price of vehicle |20,667 | |Tax at end of lease |746 |

|Additional fees due at signing |20 | |Additional fees at end of lease |20 |

|Promotions (enter as negative number) |(3,000) | |Total to purchase vehicle at end of lease |12,707 |

|Sales tax | | |Present value of vehicle purchase after lease |10,307 |

| |1,292 | | | |

|Real Cost to Buy |18,979 | |Real Cost to Lease |22,402 |

| | | | | |

| | | | | |

| | | | | |

| | | |Summary | |

| | | |Real cost to buy |18,979 |

| | | |Real cost to lease |22,402 |

| | | |Difference (extra cost)/savings by leasing) |(3,423) |

Development

( Application Development—Software—Deliverable

1. Develop the software, validation and documentation for your system.

• Paste Special (PEM) the spreadsheet and its formulas using the test data above.

• After setting up your formulas, enter the data validation that you designed earlier.

Implementation

( Implementation—Business Case—Deliverable

1. Test your software with the following scenarios:

• Assume monthly payments

• Assume a 7% loan cost for both examples

• Assume a sales tax rate of 6.25%

1a. Nissan 350Z

• Buy

o Purchase Price: $26,930

o Promotion: $1,000

o Additional Fees Due at Signing: $100

• Lease

o Time Period: 36 months

o Monthly Payment: $565

o Additional Lease Fees: $100

o Market value at end of lease: $15,000

1b. Mazda RX8

• Buy

o Purchase Price: $30,680

o Promotion: $500

o Additional Fees Due at Signing: $80

• Lease

o Time Period: 48 months

o Monthly Payment: $480

o Additional Lease Fees: $30

o Market value at end of lease: $18,000

2. Describe in a paragraph the significant factors about each of these scenarios that would favor leasing over buying or vice versa.

Computer Skills:

• Software: Excel, Word

• Formulas, formatting of spreadsheet

• Interface usability

Connections:

• Marketing, Finance, and Accounting

Resources:



................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download