I need some help filling out the excel and questions

ATLAS METALS QUESTIONS 1. Explain why Jim Brakeman's method of monitoring project managers' ROIs may have contributed to an increase in the company's ROI. Then explain why this procedure may not maximize the company's value. Also explain the benefits and detriments of using simple payback to allocate funds. 2. Explain the appropriate use of shortterm debt (such as notes payable) and of spontaneously generated capital (such as accruals and accounts payable) for determining the company's capital structure. 3. Linda believes that Atlas Metals's present market capital structure is optimal, that is, it minimizes the firm's weighted average cost of capital and maximizes the stock price. Calculate Atlas Metals's market value capital structure. (Round your numbers to the nearest whole percentage.) 4. Determine the break points in the marginal cost of capital curve. Assume that the current capital structure is maintained, the dividend payout ratio remains at 37 percent, depreciation charges are $8,249,000, and 1998 earnings of $ 29,587,000 are available to common shareholders in 1999. 5. Find the weighted average cost of capital (WACC) for each interval of the marginal cost of capital curve (MCC) and graph the curve. Recall that Atlas Metals's marginal tax rate is 40 percent, and the equity return is based in part on the current price of the stock, the expected dividend (D1) from 1998 EPS, and the 30 percent payout ratio. 6. Consider the cost and benefits of the rapid prototyping (RP) device in answering the following questions. a. Determine the internal rate of return for projects A and B. b. Graph the investment opportunity schedule and the marginal cost of capital schedule. c. Based on this information, what is the company's marginal cost of capital, and which project should be accepted? 7. Linda believes the modified internal rate of return (MIRR) is superior to the traditional IRR as an evaluation criterion for capital investment projects. a. Compute the MIRR for projects A and B. b. Explain the shift in the IOS when using the MIRR rather than the \"traditional\" IRR, and then discuss the impact of the change on the accept/reject decision. (Hint: Answer this part of this question conceptually; no calculations are necessary.) 8. Discuss the choice between the mutually exclusive projects of A and B that will maximize the value of the company. 9. Capital budgeting decisions reflect the best estimate of projects at the time the decisions are made. However, activities such as issuing securities and investing in capital assets will occur over the entire year. a. How would the IOS change if more \"good\" projects (projects with higher returns) become available, and how should the company respond? b. How does the MCC change if the cost of capital increases or decreases, and how should the company respond? 10. Discuss how the analysis would change if the company's board of directors decided not to accept the plastic plant in Florida because of the increased risk of dealing with a new production process. 11. Discuss how the analysis would change if a 2 percent risk premium is included for all three expansion projects. 12. Compute the cost of internal equity based on the Security Market Line. Compare this to the cost of equity found using the discounted cash flow model. Which, if either, do you think is more correct? Explain. Atlas Metals Company 87 ESTABLISHING THE OPTIMAL CAPITAL BUDGET Directed Atlas Metals Company began operations in the 1940s as a carbon steel distributor of bars, structurals, plate, and tubing. In the 1970s, the company added stainless and aluminum plate, flatrolled, and tubing products to better meet the needs of its customers. Currently, 70 percent of the company's revenues are generated by steel, with the remaining 30 percent generated from stainless and aluminum. The company primarily serves construction and fabrication companies in the northwest United States with plants in Seattle, Washington, and Portland, Oregon. Atlas Metals has a strong reputation for offering high-quality products and excellent service to its customers. It sends drivers from its plants as early as 5 AM in order to provide materials to its regular customers for the start of the workday. In addition, it services select \"will-call\" customers in the vicinity of the plants. The company maintains its customer base through competitive pricing and attention to detail, and many customers have been with the company since its early years of operation. They appreciate the quality of service and the personal attention given to their orders. In addition, new customers are drawn to the company's ability to offer a wide range of products and services. Boyd Gerrard started Atlas Metals as a small steel manufacturing company in Seattle to service the shipping and construction industries. After successfully developing the company into a major local supplier, the company stabilized. Mr. Gerrard was interested in taking the company to the next level but did not believe he possessed the expertise to do this. In 1972 Mr. Gerrard met Scott Thompson through industry associates and was impressed with the younger man's enthusiasm and sense of vision. Mr. Gerrard discussed his interest in further developing the company and offered Scott a partnership. Scott accepted the offer and began his work by developing a computerized system to manage scheduling, shipments, production, and inventory control, including ordering raw materials. The systems allowed the company to consider diversification into new product lines. Scott recognized regional demand for stainless steel and aluminum and decided to add these goods to the company's production mix. He negotiated long-term contracts to provide these products to industrial customers all along the West Coast. With expanded inventory and a larger service region, the company grew rapidly. Even with phenomenal growth, Atlas Metals remained privately held until the early 1980s. At this time, Scott and Mr. Gerrard sold 75 percent of the outstanding stock in a public offering to raise funds for expansion into the San Francisco market. At the time of the public offering, Scott had taken over the majority of the company's management responsibilities, and the board of directors unanimously elected him Chief Executive Officer. Mr. Gerrard, who maintained involvement in Copyright 1999. The Dryden Press. All rights reserved. Copyright 1999 South-Western. All rights reserved. Case: 68 Atlas Metals Company the company's strategic decisions, held the office of chair of the board. Increasing competition and the need to provide products with value-added rather than volume potential made Scott realize the company had to strengthen its financial position. He felt that the company needed to diversify, and he was interested in investigating the feasibility of expanding to other geographic areas with high demand and more limited competition. With several strategic decisions facing the company, Scott felt it was time to concentrate on Atlas's financial management. Therefore, he hired Linda Lyrn as a special assistant to the CEO to analyze and strengthen the company's financial position. She had a finance degree and experience in the financial operations of a major building supply company. She was responsible for evaluating the company's finance-related alternatives and recommending appropriate corporate action. As she began to familiarize herself with Atlas's departments and operations, Linda became aware of the haphazard way in which capital investment decisions were made. Jim Brakeman, the company's financial vice president for twenty-five years, was responsible for capital budgeting at Atlas Metals. He approved large capital investment requests presented by local project managers, but the process was unclear. He had recently started calculating ROIs for existing projects and notifying managers if their operations' ROIs were below the company average. He reported that this procedure resulted in a jump in the company's return. Linda noticed that with this procedure, managers of operations with low returns typically avoided requesting money for substantial capital investment. Thus, the company typically allocated funds to operations with above-average ROIs. In addition, when capital funds were severely limited, Mr. Brakeman had the finance department calculate the payback period. Several potential high return capital investments whose payback periods exceeded the three-year standard had been rejected in favor of lower return projects with faster payback. Linda sent a memorandum to Scott Thompson in which she recommended that a formal process be used to make capital budgeting decisions. The memo prompted a meeting about her ideas in which she explained why a net present value approach would be best for the company. Scott felt that this approach would move the company in the right direction and was eager to implement the procedure. Therefore, he asked Linda to address the board of directors' meeting scheduled for the following month. Linda's presentation to the board went smoothly, and the members seemed to agree that the company should implement a formal capital budgeting process. Brakeman, however, was skeptical about Linda's plan, noting that his current system had improved returns. He also argued that capital allocation through the net present value approach might change depending on the rate of return used to discount cash flows. He questioned her about the appropriate cost of capital for evaluating the company's projects. Other board members asked Linda to broaden her study and include an estimate of the appropriate discount rate for generating Atlas Metals's 1999 capital budget. Also, Chair Boyd Gerrard liked to think in terms of rates of return and was uncomfortable with the concept of a net present value dollar amount as the basis for project evaluation. The board also was interested in the growing service center market. Service centers are in the position to serve market segments more effectively than production mills because they can provide reduced cycle time and more reliable delivery. In addition, Mr. Gerrard was interested in acquiring an existing plastic manufacturing facility in Florida. Although manufacturing processes for plastic differed from metal, the Florida plant's major customers were construction companies. Mr. Gerrard believed plastic could add diversification, and Atlas's current construction customers might form a ready clientele. A consultant was hired to investigate the costs and benefits of building service centers in Los Angeles and Texas as well as purchasing the Florida plant. The results of the investigation are presented in Table 1. The report stated that the cost of service facilities was based on the minimum acceptable levels of expansion. Therefore, none of the projects was divisible, and each project had to be accepted or rejected in its entirety. Scott also expressed an interest in acquiring a new rapid prototyping (RP) device to help build production grade tooling from steel and other hard metals. This system would replace the traditional production tooling used by the company in which computer numerical control (CNC) Copyright 1999 South-Western. All rights reserved. Case: 68 Atlas Metals Company TABLE 1 Expansion Opportunities (Millions of Dollars) PROJECT GROUP Los Angeles TOTAL COST $32 AVERAGE IRR 13.5% Texas $47 12.5% Florida $65 15.7% machining and electrical discharge machines were used by skilled tool and mold makers. The new system would reduce costs and increase turnaround times for manufacturing. He found two systems that would be appropriate, each costing $3 million. System A would be faster and be expected to produce annual net cash flows of $675,000 over its estimated ten-year life. System B would be expected to produce annual cash flows of only $580,000, but it would have an expected life of fifteen years. The systems would be mutually exclusive. The board members agreed that either system would give the company a major competitive advantage. The board asked Linda to determine the acceptability of the company's potential projects and to estimate the level of funding that would be required for capital projects during 1999. She also was asked to analyze the company's cost of capital and to determine an appropriate rate for use in project evaluation. Finally, she was charged with making a full report, including support for her recommendations, at the next quarterly board meeting. Linda decided that the best way to present her findings would be to calculate the company's investment opportunity schedule (IOS) and the firm's marginal cost of capital (MCC) schedule. She also felt that she had tot be prepared to field questions relating to risk and how to deal with the possibility of new projects that might surface during the year. Her first step was to find out what other projects were under consideration, so she interviewed each project manager. From these conversations she discovered that she needed to consider a large number of relatively routine projects (replacement decisions and the like) in addition to the expansions presented in Table 1 and the RP device. These routine projects could be grouped into three broad categories based on expected profitability-high, average, or low. The risks of these projects were average for the company. The total costs and expected average internal rates of return (IRR) for each group are shown in Table 2. She then obtained the projected December 1998 balance sheet (Table 3) and information on actual and estimated sales and earnings for each year from 1987 to 1998 (Table 4). Next, she met with several security analysts and investment bankers to determine investor expectations for the firm TABLE 2 Investment Project Groups (Millions of Dollars) PROJECT GROUP Group 1 (high profitability) TOTAL COST $40 AVERAGE IRR 17.3% Group 2 (medium profitability) $50 14.0% Group 3 (low profitability) $20 11.5% Copyright 1999 South-Western. All rights reserved. Case: 68 Atlas Metals Company TABLE 3 Atlas Metals Company Pro Forma Balance sheet for December 31, 1998 (Thousands of Dollars) Cash & marketable securities $ Accounts Receivable Accounts payable $ 45,243 $ 96,084 Short-term debt (6%)a $ 29,010 Inventories $134,841 Accrued expenses $ 13,658 Other current assets $ 25,279 Total current liabilities $ 87,911 $258,138 Long term debtb $121,326 Preferred stockc $ 36,010 Common equityd $211,140 Retained earnings $ 29,587 Total liabilities and shareholders equity $485,974 Total current assets Net fixed assets Total assets 1,934 $227,836 $485,974 a Atlas Metals uses short-term debt as part of its permanent capital structure. bonds have a par value of $1,000, a remaining life of ten years, and an annual (one payment per year) coupon rate of 8.0 percent. The current annual required rate of return for ten-year bonds with Atlas's bond rating is 9.0 percent. c The preferred stock currently sells at its par value of $100 per share. d Eight and one half million shares are outstanding, and the stock currently sells at a price of $24.84 per share. b Outstanding and the cost of raising outside debt and equity capital. The analysts said that investors expected the company's growth rate to increase over that of the previous decade due to expanding international demand. The experts' consensus was that the rate would be 125 percent of that experienced from 1988 to 1998. Linda Lyrn also analyzed the firm's market value capital structure and noted that the capital structure, as reflected in the balance sheet, had been stable over the past decade. She concluded that Atlas's current mix of debt, preferred stock, and common stock was close to optimal. She also noted that the company used short-term debt as a source of permanent financing. The company's accounting department estimated depreciation for 1998 to be $8,249,000. Since depreciation is not a cash charge, this amount would be available to finance new projects. The company has a 40 percent marginal tax rate. To obtain more information Linda held discussions with the firm's investment and commercial bankers. From these meetings she learned that the company would incur the following costs in obtaining additional capital. SHORT-TERM DEBT New short-term debt, which currently has an interest rate of 7.0 percent, can be issued as bank notes payable and commercial paper. Copyright 1999 South-Western. All rights reserved. Case: 68 Atlas Metals Company TABLE 4 Atlas Metals Company Sales and Earnings EARNINGS AFTER TAX AVAILABLE TO YEAR 1998 (est.) SALES ($ THOUSANDS) $705,328 COMMON STOCK EARNINGS PER SHARE ($ THOUSANDS) $49,470 OF COMMON STOCK $5.82 1997 $654,984 $28,140 $3.31 1996 $583,861 $26,250 $3.09 1995 $479,894 $21,375 $2.51 1994 $453,218 $22,206 $2.78 1993 $-286,286 ($10,545) ($1.32) 1992 $369,544 $13,778 $1.72 1991 $-211,789 ($9,310) ($1.16) 1990 $398,223 $25,606 $3.66 1989 $354,444 $15,743 $2.25 1988 $308,522 $22,260 $3.18 1987 $259,951 $21,910 $3.13 LONG-TERM DEBT The company can obtain additional $41 million long-term loans under current covenant restrictions. Analysts believe that the company can issue senior debt at 9.5 percent. If the company needs debt beyond this amount it must issue subordinated debentures with a lower rating. These lower rated bonds would carry an interest rate of 11.8 percent. PREFERRED STOCK The company's nonvoting perpetual preferred stock pays a $12.69 annual dividend, has a $100 par value, and is currently selling at par. The company can sell additional preferred stock to private investors. However, the market for new issues is weak, and the flotation costs on new issues are expected to be $6 per share. COMMON STOCK Atlas Metals's investment bankers estimate that they can sell new common stock at the current market price of $24.84 per share. The underwriting commission would be $4.50 per share. Because of difficulties in determining the exact effect of capital requirements on the costs of capital, Linda assumed that Atlas could sell any amount of new common stock at the net price. Copyright 1999 South-Western. All rights reserved. Case: 68 Atlas Metals Company Finally, the analysts noted that investors expect Atlas's 30 percent dividend payout ratio to hold steady for some time into the future. Indeed, the last annual report to shareholders emphasized that the company would continue the current dividend payout policy. Linda was told to investigate the cost of equity based on current stock price, expected 1998 dividends, and growth from historical earnings and the analyst's expectations. However, she was uncomfortable with this growth estimate and wanted to see if costs were sensitive to estimation method. She found that Treasury Bonds were selling at 5 percent and the expected market risk premium, based on the historical level of the Standard & Poor's over Treasuries, was 6.2 percent. Further, the company was somewhat more risky than the market with a ValueLine listed beta of 1.35. Conversations with several board members indicated a lack of consensus about the riskiness of the expansion projects in Los Angeles, Texas, and Florida. Some board members felt that the projects had the same risk as existing projects, while others felt that they were more risky. After reviewing the consultant's report, Linda concluded that projects should be evaluated both as having average risk as well as with a 2 percent risk premium added to the company cost of capital. Due to board disagreement, she felt that she must present both high and normal risk scenarios. Your task is to help Linda prepare a report that addresses the concerns mentioned above. In addition you must make and explain recommendations concerning the funding of projects for the coming year. Linda has asked that you answer the following questions and use this information to help you structure your report. QUESTIONS 1. Explain why Jim Brakeman's method of monitoring project managers' ROIs may have contributed to an increase in the company's ROI. Then explain why this procedure may not maximize the company's value. Also explain the benefits and detriments of using simple payback to allocate funds. 2. Explain the appropriate use of short-term debt (such as notes payable) and of spontaneously generated capital (such as accruals and accounts payable) for determining the company's capital structure. 3. Linda believes that Atlas Metals's present market capital structure is optimal, that is, it minimizes the firm's weighted average cost of capital and maximizes the stock price. Calculate Atlas Metals's market value capital structure. (Round your numbers to the nearest whole percentage.) 4. Determine the break points in the marginal cost of capital curve. Assume that the current capital structure is maintained, the dividend payout ratio remains at 37 percent, depreciation charges are $8,249,000, and 1998 earnings of $ 29,587,000 are available to common shareholders in 1999. 5. Find the weighted average cost of capital (WACC) for each interval of the marginal cost of capital curve (MCC) and graph the curve. Recall that Atlas Metals's marginal tax rate is 40 percent, and the equity return is based in part on the current price of the stock, the expected dividend (D1) from 1998 EPS, and the 30 percent payout ratio. 6. Consider the cost and benefits of the rapid prototyping (RP) device in answering the following questions. a. Determine the internal rate of return for projects A and B. Copyright 1999 South-Western. All rights reserved. Case: 68 Atlas Metals Company b. Graph the investment opportunity schedule and the marginal cost of capital schedule. c. Based on this information, what is the company's marginal cost of capital, and which project should be accepted? 7. Linda believes the modified internal rate of return (MIRR) is superior to the traditional IRR as an evaluation criterion for capital investment projects. a. Compute the MIRR for projects A and B. b. Explain the shift in the IOS when using the MIRR rather than the \"traditional\" IRR, and then discuss the impact of the change on the accept/reject decision. (Hint: Answer this part of this question conceptually; no calculations are necessary.) 8. Discuss the choice between the mutually exclusive projects of A and B that will maximize the value of the company. 9. Capital budgeting decisions reflect the best estimate of projects at the time the decisions are made. However, activities such as issuing securities and investing in capital assets will occur over the entire year. a. How would the IOS change if more \"good\" projects (projects with higher returns) become available, and how should the company respond? b. How does the MCC change if the cost of capital increases or decreases, and how should the company respond? 10. Discuss how the analysis would change if the company's board of directors decided not to accept the plastic plant in Florida because of the increased risk of dealing with a new production process. 11. Discuss how the analysis would change if a 2 percent risk premium is included for all three expansion projects. 12. Compute the cost of internal equity based on the Security Market Line. Compare this to the cost of equity found using the discounted cash flow model. Which, if either, do you think is more correct? Explain. Copyright 1999 South-Western. All rights reserved. Student's Model 05/02/16 ATLAS METALS COMPANY Establishing the Optimal Capital Budget This case illustrates basic cost of capital calculations, the construction of the marginal cost of capital and investment opportunity schedules, and the development of the optimal capital budget. The model develops the firm's market value capital structure, component costs, MCC schedule assuming two break points and available depreciation generated funds. The model could easily be expanded to include more break points in the MCC schedule, but most firms do not precisely define their MCC break schedules past the retained earnings break point. If you are using the student version of the model, some of the cells have been blanked out. All of the formulas and inputs have been blanked out but not labels. Before using the model, it is necessary to fill in the empty cells with the appropriate formulas. Once this is done, the model is ready for use. ======= =============== =========== ======= ============== =========== INPUT DATA: KEY OUTPUT: Short-Term Debt: Total $ amount Marginal cost rate Long-Term Debt: Total $ amount Par value Years to maturity Coupon rate Current req return Add. sr. debt Marginal cost rate: Senior debt Junior debt Market Value Capital Structure: % short-term debt % long-term debt % preferred stock % common stock 0% 0% 0% 0% Break Points: RE BP RE+Dep BP $0 $0 Debt BP Debt+Dept BP $0 $0 Component costs: Short-term debt 0.00% Long-term debt: First interval Second interval 0.00% 0.00% Preferred stock 0.00% Preferred Stock: Total $ amount Par value Dollar dividend Flotation costs Common Stock: Common stock: Common stock Retained earnings Shares outstanding Current price New issue net price Retained earnings New common stock 0.00% 0.00% WACCs: First interval Second interval Third interval Other Data: Div. payout ratio Dep. expense Tax rate 1998 Earnings available to com. stockholders 1998 EPS (est) 1988 EPS growth (% previous) 0.00% 0.00% 0.00% ======= =============== =========== ======= ============== =========== MODEL-GENERATED DATA: Market Value Capital Structure: # bonds outstanding Type of Financing Market Value ------------------------- ------------------Short-term debt Long term debt ------------------Total debt % ---- ---- Preferred stock Common equity ------------------- ---- =========== ==== Total financing Retained Earnings Break Point: RE BP = RE BP inc. dep. Debt BP = Debt BP inc, dep. Dividend Data: Div. growth rate End-of-year div. Component Costs: Short-term debt Long-term debt: First interval Second interval Preferred stock Retained earnings New common stock WACCs: First Interval: ST debt, senior LT debt, retained earnings & preferred stock Component Fraction Cost Short-term Long-term Preferred stock Retained earn Product ----WACC = Ka = Second Interval: ST debt, Senior LT debt, external equity and preferred stock Component Fraction Cost Short-term Long-term Preferred stock New CS Product ----WACC = Ka = Third Interval: ST debt, junior LT debt, external equity and preferred stock Component Fraction Cost Short-term Long-term Preferred stock New CS Product ----WACC = Ka = ======= =============== =========== ======= ============== =========== END