Tutorspedia

Finance-AW364 Online Services

 
Applying Capital Budgeting Techniques to Startup Business Venture
 
A rock climber has come to you for financial analysis and advice on whether he should start up a small business which introduces others to his sport. His idea is to purchase a rotating and operate it at parties and special events. The cost of this rotating climbing wall is $14,000. In addition, there is a $1,500 shipping fee.
 
Since this is primarily geared towards exhibitions and party rentals, your client will need to purchase a new truck, specifically a Ford F150 that can be used to haul the climbing wall around. Additionally, a trailer will also have to be purchased for $2,500 to load and unload the wall. The truck, trailer, and wall will all be depreciated to zero a straight-line basis over 7 years. Your client expects to stay in this business for 7 years. At the end of those 7 years, the climbing wall and trailer will have a book value of zero. However, for your terminal value calculation, you think the wall and trailer can be sold for $5,000 at that time. You will also need to estimate the expected value of the truck in 7 years.
 
Your client plans on paying a third party $200 a week to operate the wall so that his business will not take up any of his climbing time on the weekends. In addition, administrative expenses are expected to be $120 a week to pay for gas, insurance, and other miscellaneous items. Since your client already has a full-time job, and intends on running this business as a sole proprietorship, all profits will be taxed at his marginal income tax bracket of 25%. The expected weekly revenue estimate is $500 a week. Revenue is expected to increase 5% each year while labor and administrative expenses are expected to increase 3% each year. Additional working capital will also be required. Current assets such as cash on hand will increase by $1,000 and current liabilities will increase by $500. At the end of 7 years, both of these values will decrease by the same amount. The client’s required return is 15%.
 
1. What is the initial outlay of this project?
 
Initial Outlay _____________________
 
2. 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, 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: ________________
 
3. 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: What is this project’s payback, discounted payback, NPV, IRR, PI, and MIRR?
 
Payback: __________________
Discounted Payback: __________________
NPV: ___________________
IRR: ____________________
PI: _____________________
MIRR: _____________________

4. 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.

Product code: Finance-AW364

Looking for Finance-AW364,please click here