Use the videos for instruction, but the basic overview of the assignment is to: A. In a separate worksheet named "payback" 1. Calculated payback period for each of the 2021 capital expenditure requests. 2. Sort the requests by payback period - fastest payback on top 3. Make a "cumulative" column to show the cumulative investment for the accepted requests 4. Show which requests are "accepted" and which are "rejected" B. In a separate worksheet named "NPV" 1. Calculated the net present value (NPV) for each of the 2021 capital expenditure requests using the appropriate weighted average cost of capital for the discount rate. See the written assignment "Scene 2" 2. Sort the requests by NPV - Highest NPV on top 3. Make a "cumulative" column to show the cumulative investment for the accepted requests 4. Show which requests are "accepted" and which are "rejected" C. In a separate worksheet named "IRR" 1. Calculated the Internal Rate of Return (IRR) for each of the 2021 capital expenditure requests 2. Sort the requests by IRR - Highest IRR on top 3. Make a "cumulative" column to show the cumulative investment for the accepted requests 1. Show which requests are "accepted" and which are "rejected" These are the requirements. The pdf "Des Plaines Paper Case Written Scenario" and the "Des Plaines Paper Case video Scenario" have more requirements for advanced courses using this case but you do SCENE 1 COMPUTER SCREEN, ANNUAL BUDGET MEETING You (the audience) are an accountant at Des Plaines Paper, a pulp and paper manufacturer based in Mount Prospect, Illinois made up of about 4,500 employees. You are attending the annual budget meeting through the meeting platform Microsoft Teams. Two faces appear in split screen: Ofelia, Controller and Joe, Accounting Manager are discussing the annual budget. OFELIA You know, Joe, I was thinking, if we need to stay competitive as a paper manufacturer in this world that's going 'paperless', we need to find efficiencies in all the processes that we oversee. So what I think we need to do look for some opportunities to save time and money through analytics and automation. Let's start with the long list of capital expenditures requests submitted by managers from the manufacturing facilities that are located throughout the United States. We have approximately $140 million to allocate amongst the capital projects. So similar to prior years, let's take a look at these projects and stack up on a few metrics. JOE Alright, my team and I will look at the payback period, the NPV, and the IRR for each project. OFELIA Good. I also see that you, it looks like you requested a decent amount of time to evaluate these expenditures - but this is a fairly simple thing, right? JOE Yeah, well, the analysis I think is gonna be pretty straightforward and easy. Excel, you know, we can put all the data into Excel, and compile it and look at it, pretty straightforward. But, I'm a little concerned that some of the managers have been manipulating their budgets. So for example, first, my main concern is that they're just being way too conservative on that initial investment cost and then just, way overly optimistic on those five years cash 1 flows that they're expecting from each request. So the first thing I'm gonna need to do is identify those managers that are just completely off from the budget that they submitted originally. The second thing I'm a little concerned about that we've noticed, some of these managers seem to be really focused on getting that bonus, so what they do is, they pick these projects with really, really strong cash flows in the first two years, and then those projects just drop off in the last three years. So the second thing I think we really need to look at, are those managers that OFELIA Great observation, I agree with you, this is concerning. I'd love to see that list of the repeat offenders, so as soon as you have it, please share it with me. JOE Ok, sure. I think we can do some analytics to automate these steps. OFELIA Great start, Joe. Let's regroup when you're ready to present me your recommendations. JOE Ok, sounds good. SCENE 2 COMPUTER SCREEN, ALLOCATING THE BUDGET You (the audience) are now alone with Joe in Microsoft Teams. Joe shares screen and opens the file "2021 Capital Expenditure Requests" (Exhibit 1). JOE (to audience) Ok, so this first spreadsheet that I'm going to provide to you is this 2021 capital expenditures request spreadsheet. So this is a list of all of the requests for managers for this year. And what I want you to do is go through each one of these requests and calculate those three things that we were talking about before. So that's the payback period, the net present value, and the internal rate of return. I want you to do that with each one independently, so don't 2 consider the results of the other methods that you've run, just evaluate each project with each method one at a time. We'll come back later and evaluate all of that stuff combined but we'll do that once we've identified who those biased managers are and have pulled them out of that information. The other thing I want you to consider are, a couple of other things actually, is that per company policy, when you're discounting you need to assume a weighted average cost of capital of 8 percent and also that all the cash flows come in at the end of the year. So take this spreadsheet, run those figures that I've asked you, and we'll meet back later and discuss what you've found. SCENE 3 COMPUTER SCREEN, IDENTIFYING THE BIASED MANAGERS You (the audience) are alone with Joe in Microsoft Teams. JOE (to audience) Alright, so we should be able to identify those biased managers that we're concerned about. And the way we're gonna do this, is by looking at some historical Information. So I have two bits of information that I'm gonna provide you with. Joe shares screen and opens the file "Actual Cash Flows for 2015 Requests (Exhibit 2). JOE (to audience) The first spreadsheet I'm gonna give you, are the actual investment costs and the actual cash flows for projects from 2015. So here in column.C, is the initial investment, that's the actual investment cost, and then these five years of information right here, are the actual cash flows that we saw, for each one of these requests, and the manager that made them. Joe then opens the file "2015 Capital Expenditure Requests" (Exhibit 3). JOE (to audience) The second sheet that I'm going to provide you with, are the original requests that those managers made. So this is the initial investment, what they estimated 3 the initial cost of the project to be, and then what they estimated the five years of cash flows to be. Joe returns to the file "Actual Cash Flows for 2015 Requests (Exhibit 2). JOE (to audience) So using these two sheets, we need to determine a number of things. In order to do that, let's take a look at some of this data side-by-side here. So I'm gonna copy some of this information from the original requests and copy it over here into the actual cash flow statement. Joe copies information from Exhibit 3 into Exhibit 2 for one of the requests. Joe copies information from Exhibit 3 into Exhibit 2 for one of the requests. So you're gonna need to have basically six new columns here because you're going to need to compare not only every single request to the actual cash flows for that same request number, but you're also going to need to do it for every single time point. So years one, two, three, four, five, etc. A couple of things to keep in mind for this, is that there's not necessarily going to be a matching cash flow for every request, because not every request is approved. So you can obviously only run this analysis for requests that were ultimately approved. The second thing you need to be sure to do, is to set up the formulas so that the negative percentages represent unfavorable variances. This means that the formula for the initial investment cash outflow will differ slightly from the formulas for the five years of cash inflows. Once you have that, you will need to average the percentage difference for each project both the overall project net cash flows, as well as consider only those later years, years three through five. Once you have those results, you will need to average those percentages by each manager to identify which managers are falling well short of budget for their project as a whole, and then also, to show those that regularly show lower net cash flows in those later years of the project than originally submitted. So we're looking at two different averages here, the overall average for every project, and then the average difference between the requests and the actual 4 inflows for those last three years. In addition to representing separate lists those two averages, I need to see charts so that I can share them with Ofelia. So go ahead and see if the data allows us to find appropriate quantitative thresholds to identify these "repeat offender" managers for each of those considerations. So go ahead and take all this information, take these two spreadsheets, run that analysis and we'll meet back up again and we can share what you find. SCENE 4 COMPUTER SCREEN, RE-ALLOCATING THE BUDGET You (the audience) are alone with Joe in Microsoft Teams. JOE (to audience) Alright, so now that we've identified who those biased managers are, we can go back to that 2021 capital expenditures spreadsheet and remove those biased managers from that data. That's going to leave us with a bunch of requests for which we've already calculated that payback period, the NPV, and the IRR. And then you can use that info to update which projects you're going to choose. Doing so, though, is going to take a little bit of judgment on your part. Just, you know, keep in mind that that payback period gives us a reality check over the positive cash flows, you know, over that five-year window that we're looking at. The IRR shows us which projects are expected to exceed the cost of the capital. And the NPV lets us compare the discounted cash flows for each request. We don't really have a great systematic process in place for this type of thing. So I want you to look at all that data, look at the three different sets of information using those three things that we calculated, and come up with a process on your own. And then, you know we'll meet back later and see what you've come up with and see if it can help us move forward in making requests in the future. SCENE 5 COMPUTER SCREEN, AUTOMATING THE PROCESS You (the audience) are alone with Joe in Microsoft Teams. JOE (to audience) 5 Alright, the last thing I wanna talk about is automating this entire process. I don't want a bunch of staff time used in the future re-running this same type of analysis with a bunch of Excel spreadsheets. Excel can be tedious and very time-consuming and complex, So I want to introduce you to one of the other tools we use around here, which is called Alteryx. The nice thing about Alteryx is it can very cleanly join large datasets of dnformation, and then you can just use these drag and drop tools to create these very intuitive and automated workflows. You're not going to need to know how to write a bunch of complex code or anything like that. If you're pretty good in Excel, you're gonna be pretty good with Alteryx. Alteryx has pretty much a substitute for every Excel function or formula that you can come up with. So then what, really what this becomes in the future, is you can take all this same data, dump it into Alteryx click a button to launch this automated cleanly join large datasets of information, and then you can just use these drag and drop tools to create these very intuitive and automated workflows. You're not going to need to know how to write a bunch of complex code or anything like that. If you're pretty good in Excel, you're gonna be pretty good with Alteryx. Alteryx has pretty much a substitute for every Excel function or formula that you can come up with. So then what, really what this 'becomes in the future, is you can take all this same data, dumpit into Alteryx, click a button to launch this automated workflow that you've created, and it will re-run this analysis in a matter of minutes. So it's really an invaluable tool and it will save a bunch of staff time going forward. So what I want you to do is to create your own automated workflow, and then, you know, we can discus that the next time we meet and see how it works. 2. CASE REQUIREMENTS Allocating the budget 1. Without adjusting for the bias in the data, copy Exhibit 1 to three new worksheets and demonstrate, with appropriate formulas, which projects should be accepted or rejected for each of the following methods: payback method (using the payback period) net present value (be sure to make it a net present value) internal rate of return Sort each worksheet by favorability, and add a column showing a running total of the investment amount. Based on the running total, add an additional column to label each project as an "Accept" or "Reject" decision, given the S140M capital budget limit (and an acceptable return for the IRR method) 2. Using the results from step 1 and Exhibit 4 ("Manager Location"), create a worksheet and summarize by location the total initial investment amount for each of the three methods Add filters to only include the "Accept" decisions. Sort the results to identify the five facilities receiving the largest allocation for each of the three methods. Identifying the biased managers 3. Develop a worksheet to calculate the percentage of bias for each project, both in total and over the final three years. a. Copy Exhibit 2 (actual results) to a new tab and bring in the 2015 budget requests (initial investment and cash flows) from Exhibit 3. b. Use six additional columns to compute the percentage differences between actual results and budget requests. Set up the formulas so that negative percentages represent unfavorable variances. c. Add another column to determine the average of the percentage differences for the initial investment plus the five years of cash flows. d. Add a final column to determine the average of the percentage differences for the final three years of cash flows. e. Use conditional formatting to distinguish between favorable and unfavorable variances for all percentages. 4. Identify the managers showing bias that historically deviated from budget. Using the results from step 4, create a worksheet and summarize by manager both the average variances in total and the average variances for the final three years. Sort the results for each of the pivot tables by unfavourability (smallest to largest). 5. Add charts showing these results visually a. First, create one set of charts to include all managers. b. Then, create a second set of charts by adding a value filter to the results from step 4, to show a sufficient number of managers to highlight the cutoff point between the bias managers and other managers (so that this set of charts should include both the biased managers and a comparable number of the next closest unbiased managers) Re-allocating the budget 6. Using a copy of the results from step 1, remove the projects requested by the managers who show bias in excess of your determined thresholds. Then, reevaluate which projects should be accepted considering each of the three methods, individually. 7. Create a worksheet and summarize by location the initial investment amount for each of the three methods. Compare to the results from step 2. 8. Create a worksheet to develop a systematic process that chooses the projects that you think result in the best outcome when considering all three methods together. Your process should be evident from your work. 9. Write a brief report andor prepare a presentation that summarizes your analyses and recommends how to allocate the $140 million amongst the projects. Automating the process 10. Create a program in Alteryx to automate the process