I need to develope the following for the attached case information: Spreadsheet (with seperate tabs for each) and all calculations -Operating cash flows -Incremental Cash Flows, including invenstment and salvage -warranty costs -wacc -NPV, IRR,Profatibility Index, and payback period

Tesca Works Introduction Michael Burton has recently been hired as the CEO of Tesca Works, Inc. Previously he had been the marketing manager for a large manufacturing company and had established a reputation for identifying new consumer trends. Tesca Works Inc. is a California-based generator manufacturing company. The company is well known for manufacturing large, heavy-duty generators at a reasonable cost. One of its greatest achievements is that its generators can be easily modified or customized for different applications. Also, Tesca Works currently builds commercial appliances. The company is considering an expansion of its current product line to include refrigerator and maybe, sometime in the future, consumer appliances. Mr. Burton feels that due to high energy prices, consumers will be more willing to consider purchasing new efficient appliances. Tesca Works Inc. is a California-based generator manufacturing company. The company is well known for its innovation and ability to produce high quality products at a reasonable cost. One of its greatest achievements is that its manufacturing processes are adaptable to other durable goods. Also, Tesca Works currently builds commercial appliances. Profile of Tesca Works Tesca Works, Inc. was established by the Smith brothers in 1880 as the Logging Saw Company. The firm started manufacturing large steam saws to serve the logging industry which processed lumber. Their customers were construction companies that provided housing for the population increase in California. The Smith brothers quickly realized that the times were changing. They started looking for the technologies that would keep them at the forefront of their field of business. In 1915, the Smith brothers decided that they needed to make generators as replacements for the saws. They realized that the logging industry was not viable anymore and that generators were starting to serve the same purpose. The company started making generators in the early 1940's. Tesca Works then opted to produce commercial appliances. It was an easy decision to make since the commercial appliances would use common parts with the company's generators and the customers were local hospitals, schools, and governments. Starting in the 1950's the commercial appliances business accounted for about 50% of Tesca Works' revenues. The refrigerator Mr. Burton arranged a meeting with the firm's top management and the chief design and the chief manufacturing engineers to propose a new product. Mr. Burton presented an argument that more individuals in the United State and Canada would be willing to purchase newer appliances because people are becoming more environmentally conscious. The new appliances are more efficient and environmentally friendly. Also, 2 the recent increase in electricity costs seems to be long lasting. This is an opportunity to get people hooked on environmentally friendly appliances as he put it. The proposal under consideration is for the introduction of a new, energy star refrigerator. To distinguish Tesca Works from other manufacturers, the proposal included details about the convenience, large shelves in the doors, high volume water and ice dispensers, efficiency, and quietness of operation that need to be developed. Mr. Phillips and Mr. Lopez, the two engineers, enthusiastically and quickly pointed out that the needed technology could be based on the company's generators. The framework currently used for building the generators can be modified to work for appliances at a low cost. The marketing vice president, Mr. Chen, pointed out that the marketing analysis could be done quickly and at a reasonable cost. At this point, Mr. Burton charged the participants in the meeting to produce a financial plan for the development and production of the refrigerator. Consumer Appliances Most people purchase appliances and keep them for a very long time or until they stop working. Some get them when they purchase a home and do not think about them. Recently, most power companies started educating people about the efficiency of new appliances and began offering rebates on the most efficient consumer models. These 3 approaches increased public interest. This renewed the public's interest in low powerconsuming appliances. The decision Three weeks later, the vice presidents presented the sales and cost forecasts shown in the exhibits. The information presented contains the cost of production, financing information, and warranty cost estimates. In addition, there were two options for the compressor in the refrigerators. The MC - 004 is more expensive to install, but has a lower warranty cost. The TS - L12 is cheaper to install, but has a higher warranty cost. Which compressor should be used? The analysis Mr. Burton noticed that there is an abundance of enthusiasm about entering the refrigerator building business, but his cautious nature made him seek a more neutral analyst. This is your responsibility. You have been hired by Tesca Works to analyze the proposal to build the refrigerator and provide recommendations to Mr. Burton. The issues that need to be addressed in your report are the following: 1) How much importance should be given to the energy cost situation? 2) What is the project's cost of equity? What is the appropriate discount factor to use for evaluating the refrigerator project? 3) Which of the two compressors should be used in the refrigerator if you decide to go ahead with the project and why? 4 4) Forecast the project's cash flows for the next twenty years. What assumptions did you use? 5) Use the appropriate capital budgeting techniques to evaluate the project. 6) Use the average demand scenario to evaluate the sensitivity of the project's NPV with respect to sale price of the refrigerator and the cost of the compressor. 7) Based on the scenario and sensitivity analysis you performed above, comment on the overall riskiness of the project. 8) Would you recommend that Tesca Works accept or reject the project? What is the basis for your recommendation? 5 Exhibit 1 Sales forecasts: The forecasts are based on projected levels of demand. The firm could face weak, average, and strong demand. All the numbers are expressed in today's dollars. The forecasted average inflation per year is 3.5%. Demand level Weak Average Strong Probability 25% 45% 30% Price per refrigerator $1,375 $1,575 $1,600 Units sold per year 40,000 42,500 43,000 Labor cost per refrigerator $250 Parts $300 Selling General & Administrative $10,000,000 Average warranty cost per year per refrigerator for the first five years is $75. The present value of this cost will be used as a cost figure for each refrigerator. Afterwards, the refrigerator owners will become responsible the repairs. The refrigerators can be produced for twenty years. Afterwards, the designs become obsolete. Exhibit 2 Compressor costs: Compressor choices: Compressor model number CM - 004 TS - L12 Price per compressor and installation $280 $260 Average annual warranty cost per year for five $40 $50 years. Afterwards, the refrigerator owner will become responsible the repairs*. The chosen compressor will be installed in every refrigerator and will become a cost figure for each unit produced. * The compressor manufacturers are not providing Tesca Works with any warranty. However, Tesca Works will provide warranty to its customers. After the initial five years, the refrigerator owners may purchase extended warranty from any insurance company that offers such packages. 6 Exhibit 3 Investment needs: To implement the project, the firm has to invest funds as shown in the following table: Year 0 $3 million Year 1 $5 million Year 2 $3 million Year 3 Production and selling of commercial appliances starts Straight line depreciation will be used. To facilitate the operation of manufacturing the refrigerators, the company will have to allocate funds to net working capital (NWC) equivalent to 12% of annual sales. The investment in NWC will be recovered at the end of the project. Exhibit 4 Financing The following assumptions are used to determine the cost of capital. Historically, the company tried to maintain a debt to equity ratio equal to 0.40. This ratio was used because lowering the debt implies giving up the debt tax shield and increasing it makes debt service a burden on the firm's cash flow. In addition, increasing the debt level may cause a reduced rating of the company's bonds. The marginal tax rate is 40%. All the numbers are expressed in today's dollars. The forecasted average inflation per year is 3.5%. Cost of debt: The company's bond rating is roughly at the high end of the A range. Surveying the debt market yielded the following information about the cost of debt for different rating levels: Bond rating AA A BBB Interest cost range 5.5% ~ 6.5% 6.25% ~ 7.5% 7.5% ~ 9% The company's current bonds have a rating of A. Cost of equity: The current 10-year Treasury notes have a yield to maturity of 6% and the forecast for the S&P 500 market premium is 6.5%. The company's overall is 1.3. analysis: Company Tesca Electrics General Universal Generators International Works Plus Generators Power Inc. Generators 1.3 1.4 1.3 1.6 1.2 1.35 Over all Debt to 0.4 0.3 0.5 0.45 0.35 0.25 equity Percentage of 50 45 90 95 85 85 income from generators 7