It would cost $10 million at Year 0 to buy the equipment necessary to manufacture the server.

User Generated

Uvtureyrneavat

Business Finance

Nova Southeastern University

Description

Please provide answer and calculations. Complete in the excel file.

Webmasters.com has developed a powerful new server that would be used for corporations’ Internet activities. It would cost $10 million at Year 0 to buy the equipment necessary to manufacture the server. The project would require net working capital at the beginning of each year in an amount equal to 10% of the year's projected sales; for example, NWC0 = 10%(Sales1). The servers would sell for $24,000 per unit, and Webmasters believes that variable costs would amount to $17,500 per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 3%. The company’s nonvariable costs would be $1 million at Year 1 and would increase with inflation.

Unformatted Attachment Preview

4/16/2010 Chapter 11. Ch 11-18 Build a Model Webmasters.com has developed a powerful new server that would be used for corporations’ Internet activities. It would cost $10 million at Year 0 to buy the equipment necessary to manufacture the server. The project would require net working capital at the beginning of each year in an amount equal to 10% of the year's projected sales; for example, NWC 0 = 10%(Sales1). The servers would sell for $24,000 per unit, and Webmasters believes that variable costs would amount to $17,500 per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 3%. The company’s nonvariable costs would be $1 million at Year 1 and would increase with inflation. The server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 years. Also, the project's returns are expected to be highly correlated with returns on the firm's other assets. The firm believes it could sell 1,000 units per year. The equipment would be depreciated over a 5-year period, using MACRS rates. The estimated market value of the equipment at the end of the project’s 4-year life is $500,000. Webmasters’ federal-plus-state tax rate is 40%. Its cost of capital is 10% for average-risk projects, defined as projects with a coefficient of variation of NPV between 0.8 and 1.2. Low-risk projects are evaluated with a WACC of 8%, and high-risk projects at 13%. a. Develop a spreadsheet model, and use it to find the project’s NPV, IRR, and payback. Key Output: NPV = IRR = MIRR = Part 1. Input Data (in thousands of dollars) Equipment cost Net WC/Sales First year sales (in units) Sales price per unit Variable cost per unit Nonvariable costs $10,000 10% 1,000 $24.00 $17.50 $1,000 Part 2. Depreciation and Amortization Schedule Year Initial Cost Equipment Depr'n Rate Equipment Depr'n, Dollars Ending Bk Val: Cost – Accum Dep'rn Part 3. Net Salvage Values, in Year 4 Estimated Market Value in Year 4 Book Value in Year 4 Expected Gain or Loss Taxes paid or tax credit Net cash flow from salvage Market value of equipment at Year 4 Tax rate WACC Inflation Years 2 1 20.0% 32.0% Equipment Page 1 3 19.0% $500 40% 10% 3.0% 4 12.0% Accum'd Depr'n Part 4. Projected Net Cash Flows (Time line of Annual Cash Flows) Years 0 Investment Outlays at Time Zero: Equipment 1 2 3 4 2 3 4 Operating Cash Flows over the Project's Life: Units sold Sales price Variable costs Sales revenue Variable costs Nonvariable operating costs Depreciation (equipment) Oper. income before taxes (EBIT) Taxes on operating income (40%) After-tax operating income Add back depreciation Operating cash flow Terminal Year Cash Flows: Required level of net working capital Required investment in NWC Terminal Year Cash Flows: Net salvage value Net Cash Flow (Time line of cash flows) Part 5. Key Output: Appraisal of the Proposed Project Net Present Value (at 10%) IRR MIRR Payback (See calculation below) Data for Payback Years Net cash flow Cumulative CF Part of year required for payback 3 0 1 0 b. Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units sold. Set these variables’ values at 10% and 20% above and below their base-case values. Include a graph in your analysis. Page 2 Part 6. Evaluating Risk: Sensitivity Analysis I. Sensitivity of NPV to Changes in Inputs. Here we use Excel "Data Tables" to find NPVs at different unit sales, WACC, variable costs, sales price and nonvariable costs--changing one variable at a time, holding other things constant. % Deviation 1st YEAR UNIT SALES from Units NPV Base Case Sold $0 -20% 0 -10% 0 0% 0 10% 0 20% 0 % Deviation from Base Case -20% -10% 0% 10% 20% % Deviation from Base Case -20% -10% 0% 10% 20% % Deviation from Base Case -20% -10% 0% 10% 20% VARIABLE COST Variable NPV Costs $0 0 0 0 0 0 % Deviation NONVARIABLE COST from Fixed NPV Base Case Costs $0 -20% 0 -10% 0 0% 0 10% 0 20% 0 WACC WACC NPV $0 0 0 0 0 0 SALES PRICE Sales NPV Price $0 0 0 0 0 0 Note about data tables. The data in the column input should NOT be input using a cell reference to the column input cell. For example, the base case number of units sold in Cell B105 should be the number 1000; you should NOT have the formula =D29 in that cell. This is because you'll use D29 as the column input cell in the data table and if Excel tries to iteratively replace Cell D29 with the formula =D29 rather than a series of numbers, Excel will calculate the wrong answer. Unfortunately, Excel won't tell you that there is a problem, so you'll just get the wrong values for the data table! Page 3 Deviation from Base Case -20% -10% 0% 10% 20% Sales Price $0 0 0 0 0 NPV at Different Deviations from Base Variable Nonvariable Cost/Unit Units Sold Cost $0 $0 $0 0 0 0 0 0 0 0 0 0 0 0 0 WACC $0 0 0 0 0 Range c. Now conduct a scenario analysis. Assume that there is a 25% probability that best-case conditions, with each of the variables discussed in Part b being 20% better than its base-case value, will occur. There is a 25% probability of worst-case conditions, with the variables 20% worse than base, and a 50% probability of base-case conditions. Page 4 Part 7. Evaluating Risk: Scenario Analysis Scenario Best Case Base Case Worst Case Probability Sales Price Unit Sales Variable Costs 25% 50% 25% $28.80 $24.00 $19.20 1,200 1,000 800 $14.00 $17.50 $21.00 NPV Squared Deviation Times Probability Expected NPV = sum, prob times NPV Standard Deviation = Sq Root of column H sum Coefficient of Variation = Std Dev / Expected NPV d. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV, IRR, and payback. CV range of firm's average-risk project: Low-risk WACC = 8% WACC = 10% High-risk WACC = 13% 0.8 to 1.2 Risk-adjusted WACC = Risk adjusted NPV = IRR = Payback = e. On the basis of information in the problem, would you recommend that the project be accepted? Page 5 Page 6 (11–11) Sceanrio Analysis Shao Industries is considering a proposed project for its capital budget. The company estimates the project’s NPV is $12 million. This estimate assumes that the economy and market conditions will be average over the next few years. The company’s CFO, however, forecasts there is only a 50% chance that the economy will be average. Recognizing this uncertainty, she has also performed the following scenario analysis: What is the project’s expected NPV, its standard deviation, and its coefficient of variation? 11-12 New Project Analysis Madison Manufacturing is considering a new machine that costs $250,000 and would reduce pre-tax manufacturing costs by $90,000 annually. Madison would use the 3-year MACRS method to depreciate the machine, and management thinks the machine would have a value of $23,000 at the end of its 5-year operating life. The applicable depreciation rates are 33%, 45%, 15%, and 7%, as discussed in Appendix 11A. Working capital would increase by $25,000 initially, but it would be recovered at the end of the project’s 5-year life. Madison’s marginal tax rate is 40%, and a 10% WACC is appropriate for the project. a. Calculate the project’s NPV, IRR, MIRR, and payback. b. Assume management is unsure about the $90,000 cost savings—this figure could deviate by as much as plus or minus 20%. What would the NPV be under each of these extremes? c. Suppose the CFO wants you to do a scenario analysis with different values for the cost savings, the machine’s salvage value, and the working capital (WC) requirement. She asks you to use the following probabilities and values in the scenario analysis: Calculate the project’s expected NPV, its standard deviation, and its coefficient of variation. Would you recommend that the project be accepted? 11.13 Replacement Analysis The Everly Equipment Company purchased a machine 5 years ago at a cost of $90,000. The machine had an expected life of 10 years at the time of purchase, and it is being depreciated by the straight-line method by $9,000 per year. If the machine is not replaced, it can be sold for $10,000 at the end of its useful life. A new machine can be purchased for $150,000, including installation costs. During its 5-year life, it will reduce cash operating expenses by $50,000 per year. Sales are not expected to change. At the end of its useful life, the machine is estimated to be worthless. MACRS depreciation will be used, and the machine will be depreciated over its 3-year class life rather than its 5-year economic life, so the applicable depreciation rates are 33%, 45%, 15%, and 7%. The old machine can be sold today for $55,000. The firm’s tax rate is 35%, and the appropriate WACC is 16%. a. If the new machine is purchased, what is the amount of the initial cash flow at Year 0? b. What are the incremental net cash flows that will occur at the end of Years 1 through 5? c. What is the NPV of this project? Should Everly replace the old machine? 11.14 Replacement Analysis The Balboa Bottling Company is contemplating the replacement of one of its bottling machines with a newer and more efficient one. The old machine has a book value of $600,000 and a remaining useful life of 5 years. The firm does not expect to realize any return from scrapping the old machine in 5 years, but it can sell it now to another firm in the industry for $265,000. The old machine is being depreciated by $120,000 per year, using the straight-line method. The new machine has a purchase price of $1,175,000, an estimated useful life and MACRS class life of 5 years, and an estimated salvage value of $145,000. The applicable depreciation rates are 20%, 32%, 19%, 12%, 11%, and 6%. It is expected to economize on electric power usage, labor, and repair costs, as well as to reduce the number of defective bottles. In total, an annual savings of $255,000 will be realized if the new machine is installed. The company’s marginal tax rate is 35%, and it has a 12% WACC a. What is the initial net cash flow if the new machine is purchased and the old one is replaced? b. Calculate the annual depreciation allowances for both machines, and compute the change in the annual depreciation expense if the replacement is made. c. What are the incremental net cash flows in Years 1 through 5? d. Should the firm purchase the new machine? Support your answer. e. In general, how would each of the following factors affect the investment decision, and how should each be treated? (1) The expected life of the existing machine decreases. (2) The WACC is not constant but is increasing as Balboa adds more projects into its capital budget for the year. 11.15 Risky Cash Flows The Bartram-Pulley Company (BPC) must decide between two mutually exclusive investment projects. Each project costs $6,750 and has an expected life of 3 years. Annual net cash flows from each project begin 1 year after the initial investment is made and have the following probability distributions: BPC has decided to evaluate the riskier project at a 12% rate and the less risky project at a 10% rate. a. What is the expected value of the annual net cash flows from each project? What is the coefficient of variation (CV)? (Hint: σB = $5,798 and CVB = 0.76.) b. What is the risk-adjusted NPV of each project? c. If it were known that Project B is negatively correlated with other cash flows of the firm whereas Project A is positively correlated, how would this affect the decision? If Project B’s cash flows were negatively correlated with gross domestic product (GDP), would that influence your assessment of its risk? 11.16 Simulation Singleton Supplies Corporation (SSC) manufactures medical products for hospitals, clinics, and nursing homes. SSC may introduce a new type of X-ray scanner designed to identify certain types of cancers in their early stages. There are a number of uncertainties about the proposed project, but the following data are believed to be reasonably accurate. SSC uses a cost of capital of 15% to analyze average-risk projects, 12% for low-risk projects, and 18% for high-risk projects. These risk adjustments primarily reflect the uncertainty about each project’s NPV and IRR as measured by their coefficients of variation. The firm is in the 40% federal-plus-state income tax bracket. a. What is the expected IRR for the X-ray scanner project? Base your answer on the expected values of the variables. Also, assume the after-tax “profits” figure that you develop is equal to annual cash flows. All facilities are leased, so depreciation may be disregarded. Can you determine the value of σIRR short of actual simulation or a fairly complex statistical analysis? b. Assume that SSC uses a 15% cost of capital for this project. What is the project’s NPV? Could you estimate σNPV without either simulation or a complex statistical analysis? c. Show the process by which a computer would perform a simulation analysis for this project. Use the random numbers 44, 17, 16, 58, 1; 79, 83, 86; and 19, 62, 6 to illustrate the process with the first computer run. Actually calculate the firstrun NPV and IRR. Assume the cash flows for each year are independent of cash flows for other years. Also, assume the computer operates as follows: (1) A developmental cost and a project life are estimated for the first run using the first two random numbers. (2) Next, sales volume, sales price, and cost per unit are estimated using the next three random numbers and used to derive a cash flow for the first year. (3) Then, the next three random numbers are used to estimate sales volume, sales price, and cost per unit for the second year, hence the cash flow for the second year. (4) Cash flows for other years are developed similarly, on out to the first run’s estimated life. (5) With the developmental cost and the cash flow stream established, NPV and IRR for the first run are derived and stored in the computer’s memory. (6) The process is repeated to generate perhaps 500 other NPVs and IRRs. (7) Frequency distributions for NPV and IRR are plotted by the computer, and the distributions’ means and standard deviations are calculated. 11.17 Decision Tree The Yoran Yacht Company (YYC), a prominent sailboat builder in Newport, may design a new 30-foot sailboat based on the “winged” keels first introduced on the 12-meter yachts that raced for the America’s Cup. The Yoran Yacht Company (YYC), a prominent sailboat builder in Newport, may design a new 30-foot sailboat based on the “winged” keels first introduced on the 12-meter yachts that raced for the America’s Cup. The next stage, if undertaken, would consist of making the molds and producing two prototype boats. This would cost $500,000 at t = 1. If the boats test well, YYC would go into production. If they do not, the molds and prototypes could be sold for $100,000. The managers estimate the probability is 80% that the boats will pass testing and that Stage 3 will be undertaken. Stage 3 consists of converting an unused production line to produce the new design. This would cost $1 million at t = 2. If the economy is strong at this point, the net value of sales would be $3 million; if the economy is weak, the net value would be $1.5 million. Both net values occur at t = 3, and each state of the economy has a probability of 0.5. YYC’s corporate cost of capital is 12%. a. Assume this project has average risk. Construct a decision tree and determine the project’s expected NPV. b. Find the project’s standard deviation of NPV and coefficient of variation of NPV. If YYC’s average project had a CV of between 1.0 and 2.0, would this project be of high, low, or average stand-alone risk?
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

CANCELLATIONThere was a lot involved in th...


Anonymous
Great! 10/10 would recommend using Studypool to help you study.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags