Trix Corporation manufactures seats for automobiles, vans, trucks, and various recreational vehicles. The company has a number of plants around the world, including the Denver Cover Plant, which makes seat covers. Ted Vosilo is the plant manager of the Denver Cover Plant but also serves as the regional production manager for the company. His budget as the regional manager is charged to the Denver Cover Plant. Vosilo has just heard that Trix has received a bid from an outside vendor to supply the equivalent of the entire annual output of the Denver Cover Plant for $35 million. Vosilo was astonished at the low outside bid because the budget for the Denver Cover Plants operating costs for the upcoming year was set at $52 million. If this bid is accepted, the Denver Cover Plant will be closed down. The budget for Denver Covers operating costs for the coming year is presented below. Additional facts regarding the plants operations are as follows: a. Due to Denver Covers commitment to use high-quality fabrics in all of its products, the Purchasing Department was instructed to place blanket purchase orders with major suppliers to ensure the receipt of sufficient materials for the coming year. If these orders are canceled as a consequence of the plant closing, termination charges would amount to 20% of the cost of direct materials. b. Approximately 400 plant employees will lose their jobs if the plant is closed. This includes all of the direct laborers and supervisors as well as the plumbers, electricians, and other skilled workers classified as indirect plant workers. Some would be able to find new jobs while many others would have difficulty. All employees would have difficulty matching Denver Covers base pay of $18.80 per hour, which is the highest in the area. A clause in Denver Covers contract with the union may help some employees; the company must provide employment assistance to its former employees for 12 months after a plant closing. The estimated cost to administer this service would be $1.5 million for the year. c. Some employees would probly choose early retirement because Trix has an excellent pension plan. In fact, $3 million of the annual pension expension plan. In fact, $3 million of the annual pension expense would continue whether Denver Cover is open or not. d. Vosilo and his staff would not be affected by the closing of Denver Cover. They would still be responsible for administering three other area plants. e. If the Denver Cover plant were closed, the company would realize about $3.2 million salvage value for the equipment and building. If the plant remains open, there are no plans to make any significant investments in new equipment or buildings. The old Equipment is adequate and should last indefinitely.

Denver Cover Plant

Annual Budget for Operating Costs

Materials $14,000,000 Labor: Direct $13,100,000 Supervision 900,000 Indirect plant 4,000,000 18,000,000 Overhead: Depreciationequipment 3,200,000 Depreciationbuilding 7,000,000 Pension expense 5,000,000 Plant manager and staff 800,000 Corporate expenses* 4,000,000 20,000,000 Total budgeted costs $52,000,000 *Fixed corporate expenses allocated to plants and other operating units based on total budgeted wage and salary costs.

QUESTIONS:

Criteria 1. Only Answer Each Question With Excel No Writeup

Criteria 2. (Show the FORMULA & NUMBERS in excel that you used to get EACH Answer)

For Example:

1.) Trix Corporation plans to prepare a financial analysis that will be used in deciding whether or not to close the Denver Cover Plant. Management has asked you to identify: a. The annual budgeted costs that are relevant to the decision regarding closing the plant (show the dollar amounts). b. The annual budgeted costs that are not relevant to the decision regarding closing the plant and explain why they are not relevant (again show the dollar amounts). c. Any nonrecurring costs that would arise due to the closing of the plant, and explain how they would affect the decision (again show any dollar amounts). (Show the FORMULA & NUMBERS in excel that you used to get EACH answer)

2.) Looking at the data you have prepared in (1) above, should the plant be closed? Show computations and explain your answer. (Show the FORMULA & NUMBERS in excel that you used to get EACH answer)

D2 &":"& C2/D2 B Shop 2 |15 50 20 200 16 8 D GCD !=GCD(B2,C2) =GCD(B3,C3) =GCD(B4,C4) =GCD(B5,C5) =GCD(B6, C6) =GCD(B7,c7) E Ratio I=B2/D2&":"& C2/D2 =B3/D3 &":"& C3/D3 =B4/04 &":"& C4/04 =B5/05 &":"& C5/05 =B6/06 &":"& C6/06 =B7/07&":"& C7/07 D2 &":"& C2/D2 B Shop 2 |15 50 20 200 16 8 D GCD !=GCD(B2,C2) =GCD(B3,C3) =GCD(B4,C4) =GCD(B5,C5) =GCD(B6, C6) =GCD(B7,c7) E Ratio I=B2/D2&":"& C2/D2 =B3/D3 &":"& C3/D3 =B4/04 &":"& C4/04 =B5/05 &":"& C5/05 =B6/06 &":"& C6/06 =B7/07&":"& C7/07