Discount Cash Flow HW Problem $4740$ Summer $2024$

You have been hired to evaluate a proposal presented to a small regional trucking company, located in Davie, FL$,$ by an engine manufacturer. The proposal would extend the life of a $45-$truck fleet by $5$ years, reducing their operating costs while improving fuel efficiency.

Given the uncertainty in the trucking industry over the next few years, due in part to Amazon$$s massive growth, the company$$s owner has been advised to consider any short$-$term fix which exceeds an $18\mathrm{.}00\%$ internal rate of return.

Prepare a discounted cash flow model to determine if the company should move forward with the proposal.

Additional Company Facts$/$Data:

$$ The average number of annual miles driven per truck $110,000$

$$ Cost per new engine $-$ $$37,500$

$$ Installation cost per engine $-$ $$9,500$

$$ Estimated fuel efficiency $($Miles Per Gallon$)-4$ MPG$,$ from $9$ MPG to $13$ MPG on average over

the engine$$s life. $($good stress test candidate$),$ declining by $5\%$ per year.

$$ Annual maintenance savings, per vehicle $-$ $$10,000$ in year one, declining by $25\%$ per year $($from

prior year$$s expense$).$

$$ National average price for a gallon of diesel fuel is $[$each student needs to source their own$],$ and

is expected to remain relatively consistent over the life of the engines. This is a good place for a stress test, however.

The current truck fleet is owned by the company with no debt. The trucks have been fully depreciated. However, as the engines will extend the trucks$$ useful lives their cost much be capitalized for GAAP purposes.

The company is able to fund the entire fleet upgrade with excess cash, so no financing cost is required. The company enjoys an effective tax rate of $21\%.$

Assignment:

Prepare a DCF model to analyze the above situation. Make any assumptions you feel are not included within the stated fact pattern necessary to complete your analysis. Be certain to clearly label your assumptions. Your model should include at least five stress tests of management$$s$,$ or your, assumptions demonstrating were the model fails to meet the required rate of return.

Summarize your results when submitting your model in a brief memo to management. Your memo should clearly Identify the two highest risk assumptions $$ those which cause the most volatility within your model. Highlight your NPV $-$ assuming the benefits are realized evenly over the course of each year. Your analysis must show NPV$,$ Mid$-$Point NPV$,$ and IRR.

Submit model $($excel$).$