Hi,

You were recommended by a colleague.

Please find attached excelfinance question. I also included the case study for the question. The questions must be answered using the attached excel.

Please let me know if you will be able to answer this question.

Thank you

For the exclusive use of Z. Wang HBSP Product Number TCG 3 THE CRIMSON PRESS CURRICULUM CENTER THE CRIMSON GROUP, INC. Green Valley Medical Center I don't object to the priority given to medical equipment by the board of directors. At the same time, though, requests for administrative or support service capital frequently have significant cost-saving potential, and should not continue to be overlooked. There must be some way that these requests can be assessed on their merits without infringing on the hospital's ability to provide the best possible patient care. The speaker was DeeAnne Willis, CEO of Green Valley Medical Center (GVMC). She was expressing concern that in her 12 years at the hospital, both administration and support services typically had taken a back seat in the capital budgeting process. This concern was of particular importance to Allen Klein, GVMC's newly hired chief financial officer, who faced several decisions regarding the hospital's capital budgeting process. His decisions needed to be made quickly since departmental directors were just weeks away from the October 15 deadline to submit both operating budgets and capital requests. Mr. Klein already had been approached by various senior managers in the hospital regarding their department requests for capital purchases. All had welcomed him with friendly greetings, followed immediately by informal presentations of their departments' proposals for capital improvements. It did not take long for Mr. Klein to realize that he needed to understand better how the capital budgeting process worked, both formally and informally. BACKGROUND GVMC was a 330-bed nonprofit teaching hospital affiliated with a large state university in a mid-size town located several hours from the state's two urban centers. Established in the 1930s with a federal grant, Green Valley had grown with continuous support from state revenues. It also had issued municipal revenue bonds on several occasions to finance large expansions and improvements. Recent financial statements are contained in Exhibit 1. Green Valley served a regional patient base of over one million. It was the only regional hospital, and one of only two in the state, with facilities in cardiology, oncology, and neurology. Green Valley's specialty in these fields included teaching and research as well as clinical care. It prided itself on its state-of-the-art technology and overall medical expertise. In fact, the hospital was widely regarded for the innovative work and research conducted by its medical community, particularly in the neurological and oncological sciences. The Current Capital Budgeting Process Mr. Klein was delighted to find that the hospital's available funds for capital purchases had grown at a rate of 10 to 15 percent per year during the past 5 years. How the money was distributed, however, was not so clear. The process began in each service area, where individuals submitted requests to their department head for new and replacement equipment and machinery. Capital requests included items costing $1,000 or more. Anything under $1,000 was included as an operating expense by the department. Once all new requests had been received by department heads, they were reviewed and ranked, and then ranked again incorporating all requests outstanding from the previous year. At that point, any requests not deemed necessary by the department were dropped from the list. _____________________________________________________________________________________________ This case was prepared by Gregory Dorf, under the supervision of Professor David W. Young. It is based on the case Green Valley Hospital, by H. James Graham. It is intended as a basis for class discussion and not to illustrate either effective or ineffective handling of an administrative situation. Copyright 2012 by The Crimson Group, Inc. To order copies or request permission to reproduce this document, contact Harvard Business Publications (http://hbsp.harvard.edu/). Under provisions of United States and international copyright laws, no part of this document may be reproduced, stored, or transmitted in any form or by any means without written permission from The Crimson Group (www.thecrimsongroup.org) This document is authorized for use only by Zhaomengqi Wang in Applications in Corporate Finance Spring 2014 taught by Dr. Ricardo Rodriguez from January 2014 to July 2014. For the exclusive use of Z. Wang All additions to the list were documented on an Equipment Request Form which contained the name of the requester, the date, price, and a brief explanation of the request. Once the department head had a final list, he or she submitted it to the hospital's fiscal affairs department where it was consolidated and prioritized along with the requests of all departments to form one master list. The master list was reviewed and approved by the Board of Trustees at their November meeting. At this point, it became clear to Mr. Klein why he had been pursued by so many department heads regarding their capital requests. Through his investigations, he learned that the general practice of the board of trustees and of the previous CFO was to give high priority to medical equipment. In fact, several department chiefs and clinical program directors did not hesitate to confirm this unwritten policy. According to the Director of Cardiology: Let's face it, it's no mystery that medical departments win out in head to head competition. We've got tradition, numbers, and the hospital's mission on our side. Of course, administration is a necessary part of the whole organization, but physicians are the ones closest to the needs of the hospital and of the patients. We're the ones who make decisions every single day. The Chief of Medicine noted that priority assessments also differed among departments: Naturally, some chiefs carry more weight than others, and they should. Ob/Gyn, for example is at a disadvantage in the capital budgeting process compared to larger departments. If surgery, for example, fills 200 beds and Ob/Gyn only 20, then, all things equal, the Chief of Surgery is more likely to get what he wants. Historically, although capital requests tended to be presented subjectively, their financial consequences usually were taken into consideration as well. The financial data presented, however, tended to be unreliable and often unfounded. In an attempt to be fair, the CFO would try to ensure that each medical department received at least one high priority spot on the master list. Mr. Klein now understood why Ms. Willis had voiced such strong concern about the process. In a follow-up conversation with her, she elaborated: The argument of efficient services and financial contribution to the hospital is very important to the board of trustees. However, physicians also should be concerned that conditions in certain administrative service areas could deteriorate to the point that we're unable to contain costs for the hospital, or that we're unable to maintain quality. Everyone associated with the medical center is affected then. Mr. Klein realized he needed to measure all capital requests with a system that would be much more objective than what had been typical at Green Valley until now. Although he was not entirely familiar with capital budgeting systems used by hospitals, he was quite familiar with common practices in the private sector, and was certain that the techniques were similar. Minimal research acquainted him with a technique used by some hospitals that consisted of a qualitative as well as a quantitative evaluation. He thought this technique would help to achieve a better balance of information for decision-making, and decided to use the current year to test its feasibility. The technique used a net present value approach to quantitative analysis, and a subjective, qualitative analysis that considered a project's impact on the hospital's physicians, its community, and its employees. Exhibit 2 contains the formal scoring sheet that was used for the qualitative assessment. Exhibit 3 was used to combine the quantitative assessment with the qualitative one, and resulted in a ranking of all proposed investments according to the combined assessments. The Decision Making Process By the October 15th deadline for departmental budgets, Mr. Klein had received a total of 130 capital requests ranging from $1,000 to $5.8 million. He felt that it was impractical to treat all capital requests as homogeneous. Rather, he thought they should be divided into several broad groupings to make it easier to compare projects of similar nature and costs. He developed four categories: Group I Group II Group III Group IV Essential items to maintain operations Revenue producing or cost saving items Optional items for improvement Miscellaneous items _____________________________________________________________________________________________ Green Valley MedicalisCenter Juneuse only by Zhaomengqi Wang in Applications in Corporate Finance Spring 2014 of 7 2012 2 This document authorized for taught by Dr. Ricardo Rodriguez from January 2014 to July 2014. For the exclusive use of Z. Wang Within each major category, all requests would then be classified by cost groupings, not as a means of prioritization, however, but only to organize them for evaluation. After checking the 130 requests for complete information, he and his assistant sorted them into the four main categories, and into cost groupings within each. TWO REQUESTS To evaluate the technique, Mr. Klein decided to use it to assess two very different capital requests. He chose the largest administrative support request and the most expensive request for medical equipment, assuming that there would be funds to support only one of the two. The cardiology, neurology and oncology programs had jointly requested a Positron Emission Tomography (PET) facility for a total investment of $5.8 million. The non medical request was for an entirely new in-house laundry facility costing approximately $1 million. The PET Proposal Positron Emission Tomography (PET) was an imaging technique that permitted examination of the chemistry of the brain and other organs. Unlike MRI and CT scanners which provided images and details about organs and tissues, PET could non-invasively measure biological and physiological activity. This application was most powerful in the areas of cardiology, oncology, and neurology/psychiatry. PET was used to detect diseases, to evaluate tissue viability, to measure tumors and the degree of malignancy, to pinpoint specific sources of neurological disorders, and to assist in the planning of various treatments. Financially, many observers believed that PET would save the healthcare system considerable resources by helping to avoid unnecessary procedures. The savings were to come from the technology that would replace other procedures and improve the medical community's ability to diagnose and treat patients. The short- and long-term need for PET scans nationally had been determined by the American Hospital Association. In February, the AHA had predicted an annual need for 1.1 million scans in the short-term and 1.7 million scans in the long-term. Currently, there were only 64 PET centers in the U.S. and Canada, roughly half of which were in research centers. There were no PET centers within 600 miles of Green Valley. Yet,the AHA's prediction, combined with a patient base of 1 million, translated into a regional demand for 2,750 scans per year: 300 epilepsy, 200 brain, and 2,250 cardiac. The hospital expected to provide a maximum of 2,300 scans, however: 1,600 scans for clinical use and 700 for research. The total capital investment included $1.4 million for the cyclotron, $2 million for each of two cameras (one for the head and one for the body), and $400,000 for facility renovations to accommodate the equipment. The anticipated operating expenses (other than depreciation) were $1.7 million for each of the ten years of the depreciable life of the equipment. According to a study by the Institute for Clinical PET (a trade association), the scans would be reimbursable at an average rate of $1,700. Although the 700 scans each year for research purposes would be charged at $1700 each to research grants, Mr. Klein was concerned about reimbursement for the 1,600 clinical scans billed directly to patients and third party payers. The PET imaging process used several radiopharmaceutical drugs. The most important of these was a drug called FDG, which had to be produced on site or close by due to the drug's short half-life. For this reason, the Food and Drug Administration had not yet approved FDG. While it was still under review, however, the FDA permitted the hospitals to continue using it. Although some third party reimbursement was already available, several of the large national insurance companies were not covering some or all of the PET scans. Mr. Klein estimated that Green Valley would be reimbursed for roughly half of the 1,600 clinical scans as long as FDG had not been approved by the FDA. Green Valley's medical staff thought the PET project not only would greatly contribute to the quality of the hospital's clinical care and research, but, once FDG was approved, would be a big money maker for the hospital. The medical staff also was convinced that PET's contribution to the medical needs of the hospital's patient base, and to society as a whole, through teaching and research, far outweighed any potential losses in the short term. The Laundry Proposal Unlike some hospitals that subcontract laundry, Granite Valley provided all of its laundry services in-house, mainly because there were no available subcontractors locally. As the hospital grew, _____________________________________________________________________________________________ Green Valley MedicalisCenter Juneuse only by Zhaomengqi Wang in Applications in Corporate Finance Spring 2014 of 7 2012 3 This document authorized for taught by Dr. Ricardo Rodriguez from January 2014 to July 2014. For the exclusive use of Z. Wang the laundry facilities had struggled to keep up with the demand. The current process was quite labor intensive due to relatively small capacity washer and dryer machines that the hospital had owned for many years. The machines also required a great deal of servicing. Nevertheless, the department's request for capital had been turned down in each of the past two years. The laundry department manager had carefully researched the most cost efficient equipment available. The request was for a continuous batch washer (CBW) with a single capacity of 3,000 pounds per hour, which would accommodate the needs of the hospital. A CBW cost $500,000. In addition, the request included three 220-pound-capacity dryers costing $75,000 each and a $100,000 press which would used to squeeze out excess water before drying. There was an additional $200,000 cost of installation which included training and maintenance for the fifteen year depreciable life of the equipment. In addition to improving the hospital's laundry services, the new facility would operate more efficiently than the current system, with large cost savings for the hospital. Currently, the hospital employed twelve full-time workers in addition to the supervisor to operate the laundry services. The full-cost of the 12 employees was $393,120. The department's manager had had some discussions with other hospitals that had purchased a CBW, and they confirmed that the CBW would require only six full-time employees plus the supervisor. Because of varying salary levels, Mr. Klein calculated that the labor savings would be approximately $197,000 per year. In addition to labor savings, the new equipment would save about $50,000 per year due to reduced utilities and maintenance costs. The current equipment could not be resold, but a local scrap dealer had offered to remove it at no cost to the hospital. THE DISCOUNT RATE Mr. Klein evaluated each of the proposals using his proposed capital budgeting technique. Since the technique used net present value, Mr. Klein had to determine the appropriate discount rate to use. This presented the problem of deriving the hospital's cost of capital and return on its assets. The hospital's liabilities entailed several different rates of interest. Long-term debt included payments on its municipal bonds averaging 6 percent and fixed mortgage payments at 8 percent. Equity capital, on the other hand, was divided between permanently restricted funds (where donors had stipulated that the principal could not be spent) and unrestricted funds (which could be used for any purpose). Mr. Klein believed that the appropriate discount rate should take all of the debt interest rates into account, but he was not sure what rate he should assign to the hospital's net assets (or equity), or whether any rate should be used for equity at all. He had read that some hospitals used 10 percent for their equity, but he wondered if the discount rate should be adjusted to reflect the risk and uncertainty associated with each project. Finally, he learned that the hospital was earning a 10 percent average return on its certificates of deposit, and about 5 percent on its cash. THE DECISION Mr. Klein was confident that his capital budgeting technique would be a vast improvement for the hospital. He was optimistic about the prospects of developing a system for that finally had a rational, systematic approach to evaluating capital requests. By including the subjective analyses, he believed it would be possible to quantify some of the hospital's returns that did not always translate into dollars. At the same time, however, he realized that someone would still lose out in the end. Assignment: 1. What are the key elements of GVMC's strategy? 2. Why does the existing capital budgeting system need to be changed? 3. How do you think the two projects will fare under Mr. Klein's new capital budgeting technique? As part of your assessment, calculate each project's net present value (NPV) and internal rate of return (IRR). Then fill out Exhibit 2, and complete Exhibit 3. 4. Assuming only one project can be accepted, which one should it be? Which one do you think will be accepted? _____________________________________________________________________________________________ Green Valley MedicalisCenter Juneuse only by Zhaomengqi Wang in Applications in Corporate Finance Spring 2014 of 7 2012 4 This document authorized for taught by Dr. Ricardo Rodriguez from January 2014 to July 2014. For the exclusive use of Z. Wang GREEN VALLEY MEDICAL CENTER Exhibit 1. Financial Statements for Most Recent Year ($000) BALANCE SHEET Assets Current Assets Cash and cash equivalents $ 11,725 Accounts receivable (net) 3,038 Inventories 3,365 Prepaid expenses and other current assets 520 $ 18,648 Assets whose uses limited Board designated for funded depreciation (principally certificates of deposit) 3,703 Property plant, and equipment (Less accumulated depreciation) 21,809 Total Assets $ 44,160 Liabilities and Net Assets Current Liabilities Accounts payable and accrued expenses Accrued compensation, payroll taxes and related withholdings Current portion of mortgage $ 5,142 3,163 1,451 $ 9,756 Long-term obligations Municipal bonds (net of unamortized issue discount) Mortgage payable $ 9,300 8,050 17,350 Net Assets Permanently restricted Unrestricted Total Liabilities and Net Assets $ 8,550 8,504 OPERATING STATEMENT Net revenues from services to patients Operating expenses: Salaries and wages Supplies and other expenses Depreciation Interest Excess of operating revenues over expenses Non-operating revenues (Investment income) Change in net assets 17,054 $ 44,160 $ 37,031 $18,406 14,970 2,163 1,060 36,599 $ 432 978 $ 1,410 _____________________________________________________________________________________________ Green Valley MedicalisCenter Juneuse only by Zhaomengqi Wang in Applications in Corporate Finance Spring 2014 of 7 2012 5 This document authorized for taught by Dr. Ricardo Rodriguez from January 2014 to July 2014. For the exclusive use of Z. Wang GREEN VALLEY MEDICAL CENTER Exhibit 2. Qualitative Evaluation A. Physician Impact Will this project have an effect on the physicians' attitude toward the hospital? Yes_____ No____ Not Accepted: -4 Intense and widespread negative reaction in the community will result in a severe blow to the hospital's image. (If no, proceed to Part B. If so, enter information about the extent of the impact, circle two answers below (one -3 A widespread negative effect on the hospital's general for non-acceptance and one for acceptance), and explain) image and reputation will result. What is the scope of impact on the physicians? -2 The hospital's image will be damaged among certain ___ a. One or two physicians will be affected. groups in the community. ___ b. The majority of the physicians in a hospital service will be affected -1 The attitudes of a few people will be negatively affected. ___ c. A substantial portion of the medical staff will be affected. Accepted: Explain your answer in a memorandum. +1 Relatively few people will be positively affected. Not Accepted: -4 The affected physicians will move their practices to other hospitals. +2 Some community groups will be favorably impressed. +3 A widespread positive effect on the hospital's image and reputation will result. -3 The affected physicians will tend to reduce their practices at the hospital. +4 Significant and widespread positive community reaction will contribute significantly to the hospital's reputa-2 The affected physicians will be disgruntled and will distion. cuss the lack of the expenditure or project in the community and with other physicians. C. Employee Impact -1 The affected physicians will be aware of the lack of support for the project, and will be less likely to believe that the hospital is maintaining a proper level of patient care. Will this project have an effect on the attitude of the hospital's personnel? Yes__ No____ If yes, circle two answers below: one for non-acceptance and one for acceptance, and explain your answers. Not Accepted: 0 No effect -4 Major and widespread negative impact on employee morale and attitude toward the hospital. Accepted: +1 The affected physicians will be aware of the expenditure -3 Widespread disappointment with the hospital and some or project and will be satisfied that the hospital is mainnegative effects on the hospital's image among employtaining a high level of patient care. ees. +2 The affected physicians will be very impressed and will -2 A limited group of employees (one or two departments) discuss the expenditure or project favorably in the comwill react negatively. munity and with other physicians. -1 Relatively few employees will be disturbed. +3 The affected physicians will moderately increase their 0 No effect practices at the hospital. +4 The affected physicians will move their practices to the Accepted: hospital. +1 Relatively few employees will know about the decision but they will be pleased. B. Community Impact Will this project have an effect on the commu- +2 A limited group of employees will be very pleased. nity attitude toward the hospital? Yes_____ No____ +3 Nearly all employees will be pleased. (If no, proceed to Part C. If so, circle two answers below: one for non-acceptance and one for acceptance, and +4 Major and widespread positive impact with long-term explain) effects on employee attitude toward the hospital will result. This document is authorized for use only by Zhaomengqi Wang in Applications in Corporate Finance Spring 2014 taught by Dr. Ricardo Rodriguez from January 2014 to July 2014. For the exclusive use of Z. Wang GREEN VALLEY MEDICAL CENTER Exhibit 3. Score Sheet1 Evaluation Instruction Potential Score Your Proposed Project's Score Economic If total investment is less than $100,000 If total investment is more than $100,000 If total annual incremental cost increase are less than $200,000 (or if there are cost savings) If total annual incremental cost increases are more than $200,000 If present value is equal to or greater than zero If present value is less than zero +1 _______ -1 +1 _______ -1 +2 _______ -2 Total economic score _______ Qualitative Add positive and negative answers (example: a -2 and a +4 would result in a total of 6 points. Scores therefore can range from 0 to 8) Physician Impact 0-8 _______ Community Impact 0-8 _______ Employee Impact 0-8 _______ Total qualitative score _______ Total Score _______ 1 Note: Exhibits 2 and 3 are a modified version of a system described in Hospital Progress. Copyright by the Catholic Hospital Association. Reprinted with permission. _____________________________________________________________________________________________ Green Valley MedicalisCenter Juneuse only by Zhaomengqi Wang in Applications in Corporate Finance Spring 2014 of 7 2012 7 This document authorized for taught by Dr. Ricardo Rodriguez from January 2014 to July 2014. Case Green Valley Medical Center (GVMC) Learning Objectives 1 Learn importance of Free Cash Flow 2 Appreciate use of cost of capital as the discount rate 3 Appreciate distinction between capital flows and operating flows 4 Use of NPV, PI, IRR, PP decision criteria to formulate a recommendation to accept or reject a project Reading CFW Chapter 5, pps. 61-89 QUESTIONS 1 What are the key elements of GVMC's strategy? 2 Populate the PET Tab with the relevant cash flows gleaned from the case. 3 Populate the Laundry Tab with the relevant cash flows gleaned from the case. Check the decision criteria (NPV, PI, IRR, PP) calculations to make sure they are accurate. 4 In the box below, make approipraite recommendations about accepting/rejecting one or both of the proposals Use Ex. 2 Qualitative Evaluation and Ex. 3 Score Sheet. (Use the enter key to keep line length within 7-8 columns.) Enter data only in the rows where data is given in the case. Leave the others blank. 1 2 A B C D E SECTION I. CALCULATE THE CHANGE IN EBIT, TAXES, AND DEPRECIATION (THIS BECOMES AN INPUT IN THE CALCULATION OF OPERATING CASH FLOWS IN SECTION II) 3 PET PROPOSAL F G H I J K L 2010 4 5 6 7 8 9 10 11 12 Less: variable cost of good sold Less: fixed cost of goods sold Less: variable GS&A expense Less: GS&A expense Less: depreciation 13 14 15 16 17 18 19 20 Equals: total operating expense Equals: EBIT Income tax rate & income tax 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 0 1 2 3 4 5 6 7 8 9 10 Units sold Sales price per unit Sales revenue Year 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0% 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 SECTION II. CALCULATE OPERATING CASH FLOW (THIS BECOMES AN INPUT IN THE CALCULATION OF FREE CASH FLOW IN SECTION IV Operating cash flow: EBIT 0 0 0 0 21 Minus: Taxes 0 0 0 0 22 Plus: Depreciation 0 0 0 23 Equals: Operating cash flows 0 0 0 0 24 25 SECTION III. CALCULATE THE NET WORKING CAPITAL (THIS BECOMES AN INPUT IN THE 26 CALCULATION OF FREE CASH FLOWS IN SECTION IV) 27 Change in Net Working Capital: 28 Revenue 0 29 Total operating expense 0 30 Receivables (enter days in Column B) 0 0 31 Inventory (enter days in Column B) 32 Payables (enter days in Column B) 33 Net working capital needs 34 Liquidation of working capital 35 Investment in working capital 36 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 37 SECTION IV. CALCULATE FREE CASH FLOW (USING INFORMATION CALCULATED IN 38 SECTIONS II AND III, IN ADDITION TO THE CHANGE IN CAPITAL SPENDING 39 Free cash flow: 40 Operating cash flow 0 0 0 41 Minus: Invesment in net working capital 0 0 0 42 Minus: Investment in PPE (CapEx) 0 0 0 43 Plus: Salvage value 44 Free cash flow 0 0 0 45 Cumulative free cash flow 0 0 0 46 47 48 49 50 SECTION V. CALCULATE DECISION CRITERIA USING FREE CASH FLOW Discount rate (K-wacc) 0.0% Net Present Value (NPV) 0.0 Profitability Index (PI) #DIV/0! 51 Internal Rate of Return (IRR) 52 Payback Period 53 Err:523 0 inspection of cumulative FCF - row 45 1 2 3 A B C D E SECTION I. CALCULATE THE CHANGE IN EBIT, TAXES, AND DEPRECIATION (THIS BECOMES AN INPUT IN THE CALCULATION OF OPERATING CASH FLOWS IN SECTION II) 4 5 6 7 LAUNDRY PROPOSAL Year Units sold Sales price per unit Sales revenue 8 9 10 11 12 Less: variable cost of good sold Less: fixed cost of goods sold Less: variable GS&A expense Less: GS&A expense Less: depreciation 13 14 15 16 17 18 19 20 Equals: total operating expense Equals: EBIT Income tax rate & income tax F G H I J K L 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 0 1 2 3 4 5 6 7 8 9 10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0% 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 SECTION II. CALCULATE OPERATING CASH FLOW (THIS BECOMES AN INPUT IN THE CALCULATION OF FREE CASH FLOW IN SECTION IV Operating cash flow: EBIT 0 0 0 0 21 Minus: Taxes 0 0 0 0 22 Plus: Depreciation 0 0 0 23 Equals: Operating cash flows 0 0 0 0 24 25 SECTION III. CALCULATE THE NET WORKING CAPITAL (THIS BECOMES AN INPUT IN THE 26 CALCULATION OF FREE CASH FLOWS IN SECTION IV) 27 Change in Net Working Capital: 28 Revenue 0 29 Total operating expense 0 30 Receivables (enter days in Column B) 0 0 31 Inventory (enter days in Column B) 32 Payables (enter days in Column B) 33 Net working capital needs 34 Liquidation of working capital 35 Investment in working capital 36 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 37 SECTION IV. CALCULATE FREE CASH FLOW (USING INFORMATION CALCULATED IN 38 SECTIONS II AND III, IN ADDITION TO THE CHANGE IN CAPITAL SPENDING 39 Free cash flow: 40 Operating cash flow 0 0 0 41 Minus: Invesment in net working capital 0 0 0 42 Minus: Investment in PPE (CapEx) 0 0 0 43 Plus: Salvage value 44 Free cash flow 0 0 0 45 Cumulative free cash flow 0 0 0 46 47 48 49 50 SECTION V. CALCULATE DECISION CRITERIA USING FREE CASH FLOW Discount rate (K-wacc) 0.0% Net Present Value (NPV) 0.0 Profitability Index (PI) #DIV/0! 51 Internal Rate of Return (IRR) 52 Payback Period 53 Err:523 0 inspection of cumulative FCF - row 45