Reference no: EM132232431
Case - Gas vs. Diesel Pickup
You are in need of a new pickup on the farm. You are set on a 2018 Dodge Ram 2500 4x4, but you are not sure whether to go with the gasoline powered Hemi or a Cummins diesel engine. Regardless of which truck you purchase, you plan to put 25,000 miles per year on it. Half of these miles will involve hauling heavy loads and/or pulling a trailer. The following information is also known or estimated.
|
Hemi powered
|
Cummins powered
|
|
Purchase price
|
$40,000
|
$48,500
|
|
Fuel mileage (empty)
|
14.0 mpg
|
21.0 mpg
|
|
Fuel mileage (loaded)
|
8.0 mpg
|
15.0 mpg
|
|
Price of fuel (gas, diesel)
|
$2.25/gal
|
$2.60/gal
|
|
Opportunity cost of money
|
6.5%
|
6.5%
|
|
Usable life
|
10 years
|
10 years
|
|
Salvage value (@ end of usable life)
|
$7,000
|
$10,000
|
|
Insurance
|
$700/year
|
$700/year
|
|
Taxes, registration
|
$300/year
|
$300/year
|
|
Repairs & Maintenance
|
$2,000/year
|
$2,500/year
|
|
Shedding/housing costs
|
$100/year
|
$100/year
|
|
Diesel Exhaust Fluid (DEF)
|
0
|
3% of fuel costs
|
Tasks:
1. Build a spreadsheet model capable of calculating the annual cost of owning and operating each pickup. Also, calculate the average cost per mile. Assume a straight line depreciation method.
2. Based on this, which pickup would you choose? Is this what you would have chosen from the beginning? Are there any other factors that should be considered? How do these costs compare to the IRS mileage rate of $0.55/mile?
3. What are some of the uncertain variables in this analysis? Which do you feel have the strongest impact on annual costs of ownership and operating?
4. What is the maximum amount you should pay for the Ram-Hemi if you want the two options to have the same per mile own and operating costs (tip: make sure to include at least 5 decimal places for the target value)? Here, you should use 'Solver' or 'Goal Seek' in Excel to calculate the precise dollar amount that you should pay for the Ram-Hemi.
5. Change the purchase price of the Ram-Hemi back to $40,000. Using 'Solver' or 'Goal Seek' again, calculate the price of diesel ($/gal) that would have to occur in order to achieve a per mile own and operating cost of $0.55/mi.
6. Assume now that all 25,000 miles are under heavy loads. Also assume that the opportunity cost of your time is $20/hr. With an average speed of 50 miles per hour (over all miles driven), calculate the total cost of owning and operating each pickup including the opportunity cost of your time (labor).
7. The result from #6 is what you might charge someone for hauling a heavy load with your pickup. If you were using a trailer, you would need to include the costs of owning and operating a trailer as well. Using the second tab, "Trailer", calculate the per mile ownership and operating costs on a per mile basis. What is the amount you should charge with you as the driver using your pickup and trailer (assume you have the Ram-Cummins)? Use the values below as inputs in your model.
|
|
Trailer
|
|
Purchase price
|
$16,000
|
|
Annual mileage
|
25,000
|
|
Opportunity cost of money
|
6.5%
|
|
Usable life
|
10 years
|
|
Salvage value (@ end of usable life)
|
$7,500
|
|
Insurance
|
$750/year
|
|
Taxes, registration
|
$100/year
|
|
Repairs & Maintenance
|
$800/year
|
|
Shedding/housing costs
|
$100/year
|
When finished, you should upload your completed spreadsheet to Bb. There should be one tab/sheet that contains your written answers to these questions. Your spreadsheet will be graded on its accuracy, usability, flexibility, and neatness.
Instructions: Please use simple excel formulas, so we can learn from the answers.
Attachment:- Assignment Files.rar