Volume 32, Number 2 139 NET PRESENT VALUE SIMULATION: A CASE STUDY Robert Stretcher Sam Houston State University • Huntsville, Texas INTRODUCTION This paper presents a strategy for teaching Monte Carlo simulation using the case of a capital expansion decision for ArkMed Corporation, a small biomedical device manufacturer located in Houston, Texas. The company is considering investing in a facility that can provide custom products using 3d printing. The facility would serve the custom orthotics and solid implant needs of the medical community of the greater Houston metropolitan area. A simulation of the financial feasibility of the new facility is appropriate since the manufacturing method is unfamiliar to the firm’s management, and because the facility would serve a custom ordered product that would be expeditiously provided, within a few hours to a few days, unlike the company’s current business. The case provides an opportunity to augment coverage of capital budgeting scenario analysis, and to give students an exercise that requires multi-page referencing in Excel. It also can provide output data that can be summarized into a histogram and frequency distribution chart. This process allows for a visual of the risk that a project could return a negative NPV result. The distribution of NPVs thus provides better information on which to base a capital asset expansion decision than would a discrete NPV value based on averages of sales growth, expense ratios, and tax rate. CASE ASSIGNMENT GIVEN TO STUDENTS Mark Findley finished off the last of the coffee and started a new pot brewing. He was waiting for a message from the Vice President of ArkMed Corporation. A new expansion project had been proposed, and it would be Mark’s responsibility to analyze the project. The firm had never invested in a project of this type before, a production facility that would custom “print” biomedical devices with a 3d printer. The firm had other types of custom manufacture, so Mark figured he could use knowledge from those facilities for cost estimates, but the potential sales could range from disappointing, making little use of the capacity of the plant - to full capacity, where the plant would be used around the clock. Randle Gere, the Vice President, finally called. “Mark! I’ve asked Roberts in operations to coordinate with Atwood in cost 140 Journal of Business Strategies accounting to get you operating cost estimates for the new plant. They should be emailing you a spreadsheet soon.” “What is our timing, Mr. Gere?” asked Mark. “Do I need to stay late today?” “No, no - not necessary. In fact they are both staying late to provide you with the information. You can just do it tomorrow. Can you get me your output and a report by the end of the day tomorrow? “Not a problem,” Mark responded. “I will get it to you around mid-afternoon.” Because of the unfamiliarity of 3d printing technology and the range of possible sales, Mark planned to carry out a simulation for the net present value of the project. The simulation was similar to other NPV analyses he had carried out in the past. He had been with ArkMed for four years and had taken over the “Chief” position for the forecasting and analytics office after his boss had retired last year. Mark had already heard from the firm’s marketing manager, who had done a breakdown of expected revenues by sales region, based on custom manufacturing sales for other products the firm offered. The facility under consideration was sort of a wild card, though, because business would be based mainly on quick turnaround. Measurements and specifications would be provided by technicians dealing with patients, an order submitted electronically, and then ArkMed would “print” the custom device, coat it with sterile coatings, package it in sterile packing, and expedite delivery to the hospital or medical office where the procedure was being done. While these devices could be delivered in as little as half a day, it was also possible that quick delivery would not be so critical, and that the firm could schedule production within a three to four day window. There was thus potential for the facility to produce at capacity, but it was also possible that there would be times where the three to four day backlog would be completed with no new orders in the queue. That brought about the possibility of idle capacity, but a full staff would have to be retained in order to handle new orders as they came in. The resulting variation in sales could be significant. Mark had waited until 7:00 pm, working on other forecasting tasks, and the email from Roberts and Atwood finally came in. Mark began summarizing the analytical inputs for his simulation immediately (Exhibit 1). He then structured the inputs into the typical inputs page template in Excel (Exhibit 2) so that he could spend the next day carrying out the simulation and writing up a brief for Mr. Gere. Volume 32, Number 2 141 Exhibit 1. Mark’s Summary. 2015 Sales estimate (as if the project were already up and running): $2,748,000 Expected sales growth per year: 2016: 8%, 2.8% std deviation 2017: 6.2%, 5.2% std deviation 2018: 6%, 2.5% std deviation 2019: 4%, 2.7% std deviation 2020: 3.5%, 3.1% std deviation 2021: 2%, 3.5% std deviation 2022: 2%, 3.7% std deviation 2023: 2%, 3.8% std deviation 2024: 2%, 4% std deviation 2025: 2%, 4.1% std deviation Cash expenses: 2016: 34% of sales, 1.3% std deviation 2017: 34% of sales, 1.5% std deviation 2018: 34% of sales, 1.6% std deviation 2019: 34% of sales, 1.8% std deviation 2020: 34% of sales, 2.0% std deviation 2021: 34% of sales, 2.2% std deviation 2022: 34% of sales, 2.4% std deviation 2023: 34% of sales, 2.6% std deviation 2024: 34% of sales, 2.7% std deviation 2025: 34% of sales, 2.8% std deviation $7,997,000 Asset Expansion, depreciated on a 7-year MACRS schedule, with a 10-year life 142 Journal of Business Strategies MACRS 7-year Depreciation Percentages per year: year 1 14.29% year 2 24.49% year 3 17.49% year 4 12.49% year 5 8.93% year 6 8.92% year 7 8.93% year 8 4.46% Tax rates expected: 33.4% of EBIT on average, 1.6% standard deviation, based on higher income levels after the expansion and expected political action concerning tax policy) Appropriate discount rate for the project: 18.9% Volume 32, Number 2 143 Exhibit 2. Mark’s Input Parameters Page. 144 Journal of Business Strategies TEACHING STRATEGY FOR THE CASE This simulation will involve many rows of calculated data. Spreadsheets, by default, recalculate the entire workbook when any equation is entered. Wait time may become problematic when 10,000 iterations times seven pages is recalculating. To speed things up a bit, students may want to turn off Autocalculation. This is done in Excel 2003 by selecting Tools… Options… Calculate from the toolbar and Select “Manual” or in Excel 2007 by selecting Formulas... Calculation Options... “Manual.” The user can then recalculate the workbook at any time by pressing F9. Students might also benefit from some guidance on how to specify random numbers or normally distributed random numbers: The “Math and Trig” function RAND() generates uniformly-distributed random numbers. Random normally- distributed numbers with, for example, a mean 100 and standard deviation 20, can be generated using the “Statistical” function NORM.INV, specifying Uniform random number inputs RAND() within the NORM.INV Formula: NORM. INV(RAND(),100,20)). This can also be specified by Selecting Formulas... More... Statistical... NORM.INV and entering RAND(), 100, and 20, as the specifications. For a bit more orderly analysis, students should set up a standard workbook with five worksheets: Mark’s Inputs page, Sales, Cash Expenses, NOCF, and PVNOCF. The “Inputs” worksheet (Mark’s Exhibit) lists the parameters of the model. In the rest of the worksheets, it is most convenient to put the data in “row form” – see Exhibit 3 for an example sales worksheet. The Sales worksheet generates data as a simple random growth rate model. Specifically, Salest+1=Salest*(1+gt). For each cell of data, the growth rate is normally distributed, with mean and standard deviation specified in the “Parameters” worksheet. Since each year’s sales may be serially correlated, the sales figure for each year should be based on the prior year’s sales times (1+gt). An initial (year zero) sales figure is shown in cell A17 in Exhibit 2. This is used as a base to calculate the expected sales in year 1 (2016). Panel A of Exhibit 3 shows the syntax for the fx input field for the 2016 sales calculation. Each subsequent year’s calculation is based on the prior year’s calculation. Once the calculation is established for 2017, the cell can be dragged (copied) to all other years by dragging the cell to year 2025. The student’s own result will differ since every student will have different randomly selected inputs. Although only a few rows of data are shown as an example here, the students’ spreadsheets will have 10,000 rows of data per sheet, dragging the row B3:K3 down to 10,000 lines. The remaining worksheets are constructed in a Volume 32, Number 2 145 similar fashion, with 10,000 rows each by 10 years of projections (2016-2025) in the columns. Exhibit 3. Sales Worksheet Visual. PANEL A PANEL B The Cash Expenses worksheet contains values calculated simply as percentages of estimated Sales. For convenience, base the Cash Expenses in a specified cell on the same column and row specified cell from the sales sheet, and the same for the other sheets. Exhibit 4 shows a sample, again with the calculations for cells B3 and B4 shown in the fx field. 146 Journal of Business Strategies Exhibit 4. Cash Expense Worksheet Visual. PANEL A PANEL B In this simple model, NOCF is calculated as: (Salest - Cash Expensest - Depreciationt)*(1-tax rate)+Depreciationt Exhibit 5 shows a sample, again with the calculations for cells B3 and B4 shown in the fx field. Exhibit 5. NOCF Worksheet Visual. PANEL A Volume 32, Number 2 147 PANEL B The last worksheet is the Present Value of NOCFt. Recall that: PV(NOCFt) = NOCFt/[1+δ] (t-2015) Where δ is the discount rate for cashflows. Exhibit 6 shows a sample, again with the calculations for cells B3 and B4 shown in the fx field. Exhibit 6. PVNOCF Worksheet Visual. PANEL A PANEL B 148 Journal of Business Strategies On the PVNOCF sheet, construct a single column of data for PV(NCF) within the PVNCF worksheet by summing the ten years of PVNOCFs. Then create another column subtracting the initial cash outlay from the PVNOCF total, to get a column of 10,000 NPVs. Exhibit 7. SUM of PVNOCF Column and NPV Column Visual. Excluding the “Inputs” worksheet, each worksheet should have a header row representing years 2016-2025 and a single row of data. This single row represents one possible future for our company (based on the chosen parameters). The purpose of the simulation is to generate many realizations of the future and use the distribution of valuations to make meaningful insights into the economic value of the project under consideration. To do so, we must fill each of the data worksheets with 10,000 rows of data. If the formulas were carefully constructed (with proper relative and/ or absolute cell addressing) then this is a trivial matter. For example, suppose you have data in cells B3:K3 and you want to fill in the next 1,000 rows. To do so, click the “Name Box” in the upper left hand corner and type B3:K1002. This will highlight a block of cells. From the Edit Menu, select Fill->Down and all contents are copied. Do this for the Sales, Cash Expenses, NOCF, and PVNOCF worksheets. After all the data is filled, students should be sure to manually recalculate the cells if autocalculation is off (Note: The fill can also be accomplished by ‘dragging’ the cell or cells through to the 10,000th line using Excel’s “+” function). To create the column of 10,000 NPV’s on the PVNOCF sheet, or each row (iteration), sum the PVNOCF for all ten years and subtract the initial cash outlay, $7,997,000. Add descriptive statistics of the distribution of NPV to the Parameters Worksheet. Specifically, calculate the minimum, median, mean, maximum, and Volume 32, Number 2 149 standard deviation of NPV using Excel’s automated statistical functions (or use the data analysis toolpack to calculate all these measures and more in a single step). An analysis with this level of sophistication has the potential of making or breaking a capital budgeting decision, or the decision by a capital provider to either fund the project or not. Absence of an analysis such as this may cause the same to question the firm’s ability to provide valid estimations of the impact of the project on the welfare of the firm’s shareholders. A chart of the distribution of NPV’s is a good visual to see the likelihood, given the input distributions, that a positive (or negative) NPV would occur. The negative probability can be calculated easily by counting all of the negative NPV’s out of 10,000 (this is the probability of a negative NPV). The same can be repeated for the positive values. Exhibit 8 shows an example of the resulting frequency distribution, created by charting the histogram output using the histogram function in the data analysis toolpack, with an indicator line showing the critical NPV value of zero drawn in. While each student’s result will differ a bit from this visual, it is clear to see that the NPV has a greater likelihood of being positive, but there is also substantial risk that the NPV could be negative as well. Exhibit 8. Frequency Distribution of NPV’s. An optional requirement could be to request that students prepare a professional report and correspondence, taking the role of Mark, and selling the analysis to upper management. 150 Journal of Business Strategies A solution Excel workbook may be downloaded by JBS readers from the author’s website: www.shsu.edu/~fin_rhs/JBSsolution.xlsx BRIEF BIOGRAPHICAL SKETCH OF AUTHOR Dr. Robert Stretcher is a Professor of Finance at Sam Houston State University. He earned his PhD in Finance and Monetary Economics from The University of Tennessee. His research focuses on empirical market studies, applied finance, and finance education. Dr. Stretcher has held prominent positions in the Academy of Economics and Finance and the Institute of Finance Case Research, and has served on editorial boards and as editor of various finance journals.