please help with question 3 and 7 , solve it on excel, thanks !

Indian River Citrus Company (B) In Case 12, Lili Romero and Brent Gibbs analyzed a lite orange juice project for the Indian River Citrus Company. The project required an initial investment of $570,000 in fixed assets (including shipping and installation charges), plus a $10,000 addition to net working capital. the machinery would be used for 4 years and be depreciated on the basis of a 3-year MACRS class life. The appropriate MACRS depreciation allowances are 0.33, 0.45, 0.15, and 0.07 in Years 1 through 4, respectively, and the machinery is expected to have a salvage value of $100,000. If the project is current dollar (Year 0) wholesale price of $2 per carton. However, the sales price will be adjusted for inflation, which is expected to average 5 percent annually, so the expected price at the end of the first year is #2.10, the expected price at the end of the second year is $2.205 and so on. The lite orange juice project is expected to cannibalize the before-tax profit Indian River earns on its regular orange juice line by $20,000, because the two product lines are somewhat competitive. Further, the company expects cash operating costs to be $1.50 per unit in Time 0 dollars, and it expects these costs during the first year of operation (Year 1) are expected to be ($1.50) (1.02) (425,000) = $650,250. Indian River's tax rate is 40 percent, and its cost of capital is 10 percent. Cash flow data and other information, as developed by Lil and Brent using Excel in Table 1. When Lili and Brent presented their initial (Case 12) analysis to Indian River's executive committee, things went well, and they were congratulated on both their analysis and their presentation. However, several questions were raised. In particular, the executive committee wanted to see some type of risk analysis on the project-it appeared to be profitable, but what were the chances that it might nevertheless turn out to be a loser, and how should risk be analyzed and worked into the decision process? As the meeting was winding down, Lili and Brent were asked to start with the base case situation they had developed and then to discuss risk analysis, both in general terms and as it should be applied to the lite orange juice project. To begin, Lili and Brent met with the marketing and production managers to get a feel for the uncertainties involved in the cash flow estimates. After several sessions, they concluded that there was little uncertainty in any of the estimates except for unit salescost and sales price estimates were fairly well defined, but unit sales could vary widely. (In theory, unit sales price is also uncertain, but companies typically set sales prices on the basis of competitor's prices, so, at least initially, it can be treated as certain.) As estimated by the marketing staff, if product acceptance were normal, then sales quantity during Year 1 would be sold (the price would be kept at the forecasted level); and if consumer response were strong, then sales volume for Year 1 would be 650,000 units. In all cases, the price would increase at the inflation rate; hence, Year 1 revenues stated in Year 1 dollars, as they would appear on the cash flow statement, would be $892,500 under the expected conditions, they would be only $420,000 if things went badly, and they would amount to $1,365,000 if things went especially well. Cash costs per unit would remain $1.50 before adjusting for inflation, so total cash operating costs in Year 1 would be approximately $650,250 under normal conditions, $306,000 in the worst-case scenario, and $994,500 in the best-case scenario. These costs would be expected to increase in each successive year at a 2 percent rate. Lili and Brent also discussed the scenarios' probabilities with the marketing staff. After considerable debate, they finally agreed on a \"guesstimate\" of 25 percent probability of poor acceptance, 50 percent probability of average acceptance, and 25 percent probability of excellent acceptance. Lili and Brent also discussed with Victor Courtland, Indian River's director of capital budgeting, both the risk inherent in Indian River's average project and how the company typically adjusts for risk. based on historical data, Indian River's average project has a coefficient of variation of NPV in the range of 0.50 to 1.00, and Courtland has been adding or subtracting 3 percentage points to the cost of capital to adjust for differential project risk. When Lili and Brent asked about the basis for the 3 percentage point adjustment, Courtland stated that the adjustment apparently had no basis except the subjective judgment of John Gerber, a former director o capital budgeting who was no longer with the company. Therefore, maybe the adjustment should be 2 percentage points, or maybe 5 points, or maybe some other number. The discussion with Courtland raised another issue: Should the project's cost of capital be based on its stand-alone risk, on its risk as measured within the context of the firm's portfolio of assets (within-firm or corporate, risk), or in a market risk context? Indian River's target capital structure calls for 50 percent common equity, and the beforetax marginal cost of debt is currently 20 percent. Lili and Brent also determined that the T-bond rate, which they use as the risk-free rate, is 8 percent, and that the market risk premium is 6 percent. Since most members of Indian River's executive committee are unfamiliar with modern techniques of financial analysis, Lili and Brent planned to take a tutorial approach in the presentation. To structure the analysis, Lili and Brent developed the following set of questions, which they planned to ask and then answer as a method of presenting the analysis to the board. However, Lili and Brent contracted a contagious, though not fatal, viral infection. Neither will be able to attend the meeting. Therefore, you must make the presentation and answer any questions. Keep in mind that anyone on the board might interrupt you with a probing question at any time, so be sure you understand the logic, and the weaknesses, behind every technique you use and every statement you make. Questions 1. a. Why should firms be concerned with the riskiness of individual projects? b. (1) What are the three types of risk that are normally considered in capital budgeting? (2) Which type of risk is most relevant? (3) Which type of risk is easiest to measure? (4) Would you normally expect the three types of risk to be highly correlated? Would they be highly correlated in this specific instance? 1. What are the three types of risk that are relevant in capital budgeting? 2. How is each of these risk types measured, and how do they relate to one another? Answer: Here are the three types of project risk: Stand-alone risk is the project's total risk if it were operated independently. Stand-alone risk ignores both the firm's diversification among projects and investors' diversification among firms. Stand-alone risk is measured either by the project's standard deviation of NPV ( NPV) or its coefficient of variation of NPV (CVNPV). Note that other profitability measures, such as IRR and MIRR, can also be used to obtain stand-alone risk estimates. Within-firm risk is the total riskiness of the project giving consideration to the firm's other projects, that is, to diversification within the firm. It is the contribution of the project to the firm's total risk, and it is a function of (a) the project's standard deviation of NPV and (b) the correlation of the projects' returns with those of the rest of the firm. Within-firm risk is often called corporate risk, and it is measured by the project's corporate beta, which is the slope of the regression line formed by plotting returns on the project versus returns on the firm. Market risk is the riskiness of the project to a well-diversified investor, hence it considers the diversification inherent in stockholders' portfolios. It is measured by the project's market beta, which is the slope of the regression line formed by plotting returns on the project versus returns on the market. 3. How is each type of risk used in the capital budgeting process? Answer: Because management's primary goal is shareholder wealth maximization, the most relevant risk for capital projects is market risk. However, creditors, customers, suppliers, and employees are all affected by a firm's total risk. Since these parties influence the firm's profitability, a project's within-firm risk should not be completely ignored. Unfortunately, by far the easiest type of risk to measure is a project's stand-alone risk. Thus, firms often focus on this type of risk when making capital budgeting decisions. However, this focus does not necessarily lead to poor decisions, because most projects that a firm undertakes are in its core business. In this situation, a project's stand-alone risk is likely to be highly correlated with its within-firm risk, which in turn is likely to be highly correlated with its market risk. 2. a. What is sensitivity analysis? 1. What is sensitivity analysis? Answer: Sensitivity analysis measures the effect of changes in a particular variable, say revenues, on a project's NPV. To perform a sensitivity analysis, all variables are fixed at their expected values except one. This one variable is then changed, often by specified percentages, and the resulting effect on NPV is noted. (One could allow more than one variable to change, but this then merges sensitivity analysis into scenario analysis.) b. Complete Table 1, assuming initially that the project has average risk. Then develop a new table which shows a sensitivity analysis of NPV to sales quantity, salvage value, and the cost of capital. Assume that each of these variable can deviate from its base case, or expected value, by plus or minus 10 percent, 20 percent, and 30 percent. See Table 2 for partial results. CH13 mini case c. Prepare a sensitivity diagram and discuss the results. d. What are the primary weaknesses of sensitivity analysis? What are its primary advantages? j. 3. What is the primary weakness of sensitivity analysis? What is its primary usefulness? Answer: The two primary disadvantages of sensitivity analysis are (1) that it does not reflect the effects of diversification and (2) that it does not incorporate any information about the possible magnitudes of the forecast errors. Thus, a sensitivity analysis might indicate that a project's NPV is highly sensitive to the sales forecast, hence that the project is quite risky, but if the project's sales, hence its revenues, are fixed by a long-term contract, then sales variations may actually contribute little to the project's risk. It also ignores any relationships between variables, such as unit sales and sales price. Therefore, in many situations, sensitivity analysis is not a particularly good indicator of risk. However, sensitivity analysis does identify those variables which potentially have the greatest impact on profitability, and this helps management focus its attention on those variables that are probably most important. 3. Complete the scenario analysis initiated in Table 3. What is the worst case NPV? The best-case NPV? Use the worst-case, most likely , and best-case NPVs, and their probabilities of occurrence, to find the project's expected NPV, standard deviation, and coefficient of variation. 4. What are the primary advantages and disadvantages of scenario analysis? l. Are there problems with scenario analysis? Define simulation analysis, and discuss its principal advantages and disadvantages. Answer: Scenario analysis examines several possible scenarios, usually worst case, most likely case, and best case. Thus, it usually considers only 3 possible outcomes. Obviously the world is much more complex, and most projects have an almost infinite number of possible outcomes. Simulation analysis is a type of scenario analysis which uses a relatively powerful financial planning software such as interactive financial planning system (IFPs) or @risk (a spreadsheet add-in). Simple simulations can also be conducted with other spreadsheet add-ins, such as Simtools. Here the uncertain cash flow variables (such as unit sales) are entered as continuous probability distribution parameters rather than as point values. Then, the computer uses a random number generator to select values for the uncertain variables on the basis of their designated distributions. Once all of the variable values have been selected, they are combined, and an NPV is calculated. The process is repeated many times, say 1,000, with new values selected from the distributions for each run. The end result is a probability distribution of NPV based on a sample of 1,000 values. The software can graph the distribution as well as print out summary statistics such as expected NPV and NPV. Simulation provides the decision maker with a better idea of the profitability of a project than does scenario analysis because it incorporates many more possible outcomes. Although simulation analysis is technically refined, its usefulness because managers are often unable to accurately specify the probability distributions. Further, the correlations among the variables must be specified, along with the correlations over managers are unable to do this with much confidence, then the simulation analyses are of limited value. is limited variables' uncertain time. If results of Recognize also that neither sensitivity, scenario, nor simulation analysis provides a decision rule--they may indicate that a project is relatively risky, but they do not indicate whether the project's expected return is sufficient to compensate for its risk. Finally, remember that sensitivity, scenario, and simulation analyses all focus on stand-alone risk, which is not the most relevant risk in capital budgeting analysis. 5. skip 6. a. Would the lite orange juice project be classified as high risk, average risk, or low risk by your analysis thus far? (Hint: Consider the project's coefficient of variation of NPV.) What type of risk have you been measuring? m. 1. Assume that Shrieves' average project has a coefficient of variation in the range of 0.2-0.4. Would the new line be classified as high risk, average risk, or low risk? What type of risk is being measured here? Answer: The project has a CV of 0.57, which is above the average range of 0.2-0.4, so it falls into the high risk category. The CV measures a project's stand-alone risk-it is merely a measure of the variability of returns (as measured by NPV) about the expected return. b. What do you think the project's corporate risk would be, and how could you measure it? c. How would it affect your risk assessment if you were told that the cash flows from this project were totally uncorrelated with Indian River's other cash flows? What of they were expected to be negatively correlated? Lower risk d. How would the project's cash flows probably be correlated with the cash flows of most other firms, say the S&P 500, and hence with the stock market? What difference would that make you in your capital budgeting analysis? 7. Calculate the project's differential risk-adjusted NPV. Should the project be accepted? What if it had a coefficient of variation (CV) of NPV of only 0.15 and was judged to be a low-risk project? 8. Lili and Brent thought long and hard about the lite orange juice project's market beta. They finally agreed, based on some data from the Florida Citrus Producers Association, to use 2.0 as their best estimate of the beta for the equity invested in the project. a. On the basis of market risk, what is the project's required rate of return? b. Describe briefly two methods that might possibly be used to estimate the project's beta. Do you think those methods would be feasible in this situation? c. What are the advantages and disadvantages of focusing on a project's market risk rather than on the other types of risk? 9. Indian River is also evaluating two different systems for disposing of wastes associated with another product, frozen grapefruit juice. Plan W requires more workers but less capital, while Plan C requires more capital but fewer workers. Both systems have an estimated 3-year life, but the one selected will probably be repeated at the end of its life into the foreseeable future. Since the waste-disposal choice has no impact on revenues, Lili and Brent think that the decision should be based on the relative costs of the two systems; these costs are set forth next (in thousands of dollars). The Year 0 costs represent the capital outlays. ______Expected Net Costs_____ Year Plan W Plan C 0 ($2,000) ($4,000) 1 (2,000) (1,200) 2 (2,000) (1,200) 3 (2,000) (1,200) a. Assume initially that the two systems are both of average risk. Which one should be chosen? b. Now assume that the labor intensive Plan W is judged to be riskier than an average project, because future labor costs are very difficult to forecast, but Plan C is still of average risk, because most of its costs can be firmly established. When these risk differences are considered, which system should be chosen? c. Suppose that during the presentation, you were asked what the two waste-disposal projects IRRs and NPVs were. How would you have answered that question? Table 1 Selected Case Data ________________________________________________________________________ Net Investment Outlay: Depreciation Schedule: Price $500,000 MACRS Depr. End-of-year Freight X Year Factor Expense Book Value Installation X 1 33% $188,100 $381,900 Change in X 2 45 X X NWC X 3 4 15 X X 7 39,900 0 100% $570,000 ________________________________________________________________________ Cash Flows: Year 0 Year 1 Year 2 Year 3 Year 4 Unit price $ 2.10 X X $ 2.43 Unit sales 425,000 X X 425,000 Revenues $892,500 X X $1,033,180 Operating costs 650,250 X X 690,051 Depreciation 188,100 X X 39,900 Other project effects 20,000 X X 20,000 Before tax income $34,150 X X $283,230 Taxes 13,660 X X 113,292 Net income $20,490 X X $169,938 Plus depreciation 188,100 X X 39,900 Net op cash flow $208,590 X X $209,838 Salvage value $100,000 SV tax 40,000 Recovery of NWC 10,000 Terminal CF $70,000 Project NCF ($580,000) $208,590 X X $279,838 ________________________________________________________________________ Decision Meaures: ________________________________________________________________________ NPV $166,719 IRR X MIRR 17.2% Payback 2.6 years ________________________________________________________________________ Table 2 Sensitivity Analysis Results: Lite Orange Juice Project ________________________________________________________________________ Variable Change NPV after Indicated Change______ from Base Level Quantity Salvage Value k -30% ($2,572) $154,425 $219,799 -20 X X X -10 112,003 162,621 183,766 base case 166,719 166,719 166,719 +10 X X X +20 276,150 174,915 134,414 +30 X X X ________________________________________________________________________ Table 3 Scenario Analysis Results: Lite Orange Juice Project ________________________________________________________________________ Scenario Prob NPV IRR MIRR______ Worst 25% ($122,952) 00.0% 3.6% Most likely 50 166,719 22.2 17.2 Best 25 X X X Expected value $166,719 21.4% X Standard deviation $204,829 14.6% X Coefficient of variation 1.2 0.7 X ________________________________________________________________________