i need help with 1,2,3,4. on excel would be great.

Fin 357 Business Finance Capital Project Analysis Case Due: Last class day (December 7) at 5:00 PM GlobalMax is a midcap printer manufacturer located in Austin, Texas. The company president is Shelby Smith. Shelby started the company in 1990. The company's original focus was the manufacturer of specialized color laser printers for high volume, high quality printing. Over the years, the company became a reliable manufacture of printers that tended to satisfy midsize publishers and the like. As a recent UT graduate, you were hired by the company's finance department to assist in the valuation of various projects under consideration for investment by the company. One of the major revenue-producing items manufactured by GlobalMax is a 3D printer. GlobalMax currently has one 3D printer model on the market, and sales have been excellent. The 3D printer is unique in that it serves a budget conscious market despite the typical cost associated with such a product. However, 3D printers have been on the market for some time now and the technology and overall price points are improving rapidly, and the current 3D printer has limited features in comparison with newer models in its price point. GlobalMax spent $800,000 to develop a prototype for a new 3D printer that has all the features of the existing 3D printer but adds new features such as a laser cutter. The company has spent an additional $250,000 for a marketing study to estimate the expected sales figures for the new printer. GlobalMax can manufacture the new printers for $225 each in variable costs. Fixed costs for the operation are estimated to run $6.38 million per year. The estimated sales volume is 158,000, 166,000, 124,000, 96,000, and 74,000 per year for the next five years, respectively. The unit price of the new printer will be $539. The necessary manufacturing equipment can be purchased for $42.5 million. Depreciation will occur on a seven-year MACRS schedule. The value of the equipment in five years is estimated to be $6.2 million. As Previously stated, Global Max currently manufactures a 3D printer. Production of the existing model is expected to be terminated in two years. If GlobalMax does not introduce the new 3D printer, sales will be 96,000 units and 64,000 units for the next two years, respectively. The price of the existing 3D printer is $389 per unit, with variable costs of $150 each and fixed costs of $4.3 million per year. If GlobalMax does introduce the new 3D printer, sales of the existing 3D printer will fall by 28,000 units per year, and the price of the existing units will have to be lowered to $220 each. Net working capital for the 3D printers will be 20% of sales and will occur with the timing of the cash flows for the year, for example, there is no initial outlay for NWC, but changes in NWC will first occur in Year 1 with the first year's sales. GlobalMax has a 21% corporate tax rate and a required return of 12% Shelby has asked You to prepare a report that answers the following questions: 1. What is the payback period of the project? 2. What is the profitability index of the project? 3. What is the IRR of the project? 4. What is the NPV of the project? 26 B C D E F A G H GlobalMax New 3D Printer Project Analysis Input Data $ 42,500,000 $ 6,200,000 800,000 $ 250,000 Institution Input the values, wh case descri Canvas. Th assist your referenced output valu worksheet blue require sunk cost sunk cost Year 1 158,000 14.29% 96,000 (28,000) Year 2 166,000 24.49% 64,000 (28,000) Year 3 124,000 17.49% Year 4 96,000 12.49% Year 5 74,000 8.93% $ $ $ S 539 225 4,300,000 389 Equipment Salvage value R&D Marketing study 3 -0 1 Sales(units) 22 Depreciation rate 93 Sales of old printer 14 Lost sales 15 16 Price of new printer 17 VC 18 FC 19 Price of old printer 20 Revised price 21 of old printer 22 VC of old printer 23 Tax rate 24 NWC percentage 25 Required return 26 27 28 29 30 31 32 33 34 35 36 37 38 $ $ 220 150 21% 20% 12% 40 44 B20 GlobalMax New 3D Printer Project Analysis 3 Volution Cekultis and Results Indodha This sheet requ reference Ardu talve each cell should be typed that is only cell formulas. The values for ser appropriate Do value blank full shaded in blue Yea $106,282,000 (6,160.000) 0 Year2 $103,554,000 16,160,000 Yea $66,836,000 0 Year $51.744.000 0 Years $39.806 000 0 5 6 Od printer price reduction 7 & Sales 9 New 19 Loated 11 Lostrov 12 Netales 1 14 VO 15 New 15 Lont sale 17 18 19 20 Sales 21 VO 22 Ried costs 23 EP 24 EBT 25 Tax 26 N 7 +Dep 28 OCH 4,300,000 4,300,000 4,300,000 4,300,000 4,300,000 30 NWC 31 Bog 32 End 33 NWC OF 34 35 Net 36 37 Savage 30 BV of equipment 39 Taxes 40 Salvage CE 41 42 Net OF 4) 14 Tune Nak CF 46 47 41 Value SO Valuation Method 51 Payback period 52 P 53 ERR 54 NPU 16 Fin 357 Business Finance Capital Project Analysis Case Due: Last class day (December 7) at 5:00 PM GlobalMax is a midcap printer manufacturer located in Austin, Texas. The company president is Shelby Smith. Shelby started the company in 1990. The company's original focus was the manufacturer of specialized color laser printers for high volume, high quality printing. Over the years, the company became a reliable manufacture of printers that tended to satisfy midsize publishers and the like. As a recent UT graduate, you were hired by the company's finance department to assist in the valuation of various projects under consideration for investment by the company. One of the major revenue-producing items manufactured by GlobalMax is a 3D printer. GlobalMax currently has one 3D printer model on the market, and sales have been excellent. The 3D printer is unique in that it serves a budget conscious market despite the typical cost associated with such a product. However, 3D printers have been on the market for some time now and the technology and overall price points are improving rapidly, and the current 3D printer has limited features in comparison with newer models in its price point. GlobalMax spent $800,000 to develop a prototype for a new 3D printer that has all the features of the existing 3D printer but adds new features such as a laser cutter. The company has spent an additional $250,000 for a marketing study to estimate the expected sales figures for the new printer. GlobalMax can manufacture the new printers for $225 each in variable costs. Fixed costs for the operation are estimated to run $6.38 million per year. The estimated sales volume is 158,000, 166,000, 124,000, 96,000, and 74,000 per year for the next five years, respectively. The unit price of the new printer will be $539. The necessary manufacturing equipment can be purchased for $42.5 million. Depreciation will occur on a seven-year MACRS schedule. The value of the equipment in five years is estimated to be $6.2 million. As Previously stated, Global Max currently manufactures a 3D printer. Production of the existing model is expected to be terminated in two years. If GlobalMax does not introduce the new 3D printer, sales will be 96,000 units and 64,000 units for the next two years, respectively. The price of the existing 3D printer is $389 per unit, with variable costs of $150 each and fixed costs of $4.3 million per year. If GlobalMax does introduce the new 3D printer, sales of the existing 3D printer will fall by 28,000 units per year, and the price of the existing units will have to be lowered to $220 each. Net working capital for the 3D printers will be 20% of sales and will occur with the timing of the cash flows for the year, for example, there is no initial outlay for NWC, but changes in NWC will first occur in Year 1 with the first year's sales. GlobalMax has a 21% corporate tax rate and a required return of 12% Shelby has asked You to prepare a report that answers the following questions: 1. What is the payback period of the project? 2. What is the profitability index of the project? 3. What is the IRR of the project? 4. What is the NPV of the project? 26 B C D E F A G H GlobalMax New 3D Printer Project Analysis Input Data $ 42,500,000 $ 6,200,000 800,000 $ 250,000 Institution Input the values, wh case descri Canvas. Th assist your referenced output valu worksheet blue require sunk cost sunk cost Year 1 158,000 14.29% 96,000 (28,000) Year 2 166,000 24.49% 64,000 (28,000) Year 3 124,000 17.49% Year 4 96,000 12.49% Year 5 74,000 8.93% $ $ $ S 539 225 4,300,000 389 Equipment Salvage value R&D Marketing study 3 -0 1 Sales(units) 22 Depreciation rate 93 Sales of old printer 14 Lost sales 15 16 Price of new printer 17 VC 18 FC 19 Price of old printer 20 Revised price 21 of old printer 22 VC of old printer 23 Tax rate 24 NWC percentage 25 Required return 26 27 28 29 30 31 32 33 34 35 36 37 38 $ $ 220 150 21% 20% 12% 40 44 B20 GlobalMax New 3D Printer Project Analysis 3 Volution Cekultis and Results Indodha This sheet requ reference Ardu talve each cell should be typed that is only cell formulas. The values for ser appropriate Do value blank full shaded in blue Yea $106,282,000 (6,160.000) 0 Year2 $103,554,000 16,160,000 Yea $66,836,000 0 Year $51.744.000 0 Years $39.806 000 0 5 6 Od printer price reduction 7 & Sales 9 New 19 Loated 11 Lostrov 12 Netales 1 14 VO 15 New 15 Lont sale 17 18 19 20 Sales 21 VO 22 Ried costs 23 EP 24 EBT 25 Tax 26 N 7 +Dep 28 OCH 4,300,000 4,300,000 4,300,000 4,300,000 4,300,000 30 NWC 31 Bog 32 End 33 NWC OF 34 35 Net 36 37 Savage 30 BV of equipment 39 Taxes 40 Salvage CE 41 42 Net OF 4) 14 Tune Nak CF 46 47 41 Value SO Valuation Method 51 Payback period 52 P 53 ERR 54 NPU 16