Screenshots below is from the Lastinger Implement Company Case. PLEASE PUT THE ANSWER IN MS EXCEL FOR QUESITONS 1-5. YOU MAY PUT IT IN DIFFERENT SHEETS.

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 firm 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 1554, 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 yel 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 ensible, 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 indlustriulization in the Russell Street section of the city. Also, there was no vacant Land adjacent to the plarit, 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 Lastingur 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 Taised 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 Mature 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.1200 units per month, kitsanal equipment with a tentative cost of $1 million would be required. Elever, the orchard equipment division has an automated milling muxhine that is carried on the books at 550,000 and which should at last 10 more years, but which is no longer required for their operations. It is unused and is exccupying space in the main plant that could be put to better use. A buyer has been found wis 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 dit removal from its present location would cost $1,000, crating S100, 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 si million, to a total of 540 million. It would cut $5,000 to reme 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 cultivater 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 ultice Sprace requirements are for at least 9,000 squale fect. Seit's stall has located several buildings that will meet their's requirements. They are recommend ing a new building in the Suburban Industrial Park that has 100,000 square fect 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 und 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 with 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 fucility. 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 Leonumie atualysis and to present the results all the next lixecutive Commi. 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, the cost of moving equipment except for the automated milling machine, whese moving cust must be analyzed sera 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 pred $40 million (gross), less an adjustment for applicable tax credits which you must calculate, plus an additional amint for revenue force in moving the milling machinery rather than selling it. You must determine a "nel cash outlay, or initial investment, for use in a discounted cash flow (DCF) analysis. (a) What investment value for the oid automated milling machine should be included in the total net cash outlay? (b) What total net cash outlay should be used in the DCP analysis? 2. Calculate the incremental after tax cash flows to the form 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 in the Russell ant), should it enter the analysis? (Hints: Calculate the net cash flows that would exist during caclaycar under the expund" and "don't expand" alternatives. The increment cash flow, or ICF, for each year is the difference in these we castillows. Be sure to take account of working cupital royuirements. Also, in this analysis, assume that the cultivator operation is terminated at the end of ten years, 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 Russull 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 un Assuming that this is a feasible alternative, do you come to the same conclusion that you did in Question 37 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 archard 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 situatan 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 firm 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 1554, 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 yel 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 ensible, 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 indlustriulization in the Russell Street section of the city. Also, there was no vacant Land adjacent to the plarit, 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 Lastingur 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 Taised 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 Mature 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.1200 units per month, kitsanal equipment with a tentative cost of $1 million would be required. Elever, the orchard equipment division has an automated milling muxhine that is carried on the books at 550,000 and which should at last 10 more years, but which is no longer required for their operations. It is unused and is exccupying space in the main plant that could be put to better use. A buyer has been found wis 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 dit removal from its present location would cost $1,000, crating S100, 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 si million, to a total of 540 million. It would cut $5,000 to reme 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 cultivater 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 ultice Sprace requirements are for at least 9,000 squale fect. Seit's stall has located several buildings that will meet their's requirements. They are recommend ing a new building in the Suburban Industrial Park that has 100,000 square fect 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 und 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 with 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 fucility. 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 Leonumie atualysis and to present the results all the next lixecutive Commi. 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, the cost of moving equipment except for the automated milling machine, whese moving cust must be analyzed sera 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 pred $40 million (gross), less an adjustment for applicable tax credits which you must calculate, plus an additional amint for revenue force in moving the milling machinery rather than selling it. You must determine a "nel cash outlay, or initial investment, for use in a discounted cash flow (DCF) analysis. (a) What investment value for the oid automated milling machine should be included in the total net cash outlay? (b) What total net cash outlay should be used in the DCP analysis? 2. Calculate the incremental after tax cash flows to the form 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 in the Russell ant), should it enter the analysis? (Hints: Calculate the net cash flows that would exist during caclaycar under the expund" and "don't expand" alternatives. The increment cash flow, or ICF, for each year is the difference in these we castillows. Be sure to take account of working cupital royuirements. Also, in this analysis, assume that the cultivator operation is terminated at the end of ten years, 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 Russull 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 un Assuming that this is a feasible alternative, do you come to the same conclusion that you did in Question 37 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 archard 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 situatan in your present analysis (no calculations are required)