need 1, 2, 3, 4. an excel explanation would help a lot.
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, GlobalMax 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 G H GlobalMax New 3D Printer Project Analysis Input Data Equipment Salvage value R&D Marketing study 3 $ 42,500,000 $ 6,200,000 800,000 $ 250,000 Instruction Input the values, wh case descri Canvas. The 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% $ 539 225 4,300,000 389 S $ $ $ 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 39 40 41 220 150 21% 20% 12% B20 GlobalMax New 3D Printer Project Analysis 3 Volution Celties and Results todos This sheet requ reference and talve each cell should be typed that it only cell formulas. The or values for ser appropriate. Do value blank full shaded in blue Yeah $106,282,000 (6,160.000) 0 Yea2 $103,554.000 16,160,000 $66,836,000 0 Year $51.744.000 0 Years $39.886.000 0 4,300,000 4,300.000 4,300,000 4,300,000 4.300.000 5 6 od printer price reduction 7 & Sales 9 New 10 Lotos 11 Lost rev 12 Netales 13 14 VO 15 New 15 Lont sale 17 18 19 20 Sales 21 Vo 22 Ried coats 20 D9 24 EBT 25 Tax 26 N 7 +Dep 28 OC 23 30 NWC 31 Bog 32 End 33 NWC OF 34 35 Nec 36 37 Savage 30 BV of quieren 39 Taken 40 Salvage CF 41 42 Nel CF Tune Net CF 1 46 47 4 Value 50 Valuation Method 51 Payback period 52 53 ERR 56 NPU 56 37
