Ford Finance Assingment Help With Solution

Ford Finance Assingment Help With Solution

 
1. We can estimate the cost of the truck. The 2013 Ford F-150 4wd 126” Reg Cab XLtruck. Estimate the price by using the MSRP. Ignore title, destination, and sales taxes. I will assume they could be bargained down
 
Cost of truck ___________________
 
2. What is the initial outlay of this project?
 
Initial Outlay _____________________
 
3. What is the expected after-tax terminal cash flow in year 7 for this project assuming your client sells the truck, trailer, and climbing wall? Note, to estimate the salvage value of the Truck, go to www.nadaguides.com. Look up the average used retail value for a 2006 4wd Reg Cab XL. I have printed out what you will find above. Use the average used retail value. Multiply this value by 1.025^7 to estimate what the value of a 2013 will be 7 years from now. We will assume the used price of a 7 year old truck today will increase by the inflation rate which we approximate at 2.5% a year.
 
A second method involves calculating the percentage of the 2006 retail value that currently remains and applying this percentage to the 2013 new truck price. A 2006 4×4 F150 originally sold for $24,020. Find the % value that remains for this truck now using average used retail value and apply it to the 2013 price. Now average the 2 values you have. This hopefully gives us a decent market perspective of what a 7 year old truck should sell for 7 years from now.
 
Don’t Forget: When estimating the terminal value, the book value of these assets will be zero 7 years from now so you will have to pay taxes on the gain from selling them. Tax rate is 25%.
 
Method 1 used truck value: _________________ Method 2 used truck value: _______________ Average: ____________________

Total Salvage value(Truck, wall, and trailer): _________________

-Taxes: ____________

+/- change in working capital __________

= After tax terminal cash flow: ________________
 
4. Determine the ANNUAL after tax cash flows for years 1 through 7 for this project. Remember to use straight-line depreciation for the costs of the truck, trailer, and climbing wall. At the end of 7 years, the book value will be zero for these assets. For accounting purposes, assume you depreciate the full value, i.e. add up the entire cost, and depreciate this amount by 1/7 each year. Your income statement should look like this:

 
5. What is this project’s payback, discounted payback, NPV, IRR, PI, and MIRR?

Payback: __________________

Discounted Payback: __________________

NPV: ___________________

IRR: ____________________

PI: _____________________

MIRR: _____________________
 

6. We are now going to measure the riskiness of this project. Although our required return of 15% accounts for this, we want to take a closer look at what this entails by performing Monte Carlo analysis and seeing how the NPV may differ from our projections. To begin, we need a program that will perform Monte Carlo analysis. There are some Excel addins, or one could write a Macro. Since the latter is not so easy and addins don’t always work, download the CBrock.xlsm spreadsheet from d2l. You will note there is already a table built for you along with a Macro that requires no programming knowledge. Just fill in the blanks….
 

Make sure you use formulas to calculate the rows: Earnings Before Tax, Taxes, Net Income, and Cash flow which should be in Rows 10, 11, 12, and 14. As an example, Earnings before tax in cell C10 should read = C6-C7-C8-C9. Taxes in Cell C11 should be = C10*.25, etc. These formulas need to be copied through column I. Below the table in Cell C25, calculate the NPV based on the Cash flows in your Cash Flow row. YOU MUST USE THIS CELL. This is going to be the cell we will run our simulation on. There is a NPV function in Excel that is =NPV(required return, Cash flows 1-7) – initial investment. DO NOT INCLUDE THE INITIAL INVESTMENT AS A CASH FLOW. Don’t forget to subtract the initial investment at the end. Make sure your cash flows refer to cells C14 to I14.
 

We are now going to let revenues vary for years 1-7. To do this, in Cell C6, which should be Revenue in Year 1, write the following formula: =26000*(1+NORMINV(RAND(),0,0.1)). This projects revenue to be equal to your expectation, but could vary by 20% with a 95% confidence interval (2* the 10% standard deviation represented by 0.1). Now in Cell D6, which should be Revenue in Year 2, write the following formula: =C6*(1+NORMINV(RAND(),0.05,0.1)). This shows revenues are expected to increase 5% each year with a 10% standard deviation reflecting the fact that revenues may be greater or less than the expected 5% increase each year. Copy this formula to cells E6-I6 which should be your revenues for years 3-7. Now hit the F9 key a few times. Your NPV value in Cell C25 should be changing each time. We are going to hit this key 10,000 times to attain confidence intervals and an average NPV value. Instead of actually hitting the key 10,000 times and copying the numbers down each time, we are going to use our Monte Carlo Macro which will do this for us. Monte Carlo results are posted in rows 27-36. Pay particular attention to the median, this is the value where 50% of the observations are above and below and is often quite different from the mean which can be skewed above or below this. Another cell to pay attention to is how often the NPV is greater than 0. As a check, the mean of your Monte Carlo results should not be significantly different from the NPV in your original results….within a few hundred dollars anyway. We are simply allowing the results to vary around your original calculation based on the uncertainty we have about future revenues.
 

Copy output and paste here. It should look like this:
Monte Carlo Results
Mean xx
Median xx
Standard Deviation xx
Set Confidence interval xx
Lower limit 10% xx
Upper limit 90% xx
Minimum xx
Maximum xx
How often greater than zero xx

7. Based on all of your analysis above, what do you advise? Accept/Reject and why? How risky is it based on your analysis in question 6?

Product Code :Fin287

To get answer for this question, kindly click here (Note: Don’t forget to write the product code in comment section)

You can also email us at assignmentconsultancy.help@gmail.com but please mentioned product code in the mail body while sending emails.You can browse more questions to get answer in our Q&A sections here.