Posted: February 8th, 2015

Creating a Decision Support System

Creating a Decision Support System

Project description
A leading supplier of grapes to the wine- producing industry in California, On the Vine Grapes, wants to expand its delivery services and expand its reach to market by increasing its current fleet of delivery trucks. Some of the older vehicles were acquired through closed-end leases with required down payments, mileage restrictions, and hefty early termination penalties. Other vehicles were purchased using traditional purchase-to-own loans, which often resulted in high depreciation costs and large maintenance fees. All vehicles were acquired one at a time through local dealers.

On the Vine Grapes has asked you to assist in developing a lease/ buy cost analysis worksheet in order to make the most cost-effective decision. Currently the director of operations, Bill Smith, has identified a 2005 Ford F-550 4×2 SD Super Cab 161.8 in. WB DRW HD XLT as the truck of choice for the business. This vehicle has a retail price of $ 34,997.00 or a lease price of $ 600/ month through Ford Motor Credit Company.

Here are some basic fees and costs that you need to factor in:
1.Lease Costs ? Refundable security deposit $ 500
? First month’s payment at inception $ 500
? Other initial costs $ 125
? Monthly lease payment for remaining term $ 600
? Last month payment in advance No
? Allowable annual mileage 15,000
? Estimated annual miles to be driven 20,000
? Per mile charge for excess miles 0.10

2.Purchase Costs ? Retail price including sales taxes, title $ 34,997
? Down payment $ 4,000
? Loan interest rate 8.75%
? Will interest be deductible business or home equity interest? Yes
? Is the gross loaded weight of the vehicle over 6,000 lbs? Yes

3. Common Costs and Assumptions ? Total lease/ loan term 36
? Discount percent 8.75
? Tax bracket- combined federal and state 33%
? Business use percentage 100%

Some particulars you should know
1.Buyorlease.xls Spreadsheet ( Excel file).
2.In the file BUYORLEASE.xls is a template you can use to enter the information. There is also a sheet that has been developed to assist you with the annual depreciation for an automobile. (Attached)
3.Create a detailed summary sheet of the lease/ buy option for On the Vine Grapes.
———-
Added on 06.02.2015 11:19
See attached Excel file and above instructions.

Lease or Buy

Lease
I. Lease Assumptions                        Assumptions
Refundable Security Deposit                        $500
First Month’s Payment at Inception                        $500
Other initial costs, if any                        $125
Monthly Lease Payment For Remaining Term                        $600
Last Month Payment in Advance?                    Type “Yes” or “No”      No
Allowable Annual Mileage – If not applicable, enter “0”                        15,000
Estimated Annual Miles to Be Driven, If not applicable, enter “0”                         20,000
Per Mile Charge for Excess Miles                        $0.10

Buy
II. Buy Assumptions                        Assumptions
Retail Price including sales taxes, title, etc.                        $34,997
Down Payment (If paying cash, down payment equals Retail Price)                        $4,000
Loan Interest Rate                        8.75%
Will Interest Be Deductible Business or Home Equity Interest                    Type “Yes” or “No”      Yes
Resale Value at End of Term – This may equal the lease option price
Is the gross loaded wait of the vehicle over 6,000 lbs? The answer is typically                    Type “Yes” or “No”      Yes
yes for large vans, pickups and large sports utility vehicles.

Common
III. Common Assumptions                        Assumptions
Total Lease/Loan Term  –  24, 36, 48 or 60 months                        36
Discount Percent                        8.75%
Tax Bracket – combined federal and state                        33.00%
Business Use Percentage                        100.00%