Question

Solve the problems described below, and present your solutions in the Excel template provided; no memo is required with this assignment. Also, the excel spreadsheet specifies monthly billable hour data. Set March, 2013, as month zero. Ignore taxes. The due date is SATURDAY at 11:55 PM Eastern. No late work is accepted. Jane Doe, president of Big Stick Construction Co., has asked for your help in selecting new equipment for purchase. She has decided to purchase a new track hoe and has narrowed the selection to two competitors. Track Hoe #1 costs $100,000 and has an hourly operating cost of $31.00 and a salvage value of $35,000 at the end of three operating seasons (November 2015 not December 2015). The Track Hoe #2 costs $65,000 and has an hourly operating cost of $36.00 and no salvage value after 3 operating seasons. A track hoe operator costs $29.00 per hour of operation and works on other tasks when the track hoe is not in use. With either track hoe, revenue will be $95.00 per billable hour. Based on experience, Big Stick expects to use the new track hoe for 1,200 billable hours per year. Since the company is located in Vermont, the track hoe normally is only used for 8 months each year (April through November), and the related Excel workbook specifies the expected distribution of billable hours over those 8 months. Big Stick assumes that it can dispose of Track Hoe #1 for the salvage value at the end of the last month in the 2015 construction season. Big Stick expects no inflation. Dollar values should be constant over the next 3 years. Because the track hoe will not be needed until April of 2013, they plan to close the purchase March 31, 2013 and to take delivery the next business day. Big Stick’s cost of capital is 7.5%. The Board of Directors demands a 5% profit margin. Recently, Big Stick personnel have engaged in extended discussions regarding the decision tools to use in comparing alternatives such as these. The company needs a respected and impartial outside party to settle this debate and to advise them on techniques for such financial decisions. Therefore, President Jane is willing to pay a steep consulting fee for your financial advice in this situation. She wants these: Net Present Value, Future Worth, Rate of Return or Internal Rate of Return, Payback without interest, and Payback with interest. To score points with President Jane (and you do want her business), you need to complete the spreadsheet and provide solutions in each highlighted cell. Financial Comparison of Track Hoe #1 with Track Hoe #2
	% or Unit		Start		End
	Price	3 Year	2013	2014	2015
	($ / Hr)	Total	Sub-Tot	Sub-Tot	Sub-Tot
Data For Both Track Hoes
Billable Hours of Operation		3600	1200	1200	1200
MARR and Monthly Discount Factors
Annual effective rate	12.5%
Monthly effective rate
Monthly discount factors
Track Hoe #1
Capital Values
Track Hoe Investment	$100,000
Track Hoe Salvage	$35,000
Revenues
Track Hoe Operation	$95.00
Costs
Track Hoe Operation	$31.00
Operator	$29.00
Total Costs
NCF
Cumulative Cash
Present Values
PVs of Monthly NCFs
Project Net -- NPV
IRR
Track Hoe #2
Capital Values
Track Hoe Investment	$65,000
Track Hoe Salvage	$0
Revenues
Track Hoe Operation	$95.00
Costs
Track Hoe Operation	$36.00
Operator	$29.00
Total Costs
NCF
Cumulative Cash
Present Values
PVs of Monthly NCFs
Project Net -- NPV
IRR
Requested Valuation Methods
1. Net Present Value
TH1	TH2
2. Future Value
TH1	TH2
3. Monthly Rate of Return or Internal Rate of Return (IRR)
TH1	TH2
4. Payback without interest (number of months)
TH1	TH2
5. Payback with interest (number of months)
TH1	TH2
Final recommendation
TH1 or TH2?

Answer 1

Financial Comparison of Track Hoe #1 with Track Hoe #2
	% or Unit		Start		End
	Price	3 Year	2013	2014	2015
	($ / Hr)	Total	Sub-Tot	Sub-Tot	Sub-Tot
Data For Both Track Hoes
Billable Hours of Operation		3600	1200	1200	1200
MARR and Monthly Discount Factors
Annual effective rate	12.50%
Monthly effective rate	1.04%
Monthly discount factors	0.99
Track Hoe #1
Capital Values
Track Hoe Investment	$100,000	$100,000
Track Hoe Salvage	$35,000	$35,000
Revenues
Track Hoe Operation	$95.00	$342,000.00	$114,000.00	$114,000.00	$114,000.00
Costs
Track Hoe Operation	$31.00	$111,600.00	$37,200.00	$37,200.00	$37,200.00
Operator	$29.00	$104,400.00	$34,800.00	$34,800.00	$34,800.00
Total Costs	$60.00	$216,000.00	$72,000.00	$72,000.00	$72,000.00
NCF	$35.00	$126,000.00	$42,000.00	$42,000.00	$42,000.00
Cumulative Cash
Present Values
PVs of Monthly NCFs	$34.64	$100,016.46	$37,333.33	$33,185.19	$29,497.94
Project Net -- NPV	$24,598.08
IRR
PV	$1,571.78
PV	$0.000000
PV	($96.00)
IRR	24.942%
Track Hoe #2
Capital Values
Track Hoe Investment	$65,000
Track Hoe Salvage	$0
Revenues
Track Hoe Operation	$95.00	$342,000.00	$114,000.00	$114,000.00	$114,000.00
Costs
Track Hoe Operation	$36.00	$129,600.00	$43,200.00	$43,200.00	$43,200.00
Operator	$29.00	$104,400.00	$34,800.00	$34,800.00	$34,800.00
Total Costs	$65.00	$234,000.00	$78,000.00	$78,000.00	$78,000.00
NCF	$30.00	$108,000.00	$36,000.00	$36,000.00	$36,000.00
Cumulative Cash
Present Values
PVs of Monthly NCFs	$29.69	$85,728.40	$32,000.00	$28,444.44	$25,283.95
Project Net -- NPV	$20,728.40
IRR
PV	$380.06
PV	($0.0000)
PV	($527.70)
IRR	30.416%
Requested Valuation Methods
1. Net Present Value
TH1	TH2
$24,598.08	$20,728.40
2. Future Value
TH1	TH2
3. Monthly Rate of Return or Internal Rate of Return (IRR)
TH1	TH2
24.94%	30.42%
4. Payback without interest (number of months)
TH1	TH2
5. Payback with interest (number of months)
TH1	TH2
Final recommendation
TH1 or TH2?	TH2	Because IRR is more