how to use these tables make a spreadsheet?
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
