how to use these tables make a spreadsheet?

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 controller of the electric motor in the conversion kits.The CTX - 13 is more expensive to install, but has a lower warranty cost.The MT - 78 is cheaper to install, but has a higher warranty cost.Which controller should be used?

The Analysis

Mr. Livingston noticed that there is an abundance of enthusiasm about entering the electric car conversion kit building business, but his cautious nature made him seek a more neutral analyst.This is your responsibility.You have been hired by Electrics to analyze the proposal to build the electric motor and provide recommendations to Mr. Livingston.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?
3. What is the appropriate discount factor to use for evaluating the electric motor project?
4. Which of the two controllers should be used in the conversion kit if you decide to go ahead with the project and why?
5. Forecast the project's cash flows for the next eight years.What assumptions did you use? Use MACRS depreciation for this case.
6. Use the appropriate capital budgeting techniques to evaluate the project.
7. Use the average demand scenario to evaluate the sensitivity of the project's NPV with respect to sale price of the electric motor and the cost of the controller.
8. Based on the scenario and sensitivity analysis you performed above, comment on the overall riskiness of the project.
9. Would you recommend that Electrics accept or reject the project?What is the basis for your recommendation?

Exhibit 3 Investment needs: To implement the project, the firm has to invest funds as shown in the following table: Year 0 Year 1 $17 million Production and selling of commercial appliances starts MACRS depreciation will be used. To facilitate the operation of manufacturing the electric motors, the company will have to allocate funds to net working capital (NWC) equivalent to 10% 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.50. 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 35%. All the numbers are expressed in today's dollars. The forecasted average inflation per year is 3.0%. Cost of debt: The company's bond rating is roughly at the high end of the A range. Surveying the debt market vielded the following information about the cost of debt for different rating levels: Bond rating AA A BBB Interest cost range 4.5% - 5.5% 5.25% - 6.5% 6.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 3% and the forecast for the S&P 500 market premium is 6.5%. The company's overall B is 1.35. B analysis: The following is information about companies that manufacture generators. The team was not able to find many companies that only manufacture AC motors. Company Electrics Gen, General Universal Generators International Inc Generators Power Inc Motors Over all B 1.35 1.4 1.5 1.6 1.3 1.45 Debt to 0.5 0.3 0.5 0.45 0.35 0.25 equity Percentage of | 50 45 90 95 85 90 income from generatorsExhibit 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.0%. Demand level Weak Average Strong Probability 25% 45% 30% Price per electric motor $9.100 $9.200 $9.250 Units sold per year 40.000 40.500 40.750 Labor cost per electric motor $4,250 Parts $2,500 Selling General & Administrative $9,500,000 Average warranty cost per year per electric motor for the first five years is $75. The present value of this cost will be used as a cost figure for each electric motor. Afterwards, the electric motor owners will become responsible the repairs. The electric motors can be produced for eight years. Afterwards, the designs become obsolete. Exhibit 2 Controller costs: Controller choices: Controller model number CTX - 13 MT - 78 Price per controller and installation $1280 $1260 Average annual warranty cost per year for five $90 $100 years. Afterwards, the electric motor owner will become responsible the repairs* The chosen controller will be installed in every electric motor and will become a cost figure for each unit produced. * The controller manufacturers are not providing Electrics with any warranty. However, Electrics will provide warranty to its customers. After the initial five years, the electric motor owners may purchase extended warranty from any insurance company that offers such packages