The screenshots is from the Lastinger Implement Company Case. Please show the answers in MS EXCEL. YOU MAY USE MULTIPLE SHEETS, if necessary.

Lastinger Implement Company "It doesn't make a bit of sense!" fumed Dan Boyd, director of marketing. "Bob (Robert Lastinger, president of Lastinger Implement Company) says that he wants to increase profits in all product lines, and he shifted more of the salary of the outside sales force to a commission basis, but your people can't even produce enough to keep up with the orders we're bringing in now!" As they walked back to their office after the executive committee meeting, Boyd and Ralph Speir, vice- president for operations, discussed the controversy that had consurned most of the two-hour session. "Don't blame me for not being able to expand production," Speir said. "I told Bob last year that the city government was not about to let us expand the Russell plant" (the factory on Russell Street where the firm's line of garden cultivators is produced). "But no, he wanted to try to enlarge the plant rather than move the operation out of town! Now we're in a bind, and he's putting the pressure on us to bail him out. It's a good thing my staff has been working on a contingency plan in case the Russell plant expansion fell through." By the time he had gotten back to his office, Speir had decided to ask the treasurer, Beverly Hoover, to help him prepare a formal proposal for presentation to the Executive Committee at next week's meeting. He hoped that his staff had been able to get all the necessary information, because Boyd correctly noted that action had to be taken as soon as possible to prevent the competition from eroding Lastinger's share of the garden cultivator market. Lastinger Implement Company was started in 1884 by Robert Lastinger's grandfather, Adolphe Lastinger, to produce a line of specialized agricultural equipment for fruit orchardists. By the time Bob's father, Earle Lastinger, assumed the presidency in 1918, the firm had grown significantly and had expanded the product line to include more generalized equipment used on small family farms. Under Earle Lastinger, the firin continued to grow, and a second production facility was added the Russell Street plant) to manufacture a more complex line of mechanical cultivators. When Bob Lastinger became president in 1954, the company's operations were divided between the "main plant" and the smaller "Russell plant." Today, there are three profit centers in the firm: the garden cultivator division (Russell plant), the orchard equipment division (main plant), and the repair and overhaul facility (main plant). Central management offices are spread over both plants, an arrangement that has worked well over the years. The controversy that had occupied most of the meeting of the Executive Committee that day was precipitated by the rapidly growing sales of garden cultivators. These versatile yet relatively inexpensive cultivators are particu- larly well suited for the average suburban gardener who cannot justify spend ing more money for a power rotary tiller. The "organic gardening" phe- nomenon had proved to be a real boon for the company, and sales of the cultivator doubled in less than five years. With three shifts, seven days a week, maximum production is 100,000 units per month. Average sales for 1981 were 99,500 units per month, and orders received in August 1981 through January 1982 averaged 108,000 per month. Clearly, a backlog of orders was being generated, with the amount increasing all the time. Dan Boyd, the marketing manager, believed that demand could easily be increased to 150,000 units per month within a few years if the firm were to engage in a coordinated national advertising campaign. Ralph Speir, the production manager, realized over a year ago that capacity at the Russell plant would be insufficient even in the short run if demand were to grow as forecast. He also recognized that having production in two locations would not be feasible, so he proposed that a larger facility be leased or purchased as soon as possible. Speir did not believe that the city would grant a permit to expand the present facility because the town council was already on record as opposing further industrialization in the Russell Sirect section of the city. Also, there was no vacant land adjacent to the plant, and none of the surrounding buildings were really suitable for a large-scale manufacturing facility. Nevertheless, Bob Lastinger was opposed to moving the operation away from Russell Street except as a last resort, and he directed the production department to prepare expansion plans for submis- sion to the city commission. The expansion permits were denied and the denial had been upheld in court Rather than file a further appeal, Lastinger directed Speir to examine other locations where it would be feasible to move the Russell facility. As Lastinger saw it, there were two options available to the company: 1. Continue operations at the Russell plant and forego the added sales. If this alternative were chosen, perhaps the selling price of the cultivators could be raised to increase the contribution from each unit sold. This would decrease demand to the level at which the company was able to produce. 2. Shut down the Russell plant and move the operation to a new suburban location. If this were done, both the manufacturing operations and the central anagement staff housed in the Russell facility would have to be transferred. With an eye to future expansion of the orchard equipment division; Lastinger directed that if the Russell facility were closed, a building sufficiently large to accommodate the cultivator division, central stall personnel, and the repair and overhaul facility (now in the main plant) be acquired. If the repair and overhaul facility were to be relocated, space would then be available in the main plant to expand orchard equipment production. To increase garden cultivator production to 150,000 units per month additional equipment with a tentative cost of $41 million would be required. However, the orchard equipment division has an automated milling machine that is carried on the books at $50,000 and which should last at least 10 more years, but which is no longer required for their operations. It is unused and is occupying space in the main plant that could be put to better use. A buyer has been found who is willing to pay $110,500 for the machine if it is removed from the plant, crated, and sent to the railroad loading dock. It is estimated that removal from its present location would cost $1,000, crating $-100, and transportation to the depot $100. However, close inspection of the operating specifications of the machine shows that it can be used by the cultivator division, thus reducing the capital outlay required to expand production by $1 million, to a total of $40 million. It would cost $5,000 to remove the machine from the main plant, transport it to the new plant, and reinstall it in the proper place. Current depreciation on this machine is $5,000 per year for the next ten years. It has been estimated that the cultivator division requires about 60,000 square feet of space to achieve a production of 150,000 units per month. The repair and overhaul facility requires about 30,000 square feet, and the office space requirements are for at least 9,000 square feet. Speir's still has located several buildings that will meet the firm's requirements. They are recommend- ing a new building in the Suburban Industrial Park that has 100,000 square feet of usable space and has the further advantage of being located next to the railroad siding-a definite plus for deliveries of raw materials and shipping out finished products. The lease payment for the new building is $2 million per year, an increase over the $750,000 now paid annually for the Russell plant: The engineering and cost accounting departments have been able to supply various direct and allocated costs for both the Russell plant and the proposed new facility. Their estimates appear in Table 1. Since sales are not constant, Table 2 gives estimates of marketing costs and working capital requirements for various levels of sales. With the new facility, Dan Boyd estimates that in 1982, sales will average 112,500 units per month for the entire year; 125,000 for 1983, 137,500 for 1984, and 150,000 per year thereafter. Included in the figure for 1982 is a provision for the approximately 45 days of lost production resulting from the move to the new facility. Ralph Speir gathered together all of this information, then took it to Beverly Hoover, the treasurer. It would be the task of Beverly's department to perform the economic analysis and to present the results at the next lixecutive Commit. Table I Estimates of Direct and Allocated Costs 75 Russell Plant New Facility Production capacity 100,000 units/mo. 150,000 units/mo." Fixed proxiuction costs $12,500,000/ycar $18,200,000/year Variable cost per unit $ 80 $ Selling price per unit $ 115 $ 115 Lease payment per year $ 750,000 $ 2,000,000 General overhead allocated to the cultivator division per year $ 100,000 $ 300,000 Depreciation per year for existing cultivator division assets $5,000,000 $5,000,000 a. Expandable 10 175,000 units by storing raw materials in a warchouse on available land adjacent to the factory. tee meeting. As Speir's discussion with Hoover progressed, a number of differences of opinion arose concerning the treatment of such items as: (1) the inyestment value to be assigned to the milling machine to be transferred from the orchard equipment division if the project is accepted; (2) whether or not the total lease payment for the new facility should be charged to the garden cultivator division, since two other operations are to be housed there; and (3) whether allocated general overhead costs should be included in the economic evaluation. Before these differences could be resolved, Speir was called away to a meeting with Lastinger and the firm's auditors. Hoover knew that Speir wanted to move to the new facility, so if her analysis indicated otherwise, there would be sharp questioning when she presented the results before the Executive Committee. As Hoover's assistant, you must perform the economic analysis, paying particular attention to questions listed below on which she is sure to be questioned closely. Other data relevant to the decision are as follows: the project's life is ten years; straight line depreciation to a zero salvage value is appropriate; the firm Table 2 Estimated Annual Marketing Costs and Working Capital Requirements Sales Level Annual Average Working (units/mo.) Marketing Costs Capital Investment 100,000 $2,450,000 $11,800,000 112,500 $2,650,000 $12,850,000 125,000 $2,840,000 $13,875,000 137,500 $3,055,000 $15,000,000 150,000 $3,240,000 $16,420,000 can take an investment tax credit of 10 percent on all new assets purchased; the firm uses 15 percent as a discount rate for this type of project; and the corporate marginal tax rate (federal plus state) is 55 percent. Also, the first year's lease payment is not due until period 1; the cost of moving equipmentexcept for the automated milling machine, whose moving cost must be analyzed sepa rately--is included in the $40 million investment; and the initial increment of additional working capital is not required until period 1. Questions 1. If the expansion and move) is undertaken. Lastinger must spend $40 million (gross), less an adjustment for applicable tax credits which you must calculate, plus an additional amount for revenue foregone in moving the milling machinery rather than selling it. You must determine a "net cash outlay," or initial investment, for use in a discounted cash flow (DCF) analysis. (a) What investment value for the old automated milling machine should be included in the total net cash outlay? (b) What total net cash outlay should be used in the DCF analysis? 2. Calculate the incremental after-tax cash flows to the firm for the life of the project. In answering this question, be sure to consider whether the full $2 million lease cost should be considered in the analysis, or whether some other amount is more appropriate given that the repair facility will utilize 30 percent of the space. Also, assuming that the increase in general overhead is a direct result of the move to the new facility (that is, the increase will not be incurred if the decision is made to remain at the Russell plant), should it enter the analysis? (Hints: Calculate the net cash flows that would exist during each year under the "expand" and "don't expand" alternatives. The increment cash flow, or ICF, for each year is the difference in these two cash flows. Be sure to take account of working capital reyuirements. Also, in this analysis, assume that the cultivator operation is terminated at the end of ten years. cars. 3. On the basis of your answers to the first two questions, conduct a DCF analysis to determine if the firm should move to the new facility. Explain fully. 4. If the company decides to remain at the Russell plant, it has been suggested that the selling price be increased to $120 per unit with demand reduced accordingly to a level of about 100,000 units per month. Assuming that this is a feasible alternative, do you come to the same conclusion that you did in Question 3? Explain. 5. It was stated that the automated milling machine is no longer required by the orchard equipment division and is occupying needed space. Suppose it was not obsolete but simply represented excess capacity that the orchard equipment division will not need for four years. Thus, if the cultivator division takes the machine (that is, moves to the new facility), a new one will have to be purchased in four years by the orchard equipment division. However, if the cultivator division does not take the equipment and re- mains at the Russell plant, it will be eight years before the orchard equip- ment division has to purchase another machine. Describe how you would have handled this situaiton in your present analysis (no calculations are required). Lastinger Implement Company "It doesn't make a bit of sense!" fumed Dan Boyd, director of marketing. "Bob (Robert Lastinger, president of Lastinger Implement Company) says that he wants to increase profits in all product lines, and he shifted more of the salary of the outside sales force to a commission basis, but your people can't even produce enough to keep up with the orders we're bringing in now!" As they walked back to their office after the executive committee meeting, Boyd and Ralph Speir, vice- president for operations, discussed the controversy that had consurned most of the two-hour session. "Don't blame me for not being able to expand production," Speir said. "I told Bob last year that the city government was not about to let us expand the Russell plant" (the factory on Russell Street where the firm's line of garden cultivators is produced). "But no, he wanted to try to enlarge the plant rather than move the operation out of town! Now we're in a bind, and he's putting the pressure on us to bail him out. It's a good thing my staff has been working on a contingency plan in case the Russell plant expansion fell through." By the time he had gotten back to his office, Speir had decided to ask the treasurer, Beverly Hoover, to help him prepare a formal proposal for presentation to the Executive Committee at next week's meeting. He hoped that his staff had been able to get all the necessary information, because Boyd correctly noted that action had to be taken as soon as possible to prevent the competition from eroding Lastinger's share of the garden cultivator market. Lastinger Implement Company was started in 1884 by Robert Lastinger's grandfather, Adolphe Lastinger, to produce a line of specialized agricultural equipment for fruit orchardists. By the time Bob's father, Earle Lastinger, assumed the presidency in 1918, the firm had grown significantly and had expanded the product line to include more generalized equipment used on small family farms. Under Earle Lastinger, the firin continued to grow, and a second production facility was added the Russell Street plant) to manufacture a more complex line of mechanical cultivators. When Bob Lastinger became president in 1954, the company's operations were divided between the "main plant" and the smaller "Russell plant." Today, there are three profit centers in the firm: the garden cultivator division (Russell plant), the orchard equipment division (main plant), and the repair and overhaul facility (main plant). Central management offices are spread over both plants, an arrangement that has worked well over the years. The controversy that had occupied most of the meeting of the Executive Committee that day was precipitated by the rapidly growing sales of garden cultivators. These versatile yet relatively inexpensive cultivators are particu- larly well suited for the average suburban gardener who cannot justify spend ing more money for a power rotary tiller. The "organic gardening" phe- nomenon had proved to be a real boon for the company, and sales of the cultivator doubled in less than five years. With three shifts, seven days a week, maximum production is 100,000 units per month. Average sales for 1981 were 99,500 units per month, and orders received in August 1981 through January 1982 averaged 108,000 per month. Clearly, a backlog of orders was being generated, with the amount increasing all the time. Dan Boyd, the marketing manager, believed that demand could easily be increased to 150,000 units per month within a few years if the firm were to engage in a coordinated national advertising campaign. Ralph Speir, the production manager, realized over a year ago that capacity at the Russell plant would be insufficient even in the short run if demand were to grow as forecast. He also recognized that having production in two locations would not be feasible, so he proposed that a larger facility be leased or purchased as soon as possible. Speir did not believe that the city would grant a permit to expand the present facility because the town council was already on record as opposing further industrialization in the Russell Sirect section of the city. Also, there was no vacant land adjacent to the plant, and none of the surrounding buildings were really suitable for a large-scale manufacturing facility. Nevertheless, Bob Lastinger was opposed to moving the operation away from Russell Street except as a last resort, and he directed the production department to prepare expansion plans for submis- sion to the city commission. The expansion permits were denied and the denial had been upheld in court Rather than file a further appeal, Lastinger directed Speir to examine other locations where it would be feasible to move the Russell facility. As Lastinger saw it, there were two options available to the company: 1. Continue operations at the Russell plant and forego the added sales. If this alternative were chosen, perhaps the selling price of the cultivators could be raised to increase the contribution from each unit sold. This would decrease demand to the level at which the company was able to produce. 2. Shut down the Russell plant and move the operation to a new suburban location. If this were done, both the manufacturing operations and the central anagement staff housed in the Russell facility would have to be transferred. With an eye to future expansion of the orchard equipment division; Lastinger directed that if the Russell facility were closed, a building sufficiently large to accommodate the cultivator division, central stall personnel, and the repair and overhaul facility (now in the main plant) be acquired. If the repair and overhaul facility were to be relocated, space would then be available in the main plant to expand orchard equipment production. To increase garden cultivator production to 150,000 units per month additional equipment with a tentative cost of $41 million would be required. However, the orchard equipment division has an automated milling machine that is carried on the books at $50,000 and which should last at least 10 more years, but which is no longer required for their operations. It is unused and is occupying space in the main plant that could be put to better use. A buyer has been found who is willing to pay $110,500 for the machine if it is removed from the plant, crated, and sent to the railroad loading dock. It is estimated that removal from its present location would cost $1,000, crating $-100, and transportation to the depot $100. However, close inspection of the operating specifications of the machine shows that it can be used by the cultivator division, thus reducing the capital outlay required to expand production by $1 million, to a total of $40 million. It would cost $5,000 to remove the machine from the main plant, transport it to the new plant, and reinstall it in the proper place. Current depreciation on this machine is $5,000 per year for the next ten years. It has been estimated that the cultivator division requires about 60,000 square feet of space to achieve a production of 150,000 units per month. The repair and overhaul facility requires about 30,000 square feet, and the office space requirements are for at least 9,000 square feet. Speir's still has located several buildings that will meet the firm's requirements. They are recommend- ing a new building in the Suburban Industrial Park that has 100,000 square feet of usable space and has the further advantage of being located next to the railroad siding-a definite plus for deliveries of raw materials and shipping out finished products. The lease payment for the new building is $2 million per year, an increase over the $750,000 now paid annually for the Russell plant: The engineering and cost accounting departments have been able to supply various direct and allocated costs for both the Russell plant and the proposed new facility. Their estimates appear in Table 1. Since sales are not constant, Table 2 gives estimates of marketing costs and working capital requirements for various levels of sales. With the new facility, Dan Boyd estimates that in 1982, sales will average 112,500 units per month for the entire year; 125,000 for 1983, 137,500 for 1984, and 150,000 per year thereafter. Included in the figure for 1982 is a provision for the approximately 45 days of lost production resulting from the move to the new facility. Ralph Speir gathered together all of this information, then took it to Beverly Hoover, the treasurer. It would be the task of Beverly's department to perform the economic analysis and to present the results at the next lixecutive Commit. Table I Estimates of Direct and Allocated Costs 75 Russell Plant New Facility Production capacity 100,000 units/mo. 150,000 units/mo." Fixed proxiuction costs $12,500,000/ycar $18,200,000/year Variable cost per unit $ 80 $ Selling price per unit $ 115 $ 115 Lease payment per year $ 750,000 $ 2,000,000 General overhead allocated to the cultivator division per year $ 100,000 $ 300,000 Depreciation per year for existing cultivator division assets $5,000,000 $5,000,000 a. Expandable 10 175,000 units by storing raw materials in a warchouse on available land adjacent to the factory. tee meeting. As Speir's discussion with Hoover progressed, a number of differences of opinion arose concerning the treatment of such items as: (1) the inyestment value to be assigned to the milling machine to be transferred from the orchard equipment division if the project is accepted; (2) whether or not the total lease payment for the new facility should be charged to the garden cultivator division, since two other operations are to be housed there; and (3) whether allocated general overhead costs should be included in the economic evaluation. Before these differences could be resolved, Speir was called away to a meeting with Lastinger and the firm's auditors. Hoover knew that Speir wanted to move to the new facility, so if her analysis indicated otherwise, there would be sharp questioning when she presented the results before the Executive Committee. As Hoover's assistant, you must perform the economic analysis, paying particular attention to questions listed below on which she is sure to be questioned closely. Other data relevant to the decision are as follows: the project's life is ten years; straight line depreciation to a zero salvage value is appropriate; the firm Table 2 Estimated Annual Marketing Costs and Working Capital Requirements Sales Level Annual Average Working (units/mo.) Marketing Costs Capital Investment 100,000 $2,450,000 $11,800,000 112,500 $2,650,000 $12,850,000 125,000 $2,840,000 $13,875,000 137,500 $3,055,000 $15,000,000 150,000 $3,240,000 $16,420,000 can take an investment tax credit of 10 percent on all new assets purchased; the firm uses 15 percent as a discount rate for this type of project; and the corporate marginal tax rate (federal plus state) is 55 percent. Also, the first year's lease payment is not due until period 1; the cost of moving equipmentexcept for the automated milling machine, whose moving cost must be analyzed sepa rately--is included in the $40 million investment; and the initial increment of additional working capital is not required until period 1. Questions 1. If the expansion and move) is undertaken. Lastinger must spend $40 million (gross), less an adjustment for applicable tax credits which you must calculate, plus an additional amount for revenue foregone in moving the milling machinery rather than selling it. You must determine a "net cash outlay," or initial investment, for use in a discounted cash flow (DCF) analysis. (a) What investment value for the old automated milling machine should be included in the total net cash outlay? (b) What total net cash outlay should be used in the DCF analysis? 2. Calculate the incremental after-tax cash flows to the firm for the life of the project. In answering this question, be sure to consider whether the full $2 million lease cost should be considered in the analysis, or whether some other amount is more appropriate given that the repair facility will utilize 30 percent of the space. Also, assuming that the increase in general overhead is a direct result of the move to the new facility (that is, the increase will not be incurred if the decision is made to remain at the Russell plant), should it enter the analysis? (Hints: Calculate the net cash flows that would exist during each year under the "expand" and "don't expand" alternatives. The increment cash flow, or ICF, for each year is the difference in these two cash flows. Be sure to take account of working capital reyuirements. Also, in this analysis, assume that the cultivator operation is terminated at the end of ten years. cars. 3. On the basis of your answers to the first two questions, conduct a DCF analysis to determine if the firm should move to the new facility. Explain fully. 4. If the company decides to remain at the Russell plant, it has been suggested that the selling price be increased to $120 per unit with demand reduced accordingly to a level of about 100,000 units per month. Assuming that this is a feasible alternative, do you come to the same conclusion that you did in Question 3? Explain. 5. It was stated that the automated milling machine is no longer required by the orchard equipment division and is occupying needed space. Suppose it was not obsolete but simply represented excess capacity that the orchard equipment division will not need for four years. Thus, if the cultivator division takes the machine (that is, moves to the new facility), a new one will have to be purchased in four years by the orchard equipment division. However, if the cultivator division does not take the equipment and re- mains at the Russell plant, it will be eight years before the orchard equip- ment division has to purchase another machine. Describe how you would have handled this situaiton in your present analysis (no calculations are required)