USING ANALYTICS IN CAPITAL BUDGETING

1.      The case

You are an accountant at Des Plaines Paper, which is a pulp and paper manufacturer based in Mount Prospect, Illinois and made up of about 4,500 employees. Staying competitive as a paper manufacturer in a world going ‘paperless’ means finding efficiencies in all processes. So the company has been looking for opportunities to save time and money through analytics. This is the focus of the annual budget meeting, which is being led by the controller (Erin) and the accounting manager (Lawrence). You are particularly interested in the discussion about one area you have been assigned to – evaluating the long list of capital expenditure requests submitted by managers from the manufacturing facilities located throughout the United States:

Erin: We have approximately $140 million to allocate amongst the capital projects. So similar to prior months, we will want to look at how these projects stack up on a few metrics.

Lawrence: Ok, my accounting team and I will look at the payback period, NPV, and IRR for each project.

Erin: I’ve been meaning to ask: You have time to calculate all three measures?

Lawrence: Oh, sure, particularly with the technologies that are available. And it helps to see them all. Payback gives us a reality check as to positive cash flows over the five year time horizon we use, IRR shows us which projects are expected to exceed our cost of capital, and NPV lets us compare the discounted cash flows. It works well to take a look at all three sets of numbers.

Erin: Good. Also, every month we spend a lot of time evaluating these expenditures – but shouldn’t this be a fairly simple thing?

Lawrence: It should be, but there are a couple of things that some of the managers do that cause us problems. First, some of them try to get the proposal approved by being overly optimistic on the estimate of their project’s cost. So the first thing we have to do is adjust the numbers for any of the managers that went over budget too much in prior years. Second, some of them are so focused on getting their bonus this year that they submit proposals for which the later years’ cash inflows are questionable. So we also have to adjust the numbers for any of the managers whose prior years’ projects went bust after the first couple of years.

Erin: That is concerning. I would be interested to see if there are repeat offenders – send me that list. Let’s regroup when you are ready to present your recommendations.

After the meeting, you then meet with Lawrence to discuss how to complete the capital expenditure approval process. Lawrence provides the following information about allocating the budget based on the metrics and identifying the managers that biased their numbers.

Allocating the budget

Lawrence provides you with an Excel analysis prepared by another staff member showing the financial impacts of each project using the payback period, net present value (NPV), and internal rate of return (IRR) based on a weighted average cost of capital of 8 percent and assuming cash flows occur at the end of the year.  His request to you:

Lawrence: As you can see, we’ve identified the projects that will fit within the $140 million capital budget for each of the analyses.  We have about $15 million of funding ready to go right now. I’d like to see which locations have the greatest amount of approved funding for each of the three methods.  We’ll start with some of those cities in to keep a tight focus.  Put that list together for me, but keep it on a single tab so I can see the locations side-by-side in to select several of them.

Identifying the biased managers    

Lawrence explains that an analysis has also been prepared of the biased managers using information about the net cash inflows and outflows for all of the projects approved in the past five years, along with the related capital expenditure requests submitted. To do this, he shows you two files: “Actual cash flows by project” (Exhibit 1) summarizes the net cash inflows and outflows for all of the projects approved in the past five years; “Historical capital expenditure requests” (Exhibit 2) compiles all of the capital expenditure requests submitted in prior years for which five years of actual cash flows are available. He shows you where the other staff member has determined the percentage differences between the actual and budgeted amounts for each year (including the initial investment) for every project. That analysis has also averaged the percentage difference in net cash inflows for each project, overall as well as considering only the later years (years three through five). Lawrence says to you:

Lawrence: Using that bias analysis, average the results by manager to identify those that regularly come in way over budget for their projects as a whole, as well as those that regularly show lower net cash inflows in the later years of the project than originally submitted. In addition to presenting a list of those two averages, use visuals such as column charts to show the results.  See if the data allows us to find appropriate quantitative thresholds to identify the “repeat offenders.”

2.      case REQUIREMENTS

1. Using the results presented for Payback, NPV, and IRR, summarize the total amount allocated to each of the manufacturing facilities for each of the three methods. Sort the results for each method to identify the facilities receiving the largest allocation for each of the three methods. Per Lawrence’s request, keep the summaries on a single, new tab.

2. Using the Bias Analysis, summarize the results by manager, including visual representations, to identify both:

• Managers regularly coming in “way over budget” for their project’s cost.
• Managers regularly showing significantly lower net cash inflows in the later years of the project than originally submitted.

3. Write a brief report (one page maximum) that summarizes your recommendations regarding (a) how to allocate the original $15 million amongst the projects, and (b) appropriate thresholds for determining manager bias, both overall and for the final years of projects.