i have added the article for context, i need help figuring out how the answers were solved on excel please! i need the formulas and with a brief explination in how they were done. thank you will give thumbs up !
Capital Budgeting Methods and Cash Flow Estimation Tasty Foods Corporation is a food conglomerate with major product lines of cereals, frozen dinners, and canned sodas and fruit juices. The firm was founded in 1965 by Henry Abercrombie, an ambitious college graduate who had just acquired a small inheritance and who wanted to be his own boss. Equipped with an idea for an instant hot cereal, Henry founded Tasty Foods. The instant hot cereal was well received by the public, and through Henry's industriousness, his superior ideas, and his excellent business instincts, the company expanded considerably during its 40 -plus years of existence, both by acquisitions and innovative product ideas. Three years ago, because of his age and declining health, Henry hired his daughter, Abigail, as a management trainee with the intention that she would eventually succeed him as president of the company. Abigail, a Harvard MBA graduate and an amateur marathon runner, has a number of new product ideas she would like the company to introduce. She believes Tasty Foods has not stayed current with general foods trends, especially the trend toward low fat and low sodium content products and the introduction of athletic drinks to replace essential body fluids lost during exercise. While Tasty Foods' financial position is still healthy, Abigail believes the company must introduce new products directed toward the "athletically inclined and health conscious" public to keep its position in the marketplace. She believes that if the company doesn't "get on the bandwagon" soon, the firm will see a decline in revenues and lose its position in the marketplace. In fact, immediately after she was hired, Abigail worked with the company's marketing and new product development departments to launch a new athletic drink called "High Energy." Marketing surveys had shown that the public was dissatisfied with athletic drinks currently on the market because they tasted too much like watered down fruit juice or they left a bad after taste. High Energy tastes like a milk shake and comes in chocolate, French vanilla, and strawberry flavors, has no bad after taste, and replaces the electrolytes lost from the body during exercise. Because of the urgency given to High Energy's development and its hasty market introduction, both Abigail and the new product development team had overlooked one of High Energy's weakness - the fact that the drink contained five grams of fat per eight ounce serving. Abigail, along with the marketing department, agreed that the critical point was to introduce the product quickly, and once they were sure that High Energy had some market acceptance, then a "lite" version containing fewer calories and fat grams would be developed. In fact, if the lite athletic drink is introduced, this will open new market opportunities for Tasty Foods in their frozen dinner line. The company would use a similar "lite" process to develop a line of "lite frozen pizzas". However, if the lite athletic drink is not undertaken, then Tasty Foods would not be in a position to develop its lite frozen pizza line. High Energy has been on the market for eighteen months, and recent market surveys indicate that the drink does indeed have a small following, but would receive a much larger market share if it were available in a "lite" version. So, Abigail and Tasty Foods' management are currently evaluating "High Energy- Lite". A test marketing program carried out earlier this year a cost of $262,500 showed enthusiastic acceptance for the product, which would cost more than original version but offer 50% less calories, have only two grams of fat per eight ounce serving, and still would have its milk shake taste. Abigail has hired you and your consulting firm as financial analysts to help her analyze this project and to present the findings to Tasty Foods' Executive Committee. Abigail has stressed the importance of this presentation to you, to the consulting firm, and to her. If the presentation goes well, there's a very good chance that Abigail will be promoted to Vice PresidentOperation, that you will be promoted to senior financial analyst, and that Abigail would use the consulting firm on a regular basis. an unused section of Tasty Foods' main plant. Relatively inexpensive used machinery with an estimated cost of only $700,000 would be purchased, but shipping costs to move the machinery to Tasty Foods' plant would add another $35,000, and installation charges would amount to another $70,000. Further, Tasty Foods inventories (raw material, work-in-process, and finish goods) would have to be increased by $30,000 at the time of the initial investment. If the used machinery is purchased, it would have remaining economic life of 4 years, and the company has obtained a special tax ruling that would allow it to depreciate the equipment under the MACRS 3-year class. The depreciation allowances are 0.33,0.45,0.15, and 0.07 in Years 1 through 4 , respectively. The machinery is expected to have a salvage value of $87,500 after 4 years of use. Tasty Foods' management expects to sell 700,000 16-ounce cartons of the new lite drink in each of the next 4 years. The price is expected to be $2.00 per carton. Fixed costs are estimated to be $190,000 per year, and variable cash operating costs are estimated at $1.25 per unit. Note that operating cost are a function of the number of units sold rather that unit price, so unit price changes have no direct effect on operating costs. In examining the sales figures, you noted a short memo from Tasty Foods' sales manager which indicated that High EnergyLite would cut into the firm's sales of High Energy-Original this type of effect is called cannibalization. Specially, the sales manager estimated that sales of High Energy-Original would fall by 30 percent if High Energy-Lite were introduced. You pursued this further, with both the sales and production managers, and they estimated that the new athletic drink would probably lower the firms' High Energy-Original sales by $80,000 per year. However, this volume reduction would also reduce production costs by $35,000 per year on a pre-tax basis, so the net pre-tax cannibalization effect would be $80,000+$35,000= $45,000. Tasty Foods' federal-plus-state tax rate is 40 percent, and its overall cost of capital is 12 percent, calculated as follows: As you began to look into the situation, you learned from Abigail that All Natural Foods, Inc. has expressed an interest in leasing the space that would be used to produce High EnergyLite. The terms of the lease, if it were made, would call for Tasty Foods to receive rental payments of $43,750 at the beginning of each year, and, at All Natural Foods' insistence, the lease would have to run for 20 years. However, it is not at all certain that All Natural Foods will ultimately agree to rent the space. Yet another consideration is the possibility that Tasty Foods could itself rent space in the local Coca Cola bottling plant. In that event, almost no capital would have to be invested in the project, but rental payments would have to be made to the Coke Bottler. Finally, Abigail has made you aware of a concern the production VP has raised, namely, that while the space is not being used now, it will be needed for other products within the next 2 or 3 years, assuming normal growth in sales of those products. Your task is to work with Abigail to analyze the situation. You must recommend acceptance or rejection, and evaluate he project's acceptability using the NPV, IRR, modified IRR (MIRR), and payback criteria. The company currently requires a project payback to be less than two and one-half years. For the initial analysis, assume that High Energy-Lite is of average risk, so the 12%WACC is the appropriate discount/hurdle rate. Abigail is also concerned about the proper estimation of the relevant cash flows associated with the project. For example, should the test marketing costs be charge to the project? How should the cannibalization effect be handled under different assumptions of expected competitor actions? How about All Natural Foods' interest in leasing the space that would be used for High Energy-Lite production, and the other divisions' plant needs 2 or 3 years hence? Abigail also noted that the 12% discount rate is based on quoted, or nominal, market-determined component costs of capital, while both the sales price and the operating cost per unit are in current dollar terms; does that present a problem? She also wondered whether it would be appropriate to assume neutral inflation equal to the 4% general rate of inflation, and if not, how sensitive the results would be to alternative assumptions of differential inflation impacts on revenues and costs. Also, if the Coke plant is really available, how should this factor be taken into account? In addition, Tasty Foods' treasure has been reading article in a corporate finance applications journal that discuss the fact that NPV analysis doesn't consider opportunities created or destroyed by the acceptance of a project. These article suggest that an option valuation approach should be used to value these additional or lost opportunities. Abigail expects these and other questions to be raised when she presents the recommendations to the Executive Committee, and she would like your input in response to these questions. Your task is to help her perform the analysis and write up a report for the Executive Committee. To Help structure your analysis and report, answer the following questions. Salvage value SV tak Reoovery or NWC Termination CF Project NCF Step-by-step explanation INPUT DATA: Initial hvestment: Equipment cost Freight Installation Change in NWIC Operating Flows and Inflation Rotes: Unit sales t=0 sales price Fined oper costs t=0 VC per unit Price inflation Cost inflation SV. Taves, and C of C. Salvage value Usefultife (virs) Takrate Cost of capiral Cannibalization of Other Proiects: Pevenue Loss Cost Reduction Net Cannibalization KEY DUTPU $700,000 $35,000 $70,000 $30,000 \begin{tabular}{|c|c|c|} \hline NPV & $178.33 & \\ \hline RR & 21.60 & \\ \hline MPR & 17.60% & \\ \hline APR & & \\ \hline \begin{tabular}{l} Paybac \\ k \end{tabular} & 2.56 & years \\ \hline \end{tabular} 700.000 $2.00 $190,000 $125 4\% $87,500 4 40% 12% $80,00 $35,00 $45,00 0
