Evaluating the Best Use of Funds for Capital Investments
Data analytics can be used to evaluate the best use of funds for capital investments. Joe Fox is the manager of the Gear division and plans to submit a proposal for an expanded production area. Joe has projected various conditions for revenues and expenses for the expansion, including different sizes and quality components. Knowing that the effect will likely differ under each, Joe plans to submit a conservative estimate for his proposed capital budgeting project this year to avoid a repeat of an unfavorable post-audit evaluation as occurred two years ago. Joe has provided the following data to be used in the analysis of selecting the proposal to be submitted to the capital budget committee.
Required rate of return: 6.0%
Table of useful lives and cash flows for different capital budgeting scenarios (Dollar amounts in thousands)
Project Scenario
Years of Useful Life
Net Initial Investment
Operating Cash Flows Year 1
Operating Cash Flows Year 2
Operating Cash Flows Year 3
Operating Cash Flows Year 4
Operating Cash Flows Year 5
Operating Cash Flows Year 6
A
6
$(43,869)
$ 9,006
$ 9,564
$ 8,831
$ 9,557
$ 9,143
$ 8,906
B
6
(51,967)
11,372
12,042
11,259
11,517
11,880
10,758
C
4
(46,516)
12,414
12,970
12,586
11,730
D
4
(51,118)
14,982
12,853
13,817
14,031
E
5
(71,715)
15,237
17,496
17,826
15,962
17,682
F
4
(49,709)
13,054
12,078
13,866
13,686
G
4
(55,519)
15,361
13,898
15,514
15,491
H
6
(46,336)
10,422
10,050
9,228
10,221
10,725
9,079
I
5
(53,417)
12,843
13,283
12,562
11,670
12,677
J
5
(60,032)
12,845
12,328
10,734
10,113
10,960
K
6
(51,321)
10,630
8,576
9,787
8,273
9,265
10,909
L
5
(67,073)
13,627
12,263
13,931
13,080
12,984
M
4
(43,994)
9,537
8,490
8,690
9,395
N
5
(39,793)
9,526
9,079
9,779
10,746
8,277
Joe Fox is the manager of the Gear division and plans to submit a proposal for an expanded production area. Joe has projected various conditions for revenues and expense for the expansion, including different sizes and quality components. Knowing that the effect will likely differ under each, Joe plans to submit a conservative estimate for his proposed capital budgeting project this year to avoid a repeat of an unfavorable post-audit evaluation as occurred two years ago. Joe has provided the following data to be used in the analysis of selecting the proposal to be submitted to the capital budget committee.
Use the following template:
- Module 6 CTA Excel File TemplateDownload Module 6 CTA Excel File Template
Requirements
There are three parts to this problem. Use Excel to perform the following:
- Use Excel’s IRR function to calculate IRR for each project scenario.
- Create a column chart that illustrates the IRR for each project. Include a descriptive chart title and axes labels in the chart.
- Examine the chart. Which project scenarios are acceptable? Which project scenario would you recommend that Joe submit to the capital budget committee? Justify your response. Be specific.
Submit the provided Excel spreadsheet containing your answers to each of the above requirements. Use Excel functions to make any required calculations described in the requirements. Post your completed Excel spreadsheet containing your answers for your instructor to grade. Submit a Word file detailing your answer to part 4 above to indicate and explain any relationships you identify from examining the chart you prepare and your responses to parts 1 and 2.
